ERROR 1048 (23000): Column 'start_date' cannot be null<\/code><\/pre>\n\n\n\nIn 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.<\/p>\n\n\n\n
Also, there’s golden bullet to solve any production problem like this towards the end of this blog: make sure to read through completely.<\/p>\n\n\n\n
He shared the table definition with me and it looked pretty OK to me.<\/p>\n\n\n\n
CREATE TABLE notnull (\nid int NOT NULL AUTO_INCREMENT,\nstart_date timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,\nend_date timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,\nPRIMARY KEY (id)\n) ENGINE=InnoDB;<\/code><\/pre>\n\n\n\n<\/p>\n\n\n\n
Testing timestamp behaviour in MySQL 5.7<\/h3>\n\n\n\n
I started my lab (Percona Server 5.7) and tested:<\/p>\n\n\n\n
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 ;\nQuery OK, 0 rows affected (0.01 sec)\n\nmysql> insert into notnull values (null, null, null);\nQuery OK, 1 row affected (0.00 sec)<\/code><\/pre>\n\n\n\nmysql> select * from notnull;\n+----+---------------------+---------------------+\n| id | start_date | end_date |\n+----+---------------------+---------------------+\n| 1 | 2023-02-24 12:37:35 | 2023-02-24 12:37:35 |\n+----+---------------------+---------------------+\n1 row in set (0.00 sec)<\/code><\/pre>\n\n\n\n\n\n\n\nIt works! What’s the problem? I further asked his SQL_MODE and MySQL Version. <\/p>\n\n\n\n
<\/p>\n\n\n\n
A workaround that wasn’t good<\/h4>\n\n\n\n
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.<\/p>\n\n\n\n
CREATE TRIGGER start_date_trigger BEFORE INSERT ON notnull FOR EACH ROW SET NEW.start_date = CURRENT_TIMESTAMP;\nCREATE TRIGGER end_date_trigger BEFORE INSERT ON notnull FOR EACH ROW SET NEW.end_date = CURRENT_TIMESTAMP;\n\nMySQL [test]> insert into notnull values(null,null,null);\nQuery OK, 1 row affected (0.051 sec)\n\nMySQL [test]> select * from notnull ;\n| 29 | 2023-02-24 11:57:34 | 2023-02-24 11:57:35 |\n| 30 | 2023-02-24 13:33:24 | 2023-02-24 13:33:24 |<\/code><\/pre>\n\n\n\nBut we both agreed, that it’s not a correct way.<\/p>\n\n\n\n
<\/p>\n\n\n\n
Reproducing the error on MySQL 8<\/h3>\n\n\n\n
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.<\/p>\n\n\n\n
mysql [localhost:8028] {msandbox} (test) > insert into notnull values (null,null,null);\nERROR 1048 (23000): Column 'start_date' cannot be null<\/strong><\/code><\/pre>\n\n\n\nOn 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.<\/p>\n\n\n\n
So the (temporary) solution to the problem here is to make the change and retain in config.<\/p>\n\n\n\n
SET GLOBAL explicit_defaults_for_timestamp = OFF;<\/code><\/pre>\n\n\n\n<\/p>\n\n\n\n
Explanation of the issue<\/h3>\n\n\n\n
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.<\/p>\n\n\n\n
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.<\/p>\n\n\n\n
However, with the variable set to ON, MySQL uses explicit DEFAULT expressions for timestamp columns, even if they are not explicitly defined.<\/p>\n\n\n\n
As of MySQL 8.0.22, attempting to insert NULL into a generated column declared as TIMESTAMP NOT NULL is rejected with an error.<\/p>\n\n\n\n
mysql [localhost:8028] {msandbox} (test) > set explicit_defaults_for_timestamp=ON;\nQuery OK, 0 rows affected (0.00 sec)\n\nmysql [localhost:8028] {msandbox} (test) > insert into notnull values (2,null,null);\nERROR 1048 (23000): Column 'start_date' cannot be null<\/code><\/pre>\n\n\n\nBut when we turn this variable OFF:<\/p>\n\n\n\n
mysql [localhost:8028] {msandbox} (test) > set explicit_defaults_for_timestamp=OFF;\nQuery OK, 0 rows affected, 1 warning (0.00 sec)\n\nmysql [localhost:8028] {msandbox} (test) > show warnings;\n+---------+------+------------------------------------------------------------------------------------------+\n| Level | Code | Message |\n+---------+------+------------------------------------------------------------------------------------------+\n| Warning | 1287 | 'explicit_defaults_for_timestamp' is deprecated and will be removed in a future release. |<\/strong>\n+---------+------+------------------------------------------------------------------------------------------+\n\nmysql [localhost:8028] {msandbox} (test) > insert into notnull values (3,null,null);\nQuery OK, 1 row affected (0.00 sec)\n\nmysql [localhost:8028] {msandbox} (test) > select * from notnull;\n+----+---------------------+---------------------+\n| id | start_date | end_date |\n+----+---------------------+---------------------+\n| 1 | 2023-03-19 19:16:01 | 2023-03-19 19:16:01 |\n| 3 | 2023-03-19 19:18:12 | 2023-03-19 19:18:12 |\n+----+---------------------+---------------------+<\/code><\/pre>\n\n\n\nI hope you did not miss the WARNING above “explicit_defaults_for_timestamp’ is deprecated and will be removed in a future release.”<\/em>. <\/p>\n\n\n\nAlso, not providing the column names should allow it to pick up the defaults.<\/p>\n\n\n\n
mysql [localhost:8028] {msandbox} (test) > insert into notnull(id) values (null);\nQuery OK, 1 row affected (0.00 sec)\n\nmysql [localhost:8028] {msandbox} (test) > select * from notnull;\n+----+---------------------+---------------------+\n| id | start_date | end_date |\n+----+---------------------+---------------------+\n| 1 | 2023-03-19 19:16:01 | 2023-03-19 19:16:01 |\n| 3 | 2023-03-19 19:18:12 | 2023-03-19 19:18:12 |\n| 4 | 2023-03-19 19:20:20 | 2023-03-19 19:20:20 |\n+----+---------------------+---------------------+\n3 rows in set (0.00 sec)<\/code><\/pre>\n\n\n\nConclusion<\/h3>\n\n\n\n
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.<\/p>\n\n\n
![\"\"](\"http:\/\/kedar.nitty-witty.com\/wp-content\/uploads\/2023\/03\/image-300x217.png\")
<\/figure>\n<\/div>\n\n\n