Bug #55183 Errno rather than message when adding foreign key constraint

• Submitted: 12 Jul 2010 17:58
• Modified: 24 Dec 2012 9:05
• Reporter: John Russell
• Status: Duplicate
• Category: MySQL Server: InnoDB storage engine
• Severity: S3 (Non-critical)
• Version: 5.1
• OS: Any
• Assigned to: Assigned Account
• CPU Architecture: Any

[12 Jul 2010 17:58] John Russell

Description:
Adding a foreign key can produce an errno error rather than a descriptive message, for some types of inconsistencies between the tables, such as character set.

Reported as a troubleshooting tip by a doc reader, on the foreign key constraints page:

http://dev.mysql.com/doc/mysql/en/innodb-foreign-key-constraints.html

"If you're seeing errno 150 when trying to alter a table to add a foreign
key, you have to check the properties of the *table* as well as the types
of the columns being referenced.

For example, if you're trying to create a foreign key constraint between
two varchar(30) columns, but one table is using latin1 encoding and the
other table is using utf8 encoding, the ALTER will fail with errno 150 and
no clear indication of the above."

How to repeat:
ALTER TABLE statement with tables of different character sets.

Suggested fix:
Add a descriptive error message when MySQL receives errno 150 from InnoDB.

[12 Jul 2010 20:00] MySQL Verification Team

Thank you for the bug report.

[24 Dec 2012 9:05] Erlend Dahl

Fixed in 5.6.9 as a duplicate of bug#64617: MYSQL MUST IMPROVE ERROR MESSAGES