[MDEV-34037] DATETIME <-> TIMESTAMP conversion in a virtual column corrups the table on @@time_zone change - Jira

Details

Type: Bug
Status: In Review (View Workflow)
Priority: Major
Resolution: Unresolved
Affects Version/s: 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)
Fix Version/s: 11.8
Component/s: Data types, Virtual Columns
Labels:
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-15751 CURRENT_TIMESTAMP should return a TIMESTAMP (WITH TIME ZONE?)

Closed

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

(2 relates to)

Activity

Ascending order - Click to sort in descending order

Alexander Barkov added a comment - 2024-04-30 04:36

The same problem is repeatable with DECIMAL instead of DATETIME:

DECIMAL->TIMESTAMP conversion

This conversion is performed in two steps: DECIMAL->DATETIME->TIMESTAMP. It uses @@time_zone on the second step.

SET time_zone='+00:00';

CREATE OR REPLACE TABLE t1 (

  a DECIMAL(32,0),

  v TIMESTAMP GENERATED ALWAYS AS (a),

  KEY(v)

) ENGINE=MyISAM;

INSERT INTO t1 (a) VALUES (20010101102030);

SET time_zone='+10:00';

UPDATE t1 SET a='20000101102030';

ERROR 126 (HY000): Index for table './test/t1.MYI' is corrupt; try to repair it

TIMESTAMP->DECIMAL conversion

This conversion is performed in two steps: TIMESTAMP->DATETIME->DECIMAL. It uses @@time_zone on the first step.

SET time_zone='+00:00';

CREATE OR REPLACE TABLE t1 (

  a TIMESTAMP,

  v DECIMAL(32,0) 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

Alexander Barkov added a comment - 2024-04-30 04:36 The same problem is repeatable with DECIMAL instead of DATETIME: DECIMAL->TIMESTAMP conversion This conversion is performed in two steps: DECIMAL->DATETIME->TIMESTAMP. It uses @@time_zone on the second step. SET time_zone= '+00:00' ; CREATE OR REPLACE TABLE t1 ( a DECIMAL (32,0), v TIMESTAMP GENERATED ALWAYS AS (a), KEY (v) ) ENGINE=MyISAM; INSERT INTO t1 (a) VALUES (20010101102030); SET time_zone= '+10:00' ; UPDATE t1 SET a= '20000101102030' ; ERROR 126 (HY000): Index for table './test/t1.MYI' is corrupt; try to repair it TIMESTAMP->DECIMAL conversion This conversion is performed in two steps: TIMESTAMP->DATETIME->DECIMAL. It uses @@time_zone on the first step. SET time_zone= '+00:00' ; CREATE OR REPLACE TABLE t1 ( a TIMESTAMP , v DECIMAL (32,0) 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

Alexander Barkov added a comment - 2024-05-03 13:25 - edited

Hello serg,

Please review:
https://github.com/MariaDB/server/commit/ad88980b16bcc04047f46dbc63125a1e74c1f50b

Alexander Barkov added a comment - 2024-05-03 13:25 - edited Hello serg , Please review: https://github.com/MariaDB/server/commit/ad88980b16bcc04047f46dbc63125a1e74c1f50b

Sergei Golubchik added a comment - 2025-03-26 10:20

How does it interfere with ~~MDEV-15751~~? After it UNIX_TIMESTAMP() always returns a valid timestamp, doesn't it?
May be, let's move this, rather big, change to after ~~MDEV-15751~~?

Sergei Golubchik added a comment - 2025-03-26 10:20 How does it interfere with MDEV-15751 ? After it UNIX_TIMESTAMP() always returns a valid timestamp, doesn't it? May be, let's move this, rather big, change to after MDEV-15751 ?

Alexander Barkov added a comment - 2025-04-03 08:55 - edited

Hi serg. Thanks for noticing this. The patch indeed will change slightly after ~~MDEV-15751~~. Let's move it to a newer version.

Which version do you suggest?

Alexander Barkov added a comment - 2025-04-03 08:55 - edited Hi serg . Thanks for noticing this. The patch indeed will change slightly after MDEV-15751 . Let's move it to a newer version. Which version do you suggest?

Sergei Golubchik added a comment - 2025-04-03 11:01

it's a bug fix, so the the lowest maintained version that has ~~MDEV-15751~~ is good. That is, 11.8

Sergei Golubchik added a comment - 2025-04-03 11:01 it's a bug fix, so the the lowest maintained version that has MDEV-15751 is good. That is, 11.8

People

Assignee:: Sergei Golubchik

Reporter:: Alexander Barkov

Votes:: 0 Vote for this issue

Watchers:: 3 Start watching this issue

Dates

Created:: 2024-04-30 04:32

Updated:: 2025-04-03 11:01

Git Integration

Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.

MariaDB Server