[MDEV-13671] InnoDB should use case-insensitive column name comparisons like the rest of the server Created: 2017-08-29  Updated: 2020-10-20  Resolved: 2018-11-05

Status: Closed
Project: MariaDB Server
Component/s: Storage Engine - InnoDB, Storage Engine - XtraDB
Affects Version/s: 5.5, 10.0, 10.1, 10.2, 10.3, 10.4
Fix Version/s: 10.3.11, 10.2.19, 10.1.38, 10.0.38

Type: Bug Priority: Critical
Reporter: Marko Mäkelä Assignee: Marko Mäkelä
Resolution: Fixed Votes: 1
Labels: compat56, corruption, ddl

Issue Links:
Duplicate
duplicates MDEV-16696 Changing column case, does not update... Closed
Problem/Incident
causes MDEV-18041 Database corruption after renaming a ... Closed
Relates
relates to MDEV-5800 indexes on virtual (not materialized)... Closed
relates to MDEV-13625 Merge InnoDB test cases from MySQL 5.6 Closed
relates to MDEV-13838 Wrong result after altering a partiti... Closed
relates to MDEV-16557 Remove INNOBASE_SHARE::idx_trans_tbl Closed
relates to MDEV-23852 alter table rename column to uppercas... Closed

 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.

 Comments   
Comment by Elena Stepanova [ 2017-08-29 ]

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

Comment by Marko Mäkelä [ 2018-10-17 ]

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.

Comment by Eugene Kosov (Inactive) [ 2018-10-26 ]

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

Comment by Marko Mäkelä [ 2018-10-26 ]

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.

Comment by Marko Mäkelä [ 2018-10-29 ]

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.

Comment by Eugene Kosov (Inactive) [ 2018-10-29 ]

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;

Comment by Eugene Kosov (Inactive) [ 2018-11-01 ]

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

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

Comment by Marko Mäkelä [ 2018-11-02 ]

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.

Comment by Matthias Leich [ 2018-11-05 ]

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

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