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.4, 10.5, 10.6, 10.7, 10.8, 10.9, 10.10, 10.11, 11.0, 11.1, 11.2, 11.3, 11.4, 11.5
    • 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

          relates to

          Bug - A problem which impairs or prevents the functions of the product. MDEV-20610 Assertion failed or btr_validate_index(..) in row_upd_sec_index_entry on a time_zone change

          • Major - Major loss of function.
          • In Progress

          Bug - A problem which impairs or prevents the functions of the product. MDEV-34061 unix_timestamp(coalesce(timestamp_column)) returns NULL on '1970-01-01 00:00:00.000001'

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-34088 The TIMESTAMP value of '1970-01-01 00:00:00' can be indirectly inserted in strict mode

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-34038 A division in an indexed virtual column corrupts the table on @@dev_precision_increment change

          • Major - Major loss of function.
          • Open

          Bug - A problem which impairs or prevents the functions of the product. MDEV-34039 Some string functions in a virtual column lead the table corruption on @@max_allowed_packet change

          • Major - Major loss of function.
          • Open

          Bug - A problem which impairs or prevents the functions of the product. MDEV-34069 Zero datetime reinterprets as '1970-01-01 00:00:00' on field_datetime=field_timestamp

          • Major - Major loss of function.
          • Closed

          Activity

            People

              serg Sergei Golubchik
              bar Alexander Barkov
              Votes:
              0 Vote for this issue
              Watchers:
              2 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.