Changes not reverted after an exception occurs

[dakujem]

When an SQL error or any other exception occurs during the migration, the database remains in an intermediary state and

• can not be migrated as the migration is partially applied (e.g. the tables already exist)
• can not be reverted, because the migration does not appear in the phinxlog log

A simple use case:

    public function change(): void
    {
        $users = $this->table('users')->create();
        throw new Exception();
    }

The users table will be left in the database after the exception is thrown and the migration can neither be reverted nor repeated after the issue is fixed, because of PDOException: SQLSTATE[42S01]: Base table or view already exists: 1050 Table 'users' already exists.

Does this not defy the purpose of a migration toolkit?

The interesting part is that I can see TRANSACTION START if I do dry-run.

START TRANSACTION;
CREATE TABLE `users` (`id` INT(11) NOT NULL AUTO_INCREMENT, PRIMARY KEY (`id`)) ENGINE = InnoDB CHARACTER SET utf8 COLLATE utf8_general_ci;
Exception in ...

---

version: 0.12.5 + MariaDB

[garas]

Phinx executes within transaction expecting database will rollback on failure.

But MySQL/MariaDB does not support transactional DDL. In MySQL, CREATE/ALTER/DROP TABLE and similar DDL commands cause implicit commit of the current transaction and are not rolled back on failure.

Oracle, PostgreSQL, MS SQL, SQLite supports transactional DDL. So such DDL commands would be rolled back.

Reversible migrations probably could be implemented to rollback executed part, but Phinx doesn't currently able to do this.

You can

• split migrations to multiple smaller parts to have less to revert manually
• separate schema and data migrations (data migration only migrations would be rolled back),
• use $this->hasTable('users') or $this->table('users')->hasColumn('username') to create table/columns conditionally,
• use database which supports transactional DDL.

[MasterOdin]

Is the MySQL limitation mentioned anywhere within the docs? Might be good to add? Maybe add a "Database Limitations" section under Writing Migrations? Could be a good place to document as well things SQLite does and does not support.

[garas]

Would you like to make PR for docs?

[MasterOdin]

I can do that.

[dakujem]

Okay, I was not aware it was a limitation of MySQL/MariaDB. Interesting point. I'm usually migration database structure, it kinda defies the purpose. 🤷‍♂️ Anyway thanks for the info.

[othercorey]

MySQL docs: https://dev.mysql.com/doc/refman/8.0/en/implicit-commit.html