MySQL Bugs: #65701: character set mismatch when altering foreign keys can lead to missing tables

Bug #65701	character set mismatch when altering foreign keys can lead to missing tables
Submitted:	21 Jun 2012 16:26	Modified:	12 Jun 2013 14:53
Reporter:	Ernie Souhrada	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S2 (Serious)
Version:	5.5.24, 5.5.26, 5.6.6	OS:	Linux (CentOS 6.2)
Assigned to:		CPU Architecture:	Any
Tags:	regression

Description:
In a database with multiple tables that have interconnected foreign keys, running ALTER TABLE to change the character set of those FK'ed columns can leave the database in an inconsistent state. 

With MySQL 5.5.24:

SET FOREIGN_KEY_CHECKS=0;
create table dos1 (
  fkto2   char(32) not null primary key,
  fkto3   char(32) not null,
  fkfrom3 char(32) not null,
  index(fkto3),
  index(fkfrom3),
  foreign key (fkto3) references dos3(fkfrom1),
  foreign key (fkto2) references dos2(fkfrom1)
) character set=utf8;

create table dos2 (
  fkfrom1 char(32) not null,
  fkto3 char(32) not null primary key,
  index (fkfrom1),
  foreign key (fkto3) references dos3(fkfrom1)
) character set=utf8;

create table dos3 (
  fkfrom1 char(32) not null,
  fkfrom2 char(32) not null,
  fkto1   char(32) not null primary key,
  index(fkfrom1),
  index(fkfrom2),
  foreign key (fkto1) references dos1(fkfrom3)
) character set=utf8;

INSERT INTO dos1 VALUES ('value_fk1_to_2', 'value_fk1_to_3', 'value_fk3_to_1');
INSERT INTO dos2 VALUES ('value_fk1_to_2', 'value_fk2_to_3');
INSERT INTO dos3 VALUES ('value_fk1_to_1', 'value_fk2_to_3', 'value_fk3_to_1');

alter table dos1 change column fkto2 fkto2 char(32) character set ascii not null, change column fkto3 fkto3 char(32) character set ascii not null, change column fkfrom3 fkfrom3 char(32) character set ascii not null;

set foreign_key_checks=1;

alter table dos2 change column fkto3 fkto3 char(32) character set ascii not null, change column fkfrom1 fkfrom1 char(32) character set ascii not null;

ERROR 1025 (HY000): Error on rename of './oops4/#sql-3c13_14' to './oops4/dos2' (errno: 150)

(root@localhost) [oops5]> show tables like 'dos2';
Empty set (0.00 sec)

From 'SHOW ENGINE INNODB STATUS':
120621  9:14:11 Error in foreign key constraint of table oops5/dos1:
there is no index in referenced table which would contain
the columns as the first columns, or the data types in the
referenced table do not match the ones in table. Constraint:
,
  CONSTRAINT "dos1_ibfk_2" FOREIGN KEY ("fkto2") REFERENCES "dos2" ("fkfrom1")
The index in the foreign key in table is "PRIMARY"
See http://dev.mysql.com/doc/refman/5.5/en/innodb-foreign-key-constraints.html
for correct foreign key definition.
InnoDB: Renaming table `oops5`.<result 2 when explaining filename '#sql2-3c13-1c'> to `oops5`.`dos2` failed!

This bug may be similar to http://bugs.mysql.com/bug.php?id=13778 - but towards the end of that particular bug report it's stated that you're not going to fix the issue with renaming the temporary table back since "it's a minor issue and somewhat easy to recover from".  

I'm not sure that I would agree that it's a minor issue.  Yes, you can get your data back if you do the suggested "SELECT * FROM `#sql2-2f00-2` INTO OUTFILE 'filename'" but you can't get rid of the temporary table and you can't recreate a table in the database with the name of the one that you tried to alter, so I'm not sure if I'd really consider that a recovery.  Plus, now that the database has managed to get into this kind of inconsistent state, it's too easy to continue making things worse.

How to repeat:
Create the tables as shown in the description
Run the ALTER TABLE on "dos1"
SET FOREIGN_KEY_CHECKS=1 (or start a new session)
Attempt to run the ALTER TABLE on "dos2"

Table "dos2" is now stuck in limbo.

Try altering "dos1" or "dos3" - they'll also disappear.

Suggested fix:
InnoDB should check whether or not a proposed ALTER TABLE is going to leave the database in an inconsistent state before actually attempting to do it.  

Alternatively, since running an ALTER with SET FOREIGN_KEY_CHECKS=0 fails if you're changing major data types (INT -> SMALLINT, for example) it should not succeed if you're changing between character sets that use different numbers of bytes per character, either.

Thank you for the report.

Verified as described. Not repeatable with 5.1

Hello!

Note that bug is still repeatable in current 5.6 tree (5.6.12-bzr).

But test case needs slight adjusting.

SET FOREIGN_KEY_CHECKS=0;
create table dos1 (
  fkto2   char(32) not null primary key,
  fkto3   char(32) not null,
  fkfrom3 char(32) not null,
  index(fkto3),
  index(fkfrom3),
  foreign key (fkto3) references dos3(fkfrom1),
  foreign key (fkto2) references dos2(fkfrom1)
) character set=utf8 engine=InnoDB;

create table dos2 (
  fkfrom1 char(32) not null,
  fkto3 char(32) not null primary key,
  index (fkfrom1),
  foreign key (fkto3) references dos3(fkfrom1)
) character set=utf8 engine=InnoDB;

create table dos3 (
  fkfrom1 char(32) not null,
  fkfrom2 char(32) not null,
  fkto1   char(32) not null primary key,
  index(fkfrom1),
  index(fkfrom2),
  foreign key (fkto1) references dos1(fkfrom3)
) character set=utf8 engine=InnoDB;

INSERT INTO dos1 VALUES ('value_fk1_to_2', 'value_fk1_to_3', 'value_fk3_to_1');
INSERT INTO dos2 VALUES ('value_fk1_to_2', 'value_fk2_to_3');
INSERT INTO dos3 VALUES ('value_fk1_to_1', 'value_fk2_to_3', 'value_fk3_to_1');

alter table dos1 change column fkto2 fkto2 char(32) character set ascii not null, change column fkto3 fkto3 char(32) character set ascii not null, change column fkfrom3 fkfrom3 char(32) character set ascii not null;

set foreign_key_checks=1;

--error 1025
alter table dos2 drop primary key;
# "dos2" has gone missing!
--error 1146
show create table dos2;

Added a changelog entry for 5.5.33, 5.6.13, 5.7.2:

"Setting "foreign_key_checks=0" and running "ALTER TABLE" to change the
character set of foreign key columns for a database with multiple tables
with foreign key constraints would leave the database in an inconsistent
state. Subsequent "ALTER TABLE" operations (using the "COPY" algorithm)
with "foreign_key_checks=1" would fail due to the detected inconsistency.
Reversion of the partially executed "ALTER TABLE" operation would also
fail, resulting in the loss of the table being altered. When running the
same "ALTER TABLE" operation with a "RENAME" clause, the inconsistency
would not be detected but if the "ALTER TABLE" operation failed for some
other reason, reversion of the partially executed "ALTER TABLE" would fail
with the same result. 

The bug fix temporarily disables "foreign_key_checks" while the previous table definition is restored. 

Thank you for the bug report.

Still broken in 5.5.37 on debian x86_64