CODEWITHSUNDEEP
mysqlsql-timestampgenerated
sillydong
sillydong

Reputation: 71

in MySQL generated columns referring to current_timestamp fail

Given this table structure:

CREATE TABLE t3 (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `abc` bigint NOT NULL,
  `ts` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `createdday` int GENERATED ALWAYS AS (cast(`ts` as date)) STORED NOT NULL,
  PRIMARY KEY (`id`)
);

In one MySQL version 8.0.25 instance, when we do insert into t3 (abc) values (2); it can successfully write the row and createdday can has right value, but in another MySQL instance with same version, it shows error ERROR 1048 (23000): Column 'createdday' cannot be null.

In MySQL bug list, the was a question about this https://bugs.mysql.com/bug.php?id=94550, but there is no update in it.

I checked the configuration variables, they are same. Especially the explicit_defaults_for_timestamp is OFF. Anyone knows why one of the MySQL instances can save it in right way but another cannot? and how to fix this?

2024/09/21 Update: Did more tests with different MySQL versions, I found that in MySQL 8.0.23 it is allowed to run that insert statement and successfully write the data, but when I upgrade to 8.0.24, it starts to error out with column cannot be null. I think this change starts from 8.0.24.

Upvotes: 0

Views: 81

Answers (1)

Ergest Basha
Ergest Basha

Reputation: 9038

I made some tests on MySQL version 8.0.39-0ubuntu0.22.04.1.

Table description

CREATE TABLE `t3` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `abc` bigint NOT NULL,
  `ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `createdday` int GENERATED ALWAYS AS (cast(`ts` as date)) STORED NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB

mysql> insert into t3(abc) values(1);
ERROR 1048 (23000): Column 'createdday' cannot be null

With explicit_defaults_for_timestamp session and global set to OFF and ON the same error occurred, but if I used the following query

mysql> insert into t3(abc,ts) values(1,current_date());
Query OK, 1 row affected (0.02 sec)

mysql> select * from t3;
+----+-----+---------------------+------------+
| id | abc | ts                  | createdday |
+----+-----+---------------------+------------+
|  1 |   1 | 2024-09-19 00:00:00 |   20240919 |
+----+-----+---------------------+------------+
1 row in set (0.00 sec)

It works just fine.

Using DATETIME the error did not occur.

 CREATE TABLE `t4` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `abc` bigint NOT NULL,
  `ts` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `createdday` int GENERATED ALWAYS AS (cast(`ts` as date)) STORED NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB

mysql> insert into t4(abc) values(1);
Query OK, 1 row affected (0.02 sec)

mysql> select * from t4;
+----+-----+---------------------+------------+
| id | abc | ts                  | createdday |
+----+-----+---------------------+------------+
|  1 |   1 | 2024-09-19 17:47:10 |   20240919 |
+----+-----+---------------------+------------+
1 row in set (0.00 sec)

In the manual I can't find a proper solution to this behavior CREATE TABLE and Generated Columns

If a generated column uses the TIMESTAMP data type, the setting for explicit_defaults_for_timestamp is ignored. In such cases, if this variable is disabled then NULL is not converted to CURRENT_TIMESTAMP. If the column is also declared as NOT NULL, attempting to insert NULL is explicitly rejected with ER_BAD_NULL_ERROR

Upvotes: 0

Related Questions