[MDEV-13671] InnoDB should use case-insensitive column name comparisons like the rest of the server - Jira

Details

Type: Bug
Status: Closed (View Workflow)
Priority: Critical
Resolution: Fixed
Affects Version/s: 5.5(EOL), 10.0(EOL), 10.1(EOL), 10.2(EOL), 10.3(EOL), 10.4(EOL)
Fix Version/s: 10.3.11, 10.2.19, 10.1.38, 10.0.38
Component/s: Storage Engine - InnoDB, Storage Engine - XtraDB
Labels:
- compat56
- corruption
- ddl

Description

One way how the InnoDB internal data dictionary can get out of sync with the .frm files is when columns are renamed between upper case and lower case, so that there is no change when considering a case-insensitive match.

A change in MySQL 5.6.22 adjusted fill_alter_inplace_info() so that for InnoDB, it performs a case-sensitive column name comparison, while for other storage engines it uses the old my_strcasecmp().

A more robust fix would be to make all column name comparisons in InnoDB case insensitive. This is safe to do, because there are no indexes defined on the InnoDB internal data dictionary tables SYS_COLUMNS and SYS_FOREIGN_COLS.

Attachments

Issue Links

causes

MDEV-18041 Database corruption after renaming a prefix-indexed column

Closed

duplicates

MDEV-16696 Changing column case, does not update constraint case

Closed

relates to

MDEV-5800 indexes on virtual (not materialized) columns

Closed

MDEV-13625 Merge InnoDB test cases from MySQL 5.6

Closed

MDEV-13838 Wrong result after altering a partitioned table

Closed

MDEV-16557 Remove INNOBASE_SHARE::idx_trans_tbl

Closed

MDEV-23852 alter table rename column to uppercase doesn't work

Closed

(2 relates to)

Activity

Ascending order - Click to sort in descending order

Elena Stepanova added a comment - 2017-08-29 21:36 - edited

Test case from the MySQL commit
CREATE TABLE t1(c1 INT NOT NULL, PRIMARY KEY(c1))ENGINE=INNODB;
CREATE TABLE t2(c2 INT NOT NULL, FOREIGN KEY(c2) REFERENCES t1(c1))ENGINE=INNODB;
SHOW CREATE TABLE t1;
SHOW CREATE TABLE t2;
ALTER TABLE t1 CHANGE COLUMN c1 C1 INT;
SHOW CREATE TABLE t1;
SHOW CREATE TABLE t2;
ALTER TABLE t1 CHANGE COLUMN C1 c5 INT;
SHOW CREATE TABLE t1;
SHOW CREATE TABLE t2;
DROP TABLE t2, t1;

10.0 debug e634fdcd5b5ae8e8db38aa71307c59be295abc1f
2017-08-30 00:35:56 7f643cb18700 InnoDB: Assertion failure in thread 140068491724544 in file dict0mem.cc line 505
InnoDB: Failing assertion: !strcmp(from, s)

