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

InnoDB: Wrong results in ALTER IGNORE TABLE with ALGORITHM=INPLACE

    Details

      Description

      Problem found during RQG testing involving row based replication.

      SET SESSION sql_mode=NO_ENGINE_SUBSTITUTION;
      CREATE TABLE t_innodb_DEFAULT ( col2 INT, col_int INT, col_int_g INT AS (col_int) PERSISTENT ) ENGINE = InnoDB  ;
      CREATE TABLE t_innodb_INPLACE ( col2 INT, col_int INT, col_int_g INT AS (col_int) PERSISTENT ) ENGINE = InnoDB  ;
      CREATE TABLE t_innodb_COPY    ( col2 INT, col_int INT, col_int_g INT AS (col_int) PERSISTENT ) ENGINE = InnoDB  ;
      CREATE TABLE t_myisam_DEFAULT ( col2 INT, col_int INT, col_int_g INT AS (col_int) PERSISTENT ) ENGINE = MyISAM  ;
      INSERT INTO t_innodb_DEFAULT (col_int, col2) VALUES (NULL, 1);
      INSERT INTO t_innodb_INPLACE (col_int, col2) VALUES (NULL, 1);
      INSERT INTO t_innodb_COPY    (col_int, col2) VALUES (NULL, 1);
      INSERT INTO t_myisam_DEFAULT (col_int, col2) VALUES (NULL, 1);
      ALTER TABLE t_innodb_DEFAULT MODIFY COLUMN col_int INT NOT NULL;
      Warnings:
      Warning 1265    Data truncated for column 'col_int' at row 1
      ALTER TABLE t_innodb_INPLACE MODIFY COLUMN col_int INT NOT NULL, ALGORITHM = INPLACE;
      Warnings:
      Warning 1265    Data truncated for column 'col_int' at row 1
      ALTER TABLE t_innodb_COPY    MODIFY COLUMN col_int INT NOT NULL, ALGORITHM = COPY;
      Warnings:
      Warning 1265    Data truncated for column 'col_int' at row 1
      ALTER TABLE t_myisam_DEFAULT MODIFY COLUMN col_int INT NOT NULL;
      Warnings:
      Warning 1265    Data truncated for column 'col_int' at row 1
      INSERT INTO t_innodb_DEFAULT (col_int, col2) VALUES (NULL, 2);
      ERROR 23000: Column 'col_int' cannot be null
      INSERT INTO t_innodb_INPLACE (col_int, col2) VALUES (NULL, 2);
      ERROR 23000: Column 'col_int' cannot be null
      INSERT INTO t_innodb_COPY    (col_int, col2) VALUES (NULL, 2);
      ERROR 23000: Column 'col_int' cannot be null
      INSERT INTO t_myisam_DEFAULT (col_int, col2) VALUES (NULL, 2);
      ERROR 23000: Column 'col_int' cannot be null
      ---- The behaviour of the different tables above is consistent ----
      SELECT * FROM t_innodb_DEFAULT;
      col2    col_int col_int_g
      1   0   NULL                    <====== Why NULL?
      SELECT * FROM t_innodb_INPLACE;
      col2    col_int col_int_g
      1   0   NULL                   <====== Why NULL?
      SELECT * FROM t_innodb_COPY;
      col2    col_int col_int_g
      1   0   0
      SELECT * FROM t_myisam_DEFAULT;
      col2    col_int col_int_g
      1   0   0
      DROP TABLE t_innodb_DEFAULT;
      DROP TABLE t_innodb_INPLACE;
      DROP TABLE t_innodb_COPY;
      DROP TABLE t_myisam_DEFAULT;
       
      10.3 09bd2138522787a4e0b015695c462903f4a9e728 2019-02-22
      10.4 5b4d6595d26aaaf0bf8bb3c9171cf1da96306a7c 2019-02-21
      No replay on 10.2 af6fdc13072cc310cf17fd3b28c749515d9c563c 2019-02-19
              (ALTER ... COLUMN ...INPLACE is not supported and DEFAULT seems to be COPY).
      

        Attachments

          Issue Links

            Activity

              People

              • Assignee:
                marko Marko Mäkelä
                Reporter:
                mleich Matthias Leich
              • Votes:
                0 Vote for this issue
                Watchers:
                3 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: