Details
-
Bug
-
Status: In Review (View Workflow)
-
Major
-
Resolution: Unresolved
-
10.5, 10.6, 10.11, 11.1(EOL), 11.2, 11.4, 10.4(EOL), 10.7(EOL), 10.8(EOL), 10.9(EOL), 10.10(EOL), 11.0(EOL), 11.3(EOL), 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
-
MDEV-20610 Assertion failed or btr_validate_index(..) in row_upd_sec_index_entry on a time_zone change
- Stalled
-
MDEV-34061 unix_timestamp(coalesce(timestamp_column)) returns NULL on '1970-01-01 00:00:00.000001'
- Closed
-
MDEV-34088 The TIMESTAMP value of '1970-01-01 00:00:00' can be indirectly inserted in strict mode
- Closed
-
MDEV-34038 A division in an indexed virtual column corrupts the table on @@dev_precision_increment change
- Open
-
MDEV-34039 Some string functions in a virtual column lead the table corruption on @@max_allowed_packet change
- Open
-
MDEV-34069 Zero datetime reinterprets as '1970-01-01 00:00:00' on field_datetime=field_timestamp
- Closed