Phinx: MYSQL default value: CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

[ricksanchez]

It is not supported yet:

$table->addColumn('updated', 'datetime', array('default' => 'CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP'))

Error:

  [PDOException]                                                                               
  SQLSTATE[42000]: Syntax error or access violation: 1067 Invalid default value for 'updated'

[Ma-ve]

That's a feature I'd like too.

In the meantime, you can do this:

$table = $this->table('my_table');
$table
    ->addColumn('updated_at', 'datetime')
    ->save();
 $this->execute('ALTER TABLE `my_table` MODIFY COLUMN `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP');

[rquadling]

According to http://dev.mysql.com/doc/refman/5.5/en/timestamp-initialization.html

With neither DEFAULT CURRENT_TIMESTAMP nor ON UPDATE CURRENT_TIMESTAMP, it is the same as specifying both DEFAULT CURRENT_TIMESTAMP and ON UPDATE CURRENT_TIMESTAMP.

So, technically, Phinx DOES support this. Just don't supply the default value for a timestamp type. But I've not tested that yet.

In addition to that though, Phinx also supports an 'update' option for timestamp : http://docs.phinx.org/en/latest/migrations.html#working-with-columns

So

$table->addColumn('updated', 'timestamp', array('default' => 'CURRENT_TIMESTAMP', 'update' => 'CURRENT_TIMESTAMP'));

may be more appropriate. Notice the column type is a timestamp and not a datetime.

[mamchenkov]

We are seeing this issue now as well. Is there any way we can help with resolving the issue?

[lorenzo]

@mamchenkov send a pull request if you know how to fix it :)

[mamchenkov]

@lorenzo after digging a bit more into it, it seems the newer versions have the fix already applied:

https://github.com/cakephp/phinx/blob/v0.8.0/src/Phinx/Db/Adapter/PdoAdapter.php#L531-L532

If we could only get v0.8.0 into cakephp/migrations now. :)

[lorenzo]

@mamchenkov want to help making the changes there? I would like to focus on the code here directly for the next few months

[mamchenkov]

@lorenzo yes, sure. Let me have a look at it, and I'll send the PR :)

[lorenzo]

Thanks

[rquadling]

OOI, if you have createdAt and updatedAt, should, at creation, updatedAt = createdAt?

For me, updatedAt = null means this is a row that hasn't been edited.

createdAt TIMESTAMP(6) DEFAULT CURRENT_TIMESTAMP(6),
updatedAt TIMESTAMP(6) NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP(6)

is what I would prefer.

[mamchenkov]

@rquadling I think, the field can be populated by either the database engine or the application. Making it portable across different database engines is not as easy as making it updated by the application.

[rquadling]

For me, createdAt and updatedAt are not for the consumer of the table but for the DB to help provide built in row tracking. If the db provides the facility, it is much nicer to use it. Trying to do everything in code because of the lowest common denominator is just tedious.

[rquadling]

If I use DB engine x, then I want to take as much advantage as possible of that engine.

So, MSSQL has pivots. Mysql doesn't. Won't stop me from using the native approach when I can and simulating them where I have to.

DBAL can only do so much and it should provide the best it can, not the lowest it can.

[dereuromark]

Are you able to provide a patch with your suggested changes as PR?

[JRDuncan]

Hi @dereuromark, @lorenzo

Shouldn't this ticket be closed.

$table->addColumn('updated', 'timestamp', array('default' => 'CURRENT_TIMESTAMP', 'update' => 'CURRENT_TIMESTAMP'));
works for me

[kevingentile]

@JRDuncan 's solution worked for me. For the convenience methods (i.e addTimestamps()) should CURRENT_TIMESTAMP on update be provided? v0.11.4

$table->addColumn('created_at', 'timestamp', array('default' => 'CURRENT_TIMESTAMP'));
$table->addColumn('updated_at', 'timestamp', array('update' => 'CURRENT_TIMESTAMP'));