Yii2 migration transaction methods, safeup() and safedown() not working

Question

I am using the safeUp and safeDown methods for Database transactions in yii but it seems not to be working. Here is my code below:

    public function safeUp()
{
    $this->createTable(self::TABLE_EMPLOYMENT_HISTORY, [
        'id' => $this->integer() . ' UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY',
        'user_id' => $this->integer()->unsigned()->notNull(),
        'start_date' => $this->dateTime()->notNull(),
        'end_date' => $this->dateTime(),
        'role' => $this->string(150)->notNull(),
        'position' => $this->string(50)->notNull(),
        'achievements' => $this->text(),
        'created_at' => $this->dateTime()->notNull(),
        'updated_at' => $this->dateTime()->notNull(),
        'status' => $this->string(50)->notNull()
    ]);
    $this->addForeignKey('fk_' . self::TABLE_EMPLOYMENT_HISTORY . '_' . self::TABLE_USERS . '_id', self::TABLE_EMPLOYMENT_HISTORY, 'user_id', self::TABLE_USERS, 'id');
    $this->addForeignKey('fk_' . self::TABLE_EMPLOYMENT_HISTORY . '_' . self::TABLE_STATUSES . '_key', self::TABLE_MISTAKE, 'status', self::TABLE_STATUSES, 'key');
}

public function safeDown()
{
    $this->dropForeignKey('fk_' . self::TABLE_EMPLOYMENT_HISTORY . '_' . self::TABLE_STATUSES . '_key', self::TABLE_EMPLOYMENT_HISTORY);
    $this->dropForeignKey('fk_' . self::TABLE_EMPLOYMENT_HISTORY . '_' . self::TABLE_USERS . '_id', self::TABLE_EMPLOYMENT_HISTORY);
    $this->dropTable(self::TABLE_EMPLOYMENT_HISTORY);

}

When I ran that, It said of course that the table TABLE_MISTAKE does not exist when trying to create the foreign key. I was expecting a rollback but that did not happen, instead, it went ahead to create the table 'EMPLOYMENT_HISTORY'.

Why is it creating the table despite using the safeup method?

Your help would be appreciated.

Answer 1

From MySQL docs:

Some statements cannot be rolled back. In general, these include data definition language (DDL) statements, such as those that create or drop databases, those that create, drop, or alter tables or stored routines.

You should design your transactions not to include such statements. If you issue a statement early in a transaction that cannot be rolled back, and then another statement later fails, the full effect of the transaction cannot be rolled back in such cases by issuing a ROLLBACK statement.

So it's normal behavior. PostgreSQL for example doesn't have this problem.