Details
-
Bug
-
Status: In Review (View Workflow)
-
Major
-
Resolution: Unresolved
-
10.4(EOL), 10.5, 10.6, 10.7(EOL), 10.8(EOL), 10.9(EOL), 10.10(EOL), 10.11, 11.0(EOL), 11.1(EOL), 11.2(EOL), 11.3(EOL), 11.4, 11.5(EOL)
-
None
Description
DATETIME->TIMESTAMP and TIMESTAMP->DATETIME conversion depend in the @@time_zone variable value.
In case if such conversion is used in an indexed virtual column, a change in the variable value leads to the table corruption.
See also MDEV-20610, which is InnoDB specific, an is reported to fix the crash in InnoDB.
Other engines (e.g. MyISAM) do not crash, but the table corruption happens. This bug is reported to disallow unsafe expressions in virtual columns and provide safe workarounds.
These scripts demonstrate the problem
DATETIME->TIMESTAMP conversion
SET time_zone='+00:00'; |
CREATE OR REPLACE TABLE t1 ( |
a DATETIME,
|
v TIMESTAMP GENERATED ALWAYS AS (a), |
KEY(v) |
) ENGINE=MyISAM;
|
INSERT INTO t1 (a) VALUES ('2001-01-01 10:20:30'); |
SET time_zone='+10:00'; |
UPDATE t1 SET a='2000-01-01 10:20:30'; |
ERROR 126 (HY000): Index for table './test/t1.MYI' is corrupt; try to repair it
|
TIMESTAMP->DATETIME conversion
SET time_zone='+00:00'; |
CREATE OR REPLACE TABLE t1 ( |
a TIMESTAMP, |
v DATETIME GENERATED ALWAYS AS (a), |
KEY(v) |
) ENGINE=MyISAM;
|
INSERT INTO t1 (a) VALUES ('2001-01-01 10:20:30'); |
SET time_zone='+10:00'; |
UPDATE t1 SET a='2000-01-01 10:20:30'; |
ERROR 126 (HY000): Index for table './test/t1.MYI' is corrupt; try to repair it
|
Attachments
Issue Links
- relates to
-
-
- Stalled
-
-
-
- Closed
-
-
-
- Closed
-
-
-
- Open
-
-
-
- Open
-
-
-
- Closed
-