Bug #18800 Unicode quoted foreign key references are broken
Submitted: 5 Apr 2006 13:13 Modified: 7 Aug 2006 7:52
Reporter: Osku Salerma Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: InnoDB storage engine Severity:S2 (Serious)
Version:5.1 OS:Linux (Linux)
Assigned to: Marko Mäkelä CPU Architecture:Any

[5 Apr 2006 13:13] Osku Salerma
Description:
The change to use Unicode escaping broke things:

create table `a'b` (id int primary key) engine=innodb;

create table `c'd` (id int primary key, foreign key `key'name` (id) references `a'b`(id)) engine=innodb;
ERROR 1005 (HY000): Can't create table 'test.c'd' (errno: 150)

SHOW INNODB STATUS:

LATEST FOREIGN KEY ERROR
------------------------
060405 16:02:12 Error in foreign key constraint of table test/c@0027d:
foreign key `key'name` (id) references `a'b`(id)) engine=innodb:
Cannot resolve table name close to:
(id)) engine=innodb

How to repeat:
See above.

Suggested fix:
From Heikki:

-InnoDB's foreign key parsed should translate quoted identifiers to Unicode

-It should also search for both old and new style column and table names.
[5 Apr 2006 13:30] Miguel Solorzano
Thank you for the bug report. This issue not affects 5.0.
[11 Apr 2006 14:18] Heikki Tuuri
Assigning this to Marko.
[11 Apr 2006 19:15] Marko Mäkelä
There is a similar bug in the transition from MySQL 4.0 to 4.1: non-ASCII table and column names would have to be renamed (usually from latin1) to ASCII before upgrading and back (to UTF-8) after upgrading. Unlike that bug, this one can be fixed relatively easily, because  no character set translation is needed.
[12 Apr 2006 8:19] Marko Mäkelä
The most straightforward fix is to replace the thd->convert_string() call in ha_innobase::create() with appropriate calls in dict0dict.c. Table and database names would be converted by tablename_to_filename(), and column names would be converted to UTF-8 as before.

There are two possible solutions to achieving compatibility with Unicode table names from MySQL 4.1 and 5.0. Either fall back to the old UTF-8 conversion when the table cannot be found by tablename_to_filename(), or require that a table renaming script be run on 4.1 or 5.0 before upgrading to 5.1. The former option is more flexible for the user, but it may be prone to creating orphan foreign key constraints.
[19 Apr 2006 14:14] Marko Mäkelä
I have submitted a patch for internal InnoDB review.
[20 May 2006 10:34] Jared S
I confirm problem of hyphened named table on windows.
[20 May 2006 10:50] Jared S
Also effects Updating of unicode named table

SHOW INNODB STATUS:

Trying to add to index `FK_flat-features_4` tuple:
DATA TUPLE: 2 fields;
 0: len 4; hex 80000002; asc     ;; 1: len 4; hex 80000006; asc     ;;
But the parent table `goolabri/flat-rstyle`
or its .ibd file does not currently exist!
[20 May 2006 16:38] Marko Mäkelä
Yes, also table names containing hyphens are affected. I've been waiting for answers from MySQL AB staff to my questions for over a month now. I guess we'll have to submit the patch as is, before MySQL 5.1 is declared to leave the beta status.

FYI, tables created with an earlier MySQL version containing "filename-unsafe" characters will be prefixed with the string #mysql50#. When I tested, RENAME DATABASE did not rename the tables to the new "filename-safe" encoding, so it seems you would have to rename all such tables one by one.
[24 May 2006 11:29] Marko Mäkelä
Patch for Bug #18800

Attachment: bug18800.patch (text/x-patch), 60.21 KiB.

[24 May 2006 11:43] Marko Mäkelä
Source code snapshot sent to MySQL AB
[24 May 2006 22:56] Jared S
Best to wait for 5.0.10?
Who is the x-patch file for? 
How would it be used?
[25 May 2006 14:28] Marko Mäkelä
Jared,
I guess you mean 5.1.10. I don't have access to the build schedule, so I can't say if the fix will be included there. Source code patches can be applied by running the "patch" utility. Since you are apparently using Windows, it's probably easiest for you to wait for the new release. It's easier to compile the source code on Unix-like platforms.
[26 May 2006 9:49] Marko Mäkelä
The previously attached patch causes problems with foreign keys. A better patch is being worked on.
[26 May 2006 10:25] Marko Mäkelä
Patch for previous patch for Bug #18800

Attachment: bug18800-2.patch (text/x-patch), 447 bytes.

[26 May 2006 10:27] Marko Mäkelä
The 2nd attached patch to this bug report removes a bug that was introduced in the 1st attached patch, in creating tables containing foreign key constraints.
[9 Jun 2006 10:45] Jared S
Can't believe that this bug was not included in release 5.1.11!

And doesn't even look like will be fixed in 5.1.12!
[5 Aug 2006 0:32] Jared S
When will this be released as full Linux build? 

1/2/3 year?
[6 Aug 2006 19:28] Marko Mäkelä
The fix was apparently included in the MySQL source tree on June 1:

http://mysql.bkbits.net:8080/mysql-5.1/cset@1.2154.44.1

Unfortunately, I forgot to mention this bug number in the InnoDB commit message, and thus the change has not been documented.
[7 Aug 2006 7:51] Jon Stephens
For future reference: The developer to whom a bug is assigned is responsible for putting the bug into Documenting status when a patch for the bug is approved and pushed.
[7 Aug 2006 7:52] Jon Stephens
Thank you for your bug report. This issue has been committed to our source repository of that product and will be incorporated into the next release.

If necessary, you can access the source repository and build the latest available version, including the bug fix. More information about accessing the source trees is available at

    http://www.mysql.com/doc/en/Installing_source_tree.html

Bugfix documented in 5.1.12 changelog.