| Bug #115548 | When 2 timestamp NOT NULL fields in one table is different type after created | ||
|---|---|---|---|
| Submitted: | 9 Jul 2024 9:47 | Modified: | 10 Jul 2024 10:34 |
| Reporter: | XIAOJING LI | Email Updates: | |
| Status: | Not a Bug | Impact on me: | |
| Category: | MySQL Server | Severity: | S3 (Non-critical) |
| Version: | 8.0.36 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | timestamp | ||
[9 Jul 2024 9:47]
XIAOJING LI
[9 Jul 2024 10:18]
MySQL Verification Team
Hi Mr. LI,
Thank you very much for your bug report.
However, we were not able to repeat the behaviour with latest releases of 8.0, 8.4 and 9.0:
mysql> set sql_mode='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> CREATE TABLE `t1` (
-> `a` int(11) NOT NULL,
-> `create_time` timestamp NOT NULL COMMENT '创建时间',
-> `modify_time` timestamp NOT NULL COMMENT '更新时间',
-> PRIMARY KEY (`a`)
-> );
Query OK, 0 rows affected, 1 warning (0.05 sec)
mysql> show create table t1;
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t1 | CREATE TABLE `t1` (
`a` int NOT NULL,
`create_time` timestamp NOT NULL COMMENT '创建时间',
`modify_time` timestamp NOT NULL COMMENT '更新时间',
PRIMARY KEY (`a`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.02 sec)
Can't repeat.
[10 Jul 2024 10:20]
XIAOJING LI
Hi,
I guess you didn't set parameter explicit_defaults_for_timestamp=off;
It works as you did when the parameter explicit_defaults_for_timestamp is ON;
please set param explicit_defaults_for_timestamp=off and try again.
Thank you!!!
mysql> show variables like 'explicit_defaults_for_timestamp';
+---------------------------------+-------+
| Variable_name | Value |
+---------------------------------+-------+
| explicit_defaults_for_timestamp | ON |
+---------------------------------+-------+
1 row in set (0.00 sec)
mysql> set explicit_defaults_for_timestamp=off;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> select @@version;
+-----------+
| @@version |
+-----------+
| 8.0.36 |
+-----------+
1 row in set (0.00 sec)
mysql> show variables like 'sql_mode';
+---------------+-----------------------------------------------------------------------------------------------------------------------+
| Variable_name | Value |
+---------------+-----------------------------------------------------------------------------------------------------------------------+
| sql_mode | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION |
+---------------+-----------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> set sql_mode='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> CREATE TABLE `t1` (
-> `a` int(11) NOT NULL,
-> `create_time` timestamp NOT NULL COMMENT '创建时间',
-> `modify_time` timestamp NOT NULL COMMENT '更新时间',
-> PRIMARY KEY (`a`)
-> );
Query OK, 0 rows affected, 1 warning (0.08 sec)
mysql> show create table t1;
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t1 | CREATE TABLE `t1` (
`a` int NOT NULL,
`create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '创建时间',
`modify_time` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT '更新时间',
PRIMARY KEY (`a`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql>
[10 Jul 2024 10:34]
MySQL Verification Team
Hi Mr. LI, Thank you for your last comment. However, what you are reporting is clearly explained in our Reference Manual: " TIMESTAMP columns following the first one, if not explicitly declared with the NULL attribute or an explicit DEFAULT attribute, are automatically declared as 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. " This changes the status of this report from "Can't repeat" to "Not a Bug".