#5 0x00007f643a7ed3fa in abort () from /lib/x86_64-linux-gnu/libc.so.6
#6 0x0000000000d6985b in dict_mem_table_col_rename (table=0x7f64264140f8, nth_col=0, from=0x7f642642a3c9 "C1", to=0x7f64265a4770 "c5") at /data/src/10.0/storage/xtradb/dict/dict0mem.cc:505
#7 0x0000000000b6e015 in innobase_rename_columns_cache (ha_alter_info=0x7f643cb15260, table=0x7f642649e470, user_table=0x7f64264140f8) at /data/src/10.0/storage/xtradb/handler/handler0alter.cc:4791
#8 0x0000000000b6fea8 in ha_innobase::commit_inplace_alter_table (this=0x7f6426482088, altered_table=0x7f6426516070, ha_alter_info=0x7f643cb15260, commit=true) at /data/src/10.0/storage/xtradb/handler/handler0alter.cc:6036
#9 0x0000000000840cf3 in handler::ha_commit_inplace_alter_table (this=0x7f6426482088, altered_table=0x7f6426516070, ha_alter_info=0x7f643cb15260, commit=true) at /data/src/10.0/sql/handler.cc:4226
#10 0x00000000006ff859 in mysql_inplace_alter_table (thd=0x7f642f33b070, table_list=0x7f64265a4180, table=0x7f642649e470, altered_table=0x7f6426516070, ha_alter_info=0x7f643cb15260, inplace_supported=HA_ALTER_INPLACE_NO_LOCK_AFTER_PREPARE, target_mdl_request=0x7f643cb14cb0, alter_ctx=0x7f643cb15840) at /data/src/10.0/sql/sql_table.cc:7145
#11 0x0000000000703e5b in mysql_alter_table (thd=0x7f642f33b070, new_db=0x7f64265a4760 "test", new_name=0x0, create_info=0x7f643cb164e0, table_list=0x7f64265a4180, alter_info=0x7f643cb16450, order_num=0, order=0x0, ignore=false) at /data/src/10.0/sql/sql_table.cc:8949
#12 0x000000000076c58b in Sql_cmd_alter_table::execute (this=0x7f64265a4850, thd=0x7f642f33b070) at /data/src/10.0/sql/sql_alter.cc:312
#13 0x0000000000652daa in mysql_execute_command (thd=0x7f642f33b070) at /data/src/10.0/sql/sql_parse.cc:5123
#14 0x00000000006561e0 in mysql_parse (thd=0x7f642f33b070, rawbuf=0x7f64265a4088 "ALTER TABLE t1 CHANGE COLUMN C1 c5 INT", length=38, parser_state=0x7f643cb17640) at /data/src/10.0/sql/sql_parse.cc:6578
#15 0x0000000000648d0d in dispatch_command (command=COM_QUERY, thd=0x7f642f33b070, packet=0x7f642f65d071 "ALTER TABLE t1 CHANGE COLUMN C1 c5 INT", packet_length=38) at /data/src/10.0/sql/sql_parse.cc:1305
#16 0x0000000000648027 in do_command (thd=0x7f642f33b070) at /data/src/10.0/sql/sql_parse.cc:999
#17 0x0000000000767cde in do_handle_one_connection (thd_arg=0x7f642f33b070) at /data/src/10.0/sql/sql_connect.cc:1377
#18 0x0000000000767a50 in handle_one_connection (arg=0x7f642f33b070) at /data/src/10.0/sql/sql_connect.cc:1292
#19 0x0000000000ac7cd6 in pfs_spawn_thread (arg=0x7f642f2801f0) at /data/src/10.0/storage/perfschema/pfs.cc:1860
#20 0x00007f643c756494 in start_thread (arg=0x7f643cb18700) at pthread_create.c:333
#21 0x00007f643a8a193f in clone () from /lib/x86_64-linux-gnu/libc.so.6

