Phinx: timestamp default value in phinxlog table not compatible with MySQL 5.7

[pwbzh]

In the phinxlog table, the default value for the end_time column is "0000-00-00 00:00:00".

In MySQL 5.7, for the default configuration (strict SQL mode), '0000-00-00' is not permitted and inserts produce an error : http://dev.mysql.com/doc/refman/5.7/en/sql-mode.html#sqlmode_no_zero_date

With a migrate command, there is this error:
[PDOException]
SQLSTATE[42000]: Syntax error or access violation: 1067 Invalid default value for 'end_time'

Environment:
ubuntu 16.04 LTS
PHP 7.0.8
MySQL 5.7.16

[PangeranWeb]

I got a same problem too.

[pwbzh]

Here is my analysis :

I use Phinx on a project since a while and the phinxlog table was created a long time ago with an older version of Phinx. I have migrated my MySQL server to version 5.7 from 5.5. That is what caused the error.

I found a fix in the Phinx current code but this only applies to a new phinxlog table.

I managed by applying this SQL query :

ALTER TABLE `phinxlog` CHANGE `end_time` `end_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP;

[artemzhilin]

Thanks. your solution is very helpful

[dereuromark]

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

Otherwise please make an improvement in the docs.

[PangeranWeb]

I got it. Since MySQL 5.7 value "0000-00-00 00:00:00" not alowed. Use null instead.

[dereuromark]

Is there anything that can be fixed then?

[MasterOdin]

The fix would be:

ALTER TABLE phinxlog MODIFY start_time timestamp NULL DEFAULT NULL;
ALTER TABLE phinxlog MODIFY end_time timestamp NULL DEFAULT NULL;
UPDATE phinxlog SET start_time = NULL WHERE start_time = '0000-00-00 00:00:00';
UPDATE phinxlog SET end_time = NULL WHERE end_time = '0000-00-00 00:00:00';

to get phinxlog tables created prior to ~0.8.

@dereuromark would you want a MR to append this information to the UPGRADE_0.8.md file as that seems the most appropriate place for it?