The MySQL manual tells us that regardless of whether or not we use “SET FOREIGN_KEY_CHECKS=0” before making schema changes, InnoDB will not allow a column referenced by a foreign key constraint to be modified in such a way that the foreign key will reference a column with a mismatched data type. For instance, if we have these two tables:
|
1 2 3 4 5 6 7 8 9 10 11 12 |
CREATE TABLE foo ( i INT NOT NULL PRIMARY KEY, j INT NOT NULL, INDEX(j), FOREIGN KEY (j) REFERENCES bar(j) ) ENGINE=INNODB; CREATE TABLE bar ( i INT NOT NULL PRIMARY KEY, j INT NOT NULL, INDEX(j) ) ENGINE=INNODB; |
trying to do something like “ALTER TABLE bar DROP j” or “ALTER TABLE bar MODIFY COLUMN j j SMALLINT NOT NULL” will produce an error unless we first remove the foreign key constraint present in table “foo”. Indeed, if we try it, that’s exactly what happens:
|
1 2 |
(root@localhost) [foobar]> ALTER TABLE bar drop j; ERROR 1025 (HY000): Error on rename of './foobar/#sql-3c13_11' to './foobar/bar' (errno: 150) |
And that’s exactly what we’d hope would happen, too, lest table “foo” suddenly end up with an unsatisfiable FK reference due to a mismatched data type (non-existent certainly qualifies as mismatched in this case). No harm was done by this operation, which is also what we’d expect: table “foo” and table “bar” remain in their original, unmodified, and unmolested state, and if we really want to make this modification, we can drop the FK constraint and then proceed with the ALTER. No surprises yet.
But are there situations in which this operation doesn’t unfold quite so neatly? Obviously, there are, or there’d be little point in this blog post. When is a matching datatype not really a matching datatype? If you guessed that it has something to do with character fields and character sets, then you’d be absolutely correct. Consider the following 3 tables, each one with a single row inserted into it:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 |
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) ) engine=innodb 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) ) engine=innodb 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) ) engine=innodb 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'); SET FOREIGN_KEY_CHECKS=1; |
Suppose that we decide that we’ll never have any use for UTF8 in this database at all, and that we want to achieve the best performance possible, so we’re going to convert everything to ASCII. Or perhaps we have some other tables with ASCII identifiers and we’re doing JOINs against tables in this database and seeing that indexes aren’t being used in the way we were expecting. Whatever the reason, we decide to convert these columns to be ASCII. We might assume that since the underlying data type is remaining as a CHAR(32) that we don’t need to worry about dropping constraints and that we can just modify the table in place. The first table certainly proceeds well enough:
|
1 2 3 4 5 6 |
(root@localhost) [oops4]> set foreign_key_checks=0; Query OK, 0 rows affected (0.00 sec) (root@localhost) [oops4]> 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; Query OK, 1 row affected (0.09 sec) Records: 1 Duplicates: 0 Warnings: 0 |
But suppose that this was actually a long-running ALTER statement. Maybe we got pre-occupied, or we had to pick up the task the next day, and when we came back, we forgot that we had to re-disable foreign key checking since we were in a new session. We move on to table 2:
|
1 2 |
(root@localhost) [oops4]> 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) |
Oops, we forgot to disable foreign key checking. No problem, right? Not exactly….
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
(root@localhost) [oops4]> set foreign_key_checks=0; Query OK, 0 rows affected (0.00 sec) (root@localhost) [oops4]> 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 1146 (42S02): Table 'oops4.dos2' doesn't exist (root@localhost) [oops4]> show tables; +-----------------+ | Tables_in_oops4 | +-----------------+ | dos1 | | dos3 | +-----------------+ 2 rows in set (0.00 sec) |
“Oops” indeed. Where we once had three tables, there are now only 2. The “dos2” table is nowhere to be found. The good news here is that the data is not completely gone – it still exists in the temporary tables that were created as part of the ALTER operation and can be found in the data directory, but it’s no longer readily accessible by you or your applications, and the database itself is now in an odd state of limbo. You can’t drop the database, you can’t recreate the “dos2” table, and you can’t turn the orphaned temporary table back into its former self unless you’re handy with a hex editor and have a good knowledge of InnoDB file formats. Moreover, trying to modify any of the remaining tables could cause the same thing to happen to them as well.
|
1 2 3 4 |
(root@localhost) [oops4]> drop database oops4; ERROR 1010 (HY000): Error dropping database (can't rmdir './oops4', errno: 39) (root@localhost) [oops4]> create table dos2 (i int); ERROR 1005 (HY000): Can't create table 'oops4.dos2' (errno: -1) |
Yikes. Not a good day. The one thing that you can do, fortunately, is recover the data into a new table, but you’ll need to go take a look inside the InnoDB data directory for this database to get the actual filename of the temporary table rather than the temporary table name that was displayed in the error message. Once you do that, you can run something like this:
|
1 2 |
CREATE TABLE recovery LIKE `#sql2-3c13-14`; INSERT INTO recovery SELECT * FROM `#sql2-3c13-14`; |
But your database is still potentially inconsistent, and you can’t explicitly drop those temporary tables or rename them.
This looks somewhat like closed bug 13378, but I think there’s something of an inconsistency present when MySQL won’t allow us to change an FK-referenced column from INT to SMALLINT, but it will allow us to change from a multi-byte character column to a single-byte character column, so I’ve submitted this as bug report 65701.
So what did we learn here?
- Character set mismatches are bad enough when dealing with JOINs, but they can be an absolute nightmare when dealing with FKs.
- The safest way to modify a column referenced by FKs is to drop the constraints first.
- Sometimes you won’t know that an operation has failed badly until it’s too late. Just one more reason (as if you needed any more) to keep good backups that are tested regularly.
Tested under MySQL 5.5.24.
About the Author
Ernie SouhradaErnie joined Percona in April 2012 as a Senior Consultant. In his previous lives, he has been everything from a Perl/Java developer to a Linux sysadmin, a MySQL DBA to a Cisco network engineer, and a security auditor to an IT engineering manager, many of these things all at the same time. When not working on MySQL, he might be found on the ski slope, at a psytrance festival, or at the nearest sushi bar.
Hi Ernie,
Very good post!
I remembered reading another post (“Hacking FRM files to switch data types” http://goo.gl/chzTJ) I think that can be used in this case to avoid the problems shown.