/ mysql

Mysql invalid default value for timestamp

So my local mysql server is complaining about invalid timestamp valid of 0000-00-00 00:00:00.

And I happen to have 2 of these columns and whenever I tried to fix one of them, MYSQL complains the other is having the invalid default value and doesn't allow me to change anything!

I even asked the question here: http://dba.stackexchange.com/questions/153237/cannot-alter-mysql-table-stuck-in-a-invalid-default-value-loop.

So the problem turns out is because of the sql_mode: http://stackoverflow.com/a/9192372/440646

NO_ZERO_DATE

And to solve that, if you're installed mysql with homebrew like me, you'll find that you couldn't find the my.cnf file anywhere!

So with reference to: http://stackoverflow.com/questions/7973927/for-homebrew-mysql-installs-wheres-my-cnf, where's what you need to do.

  1. Copy my-default.cnf to /usr/local/etc/my.cnf.
  2. Restart the server mysq.server restart.

To find the my-default.cnf, it'll be in the mysql installation folder. Mine is in /usr/local/Cellar/mysql/5.7.10/support-files.

Actually that's all I did because if you look into /usr/local/etc/my.cnf, NO_ZERO_DATE is not part of the sql_mode.

Here's mine for reference: sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

Mysql invalid default value for timestamp
Share this