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

Virtual columns make table checksum unstable

Details

    • Bug
    • Status: Open (View Workflow)
    • Major
    • Resolution: Unresolved
    • 10.4(EOL), 10.5, 10.6, 10.10(EOL), 10.11, 11.0(EOL), 11.1(EOL)
    • 10.5, 10.6, 10.11
    • Virtual Columns
    • None

    Description

      CHECKSUM of a table with a virtual column may change, even though the contents and the structure remains visibly the same.
      The first test case is seemingly deterministic, with checksum changing after restoration of mysqldump.
      The second test case is highly non-deterministic (on unclear reasons), it shows how CHECKSUM changes after simple server restart, without dump restoration. It involves several features none of which I could remove, so I'm not sure it's the same problem, but it does involve virtual columns among others.

      Test case with dump

      		 
      CREATE TABLE t (a INT, b INT AS (a)) ENGINE=MyISAM;
      		 
      INSERT INTO t (a) VALUES (1),(2);
      		 
      --echo # Before dump
      		 
      SHOW CREATE TABLE t;
      		 
      SELECT * FROM t;
      		 
      CHECKSUM TABLE t;
      		 
      --exec $MYSQL_DUMP test --tables t > $MYSQL_TMP_DIR/t.dump
      		 
      DROP TABLE t;
      		 
      --exec $MYSQL test < $MYSQL_TMP_DIR/t.dump
      		 
      --echo # After dump
      		 
      SHOW CREATE TABLE t;
      		 
      SELECT * FROM t;
      		 
      CHECKSUM TABLE t;
      		 
       
      		 
      DROP TABLE t;

      Results with mysqldump, 10.4 900c4d69

      		 
      # Before dump
      		 
      SHOW CREATE TABLE t;
      		 
      Table	Create Table
      		 
      t	CREATE TABLE `t` (
      		 
        `a` int(11) DEFAULT NULL,
      		 
        `b` int(11) GENERATED ALWAYS AS (`a`) VIRTUAL
      		 
      ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
      		 
      SELECT * FROM t;
      		 
      a	b
      		 
      1	1
      		 
      2	2
      		 
      CHECKSUM TABLE t;
      		 
      Table	Checksum
      		 
      test.t	2865344526


      # After dump
      		 
      SHOW CREATE TABLE t;
      		 
      Table	Create Table
      		 
      t	CREATE TABLE `t` (
      		 
        `a` int(11) DEFAULT NULL,
      		 
        `b` int(11) GENERATED ALWAYS AS (`a`) VIRTUAL
      		 
      ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
      		 
      SELECT * FROM t;
      		 
      a	b
      		 
      1	1
      		 
      2	2
      		 
      CHECKSUM TABLE t;
      		 
      Table	Checksum
      		 
      test.t	1690939406

      Test case with restart

      		 
      --source include/have_innodb.inc
      		 
       
      		 
      CREATE TABLE t (
      		 
        id int(11) NOT NULL AUTO_INCREMENT,
      		 
        va mediumint GENERATED ALWAYS AS (a),
      		 
        a mediumint NOT NULL DEFAULT 0,
      		 
        b char(1) DEFAULT NULL,
      		 
        PRIMARY KEY (id)
      		 
      ) ENGINE=InnoDB WITH SYSTEM VERSIONING;
      		 
       
      		 
      INSERT INTO t (b) VALUES ('a'),('b'),('c');
      		 
      UPDATE t SET b = 'x';
      		 
       
      		 
      CHECKSUM TABLE t EXTENDED;
      		 
       
      		 
      --source include/restart_mysqld.inc
      		 
      CHECKSUM TABLE t EXTENDED;
      		 
      --source include/restart_mysqld.inc
      		 
      CHECKSUM TABLE t EXTENDED;
      		 
      --source include/restart_mysqld.inc
      		 
      CHECKSUM TABLE t EXTENDED;
      		 
       
      		 
      DROP TABLE t;

      Results #1 with restart 10.4 900c4d69

      		 
      CHECKSUM TABLE t EXTENDED;
      		 
      Table	Checksum
      		 
      test.t	4236583688
      		 
      # restart
      		 
      CHECKSUM TABLE t EXTENDED;
      		 
      Table	Checksum
      		 
      test.t	4236583688
      		 
      # restart
      		 
      CHECKSUM TABLE t EXTENDED;
      		 
      Table	Checksum
      		 
      test.t	1358680303
      		 
      # restart
      		 
      CHECKSUM TABLE t EXTENDED;
      		 
      Table	Checksum
      		 
      test.t	1358680303

      Results #2 with restart

      		 
      CHECKSUM TABLE t EXTENDED;
      		 
      Table	Checksum
      		 
      test.t	1428158860
      		 
      # restart
      		 
      CHECKSUM TABLE t EXTENDED;
      		 
      Table	Checksum
      		 
      test.t	1428158860
      		 
      # restart
      		 
      CHECKSUM TABLE t EXTENDED;
      		 
      Table	Checksum
      		 
      test.t	714221683
      		 
      # restart
      		 
      CHECKSUM TABLE t EXTENDED;
      		 
      Table	Checksum
      		 
      test.t	1428158860

      etc.

      Attachments

        Activity

          There are no comments yet on this issue.

          People

            serg Sergei Golubchik
            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.