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

Instant operation fails when add column and collation change on non-indexed column

Details

    Description

      --source include/have_innodb.inc
       
      CREATE TABLE t1 (a CHAR) ENGINE=InnoDB;
      ALTER TABLE t1 DEFAULT COLLATE = latin1_general_cs;
      ALTER TABLE t1 ADD b INT NOT NULL, MODIFY a CHAR, ALGORITHM=INSTANT;
       
      # Cleanup
      DROP TABLE t1;
      

      10.4 e9c1701e

      mysqld: /data/src/10.4/storage/innobase/handler/handler0alter.cc:539: bool dict_table_t::instant_column(const dict_table_t&, const ulint*): Assertion `!((c.prtype ^ o->prtype) & ~(256U | (16384U|32768U) | 4096U))' failed.
      190727 15:17:50 [ERROR] mysqld got signal 6 ;
       
      #7  0x00007f0887c24f12 in __GI___assert_fail (assertion=0x560a812749d8 "!((c.prtype ^ o->prtype) & ~(256U | (16384U|32768U) | 4096U))", file=0x560a81273f38 "/data/src/10.4/storage/innobase/handler/handler0alter.cc", line=539, function=0x560a8127b800 <dict_table_t::instant_column(dict_table_t const&, unsigned long const*)::__PRETTY_FUNCTION__> "bool dict_table_t::instant_column(const dict_table_t&, const ulint*)") at assert.c:101
      #8  0x0000560a80a347a7 in dict_table_t::instant_column (this=0x7f083800ac38, table=..., col_map=0x7f0838142330) at /data/src/10.4/storage/innobase/handler/handler0alter.cc:537
      #9  0x0000560a80a374cc in ha_innobase_inplace_ctx::instant_column (this=0x7f08380148d8) at /data/src/10.4/storage/innobase/handler/handler0alter.cc:1080
      #10 0x0000560a80a1c10f in innobase_instant_try (ha_alter_info=0x7f0880ff17c0, ctx=0x7f08380148d8, altered_table=0x7f0880ff1850, table=0x7f0838151630, trx=0x7f0881a8f268) at /data/src/10.4/storage/innobase/handler/handler0alter.cc:5645
      #11 0x0000560a80a3a2e3 in commit_try_norebuild (ha_alter_info=0x7f0880ff17c0, ctx=0x7f08380148d8, altered_table=0x7f0880ff1850, old_table=0x7f0838151630, trx=0x7f0881a8f268, table_name=0x7f083800be8d "t1") at /data/src/10.4/storage/innobase/handler/handler0alter.cc:10170
      #12 0x0000560a80a2c04d in ha_innobase::commit_inplace_alter_table (this=0x7f0838152498, altered_table=0x7f0880ff1850, ha_alter_info=0x7f0880ff17c0, commit=true) at /data/src/10.4/storage/innobase/handler/handler0alter.cc:10924
      #13 0x0000560a807bdc8e in handler::ha_commit_inplace_alter_table (this=0x7f0838152498, altered_table=0x7f0880ff1850, ha_alter_info=0x7f0880ff17c0, commit=true) at /data/src/10.4/sql/handler.cc:4575
      #14 0x0000560a8055a278 in mysql_inplace_alter_table (thd=0x7f0838000b00, table_list=0x7f0838013250, table=0x7f0838151630, altered_table=0x7f0880ff1850, ha_alter_info=0x7f0880ff17c0, inplace_supported=HA_ALTER_INPLACE_INSTANT, target_mdl_request=0x7f0880ff2620, alter_ctx=0x7f0880ff3150) at /data/src/10.4/sql/sql_table.cc:7718
      #15 0x0000560a80560b25 in mysql_alter_table (thd=0x7f0838000b00, new_db=0x7f08380052b0, new_name=0x7f08380056b8, create_info=0x7f0880ff3d30, table_list=0x7f0838013250, alter_info=0x7f0880ff3c70, order_num=0, order=0x0, ignore=false) at /data/src/10.4/sql/sql_table.cc:10029
      #16 0x0000560a805f2413 in Sql_cmd_alter_table::execute (this=0x7f0838013b48, thd=0x7f0838000b00) at /data/src/10.4/sql/sql_alter.cc:508
      #17 0x0000560a804800e5 in mysql_execute_command (thd=0x7f0838000b00) at /data/src/10.4/sql/sql_parse.cc:6098
      #18 0x0000560a80485355 in mysql_parse (thd=0x7f0838000b00, rawbuf=0x7f0838013118 "ALTER TABLE t1 ADD b INT NOT NULL, MODIFY a CHAR, ALGORITHM=INSTANT", length=67, parser_state=0x7f0880ff5180, is_com_multi=false, is_next_command=false) at /data/src/10.4/sql/sql_parse.cc:7908
      #19 0x0000560a804715fe in dispatch_command (command=COM_QUERY, thd=0x7f0838000b00, packet=0x7f083819df71 "ALTER TABLE t1 ADD b INT NOT NULL, MODIFY a CHAR, ALGORITHM=INSTANT", packet_length=67, is_com_multi=false, is_next_command=false) at /data/src/10.4/sql/sql_parse.cc:1843
      #20 0x0000560a8046fd44 in do_command (thd=0x7f0838000b00) at /data/src/10.4/sql/sql_parse.cc:1360
      #21 0x0000560a805e9282 in do_handle_one_connection (connect=0x560a83c06950) at /data/src/10.4/sql/sql_connect.cc:1404
      #22 0x0000560a805e8fd1 in handle_one_connection (arg=0x560a83c06950) at /data/src/10.4/sql/sql_connect.cc:1306
      #23 0x0000560a80f1496f in pfs_spawn_thread (arg=0x560a83b852d0) at /data/src/10.4/storage/perfschema/pfs.cc:1862
      #24 0x00007f08897994a4 in start_thread (arg=0x7f0880ff6700) at pthread_create.c:456
      #25 0x00007f0887ce1d0f in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:97
      

      The test case is not applicable to 10.3 (ALTER with ALGORITHM=INSTANT is rejected).
      Non-debug build doesn't crash.

      Attachments

        Issue Links

          Activity

            The following patch relaxes the debug assertion. Changing collation on non-indexed column + instant add operation leads to this failure.

            diff --git a/mysql-test/suite/innodb/r/instant_alter_bugs.result b/mysql-test/suite/innodb/r/instant_alter_bugs.result
            index 045b7468049..959170bdf22 100644
            --- a/mysql-test/suite/innodb/r/instant_alter_bugs.result
            +++ b/mysql-test/suite/innodb/r/instant_alter_bugs.result
            @@ -283,3 +283,17 @@ ALTER TABLE t CHANGE COLUMN alpha a INT WITHOUT SYSTEM VERSIONING,
             ALGORITHM=INSTANT;
             DROP TABLE t;
             set @@system_versioning_alter_history = error;
            +#
            +# MDEV-20190 Instant operation fails when add column and collation
            +#              change on non-indexed column
            +#
            +CREATE TABLE t1 (a CHAR)ENGINE=INNODB;
            +ALTER TABLE t1 DEFAULT COLLATE= latin1_general_cs;
            +ALTER TABLE t1 ADD COLUMN b INT NOT NULL, MODIFY a CHAR, ALGORITHM=INSTANT;
            +SHOW CREATE TABLE t1;
            +Table  Create Table
            +t1     CREATE TABLE `t1` (
            +  `a` char(1) COLLATE latin1_general_cs DEFAULT NULL,
            +  `b` int(11) NOT NULL
            +) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_general_cs
            +DROP TABLE t1;
            diff --git a/mysql-test/suite/innodb/t/instant_alter_bugs.test b/mysql-test/suite/innodb/t/instant_alter_bugs.test
            index fda8e88d70c..f244a6be533 100644
            --- a/mysql-test/suite/innodb/t/instant_alter_bugs.test
            +++ b/mysql-test/suite/innodb/t/instant_alter_bugs.test
            @@ -293,3 +293,14 @@ ALTER TABLE t CHANGE COLUMN alpha a INT WITHOUT SYSTEM VERSIONING,
             ALGORITHM=INSTANT;
             DROP TABLE t;
             set @@system_versioning_alter_history = error;
            +
            +--echo #
            +--echo # MDEV-20190 Instant operation fails when add column and collation
            +--echo #               change on non-indexed column
            +--echo #
            +
            +CREATE TABLE t1 (a CHAR)ENGINE=INNODB;
            +ALTER TABLE t1 DEFAULT COLLATE= latin1_general_cs;
            +ALTER TABLE t1 ADD COLUMN b INT NOT NULL, MODIFY a CHAR, ALGORITHM=INSTANT;
            +SHOW CREATE TABLE t1;
            +DROP TABLE t1;
            diff --git a/storage/innobase/handler/handler0alter.cc b/storage/innobase/handler/handler0alter.cc
            index 5a7360e516a..da27a8f9ab3 100644
            --- a/storage/innobase/handler/handler0alter.cc
            +++ b/storage/innobase/handler/handler0alter.cc
            @@ -537,10 +537,6 @@ inline bool dict_table_t::instant_column(const dict_table_t& table,
             
                            if (const dict_col_t* o = find(old_cols, col_map, n_cols, i)) {
                                    c.def_val = o->def_val;
            -                       DBUG_ASSERT(!((c.prtype ^ o->prtype)
            -                                     & ~(DATA_NOT_NULL | DATA_VERSIONED
            -                                         | DATA_LONG_TRUE_VARCHAR)));
            -                       DBUG_ASSERT(c.mtype == o->mtype);
                                    DBUG_ASSERT(c.len >= o->len);
             
                                    if (o->vers_sys_start()) {
            diff --git a/storage/innobase/include/dict0mem.h b/storage/innobase/include/dict0mem.h
            index e72b24da9f5..6003cbadbb0 100644
            --- a/storage/innobase/include/dict0mem.h
            +++ b/storage/innobase/include/dict0mem.h
            @@ -680,13 +680,9 @@ struct dict_col_t{
                    @return whether the columns have the same format */
                    bool same_format(const dict_col_t& other) const
                    {
            -               return mtype == other.mtype
            -                       && len >= other.len
            +               return  len >= other.len
                                    && mbminlen == other.mbminlen
            -                       && mbmaxlen == other.mbmaxlen
            -                       && !((prtype ^ other.prtype)
            -                            & ~(DATA_NOT_NULL | DATA_VERSIONED
            -                                | DATA_LONG_TRUE_VARCHAR));
            +                       && mbmaxlen == other.mbmaxlen;
                    }
             };
            
            

            thiru Thirunarayanan Balathandayuthapani added a comment - The following patch relaxes the debug assertion. Changing collation on non-indexed column + instant add operation leads to this failure. diff --git a/mysql-test/suite/innodb/r/instant_alter_bugs.result b/mysql-test/suite/innodb/r/instant_alter_bugs.result index 045b7468049..959170bdf22 100644 --- a/mysql-test/suite/innodb/r/instant_alter_bugs.result +++ b/mysql-test/suite/innodb/r/instant_alter_bugs.result @@ -283,3 +283,17 @@ ALTER TABLE t CHANGE COLUMN alpha a INT WITHOUT SYSTEM VERSIONING, ALGORITHM=INSTANT; DROP TABLE t; set @@system_versioning_alter_history = error; +# +# MDEV-20190 Instant operation fails when add column and collation +# change on non-indexed column +# +CREATE TABLE t1 (a CHAR)ENGINE=INNODB; +ALTER TABLE t1 DEFAULT COLLATE= latin1_general_cs; +ALTER TABLE t1 ADD COLUMN b INT NOT NULL, MODIFY a CHAR, ALGORITHM=INSTANT; +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` char(1) COLLATE latin1_general_cs DEFAULT NULL, + `b` int(11) NOT NULL +) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_general_cs +DROP TABLE t1; diff --git a/mysql-test/suite/innodb/t/instant_alter_bugs.test b/mysql-test/suite/innodb/t/instant_alter_bugs.test index fda8e88d70c..f244a6be533 100644 --- a/mysql-test/suite/innodb/t/instant_alter_bugs.test +++ b/mysql-test/suite/innodb/t/instant_alter_bugs.test @@ -293,3 +293,14 @@ ALTER TABLE t CHANGE COLUMN alpha a INT WITHOUT SYSTEM VERSIONING, ALGORITHM=INSTANT; DROP TABLE t; set @@system_versioning_alter_history = error; + +--echo # +--echo # MDEV-20190 Instant operation fails when add column and collation +--echo # change on non-indexed column +--echo # + +CREATE TABLE t1 (a CHAR)ENGINE=INNODB; +ALTER TABLE t1 DEFAULT COLLATE= latin1_general_cs; +ALTER TABLE t1 ADD COLUMN b INT NOT NULL, MODIFY a CHAR, ALGORITHM=INSTANT; +SHOW CREATE TABLE t1; +DROP TABLE t1; diff --git a/storage/innobase/handler/handler0alter.cc b/storage/innobase/handler/handler0alter.cc index 5a7360e516a..da27a8f9ab3 100644 --- a/storage/innobase/handler/handler0alter.cc +++ b/storage/innobase/handler/handler0alter.cc @@ -537,10 +537,6 @@ inline bool dict_table_t::instant_column(const dict_table_t& table, if (const dict_col_t* o = find(old_cols, col_map, n_cols, i)) { c.def_val = o->def_val; - DBUG_ASSERT(!((c.prtype ^ o->prtype) - & ~(DATA_NOT_NULL | DATA_VERSIONED - | DATA_LONG_TRUE_VARCHAR))); - DBUG_ASSERT(c.mtype == o->mtype); DBUG_ASSERT(c.len >= o->len); if (o->vers_sys_start()) { diff --git a/storage/innobase/include/dict0mem.h b/storage/innobase/include/dict0mem.h index e72b24da9f5..6003cbadbb0 100644 --- a/storage/innobase/include/dict0mem.h +++ b/storage/innobase/include/dict0mem.h @@ -680,13 +680,9 @@ struct dict_col_t{ @return whether the columns have the same format */ bool same_format(const dict_col_t& other) const { - return mtype == other.mtype - && len >= other.len + return len >= other.len && mbminlen == other.mbminlen - && mbmaxlen == other.mbmaxlen - && !((prtype ^ other.prtype) - & ~(DATA_NOT_NULL | DATA_VERSIONED - | DATA_LONG_TRUE_VARCHAR)); + && mbmaxlen == other.mbmaxlen; } };

            will add the following patch to cover MDEV-20191 test case.

            diff --git a/mysql-test/suite/innodb/r/instant_alter_bugs.result b/mysql-test/suite/innodb/r/instant_alter_bugs.result
            index 959170bdf22..02742dea501 100644
            --- a/mysql-test/suite/innodb/r/instant_alter_bugs.result
            +++ b/mysql-test/suite/innodb/r/instant_alter_bugs.result
            @@ -297,3 +297,12 @@ t1 CREATE TABLE `t1` (
               `b` int(11) NOT NULL
             ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_general_cs
             DROP TABLE t1;
            +CREATE TABLE t1 (a CHAR NOT NULL) ENGINE=InnoDB ROW_FORMAT=REDUNDANT;
            +ALTER TABLE t1 DEFAULT COLLATE = latin1_general_cs;
            +ALTER TABLE t1 MODIFY a CHAR, ALGORITHM=INSTANT;
            +SHOW CREATE TABLE t1;
            +Table  Create Table
            +t1     CREATE TABLE `t1` (
            +  `a` char(1) COLLATE latin1_general_cs DEFAULT NULL
            +) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_general_cs ROW_FORMAT=REDUNDANT
            +DROP TABLE t1;
            diff --git a/mysql-test/suite/innodb/t/instant_alter_bugs.test b/mysql-test/suite/innodb/t/instant_alter_bugs.test
            index f244a6be533..bdc2b411daa 100644
            --- a/mysql-test/suite/innodb/t/instant_alter_bugs.test
            +++ b/mysql-test/suite/innodb/t/instant_alter_bugs.test
            @@ -304,3 +304,9 @@ ALTER TABLE t1 DEFAULT COLLATE= latin1_general_cs;
             ALTER TABLE t1 ADD COLUMN b INT NOT NULL, MODIFY a CHAR, ALGORITHM=INSTANT;
             SHOW CREATE TABLE t1;
             DROP TABLE t1;
            +
            +CREATE TABLE t1 (a CHAR NOT NULL) ENGINE=InnoDB ROW_FORMAT=REDUNDANT;
            +ALTER TABLE t1 DEFAULT COLLATE = latin1_general_cs;
            +ALTER TABLE t1 MODIFY a CHAR, ALGORITHM=INSTANT;
            +SHOW CREATE TABLE t1;
            +DROP TABLE t1;
            
            

            thiru Thirunarayanan Balathandayuthapani added a comment - will add the following patch to cover MDEV-20191 test case. diff --git a/mysql-test/suite/innodb/r/instant_alter_bugs.result b/mysql-test/suite/innodb/r/instant_alter_bugs.result index 959170bdf22..02742dea501 100644 --- a/mysql-test/suite/innodb/r/instant_alter_bugs.result +++ b/mysql-test/suite/innodb/r/instant_alter_bugs.result @@ -297,3 +297,12 @@ t1 CREATE TABLE `t1` ( `b` int(11) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_general_cs DROP TABLE t1; +CREATE TABLE t1 (a CHAR NOT NULL) ENGINE=InnoDB ROW_FORMAT=REDUNDANT; +ALTER TABLE t1 DEFAULT COLLATE = latin1_general_cs; +ALTER TABLE t1 MODIFY a CHAR, ALGORITHM=INSTANT; +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` char(1) COLLATE latin1_general_cs DEFAULT NULL +) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_general_cs ROW_FORMAT=REDUNDANT +DROP TABLE t1; diff --git a/mysql-test/suite/innodb/t/instant_alter_bugs.test b/mysql-test/suite/innodb/t/instant_alter_bugs.test index f244a6be533..bdc2b411daa 100644 --- a/mysql-test/suite/innodb/t/instant_alter_bugs.test +++ b/mysql-test/suite/innodb/t/instant_alter_bugs.test @@ -304,3 +304,9 @@ ALTER TABLE t1 DEFAULT COLLATE= latin1_general_cs; ALTER TABLE t1 ADD COLUMN b INT NOT NULL, MODIFY a CHAR, ALGORITHM=INSTANT; SHOW CREATE TABLE t1; DROP TABLE t1; + +CREATE TABLE t1 (a CHAR NOT NULL) ENGINE=InnoDB ROW_FORMAT=REDUNDANT; +ALTER TABLE t1 DEFAULT COLLATE = latin1_general_cs; +ALTER TABLE t1 MODIFY a CHAR, ALGORITHM=INSTANT; +SHOW CREATE TABLE t1; +DROP TABLE t1;

            I think that we should do a better effort of validating mtype. Yes, it can change DATA_VARMYSQL↔DATA_VARCHAR or DATA_MYSQL↔DATA_CHAR, but only if the prtype is referring to a character-set collation of latin1 type. As far as I can tell, your patch is removing the mtype validation altogether.

            Furthermore, we should assert somewhere that if this change of mtype occurs, any existing indexes on the column will be dropped. (If the column belongs to the primary key, then the table will have to be rebuilt.)

            marko Marko Mäkelä added a comment - I think that we should do a better effort of validating mtype . Yes, it can change DATA_VARMYSQL ↔ DATA_VARCHAR or DATA_MYSQL ↔ DATA_CHAR , but only if the prtype is referring to a character-set collation of latin1 type. As far as I can tell, your patch is removing the mtype validation altogether. Furthermore, we should assert somewhere that if this change of mtype occurs, any existing indexes on the column will be dropped. (If the column belongs to the primary key, then the table will have to be rebuilt.)

            Patch is in bb-10.4-MDEV-20190. Please take a look at it

            thiru Thirunarayanan Balathandayuthapani added a comment - Patch is in bb-10.4- MDEV-20190 . Please take a look at it

            This was a too strict debug assertion only. There should be no impact on non-debug builds.

            marko Marko Mäkelä added a comment - This was a too strict debug assertion only. There should be no impact on non-debug builds.

            People

              marko Marko Mäkelä
              elenst Elena Stepanova
              Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

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