CODEWITHSUNDEEP
mysqlyii2
Paul
Paul

Reputation: 336

MySQL throws error on Yii2 migration, doesn't in PhpMyAdmin

In one of my migrations, I add a column 'dateCreated' to all tables. This works fine for most tables, but throws a strange error on some of them:

'SQLSTATE[22007]: Invalid datetime format: 1292 Incorrect date value: '0000-00-00' for column 'start_date' at row 1 The SQL being executed was: ALTER TABLEpaymentsADDdateCreatedDATETIME NULL DEFAULT NULL'

A column 'start_date' exists already, but I can't see why this has anything to do with the newly created column. This breaks my migration. The same SQL works fine when applied directly on the table in PhpMyAdmin. Does Yii add extra checks to the query, or am I missing something else?

This is the complete migration:

public function up()
    $tableNames = Yii::$app->db->schema->tableNames;

    foreach ($tableNames as $tableName) {
        Yii::$app->db->createCommand('ALTER TABLE '.$tableName.' ENGINE = InnoDB')->query();
    }
}

Upvotes: 1

Views: 541

Answers (1)

arogachev
arogachev

Reputation: 33548

There are some errors in your migration.

1) Migration should interact with database structure and data related with time it was added, not current time.

When you write this:

Yii::$app->db->schema->tableNames;

you are referring to all existing tables at the moment of running migration.

So imagine if you add some new tables later and you don't need that column for some tables. If your new teammate will run migrations or it will be run on new server, the dateCreated will be added to all tables. If it was other operation, for example delete, this can lead to serious problems, for example unexpected data loss.

So remember - always operate with database structure and data related with moment of creating migration.

I recommend to create private static array for example and use it in both up() and down() methods.

private static $_tableNames = [
    'tableName1',
    'tableName2',
];

2) When there are many queries, it will be more safely to wrap it in transaction. Use safeUp() and safeDown() methods instead. In this case if migration will fail on certain query, rollback will be applied and you don't have to do any additional manual manipulations with database.

3) There is no need to write raw SQL for such common tasks in migration. Use yii\db\Migration methods.

4) yii\db\Command query() is used for selection, for deletion use execute(). But no need to use it, because there is shortcut in migrations for executing raw SQL: $this->execute('YOUR SQL GOES HERE').

5) I'd recommend to use undescore (_) as separator in column names, but it's not crucial.

So finally you migration will look something like this:

use yii\db\Expression;
use yii\db\Migration;

class m111111_111111_add_date_created_column_to_all_existing_tables extends Migration
{
    /**
     * @var array List of table names
     */
    private static $_tableNames = [
        'tableName1',
        'tableName2',
    ],

    public function safeUp()
    {   
        foreach (self::$_tableNames as $table) {
            $this->addColumn($table, 'date_created', $this->dateTime()->defaultValue(new yii\db\Expression('NULL')));
        }
    }

    public function safeDown()
    {   
        foreach (self::$_tableNames as $table) {
            $this->dropColumn($table, 'date_created');
        }
    }
}

This is more like a code review, but back to the main problem: in the provided code there is no addition of dateCreated column at all (in fact no changes at all) and the error related with start_date comes from different part.

The logic of such queries:

ALTER TABLE payments ADD dateCreated DATETIME NULL DEFAULT NULL

is correct, as a result dateCreated column will be added to all listed table names.

Existing rows will be filled with NULL data (for dateCreated column), so there is no chance for values like 0000-00-00 to appear (also it's datetime, not date). start_date is a different column, check the logic related with it in other place of code.

Upvotes: 1

Related Questions