title: MySQL 5.5 foreign key constraint fails when foreign key exists
body: <p>Just installed MySQL 5.5 on mac os x 10.6 and am having a strange issue on many tables.  Below is an example.  Inserting a row fails with a foreign key constraint when it shouldn't.  The foreign key it references does exist.  Any ideas?</p>
  
  <pre><code>mysql&gt; show create table Language;
  +----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  | Table    | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               |
  +----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  | Language | CREATE TABLE `Language` (
    `Id` int(11) NOT NULL AUTO_INCREMENT,
    `Code` varchar(2) NOT NULL,
    `Name` varchar(63) CHARACTER SET utf8 DEFAULT NULL,
    `Variant` varchar(63) CHARACTER SET utf8 DEFAULT NULL,
    `Country_Id` int(11) DEFAULT NULL,
    PRIMARY KEY (`Id`),
    UNIQUE KEY `Code` (`Code`,`Country_Id`,`Variant`),
    KEY `FKA3ACF7789C1796EB` (`Country_Id`),
    CONSTRAINT `FKA3ACF7789C1796EB` FOREIGN KEY (`Country_Id`) REFERENCES `Country` (`Id`)
  ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1 |
  +----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  1 row in set (0.00 sec)
  
  mysql&gt; show create table Language_Phrases;
  +------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  | Table            | Create Table                                                                                                                                                                                                                                                                                                                                                    |
  +------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  | Language_Phrases | CREATE TABLE `Language_Phrases` (
    `Language_Id` int(11) NOT NULL,
    `Phrase` varchar(255) DEFAULT NULL,
    `Label` varchar(255) NOT NULL,
    PRIMARY KEY (`Language_Id`,`Label`),
    KEY `FK8B4876F3AEC1DBE9` (`Language_Id`),
    CONSTRAINT `FK8B4876F3AEC1DBE9` FOREIGN KEY (`Language_Id`) REFERENCES `Language` (`Id`)
  ) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
  +------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  1 row in set (0.00 sec)
  
  mysql&gt; select * from Language;
  +----+------+----------+---------+------------+
  | Id | Code | Name     | Variant | Country_Id |
  +----+------+----------+---------+------------+
  |  1 | en   | English  |         |        235 |
  |  2 | ro   | Romanian |         |        181 |
  +----+------+----------+---------+------------+
  2 rows in set (0.00 sec)
  
  mysql&gt; select * from Language_Phrases;
  Empty set (0.00 sec)
  
  mysql&gt; INSERT INTO Language_Phrases (Language_Id, Label, Phrase) VALUES (1, 'exampleLabel', 'Some phrase');
  ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`dev`.`language_phrases`, CONSTRAINT `FK8B4876F3AEC1DBE9` FOREIGN KEY (`Language_Id`) REFERENCES `Language` (`Id`))
  mysql&gt; 
  </code></pre>
  
  <p><strong><em>UPDATE</em></strong>: After dropping and recreating the database several times, I did a <code>show engine innodb status</code> after the failing insert above and got this surprising result.  The parent Language table is not found!  This seems very strange...  any ideas?</p>
  
  <pre><code>------------------------
  LATEST FOREIGN KEY ERROR
  ------------------------
  110406  9:55:49 Transaction:
  TRANSACTION CA3B, ACTIVE 0 sec, OS thread id 4494462976 inserting
  mysql tables in use 1, locked 1
  1 lock struct(s), heap size 376, 0 row lock(s)
  MySQL thread id 25, query id 50720 localhost root update
  INSERT INTO Language_Phrases (Language_Id, Label, Phrase) VALUES (1, 'exampleLabel', 'Some phrase')
  Foreign key constraint fails for table `dev`.`language_phrases`:
  ,
    CONSTRAINT `FK8B4876F3AEC1DBE9` FOREIGN KEY (`Language_Id`) REFERENCES `Language` (`Id`)
  Trying to add to index `PRIMARY` tuple:
  DATA TUPLE: 5 fields;
   0: len 4; hex 80000001; asc     ;;
   1: len 17; hex 747970654d69736d617463682e79656172; asc exampleLabel;;
   2: len 6; hex 00000000ca3b; asc      ;;;
   3: len 7; hex 00000000000000; asc        ;;
   4: len 21; hex 59656172206d7573742062652061206e756d626572; asc Some phrase;;
  
  But the parent table `dev`.`Language`
  or its .ibd file does not currently exist!
  </code></pre>
  
  <p><strong><em>UPDATE 2</em></strong>: It turns out this is simply a <strong>massive</strong> bug in MySQL.  Apparently the latest versions of MySQL do not work fully under mac os X 10.6 (maybe earlier versions too?).  Downgrading to 5.5.8 seems to work.  Extremely surprising.</p>