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

Corruption reported on indexed virtual column when timezone is changed

Details

    Description

      SET TIME_ZONE='+00:00';
      		 
       
      		 
      CREATE TABLE t (a TIMESTAMP, b DATE GENERATED ALWAYS AS (DATE(a)) VIRTUAL, KEY(b));
      		 
      INSERT INTO t (a) VALUES ('1999-01-31 22:00:00'),('1990-01-01 00:00:00');
      		 
       
      		 
      SET TIME_ZONE='+02:00';
      		 
      CHECK TABLE t EXTENDED;
      		 
       
      		 
      # Cleanup
      		 
      DROP TABLE t;

      With MyISAM:

      10.4 64f44b22d9a3dab3d4c0b77addbcbdafde57b466

      		 
      CHECK TABLE t EXTENDED;
      		 
      Table	Op	Msg_type	Msg_text
      		 
      test.t	check	error	Record at: 0  Can't find key for index:  1
      		 
      test.t	check	error	Corrupt
      		 
      DROP TABLE t;
      		 
      bug.t                                    [ fail ]  Found warnings/errors in server log file!
      		 
              Test ended at 2023-11-29 15:10:20
      		 
      line
      		 
      2023-11-29 15:10:20 4 [ERROR] Got an error from thread_id=4, /data/bld/10.4-asan/storage/myisam/ha_myisam.cc:1130
      		 
      2023-11-29 15:10:20 4 [ERROR] MySQL thread id 4, OS thread handle 140423800547008, query id 26 localhost root Checking table

      With InnoDB (on 10.6+ where CHECK .. EXTENDED became extended):

      10.6 2f467de4c4851d2f9b0f3bec54f748d92349582a

      		 
      CHECK TABLE t EXTENDED;
      		 
      Table	Op	Msg_type	Msg_text
      		 
      test.t	check	Warning	InnoDB: Index 'b' contains 1 entries, should be 2.
      		 
      test.t	check	error	Corrupt
      		 
      DROP TABLE t;
      		 
      bug.t                                    [ fail ]  Found warnings/errors in server log file!
      		 
              Test ended at 2023-11-29 15:11:00
      		 
      line
      		 
      2023-11-29 15:11:00 4 [ERROR] InnoDB: Flagged corruption of `b` in table `test`.`t` in CHECK TABLE; Wrong count

      Attachments

        Activity

          Ascending order - Click to sort in descending order
          serg Sergei Golubchik added a comment -

          Same without DATE():

          SET TIME_ZONE='+00:00';
          		 
          CREATE TABLE t (a TIMESTAMP, b DATE GENERATED ALWAYS AS (a) VIRTUAL, KEY(b));
          		 
          INSERT INTO t (a) VALUES ('1999-01-31 22:00:00'),('1990-01-01 00:00:00');
          		 
          SET TIME_ZONE='+02:00';
          		 
          CHECK TABLE t EXTENDED;
          		 
          DROP TABLE t;

          serg Sergei Golubchik added a comment - Same without DATE() : SET TIME_ZONE= '+00:00' ; CREATE TABLE t (a TIMESTAMP , b DATE GENERATED ALWAYS AS (a) VIRTUAL, KEY (b)); INSERT INTO t (a) VALUES ( '1999-01-31 22:00:00' ),( '1990-01-01 00:00:00' ); SET TIME_ZONE= '+02:00' ; CHECK TABLE t EXTENDED; DROP TABLE t;
          elenst Elena Stepanova added a comment -

          DATE was added just to make the test case more "valid", to avoid the truncation warning upon INSERT.

          For an additional note, the test case itself was not artificial, as it may seem based on timezone changes within a session.
          Initially the failure was observed upon restoration of mysqldump dump, followed by a health check via CHECK TABLE. mysqldump by default sets the timezone to 0 in the beginning and restores the original (typically non-zero) value at the end of the dump.

          elenst Elena Stepanova added a comment - DATE was added just to make the test case more "valid", to avoid the truncation warning upon INSERT. For an additional note, the test case itself was not artificial, as it may seem based on timezone changes within a session. Initially the failure was observed upon restoration of mysqldump dump, followed by a health check via CHECK TABLE. mysqldump by default sets the timezone to 0 in the beginning and restores the original (typically non-zero) value at the end of the dump.

          People

            bar Alexander Barkov
            elenst Elena Stepanova
            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.