[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

View 4 older comments

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.

MariaDB Server