Phinx: Unable to create log table in mysql

Created on 5 May 2014 · 10Comments · Source: cakephp/phinx

Using phinx 0.3.4.

With a MySQL 5.7.4 server running in STRICT_TRANS_TABLE mode, phinx is unable to create the log table (phinxlog) and exits with an error about an invalid default value for end_time.

The problem is that phinx is trying to make the default value '0000-00-00 00:00:00' on a column with the TIMESTAMP data type.

STRICT_TRANS_TABLE enabled the NO_ZERO_DATE server mode so that is an invalid date.

The correct minimum value for a TIMESTAMP column is '1970-01-01 00:00:01'. When I manually created the table with that default value for end_time, phinx was able to proceed with no problems.

bug

Source

0x15e

Most helpful comment

Using MySQL, I fixed this issue by monkey patching PdoAdapter:

$table = new Table($this->getSchemaTableName(), $options, $this);
$table->addColumn('version', 'biginteger', array('limit' => 14))
    // Fix "SQLSTATE[42000]: Syntax error or access violation: 1067 Invalid default value for 'start_time'"
    ->addColumn('start_time', 'timestamp', ['default' => 'CURRENT_TIMESTAMP'])
    ->addColumn('end_time', 'timestamp', ['default' => 'CURRENT_TIMESTAMP'])
    ->save();

willdurand on 11 Jan 2015

👍3

All 10 comments

:+1:

willdurand on 11 Jan 2015

Using MySQL, I fixed this issue by monkey patching PdoAdapter:

$table = new Table($this->getSchemaTableName(), $options, $this);
$table->addColumn('version', 'biginteger', array('limit' => 14))
    // Fix "SQLSTATE[42000]: Syntax error or access violation: 1067 Invalid default value for 'start_time'"
    ->addColumn('start_time', 'timestamp', ['default' => 'CURRENT_TIMESTAMP'])
    ->addColumn('end_time', 'timestamp', ['default' => 'CURRENT_TIMESTAMP'])
    ->save();

willdurand on 11 Jan 2015

👍3

Have you located where the default value of 0000-00-00 00:00:00 comes from? I can't find anything in the code that suggests it should act this way... no default is set in createSchemaTable.