This may be a “duh” post for some, but I had to post this because I didn’t find the answer in typical places like stackoverflow when I had the issue. I recently worked on a project to expand database capacity by deploying new MySQL installations with memory, config, and disk space tweaks by backup/restore, replication topology change and, and failover. I did not notice that the old servers had “explicit_defaults_for_timestamp=OFF”. After restoring a binary backup and starting the replication thread on the new systems I got this error in the replication thread (column name in error corresponds to examples further down).
ERROR 1048 (23000): Column 'ts' cannot be null
Below, I will provide a synopsis to show statements that caused the error and why a simple global variable change fixed the issue. First, a sample table definition.
CREATE TABLE `time_test` ( `id` int(11) NOT NULL AUTO_INCREMENT, `comment` varchar(32) NOT NULL, `ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1
Notice, the timestamp has a default of current_timestamp. Next, a statement that works fine.
mysql> insert into time_test (comment) values ('this is statement 1');
“id” and “ts” columns are omitted to allow MySQL to generate both the ID and the timestamp value.
Next – a statement that does not work, similar to the one I encountered in my replication thread.
mysql> insert into time_test (comment,ts) values ('this is statement 2',NULL); ERROR 1048 (23000): Column 'ts' cannot be null
With MySQL and many other databases, even though we have the “DEFAULT CURRENT_TIMESTAMP” on our timestamp column, explicitly supplying NULL in the INSERT statement is “non-standard” syntax. Standard syntax would be to omit the column in the column spec and VALUES (e.g. query 1). We get the error because of the default value of explicit_defaults_for_timestamp. From the MySQL manual, “This system variable determines whether the server enables certain nonstandard behaviors for default values and NULL-value handling in TIMESTAMP columns. By default, explicit_defaults_for_timestamp is enabled, which disables the nonstandard behaviors.”
I was working with an application with deprecated SQL syntax. In fact, this syntax may be legal on some modern databases; just not by default on MySQL. If a reader knows of databases where this is legal, please feel free to comment.
The fix for this is as simple as it gets, which is probably why there are not more posts about this.
mysql> set global explicit_defaults_for_timestamp='OFF'; Query OK, 0 rows affected (0.00 sec)
Because this is a global variable, one must log out and log back into the CLI. After reconnecting:
mysql> insert into time_test (comment,ts) values ('this is statement 2',NULL); Query OK, 1 row affected (0.01 sec) mysql> select * from time_test; +----+---------------------+---------------------+ | id | comment | ts | +----+---------------------+---------------------+ | 1 | this is statement 1 | 2018-08-14 02:51:21 | | 2 | this is statement 2 | 2018-08-14 02:52:20 | +----+---------------------+---------------------+ 2 rows in set (0.00 sec)
In my case, as soon as the global variable was set, I was able to start my replication thread back up. Thanks for reading my reference post.