Why am I seeing `ERROR 1290 (HY000)` when altering my table?

Question

When I run the following statements on my test_date table, I get error ERROR 1290 (HY000):

desc test_date;
+------------+-----------+------+-----+-------------------+-------+
| Field      | Type      | Null | Key | Default           | Extra |
+------------+-----------+------+-----+-------------------+-------+
| gmt_create | timestamp | NO   |     | CURRENT_TIMESTAMP |       |
+------------+-----------+------+-----+-------------------+-------+
1 row in set (0.00 sec)

alter table test_date modify gmt_create datetime not null default current_timestamp;

ERROR 1290 (HY000): The MySQL server is running with the 
--log_bin_use_old_datetime_format option so it cannot execute this statement

I don't know what is the problem.Looking for your help.

Answer 1

From the 5.6.6 Changelog:

In MySQL, the TIMESTAMP ( http://dev.mysql.com/doc/refman/5.6/en/datetime.html ) data type differs in nonstandard ways from other data types:

TIMESTAMP ( http://dev.mysql.com/doc/refman/5.6/en/datetime.html ) columns not explicitly declared with the NULL attribute are assigned the NOT NULL attribute. (Columns of other data types, if not explicitly declared as NOT NULL, permit NULL values.) Setting such a column to NULL sets it to the current timestamp.

The first TIMESTAMP ( http://dev.mysql.com/doc/refman/5.6/en/datetime.html ) column in a table, if not declared with the NULL attribute or an explicit DEFAULT or ON UPDATE clause, is automatically assigned the DEFAULT CURRENT_TIMESTAMP and ON UPDATE CURRENT_TIMESTAMP attributes.

TIMESTAMP ( http://dev.mysql.com/doc/refman/5.6/en/datetime.html ) columns following the first one, if not declared with the NULL attribute or an explicit DEFAULT clause, are automatically assigned DEFAULT '0000-00-00 00:00:00' (the “zero” timestamp). For inserted rows that specify no explicit value for such a column, the column is assigned '0000-00-00 00:00:00' and no warning occurs.

Those nonstandard behaviors remain the default for TIMESTAMP ( http://dev.mysql.com/doc/refman/5.6/en/datetime.html ) but now are deprecated and this warning appears at startup:

[Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).

As indicated by the warning, to turn off the nonstandard behaviors, enable the new explicit_defaults_for_timestamp ( http://dev.mysql.com/doc/refman/5.6/en/server-system-variables.html#sysvar_explicit_defaults_for_timestamp ) system variable at server startup. With this variable enabled, the server handles TIMESTAMP ( http://dev.mysql.com/doc/refman/5.6/en/datetime.html ) as follows instead:

TIMESTAMP ( http://dev.mysql.com/doc/refman/5.6/en/datetime.html ) columns not explicitly declared as NOT NULL permit NULL values. Setting such a column to NULL sets it to NULL, not the current timestamp.

No TIMESTAMP ( http://dev.mysql.com/doc/refman/5.6/en/datetime.html ) column is assigned the DEFAULT CURRENT_TIMESTAMP or ON UPDATE CURRENT_TIMESTAMP attributes automatically. Those attributes must be explicitly specified.

TIMESTAMP ( http://dev.mysql.com/doc/refman/5.6/en/datetime.html ) columns declared as NOT NULL and without an explicit DEFAULT clause are treated as having no default value. For inserted rows that specify no explicit value for such a column, the result depends on the SQL mode. If strict SQL mode is enabled, an error occurs. If strict SQL mode is not enabled, the column is assigned the implicit default of '0000-00-00 00:00:00' and a warning occurs. This is similar to how MySQL treats other temporal types such as DATETIME ( http://dev.mysql.com/doc/refman/5.6/en/datetime.html ).

To upgrade servers used for replication, upgrade the slaves first, then the master. Replication between the master and its slaves should work provided that all use the same value of explicit_defaults_for_timestamp ( http://dev.mysql.com/doc/refman/5.6/en/server-system-variables.html#sysvar_explicit_defaults_for_timestamp ):

Bring down the slaves, upgrade them, configure them with the desired value of explicit_defaults_for_timestamp ( http://dev.mysql.com/doc/refman/5.6/en/server-system-variables.html#sysvar_explicit_defaults_for_timestamp ), and bring them back up.

The slaves will recognize from the format of the binary logs received from the master that the master is older (predates the introduction of explicit_defaults_for_timestamp ( http://dev.mysql.com/doc/refman/5.6/en/server-system-variables.html#sysvar_explicit_defaults_for_timestamp )) and that operations on TIMESTAMP ( http://dev.mysql.com/doc/refman/5.6/en/datetime.html ) columns coming from the master use the old TIMESTAMP ( http://dev.mysql.com/doc/refman/5.6/en/datetime.html ) behavior.

Bring down the master, upgrade it, and configure it with the same explicit_defaults_for_timestamp ( http://dev.mysql.com/doc/refman/5.6/en/server-system-variables.html#sysvar_explicit_defaults_for_timestamp ) value used on the slaves, and bring it back up.

(Bug #63034, Bug #13344629, Bug #55131, Bug #11762529)

Answer 2

Either you are using mysql version before 5.6 as earlier than 5.6 default curent_timestamp feature was related with timestamp data type not datetime.

So use this feature with timestamp instead of datetime which is performance wise better and also used 4 bytes vs 8 bytes for datetime, if there is no other limitation in your case.

If you want to use datetime due to any specific reason then upgrade mysql version as 5.6.5 or higher.