Elena Stepanova added a comment - 2017-08-29 21:36 - edited Test case from the MySQL commit CREATE TABLE t1(c1 INT NOT NULL , PRIMARY KEY (c1))ENGINE=INNODB; CREATE TABLE t2(c2 INT NOT NULL , FOREIGN KEY (c2) REFERENCES t1(c1))ENGINE=INNODB; SHOW CREATE TABLE t1; SHOW CREATE TABLE t2; ALTER TABLE t1 CHANGE COLUMN c1 C1 INT ; SHOW CREATE TABLE t1; SHOW CREATE TABLE t2; ALTER TABLE t1 CHANGE COLUMN C1 c5 INT ; SHOW CREATE TABLE t1; SHOW CREATE TABLE t2; DROP TABLE t2, t1; 10.0 debug e634fdcd5b5ae8e8db38aa71307c59be295abc1f 2017-08-30 00:35:56 7f643cb18700 InnoDB: Assertion failure in thread 140068491724544 in file dict0mem.cc line 505 InnoDB: Failing assertion: !strcmp(from, s) #5 0x00007f643a7ed3fa in abort () from /lib/x86_64-linux-gnu/libc.so.6 #6 0x0000000000d6985b in dict_mem_table_col_rename (table=0x7f64264140f8, nth_col=0, from=0x7f642642a3c9 "C1", to=0x7f64265a4770 "c5") at /data/src/10.0/storage/xtradb/dict/dict0mem.cc:505 #7 0x0000000000b6e015 in innobase_rename_columns_cache (ha_alter_info=0x7f643cb15260, table=0x7f642649e470, user_table=0x7f64264140f8) at /data/src/10.0/storage/xtradb/handler/handler0alter.cc:4791 #8 0x0000000000b6fea8 in ha_innobase::commit_inplace_alter_table (this=0x7f6426482088, altered_table=0x7f6426516070, ha_alter_info=0x7f643cb15260, commit=true) at /data/src/10.0/storage/xtradb/handler/handler0alter.cc:6036 #9 0x0000000000840cf3 in handler::ha_commit_inplace_alter_table (this=0x7f6426482088, altered_table=0x7f6426516070, ha_alter_info=0x7f643cb15260, commit=true) at /data/src/10.0/sql/handler.cc:4226 #10 0x00000000006ff859 in mysql_inplace_alter_table (thd=0x7f642f33b070, table_list=0x7f64265a4180, table=0x7f642649e470, altered_table=0x7f6426516070, ha_alter_info=0x7f643cb15260, inplace_supported=HA_ALTER_INPLACE_NO_LOCK_AFTER_PREPARE, target_mdl_request=0x7f643cb14cb0, alter_ctx=0x7f643cb15840) at /data/src/10.0/sql/sql_table.cc:7145 #11 0x0000000000703e5b in mysql_alter_table (thd=0x7f642f33b070, new_db=0x7f64265a4760 "test", new_name=0x0, create_info=0x7f643cb164e0, table_list=0x7f64265a4180, alter_info=0x7f643cb16450, order_num=0, order=0x0, ignore=false) at /data/src/10.0/sql/sql_table.cc:8949 #12 0x000000000076c58b in Sql_cmd_alter_table::execute (this=0x7f64265a4850, thd=0x7f642f33b070) at /data/src/10.0/sql/sql_alter.cc:312 #13 0x0000000000652daa in mysql_execute_command (thd=0x7f642f33b070) at /data/src/10.0/sql/sql_parse.cc:5123 #14 0x00000000006561e0 in mysql_parse (thd=0x7f642f33b070, rawbuf=0x7f64265a4088 "ALTER TABLE t1 CHANGE COLUMN C1 c5 INT", length=38, parser_state=0x7f643cb17640) at /data/src/10.0/sql/sql_parse.cc:6578 #15 0x0000000000648d0d in dispatch_command (command=COM_QUERY, thd=0x7f642f33b070, packet=0x7f642f65d071 "ALTER TABLE t1 CHANGE COLUMN C1 c5 INT", packet_length=38) at /data/src/10.0/sql/sql_parse.cc:1305 #16 0x0000000000648027 in do_command (thd=0x7f642f33b070) at /data/src/10.0/sql/sql_parse.cc:999 #17 0x0000000000767cde in do_handle_one_connection (thd_arg=0x7f642f33b070) at /data/src/10.0/sql/sql_connect.cc:1377 #18 0x0000000000767a50 in handle_one_connection (arg=0x7f642f33b070) at /data/src/10.0/sql/sql_connect.cc:1292 #19 0x0000000000ac7cd6 in pfs_spawn_thread (arg=0x7f642f2801f0) at /data/src/10.0/storage/perfschema/pfs.cc:1860 #20 0x00007f643c756494 in start_thread (arg=0x7f643cb18700) at pthread_create.c:333 #21 0x00007f643a8a193f in clone () from /lib/x86_64-linux-gnu/libc.so.6

Marko Mäkelä added a comment - 2018-10-17 10:37

Starting with ~~MDEV-5800~~ (MariaDB Server 10.2), this affects virtual columns as well:

--source include/have_innodb.inc

CREATE TABLE t1 (a INT, b INT GENERATED ALWAYS AS (a) VIRTUAL) ENGINE = InnoDB;

ALTER TABLE t1 CHANGE COLUMN a A INT;

SHOW CREATE TABLE t1;

DROP TABLE t1;

10.2 2308b9afec559cd8c5717007a7ad6821c374370d

mysqld: /mariadb/10.2/storage/innobase/handler/ha_innodb.cc:5985: void innobase_build_v_templ(const TABLE*, const dict_table_t*, dict_vcol_templ_t*, const dict_add_v_col_t*, bool): Assertion `!ut_strcmp(name, field->field_name)' failed.

Marko Mäkelä added a comment - 2018-10-17 10:37 Starting with MDEV-5800 (MariaDB Server 10.2), this affects virtual columns as well: --source include/have_innodb.inc CREATE TABLE t1 (a INT , b INT GENERATED ALWAYS AS (a) VIRTUAL) ENGINE = InnoDB; ALTER TABLE t1 CHANGE COLUMN a A INT ; SHOW CREATE TABLE t1; DROP TABLE t1; 10.2 2308b9afec559cd8c5717007a7ad6821c374370d mysqld: /mariadb/10.2/storage/innobase/handler/ha_innodb.cc:5985: void innobase_build_v_templ(const TABLE*, const dict_table_t*, dict_vcol_templ_t*, const dict_add_v_col_t*, bool): Assertion `!ut_strcmp(name, field->field_name)' failed.

Eugene Kosov (Inactive) added a comment - 2018-10-26 13:23

Crash of 10.2 can't be fixed in 10.0 because relevant code is missed there. Fix is: replace strcmp() with my_strcasecmp().

