Why does MySQL fail with ERROR 156 "Table already exists" when trying to create an index?

Question

I'm attempting to add uniqueness constraint on an existing table by creating an index.

Here's the existing table (I've tried to make mysql's output a bit more readable):

mysql> show create table profile_status;

| Table          | Create Table
| profile_status | CREATE TABLE `profile_status` (
                       `user_id` int(11) NOT NULL,
                       `timestamp` datetime DEFAULT NULL,
                       `proxy_profile_id` int(11) DEFAULT NULL,
                        KEY `user_id_refs_user_id` (`user_id`),
                        KEY `profile_status` (`proxy_profile_id`),
                        CONSTRAINT `proxy_profile_id_refs_user_id` FOREIGN KEY (`proxy_profile_id`) REFERENCES `profile_userprofile` (`user_id`),
                        CONSTRAINT `user_id_refs_user_id` FOREIGN KEY (`user_id`) REFERENCES `profile_userprofile` (`user_id`)
                    ) ENGINE=InnoDB DEFAULT CHARSET=latin1

1 row in set (0.00 sec)

Here's what happens when I attempt to create the index:

mysql> CREATE UNIQUE INDEX `profile_status_unique` ON `profile_status` (`user_id`);
ERROR 156 (HY000): Table 'project.profile_status#1' already exists

My first suspicion is that the error message is misleading, and in fact MySQL is in fact refusing to create a second index on the user_id column, since there's already a non-unique key there. So I tried dropping that key first (along with the associated foreign key constraint):

mysql> ALTER TABLE profile_status DROP FOREIGN KEY user_id_refs_user_id;
Query OK, 112 rows affected (0.05 sec)
Records: 112  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE profile_status DROP KEY user_id_refs_user_id;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table profile_status;

| Table          | Create Table
| profile_status | CREATE TABLE `profile_status` (
                       `user_id` int(11) NOT NULL,
                       `timestamp` datetime DEFAULT NULL,
                       `proxy_profile_id` int(11) DEFAULT NULL,
                        KEY `profile_status` (`proxy_profile_id`),
                        CONSTRAINT `proxy_profile_id_refs_user_id` FOREIGN KEY (`proxy_profile_id`) REFERENCES `profile_userprofile` (`user_id`),
                    ) ENGINE=InnoDB DEFAULT CHARSET=latin1

1 row in set (0.00 sec)

mysql> CREATE UNIQUE INDEX `profile_status_unique` ON `profile_status` (`user_id`);
ERROR 156 (HY000): Table 'project.profile_status#1' already exists

Same result. At this point I'm thoroughly stumped, and I'd really appreciate some help. Worst case, I could create a temporary table with the correct constraints and copy over the data, but I'd like to understand this problem fully.

Thanks in advance. Here's the MySQL details:

mysql> show variables like '%version%';
| Variable_name           | Value
| innodb_version          | 1.1.8
| protocol_version        | 10
| version                 | 5.5.28-1
| version_comment         | (Debian)
| version_compile_machine | i686
| version_compile_os      | debian-linux-gnu
7 rows in set (0.00 sec)

This is a really wild guess: try `DROP TEMPORARY TABLE playmeet.profile_status#1` (with backticks around the name - can't use those in comments). — Vatev, Aug 16 '13 at 15:49
Thanks, but no dice: ERROR 1051 (42S02): Unknown table 'project.profile_status#1' — nephtes, Aug 16 '13 at 15:55
Strange cause it works fine in SQLFIDDLE : http://www.sqlfiddle.com/#!2/bd007/0 ... but I have to remove the `CONSTRAINT ... FOREIGN KEY` — Fabien TheSolution, Aug 16 '13 at 16:22
Have a look at this : http://stackoverflow.com/questions/3302476/mysql-1050-error-table-already-exists-when-in-fact-it-does-not — Fabien TheSolution, Aug 16 '13 at 16:32
@Fabien-TheSolution: Interesting. On sqlfiddle, I can run all the queries as-is, including the foreign key constraints, if I create a dummy user_profile table: http://www.sqlfiddle.com/#!2/51d6e — nephtes, Aug 16 '13 at 17:48
May be something corrupt in your DB...Did you looked at the link I provided ? There is some good ideas there... — Fabien TheSolution, Aug 16 '13 at 17:53
It also works if I try it on a new table with the same schema, in the same database. So I suppose this implies that there's something elsewhere in the state of the db that's causing the problem... — nephtes, Aug 16 '13 at 17:56
@Fabien-TheSolution: I did check your link, thank you. But REPAIR TABLE doesn't work on InnoDB tables, and mysqladmin flush-tables didn't do anything either. — nephtes, Aug 16 '13 at 17:57
let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/35615/discussion-between-nephtes-and-fabien-thesolution) — nephtes, Aug 16 '13 at 18:00

score 0 · Answer 1 · answered Sep 12 '13 at 06:53

Check if 'project' directory in your MySQL data directory does not contain any unwanted/temporary files. Remove them if you see files starting with something like ? or #. Also check if file with name 'profile_status#1' exists and remove it.

Then login to MySQL, execute FLUSH TABLES (even if there were no unwanted files in the above step) and try altering the table again.

Why does MySQL fail with ERROR 156 "Table already exists" when trying to create an index?

1 Answers1