Yii2: Migration: defaultValue CURRENT_TIMESTAMP

[everbslab]

yii 2.0.6

If defaultValue is going to be the mysql operator, migration tool builds a query string like this

        $this->createTable('page', [
            'id'=>$this->primaryKey(),
            'title'=>$this->string(100)->notNull(),
            'dt_create'=>$this->timestamp()->defaultValue('CURRENT_TIMESTAMP'),
            'dt_update'=>$this->timestamp()
        ]);

*** applying m150807_025347_page
    > create table page ...Exception: SQLSTATE[42000]: Syntax error or access violation: 1067 Invalid default value for 'dt_create'
The SQL being executed was: CREATE TABLE `page` (
        `id` int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
        `title` varchar(100) NOT NULL,
        `dt_create` timestamp DEFAULT 'CURRENT_TIMESTAMP',
        `dt_update` timestamp
) (/mnt/hgfs/webwork/public_html/###.dev/vendor/yiisoft/yii2/db/Schema.php:628)
#0 /mnt/hgfs/webwork/public_html/###.dev/vendor/yiisoft/yii2/db/Command.php(791): yii\db\Schema->convertException(Object(PDOException), 'CREATE TABLE `p...')

I suppose \yii\db\ColumnSchemaBuilder::buildDefaultString should check the value i.e. it goes like MySQL operator or it is an expression to do not encode its value.

[everbslab]

Simple patch imho:

/db/ColumnSchemaBuilder.php:177

            case 'array':
                $string .= $this->default['expression'];

'dt_create'=>$this->timestamp()->defaultValue(['expression'=>'CURRENT_TIMESTAMP']),

[klimov-paul]

Duplicates #9337

[robinclark007]

  $this->createTable('products' , [

        'id' => $this->primaryKey(),
        'name' => $this->string()->notNull(),
        'price' => $this->money()->notNull(),
        'description' => $this->text()->notNull(),
        'update_at' => $this->timestamp()->notNull(),
        'create_at' => $this->timestamp()->notNull(),
  ]);

I just put the update_at before the create_at .then it works
update_at got the

CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

automaticly..

[zrashwani]

Same problem here,
First timestamp column in the migration get DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP by default which is wrong,
Here are my migration lines:

        $this->createTable('fin_news',[
             'id'         => $this->primaryKey(),
             'market_id'  => $this->integer()->notNull(),
             'company_id' => $this->integer()->defaultValue(null),
             'attachment' => $this->string(500)->defaultValue(null),
             'daily_date' => $this->dateTime()->notNull(),
             'is_deleted' => $this->boolean()->defaultValue(false),
             'updated_at' => $this->timestamp()->defaultValue(null),
             'created_at' => $this->timestamp(),             
            ]);

In mysql, updated_at column get wrong default value, while created_at have zeros as default value as following:

  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `created_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',

[rokorolov]

+1

'updated_at' => $this->timestamp()->defaultValue(null)

Not working as expected.

[cebe]

this is a limitation of MySQL, not thing that Yii can solve:

https://dev.mysql.com/doc/refman/5.1/en/timestamp-initialization.html

One TIMESTAMP column in a table can have the current timestamp as the default value for initializing the column, as the auto-update value, or both. It is not possible to have the current timestamp be the default value for one column and the auto-update value for another column.

[zrashwani]

thank you @cebe for feedback
I have performed a workaround for this mysql limitation as following:

$this->createTable('test_timestamp',[
   'id'         => $this->primaryKey(),            
   'value_str'  => $this->string(255),
   'is_deleted' => $this->boolean()->defaultValue(false),
   'updated_at' => 'timestamp on update current_timestamp',
   'created_at' => $this->timestamp()->defaultValue(0),             
]);

and upon inserting into database, I insert null into created_at column value

that way worked correctly.

[kumis]

$this->createTable('fin_news',[
'id' => $this->primaryKey(),
'market_id' => $this->integer()->notNull(),
'company_id' => $this->integer()->defaultValue(null),
'attachment' => $this->string(500)->defaultValue(null),
'daily_date' => $this->dateTime()->notNull(),
'is_deleted' => $this->boolean()->defaultValue(false),
'updated_at' => $this->timestamp()->defaultValue(null),
'created_at' => $this->dateTime() . ' DEFAULT NOW(),
]);
i try this and i got default value on create_at is CURENT_TIMESTAMP, ooh and i use mariadb, base on this https://mariadb.com/kb/en/mariadb/datetime/

[abhimanusharma]

This will work even better.

'updated_at' => $this->timestamp(),
'created_at' => $this->timestamp()->defaultExpression('CURRENT_TIMESTAMP'),

This will set automatically 0000-00-00 00:00:00, for updated_at, and on update current timestamp. For created_at it will only work once when data is inserted.

[4t4nner]

yii2

$this->alterColumn(
            '{{%table}}',
            'col_dt',
            $this->dateTime()
                ->defaultExpression('NOW()')
                ->append('ON UPDATE NOW()')
        );

[vermajnv]

This works for me in yii2

        'id' => $this->primaryKey(),
        'title' => $this->string()->notNull(),
        'description' => $this->text(),
        'created_at' => $this->timestamp()->defaultExpression('NOW()'),
        'updated_at' => $this->timestamp()->defaultExpression('CURRENT_TIMESTAMP')->append('ON UPDATE NOW()'),

[bizley]

Let's keep discussion like this Forum.