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-18041Database corruption after renaming a prefix-indexed column
Closed
duplicates
MDEV-16696Changing column case, does not update constraint case
Closed
relates to
MDEV-5800indexes on virtual (not materialized) columns
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 - 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 - 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;
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 - Commit message fixed.
Test for FOREIGN KEY added.
10.2 PR opened.
Looks like it's ready for a more thorough testing?
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 - 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.
Bug
Critical
MDEV-5800 indexes on virtual (not materialized) columns
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.