Transactions and addColumn/addIndex()

Yii 2.0 General Discussions
1

I’ve noticed that doing a transaction/commit wrapper around addColumn/addIndex doesn’t do anything, or at least doesn’t do what I expect.

Let’s say I have a migrate:

up() {
...
$transaction = $this->getDb()->beginTransaction();
$this->addColumn('x', ..);
$this->addIndex('x', ..);
$this->insert(a, b, c);
$transaction->commit();
}

And then this fails: $this->insert(a, b, c), leaving the migration incomplete.

If I then migrate/up again to run this migration, I will get:

"duplicate column x found"

How do I protect my production database in a situation like this?

2

Is this MySQL? If so - MySQL :: MySQL 8.0 Reference Manual :: 13.3.3 Statements That Cause an Implicit Commit
In that case you must explicitly call for some check-ups before moving on.

1 Like
3

Oh gosh, I didn’t list the db. Yes, this is MySQL. Okay, that seems spot on.

So basically this “The intent is to handle each such statement in its own special transaction” means I need to wrap up the createColumn() and createIndex() in their own transactions?

$transaction = $this->getDb()->beginTransaction();
$this->addColumn('x', ..);
$transaction->commit();

$transaction = $this->getDb()->beginTransaction();
$this->addIndex('x', ..);
$transaction->commit();

$transaction = $this->getDb()->beginTransaction();
$this->insert(a, b, c);
$this->insert(a, b, c);
$this->insert(a, b, c);
$this->insert(a, b, c);
$this->insert(a, b, c);
$this->insert(a, b, c);
$transaction->commit();

That’s how I’m reading this.

4

Well, no. It means that if there is an implicit commit coming with the statement you are using your transaction is useless. You can wrap statements without implicit commit in the transaction but the rest is a matter of catching exceptions and adding your own statements that for example check if the column has been added for sure or not (or just logging the error).