[MDEV-20190] Instant operation fails when add column and collation change on non-indexed column Created: 2019-07-27  Updated: 2020-01-21  Resolved: 2019-11-25

Status: Closed
Project: MariaDB Server
Component/s: Data Definition - Alter Table, Storage Engine - InnoDB
Affects Version/s: 10.4
Fix Version/s: 10.4.11

Type: Bug Priority: Major
Reporter: Elena Stepanova Assignee: Marko Mäkelä
Resolution: Fixed Votes: 0
Labels: None

Issue Links:
Relates
relates to MDEV-20191 Assertion `index.fields[i].col->same_... Closed
relates to MDEV-21539 Assertion `!((new_col->prtype ^ col->... Closed

 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.

 Comments   
Comment by Thirunarayanan Balathandayuthapani [ 2019-09-17 ]

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;
 
        }
 
 };

Comment by Thirunarayanan Balathandayuthapani [ 2019-09-17 ]

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;

Comment by Marko Mäkelä [ 2019-09-17 ]

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.)

Comment by Thirunarayanan Balathandayuthapani [ 2019-11-23 ]

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

Comment by Marko Mäkelä [ 2019-11-25 ]

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

Generated at Thu Feb 08 08:57:31 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.