Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-34037

DATETIME <-> TIMESTAMP conversion in a virtual column corrups the table on @@time_zone change

    XMLWordPrintable

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)
    • 10.5
    • Data types, Virtual Columns
    • 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

          Activity

            People

              serg Sergei Golubchik
              bar Alexander Barkov
              Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

              Dates

                Created:
                Updated:

                Git Integration

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