Eugene Kosov (Inactive) added a comment - 2018-10-26 13:23 Crash of 10.2 can't be fixed in 10.0 because relevant code is missed there. Fix is: replace strcmp() with my_strcasecmp() .

Marko Mäkelä added a comment - 2018-10-26 16:51

Right, 10.2 introduced virtual columns inside InnoDB. Before that, virtual columns were ‘filtered out’ from the metadata that was passed to InnoDB. Hence, we will need separate fixes for 10.2 and earlier versions.

Marko Mäkelä added a comment - 2018-10-26 16:51 Right, 10.2 introduced virtual columns inside InnoDB. Before that, virtual columns were ‘filtered out’ from the metadata that was passed to InnoDB. Hence, we will need separate fixes for 10.2 and earlier versions.

Marko Mäkelä added a comment - 2018-10-29 12:25

The 10.0 fix looks OK, but I’d like to see a test that restarts the server after changing the case of a column name, and possibly adding a FOREIGN KEY constraint referring to such a renamed column in a parent table. Would FOREIGN KEY constraints on the renamed column keep working?

Also, I would like to see a separate 10.2 version of the fix.

Marko Mäkelä added a comment - 2018-10-29 12:25 The 10.0 fix looks OK, but I’d like to see a test that restarts the server after changing the case of a column name, and possibly adding a FOREIGN KEY constraint referring to such a renamed column in a parent table. Would FOREIGN KEY constraints on the renamed column keep working? Also, I would like to see a separate 10.2 version of the fix.

Eugene Kosov (Inactive) added a comment - 2018-10-29 20:06

I'm not sure I understood you correctly. This one test?

CREATE TABLE t1(c1 INT NOT NULL, PRIMARY KEY(c1))ENGINE=INNODB;

CREATE TABLE t2(c2 INT NOT NULL)ENGINE=INNODB;

insert into t1 values (1);

insert into t2 values (1);

alter table t1 change c1 C1 int not null, algorithm=inplace;

alter table t2 add foreign key(c2) references t1(C1);

--source include/restart_mysqld.inc

show create table t2;

--error ER_ROW_IS_REFERENCED_2

delete from t1 where c1=1;

--error ER_ROW_IS_REFERENCED_2

delete from t1 where C1=1;

drop table t2, t1;

Eugene Kosov (Inactive) added a comment - 2018-10-29 20:06 I'm not sure I understood you correctly. This one test? CREATE TABLE t1(c1 INT NOT NULL , PRIMARY KEY (c1))ENGINE=INNODB; CREATE TABLE t2(c2 INT NOT NULL )ENGINE=INNODB; insert into t1 values (1); insert into t2 values (1); alter table t1 change c1 C1 int not null , algorithm=inplace; alter table t2 add foreign key (c2) references t1(C1); --source include/restart_mysqld.inc show create table t2; --error ER_ROW_IS_REFERENCED_2 delete from t1 where c1=1; --error ER_ROW_IS_REFERENCED_2 delete from t1 where C1=1; drop table t2, t1;

Eugene Kosov (Inactive) added a comment - 2018-11-01 10:49

Commit message fixed.
Test for FOREIGN KEY added.
10.2 PR opened.

Looks like it's ready for a more thorough testing?

Eugene Kosov (Inactive) added a comment - 2018-11-01 10:49 Commit message fixed. Test for FOREIGN KEY added. 10.2 PR opened. Looks like it's ready for a more thorough testing?

Marko Mäkelä added a comment - 2018-11-02 19:29

I pushed this to bb-10.2-marko together with ~~MDEV-17376~~ for more thorough testing.
While rebasing, I slightly changed the test for FOREIGN KEY, so that half of the column renames are done after server restart. It passes also in that case.

Marko Mäkelä added a comment - 2018-11-02 19:29 I pushed this to bb-10.2-marko together with MDEV-17376 for more thorough testing. While rebasing, I slightly changed the test for FOREIGN KEY , so that half of the column renames are done after server restart. It passes also in that case.

Matthias Leich added a comment - 2018-11-05 15:54

During RQG testing of bb-10.2-marko no problems which might be related to ~~MDEV-13671~~ or MDEV-1376 found.

Matthias Leich added a comment - 2018-11-05 15:54 During RQG testing of bb-10.2-marko no problems which might be related to MDEV-13671 or MDEV-1376 found.

People

Assignee:: Marko Mäkelä

Reporter:: Marko Mäkelä

Votes:: 1 Vote for this issue

Watchers:: 6 Start watching this issue

Dates

Created:: 2017-08-29 14:49

Updated:: 2020-10-20 09:17

Resolved:: 2018-11-05 19:00

Git Integration

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