| Bug #112540 | Timestamp data type key column is changing implicitly | ||
|---|---|---|---|
| Submitted: | 27 Sep 2023 13:10 | Modified: | 27 Sep 2023 18:29 |
| Reporter: | Ілля Шабалін | Email Updates: | |
| Status: | Verified | Impact on me: | |
| Category: | MySQL Server: InnoDB storage engine | Severity: | S3 (Non-critical) |
| Version: | 5.7 and less MariaDB 10.4 and less | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[27 Sep 2023 18:29]
MySQL Verification Team
with default config your example would not work
master [localhost:20943] {msandbox} (test) > select @@sql_mode;
+-------------------------------------------------------------------------------------------------------------------------------------------+
| @@sql_mode |
+-------------------------------------------------------------------------------------------------------------------------------------------+
| ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+-------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
master [localhost:20943] {msandbox} (test) > create table t1 (val timestamp not null, val_n timestamp, primary key (val));
ERROR 1067 (42000): Invalid default value for 'val_n'
anyhow that part is not important, also I cannot reproduce the failed update you mentioned but anyhow this is a bug
master [localhost:20943] {msandbox} (test) > select @@sql_mode;
+------------+
| @@sql_mode |
+------------+
| |
+------------+
1 row in set (0.00 sec)
master [localhost:20943] {msandbox} (test) > create table t1 (val timestamp not null, val_n timestamp, primary key (val));
Query OK, 0 rows affected (0.01 sec)
master [localhost:20943] {msandbox} (test) > insert into t1 (val, val_n) values('1998-01-01 13:06:32', '2017-12-01 06:16:05');
Query OK, 1 row affected (0.00 sec)
master [localhost:20943] {msandbox} (test) > select * from t1;
+---------------------+---------------------+
| val | val_n |
+---------------------+---------------------+
| 1998-01-01 13:06:32 | 2017-12-01 06:16:05 |
+---------------------+---------------------+
1 row in set (0.00 sec)
master [localhost:20943] {msandbox} (test) > update t1 set val_n='2000-01-01 06:15:10' where val='1998-01-01 13:06:32';
Query OK, 1 row affected (0.03 sec)
Rows matched: 1 Changed: 1 Warnings: 0
master [localhost:20943] {msandbox} (test) > select * from t1;
+---------------------+---------------------+
| val | val_n |
+---------------------+---------------------+
| 2023-09-27 20:27:54 | 2000-01-01 06:15:10 |
+---------------------+---------------------+
1 row in set (0.00 sec)
master [localhost:20943] {msandbox} (test) > update t1 set val_n='2000-01-01 06:15:10' where val='1998-01-01 13:06:32';
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0 Changed: 0 Warnings: 0
master [localhost:20943] {msandbox} (test) > update t1 set val_n='2000-01-01 06:15:10' where val='1998-01-01 13:06:32';
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0 Changed: 0 Warnings: 0
master [localhost:20943] {msandbox} (test) > select @@version
-> ;
+------------+
| @@version |
+------------+
| 5.7.42-log |
+------------+
1 row in set (0.00 sec)
[27 Sep 2023 18:32]
MySQL Verification Team
8.x is not affected
mysql [localhost:8033] {msandbox} (test) > set @@sql_mode="";
Query OK, 0 rows affected (0.00 sec)
mysql [localhost:8033] {msandbox} (test) > create table t1 (val timestamp not null, val_n timestamp, primary key (val));
Query OK, 0 rows affected (0.01 sec)
mysql [localhost:8033] {msandbox} (test) > insert into t1 (val, val_n) values('1998-01-01 13:06:32', '2017-12-01 06:16:05');
Query OK, 1 row affected (0.00 sec)
mysql [localhost:8033] {msandbox} (test) > select * from t1;
+---------------------+---------------------+
| val | val_n |
+---------------------+---------------------+
| 1998-01-01 13:06:32 | 2017-12-01 06:16:05 |
+---------------------+---------------------+
1 row in set (0.01 sec)
mysql [localhost:8033] {msandbox} (test) > update t1 set val_n='2000-01-01 06:15:10' where val='1998-01-01 13:06:32';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql [localhost:8033] {msandbox} (test) > select * from t1;
+---------------------+---------------------+
| val | val_n |
+---------------------+---------------------+
| 1998-01-01 13:06:32 | 2000-01-01 06:15:10 |
+---------------------+---------------------+
1 row in set (0.00 sec)
mysql [localhost:8033] {msandbox} (test) >
Description: If primary key column has timestamp data type it updates to the current timestamp implicitly. Even if it is not appear in the update set clause. How to repeat: create table TEST_DT_timestamp (val timestamp not null, val_n timestamp, primary key (val)); insert into TEST_DT_timestamp (val, val_n) values ('1998-01-06 13:06:35', '2017-12-01 06:17:06') update TEST_DT_timestamp set val_n = '2007-12-01 06:17:06' where val = '1998-01-06 13:06:35' As you can see val column is not changing but if you select it you will see the current timestamp in the val column. If you provide two updates of different rows in one second the second one will fail with primary key violation error. Suggested fix: Do not update timestamp data type key column implicitly.