[Warning] TIMESTAMP With Implicit DEFAULT Value Is Deprecated – How To Fix It

Did you just upgrade your MySQL server and got a warning about an implicit TIMESTAMP default value when starting mysqld.exe?

>mysqld.exe
 2015-12-04 13:17:27 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).

If you would like to find the databases, tables and column names that cause the warning message, just run the following query on the information_schema database:

mysql> USE information_schema;
Database changed
mysql> SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME FROM Columns WHERE DATA_TYPE='datetime' AND IS_NULLABLE='NO' and COLUMN_DEFAULT IS NULL;
+--------------+-------------+-------------+
| TABLE_SCHEMA | TABLE_NAME | COLUMN_NAME |
+--------------+-------------+-------------+
| biggamedb | updatequeue | added |
| ff_testdb | hosts | added |
| ff_testdb | hosts | lastspotted |
| ff_testdb | product | added |
+--------------+-------------+-------------+
4 rows in set (0.11 sec)

Hope that helped you fix the problem. Or did you go with the –explicit_defaults_for_timestamp flag?

One thought on “[Warning] TIMESTAMP With Implicit DEFAULT Value Is Deprecated – How To Fix It

  1. Thanks very much for this hint 🙂 Solving depricated issues at the root is imho much better than just surpressing the messages (e.g. with explicit_defaults_for_timestamp).

    I found 300+ columns in several databases in my server this way. Can you say if a “simple” update query in this table (e.g. to CURRENT_TIMESTAMP) would change the columns or does it explicity need to be an ALTER query in each database?

Comments are closed.