Case insensitive constraint names in MySQL

By default, MySQL takes advantage of case-sensitive filenames on Linux, with lower_case_file_system = OFF and lower_case_table_names = 0 (i.e. the filesystem is case-sensitive, therefore database names are case sensitive; and table names aren’t forced to lower case). In this situation it’s possible to have two different databases whose names differ only in case – for example, Photos and photos – although this should probably be avoided wherever possible!

The FOREIGN KEY Constraints” section of the MySQL manual has this to say about constraint names:

If the CONSTRAINT symbol clause is given, the symbol value must be unique in the database. If the clause is not given, InnoDB creates the name automatically.

This isn’t completely accurate. Internally, InnoDB stores foreign key constraint names as case-insensitive, with the database name prepended. So although onedatabase.b_ibfk_1 is treated as different to anotherdatabase.b_ibfk_1, Photos.b_ibfk_1 and photos.b_ibfk_1 are treated as the same.

So if database names that only differ in case are to be avoided, why might this be a problem? Well, if you want to follow the advice in the MySQL manual, and use lower_case_table_names = 1, you need to change all database names to lower case before using the new setting.

Say you have this database:

CREATE DATABASE Photos;
USE Photos;
CREATE TABLE a (one INT PRIMARY KEY) TYPE=InnoDB;
CREATE TABLE b (one INT PRIMARY KEY, FOREIGN KEY (one) REFERENCES a(one)) TYPE=InnoDB;

Notice the constraint on table b. Now you want to change the database name to photos, so you create a new database with that name, dump the tables and data from the old database with mysqldump, and then try to import them into the new database. You’ll get this error:

ERROR 1005 (HY000) at line 42: Can't create table './photos/b.frm' (errno: 121)

This slightly cryptic message is telling you that table b can’t be created in database photos – because the constraint name you’re trying to use in photos (lower case) is already used in Photos (upper case). The full details of the error can be seen in the output from SHOW INNODB STATUS:


070312 21:31:27 Error in foreign key constraint creation for table `photos/b`.
A foreign key constraint of name `photos/ba_ibfk_1`
already exists. (Note that internally InnoDB adds 'databasename/'
in front of the user-defined constraint name).
Note that InnoDB's FOREIGN KEY system tables store
constraint names as case-insensitive, with the
MySQL standard latin1_swedish_ci collation. If you
create tables or databases whose names differ only in
the character case, then collisions in constraint
names can occur. Workaround: name your constraints
explicitly with unique names.

There are a number of ways to get around this:

  • drop the old database before importing into the new one;
  • remove the constraints from the old database before importing into the new one;
  • manually change the constraint names in the export file before running the import.

Since MySQL 5.0 doesn’t allow a database to be renamed, it’s worth bearing this is in mind if you’re going through the mysqldump and import procedure to rename a database, where the old and new database names differ only in their case.

Advertisements
This entry was posted in Uncategorized and tagged . Bookmark the permalink.

One Response to Case insensitive constraint names in MySQL

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s