Error "Can't create table..." on adding FOREIGN KEY

Question

I have created the table by script:

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";

DROP TABLE IF EXISTS `Table1`;
CREATE TABLE IF NOT EXISTS `Table1` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `parentId` bigint(20) DEFAULT NULL,
  `name` varchar(1024) NOT NULL,
  `description` varchar(16384) NOT NULL DEFAULT '',
  `imageId` bigint(20) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `name` (`name`(255)),
  KEY `parentId` (`parentId`),
  KEY `imageId` (`imageId`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=27 ;


INSERT INTO `Table1` (`id`, `parentId`, `name`, `description`, `imageId`) VALUES
(0, NULL, 'name1', '', NULL),
(12, 0, 'name2', '', NULL);

Then I try to add foreign key:

ALTER TABLE `Table1`
  ADD CONSTRAINT `Table1_ibfk_2` 
    FOREIGN KEY (`parentId`) REFERENCES `Table1` (`id`);

And obtain the following error:

ERROR 1005 (HY000): Can't create table 'sandbox.#sql-c28_4c' (errno: 150)

What is wrong?

I run

SHOW ENGINE INNODB STATUS;

There LATEST FOREIGN KEY ERROR follows:

------------------------
LATEST FOREIGN KEY ERROR
------------------------
110504 22:06:55 Error in foreign key constraint of table sandbox/#sql-c28_61:

    FOREIGN KEY (`parentId`) REFERENCES `Table1` (`id`):
Cannot resolve table name close to:
 (`id`)
------------

But it does not help me to realize what is wrong.

I use Windows Vista, MySql 5.5.11

UPDATE:

The issue appears when I upgrade from MySql 5.0.67.

Answer 1

Background

ERROR 1005 (HY000): Can't create table 'sandbox.#sql-c28_4c' (errno: 150)

When you command MySQL to ALTER TABLE it really does the following steps:

1. copy the data from the existing table to a new temporary table.
2. Alter the structure of the new temporary table
3. delete the old table
4. rename the temporary table to the old_table_name.

Problem
Your request is failing in step 1.
Note that tablenames link to files.
On linux tablenames are case sensitive

On Windows they are not case sensitive.

Answer
Since you are obviously running linux, the case of the table you are REFERENCES to needs to be the same case as in the definition of that table, probably all lower case.
You are running Windows, so case sensitivity should not be an issue, maybe it's the tool you are using, I've had these issue as well, and using all lowercase for tablenames everywhere solved my issues.

Proper answer
Set the system variabele

lower_case_table_names=1

To get rid of this issue.

Note that if you have lower_case_table_names=2 on Windows, your Windoze box turns into a case sensitive Linux box as far as MySQL is concerned!

Link
http://dev.mysql.com/doc/refman/5.5/en/identifier-case-sensitivity.html