MySQL 8 – timestamp cannot be null and explicit_defaults_for_timestamp

Kedar

2 years ago

A friend’s application started failing with MySQL causing error about timestamp columns and it needs urgent fixing from the database side. A timestamp column was not accepting the null values and they were sort of down.

ERROR 1048 (23000): Column 'start_date' cannot be null

In this blog we will learn how MySQL is telling you, to follow what you say. When you define the column NOT NULL it means NOT NULL and (latest) MySQL is going to error if you don’t respect that.

Also, there’s golden bullet to solve any production problem like this towards the end of this blog: make sure to read through completely.

He shared the table definition with me and it looked pretty OK to me.

CREATE TABLE notnull (
id int NOT NULL AUTO_INCREMENT,
start_date timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
end_date timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (id)
) ENGINE=InnoDB;

Testing timestamp behaviour in MySQL 5.7

I started my lab (Percona Server 5.7) and tested:

mysql> CREATE TABLE notnull ( id int NOT NULL AUTO_INCREMENT, start_date timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, end_date timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ;
Query OK, 0 rows affected (0.01 sec)

mysql> insert into notnull values (null, null, null);
Query OK, 1 row affected (0.00 sec)

mysql> select * from notnull;
+----+---------------------+---------------------+
| id | start_date | end_date |
+----+---------------------+---------------------+
| 1 | 2023-02-24 12:37:35 | 2023-02-24 12:37:35 |
+----+---------------------+---------------------+
1 row in set (0.00 sec)

It works! What’s the problem? I further asked his SQL_MODE and MySQL Version.

A workaround that wasn’t good

We also discussed further about this issue and one solution proposed was using a BEFORE TRIGGER. Since the timestamp value are inserted as NULL, we will provide a NON NULL value before the INSERT is actually processed.

CREATE TRIGGER start_date_trigger BEFORE INSERT ON notnull FOR EACH ROW SET NEW.start_date = CURRENT_TIMESTAMP;
CREATE TRIGGER end_date_trigger BEFORE INSERT ON notnull FOR EACH ROW SET NEW.end_date = CURRENT_TIMESTAMP;

MySQL [test]> insert into notnull values(null,null,null);
Query OK, 1 row affected (0.051 sec)

MySQL [test]> select * from notnull ;
| 29 | 2023-02-24 11:57:34 | 2023-02-24 11:57:35 |
| 30 | 2023-02-24 13:33:24 | 2023-02-24 13:33:24 |

But we both agreed, that it’s not a correct way.

Reproducing the error on MySQL 8

Anyways, he further confirmed that the Server version was 8.0.26 and SQL_MODE remained NO_ENGINE_SUBSTITUTION. So, I started MySQL 8 lab, and retried to produce the error and succeeded to generate a failure.

mysql [localhost:8028] {msandbox} (test) > insert into notnull values (null,null,null);
ERROR 1048 (23000): Column 'start_date' cannot be null

On MySQL 8 we see what’s the problem. One of the many changes that has been implemented in MySQL 8 (8.0.22+) is the alteration of the default value of explicit_defaults_for_timestamp from OFF to ON. This may seem like a small change, but it has significant implications for the way NOT NULL and default timestamps work.
The note says: As of MySQL 8.0.22, attempting to insert NULL into a generated column declared as TIMESTAMP NOT NULL is rejected with an error.

So the (temporary) solution to the problem here is to make the change and retain in config.

SET GLOBAL explicit_defaults_for_timestamp = OFF;

Explanation of the issue

Let’s understand a little more about explicit_defaults_for_timestamp. This system variable controls whether or not MySQL should use explicit DEFAULT expressions for timestamp columns that have no explicit DEFAULT value defined.

When explicit_defaults_for_timestamp is set to OFF, MySQL does not use explicit DEFAULT expressions for timestamp columns, and instead, it assigns a value of ‘0000-00-00 00:00:00’ if the column is defined as NOT NULL, or NULL if it’s defined as NULLABLE.

However, with the variable set to ON, MySQL uses explicit DEFAULT expressions for timestamp columns, even if they are not explicitly defined.

As of MySQL 8.0.22, attempting to insert NULL into a generated column declared as TIMESTAMP NOT NULL is rejected with an error.

mysql [localhost:8028] {msandbox} (test) > set explicit_defaults_for_timestamp=ON;
Query OK, 0 rows affected (0.00 sec)

mysql [localhost:8028] {msandbox} (test) > insert into notnull values (2,null,null);
ERROR 1048 (23000): Column 'start_date' cannot be null

But when we turn this variable OFF:

mysql [localhost:8028] {msandbox} (test) > set explicit_defaults_for_timestamp=OFF;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql [localhost:8028] {msandbox} (test) > show warnings;
+---------+------+------------------------------------------------------------------------------------------+
| Level | Code | Message |
+---------+------+------------------------------------------------------------------------------------------+
| Warning | 1287 | 'explicit_defaults_for_timestamp' is deprecated and will be removed in a future release. |
+---------+------+------------------------------------------------------------------------------------------+

mysql [localhost:8028] {msandbox} (test) > insert into notnull values (3,null,null);
Query OK, 1 row affected (0.00 sec)

mysql [localhost:8028] {msandbox} (test) > select * from notnull;
+----+---------------------+---------------------+
| id | start_date | end_date |
+----+---------------------+---------------------+
| 1 | 2023-03-19 19:16:01 | 2023-03-19 19:16:01 |
| 3 | 2023-03-19 19:18:12 | 2023-03-19 19:18:12 |
+----+---------------------+---------------------+

I hope you did not miss the WARNING above “explicit_defaults_for_timestamp’ is deprecated and will be removed in a future release.”.

Also, not providing the column names should allow it to pick up the defaults.

mysql [localhost:8028] {msandbox} (test) > insert into notnull(id) values (null);
Query OK, 1 row affected (0.00 sec)

mysql [localhost:8028] {msandbox} (test) > select * from notnull;
+----+---------------------+---------------------+
| id | start_date | end_date |
+----+---------------------+---------------------+
| 1 | 2023-03-19 19:16:01 | 2023-03-19 19:16:01 |
| 3 | 2023-03-19 19:18:12 | 2023-03-19 19:18:12 |
| 4 | 2023-03-19 19:20:20 | 2023-03-19 19:20:20 |
+----+---------------------+---------------------+
3 rows in set (0.00 sec)

Conclusion

Well, the issue was solved for my friend by setting explicit_defaults_for_timestamp OFF but remember that this MySQL variable is going away. So respect your definition – NOT NULL means NOT NULL.

Refer the MySQL worklog for detail information and high level architecture for the fixture.

Okay so for those who believed in golden bullet, :faceplam: , but I surely have a secret pro-tip.

How did this issue start? My friend confirmed that they did a MySQL Migration. They migrated from MariaDB to MySQL 8, on a Friday! Without testing! If you can’t believe that a day is Friday, use any of the methods used here to prove a Friday and act accordingly.

ProTip: Never, I repeat, Never do any production change on a Friday, your engineers will love you.