MySQL Bugs: #55465: ERROR 1280 (42000): Incorrect index name '<index name>'

Bug #55465	ERROR 1280 (42000): Incorrect index name '<index name>'
Submitted:	22 Jul 2010 1:29	Modified:	14 Dec 2010 20:11
Reporter:	Roel Van de Paar	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: InnoDB Plugin storage engine	Severity:	S1 (Critical)
Version:	5.1.47, 5.1.49	OS:	Any
Assigned to:	Jimmy Yang	CPU Architecture:	Any

Description:
Last line of "how to repeat" gives:

Using built-in InnoDB:

  Query OK, 0 rows affected (0.02 sec)
  Records: 0  Duplicates: 0  Warnings: 0

Using InnoDB Plugin 1.0.8:

  ERROR 1280 (42000): Incorrect index name 'fk1'

Also (using build-in InnoDB after creation of the index):

mysql> ALTER TABLE t2 DROP INDEX fk1;
ERROR 1025 (HY000): Error on rename of '.\bg1\#sql-1658_1' to '.\bg1\t2' (errno: 150)

On playing around with this, error log also showing entries similar to these:

----------
100722 11:22:43  InnoDB: Error: in ALTER TABLE `bg1`.`t2`
InnoDB: has or is referenced in foreign key constraints
InnoDB: which are not compatible with the new table definition.
100722 11:22:43  InnoDB: Error: table `bg1`.`t2` does not exist in the InnoDB internal
InnoDB: data dictionary though MySQL is trying to drop it.
InnoDB: Have you copied the .frm file of the table to the
InnoDB: MySQL database directory from another database?
InnoDB: You can look for further help from
InnoDB: http://dev.mysql.com/doc/refman/5.1/en/innodb-troubleshooting.html
----------

How to repeat:
DROP DATABASE IF EXISTS `bg1`;
CREATE DATABASE `bg1`;
USE `bg1`;

CREATE TABLE IF NOT EXISTS `t1`(`id` MEDIUMINT NOT NULL,PRIMARY KEY (`id`)) ENGINE = InnoDB;
CREATE TABLE IF NOT EXISTS `t2`(`id` MEDIUMINT NOT NULL,CONSTRAINT `fk1` FOREIGN KEY (`id`) REFERENCES `t1` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION) ENGINE = InnoDB;
CREATE INDEX `fk1` ON `t2` (`id` ASC);

See bug #3749 | bug #3750 | bug #5670 | bug #15324 | #55361 | bug #37538 | bug #38139 | bug #48070 and likely others.

Significantly, see bug #21704 also.

One of the main issues is that - if changes were made in this area - the "150 error message" is very confusing since it does not highlight what the real problem is. Also, there are significant errors in the error log (see bug #21704)

Hopefully some of the bugs above can be fixed together with this bug. 

Also, while fixing this, please address this other (minor) issue (error message) also:

"ERROR 1005 (HY000): Can't create table 'bg2.t2' (errno: 150)" on:

DROP DATABASE IF EXISTS `bg2`; CREATE DATABASE `bg2`; USE `bg2`;
CREATE TABLE `t1`(`id` INT NOT NULL,PRIMARY KEY (`id`)) ENGINE =InnoDB;
CREATE TABLE `t2`(`id` INT NOT NULL,PRIMARY KEY (`id`), b MEDIUMINT, INDEX(b), FOREIGN KEY (b) REFERENCES t1(id) ) ENGINE=InnoDB;

Issue here is INT<>MEDIUMINT

> "and likely others" 
There's 70+ occurrences for the following search on Google: [(("ERROR 1280" OR "ERROR 1005" OR "ERROR 1025") AND "(errno 150)") ON BUGS]:
http://www.google.com.au/search?num=100&hl=en&q=%22ERROR+1280%22+OR+%22ERROR+1005%22+OR+%2...
It would be good to get to the bottom of this one.

Verified as D2 in 5.1.49. Unacceptable workaround (as far as the main issue in this bug) would be to change to built in InnoDB.

The behavior is expected, but needs better documentation (or better error message information):

Here, a foreign key index named "fk1" is created automatcally on referencing table "t2", this is documented in our foreign key constraint section:
(http://dev.mysql.com/doc/refman/5.1/en/innodb-foreign-key-constraints.html)

=================
In the referencing table, there must be an index where the foreign key columns are listed as the first columns in the same order. Such an index is created on the referencing table automatically if it does not exist. (This is in contrast to some older versions, in which indexes had to be created explicitly or the creation of foreign key constraints would fail.) index_name, if given, is used as described previously.
=================

So in plug-in, when you create an addition index named "fk1", it will hit ER_WRONG_NAME_FOR_INDEX (Incorrect index name) error. This is the same as you create an index with duplicated name.

In build-in, the create index does not go through fast create index, rather it goes through alter table (rebuild index), in this case, it uses the user defined fk1 to substitute the default created index. So this operation is successful. However, when you drop this index, it will fail, since we must have at least one index on the foreign key on the referencing table. Since the error is detected during alter table (drop index), it spits out error message on a failed alter table attempt rather than 1553 error:
 ERROR 1553 (HY000): Cannot drop index 'fk1': needed in a foreign key constraint.

In short, the behavior is expected, and it should be better documented. In terms of error messaging, we might consider spitting out more reasonable information in the errorlog.

The storage engine cannot directly issues its own error message to sql interfaces, the error message is translated into server error message and return to sql layer. In the plugin case, ER_WRONG_NAME_FOR_INDEX is returned as there already exists an index named as fk1. Probably a "INDEX NAME ALREADY EXISTS" error is more appropriate, but that would require MySQL Server to add such ER_* error. For now, we should rely on better documentation to explain what this ER_WRONG_NAME_FOR_INDEX means:

1. The index name already exists, or already appeared in the list of indexes to be built.
2. The index name is a reserved name (such as GEN_CLUST_INDEX), in this case, a warning would be issued.

In the built-in case, the drop index is performed through alter table interface, after the new temp table is built, and we try to rename the new temp table back, we find the new temp table lack of a required index (dropped one) for the foreign key constraint. Again, we only have a few limited ER_* error available to us from MySQL server layer, DB_CANNOT_ADD_CONSTRAINT probably is the most appropriate one. However, internally, we do added two error number:

DB_FOREIGN_NO_INDEX
DB_REFERENCING_NO_INDEX

So that a warning message (storage cannot push error message) can be pushed to user interfaces:

| Warning | 150 | Alter table or rename table test/t2 failed as the new table constains a foreign key constraint and as the referencing table it does not have an index with foreign keys as its first columns. |

fix pushed to mysql-5.1-innodb

Pushed into mysql-5.1 5.1.52 (revid:sunanda.menon@sun.com-20100928083322-wangbv97uobu7g66) (version source revid:sunanda.menon@sun.com-20100928083322-wangbv97uobu7g66) (merge vers: 5.1.52) (pib:21)

Pushed into mysql-trunk 5.6.1-m4 (revid:alik@sun.com-20100928153607-tdsxkdm5cmuym5sq) (version source revid:alik@sun.com-20100928153508-0saa6v93dinqx1u7) (merge vers: 5.6.1-m4) (pib:21)

Pushed into mysql-next-mr (revid:alik@sun.com-20100928153646-pqp8o1a92mxtuj3h) (version source revid:alik@sun.com-20100928153532-lr3gtvnyp2en4y75) (pib:21)

Pushed into mysql-5.5 5.5.7-rc (revid:alik@sun.com-20100928153459-4nudf4zgzlou4s7q) (version source revid:alik@sun.com-20100928153459-4nudf4zgzlou4s7q) (merge vers: 5.5.7-rc) (pib:21)

5.1.51 still gives us this:

mysql> CREATE INDEX `fk1` ON `t2` (`id` ASC);
ERROR 1280 (42000): Incorrect index name 'fk1'
mysql> select version();
+--------------------------------+
| version()                      |
+--------------------------------+
| 5.1.51-enterprise-gpl-advanced |
+--------------------------------+
1 row in set (0.03 sec)

Shane, as explained on July 26, the behavior is expected. And there already exist such index, and error is correctly reported. Maybe it could be a bit better documenting this case. But I think documentation is already pretty clear on this:

================================================
[26 Jul 8:51] Jimmy Yang

The behavior is expected, but needs better documentation (or better error message
information):

Here, a foreign key index named "fk1" is created automatcally on referencing table "t2",
this is documented in our foreign key constraint section:
(http://dev.mysql.com/doc/refman/5.1/en/innodb-foreign-key-constraints.html)

=================
In the referencing table, there must be an index where the foreign key columns are listed
as the first columns in the same order. Such an index is created on the referencing table
automatically if it does not exist. (This is in contrast to some older versions, in which
indexes had to be created explicitly or the creation of foreign key constraints would
fail.) index_name, if given, is used as described previously.
=================

So in plug-in, when you create an addition index named "fk1", it will hit
ER_WRONG_NAME_FOR_INDEX (Incorrect index name) error. This is the same as you create an
index with duplicated name.

Pushed into mysql-5.1-telco-7.0 5.1.51-ndb-7.0.20 (revid:martin.skold@mysql.com-20101014082627-jrmy9xbfbtrebw3c) (version source revid:martin.skold@mysql.com-20101014082627-jrmy9xbfbtrebw3c) (merge vers: 5.1.51-ndb-7.0.20) (pib:21)

Pushed into mysql-5.1-telco-6.3 5.1.51-ndb-6.3.39 (revid:martin.skold@mysql.com-20101014083757-5qo48b86d69zjvzj) (version source revid:martin.skold@mysql.com-20101014083757-5qo48b86d69zjvzj) (merge vers: 5.1.51-ndb-6.3.39) (pib:21)

Pushed into mysql-5.1-telco-6.2 5.1.51-ndb-6.2.19 (revid:martin.skold@mysql.com-20101014084420-y54ecj85j5we27oa) (version source revid:martin.skold@mysql.com-20101014084420-y54ecj85j5we27oa) (merge vers: 5.1.51-ndb-6.2.19) (pib:21)

Did this get fixed---?

"Alter table or rename table test/t2 failed as the new table constains
a foreign key constraint and as the referencing table it does not have an index with
foreign keys as its first columns."

This is simply horrid. Please fix to use parseable English grammar. Thanks!

I think this text can be shorter yet much more meaningful than before. Rather than:

"Alter table or rename table test/t2 failed as the new table constains
a foreign key constraint and as the referencing table it does not have an index with
foreign keys as its first columns."

I think this gets the job done succinctly:

"Change failed due to foreign key constraint. See http://dev.mysql.com/en/5.1/foreign-key-constraints.html for details."

Or something similar...

The actual message that had been checked in was:

"Alter or rename of table '%s' failed because the new table is a child table in a FK relationship and it does not have an index that contains foreign keys as its prefix columns."

The fix for this bug means that scripts that use to run now no longer run. Earlier versions of mysql (I tested 5.1.45) use to run. Now the script generates an error. 

Here is the test script ran against 5.1.45:

mysql> SELECT @@VERSION;
+---------------+
| @@VERSION     |
+---------------+
| 5.1.45-51-log |
+---------------+
1 row in set (0.00 sec)

mysql> CREATE TABLE IF NOT EXISTS `t1`(`id` MEDIUMINT NOT NULL,PRIMARY KEY (`id`)) ENGINE =
    -> InnoDB;
Query OK, 0 rows affected (0.96 sec)

mysql> CREATE TABLE IF NOT EXISTS `t2`(`id` MEDIUMINT NOT NULL,CONSTRAINT `fk1` FOREIGN KEY
    -> (`id`) REFERENCES `t1` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION) ENGINE = InnoDB;
Query OK, 0 rows affected (0.51 sec)

mysql> show create table t2;
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                    |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t2    | CREATE TABLE `t2` (
  `id` mediumint(9) NOT NULL,
  KEY `fk1` (`id`),
  CONSTRAINT `fk1` FOREIGN KEY (`id`) REFERENCES `t1` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> CREATE INDEX `fk1` ON `t2` (`id` ASC);
Query OK, 0 rows affected (2.07 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table t2;
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                    |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t2    | CREATE TABLE `t2` (
  `id` mediumint(9) NOT NULL,
  KEY `fk1` (`id`),
  CONSTRAINT `fk1` FOREIGN KEY (`id`) REFERENCES `t1` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

Now the same against 5.1.56:

mysql> select @@version;+--------------------+
| @@version          |
+--------------------+
| 5.1.56-rel12.7-log |
+--------------------+
1 row in set (0.00 sec)

mysql> CREATE TABLE IF NOT EXISTS `t1`(`id` MEDIUMINT NOT NULL,PRIMARY KEY (`id`)) ENGINE =
    -> InnoDB;
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE TABLE IF NOT EXISTS `t2`(`id` MEDIUMINT NOT NULL,CONSTRAINT `fk1` FOREIGN KEY
    -> (`id`) REFERENCES `t1` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION) ENGINE = InnoDB;
Query OK, 0 rows affected (0.01 sec)

mysql> show create table t2;
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                    |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t2    | CREATE TABLE `t2` (
  `id` mediumint(9) NOT NULL,
  KEY `fk1` (`id`),
  CONSTRAINT `fk1` FOREIGN KEY (`id`) REFERENCES `t1` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

mysql> CREATE INDEX `fk1` ON `t2` (`id` ASC);
ERROR 1280 (42000): Incorrect index name 'fk1'

The problem with this bug is that the script use to work and now it doesn't. In earlier versions of the database, even though the foreign key automatically created the index, issuing a single index create statement worked (you could not issue it twice). This was so that scripts from earlier db versions still worked. Now, the latest version of the database breaks these scripts. Simply changing the error message isn't sufficient. The test script should succeed and not raise an error. This was the behavior before.

All:
This is one of those 'redesign needed' issues. OK, so there is an index generated automatically, and there might be some workaround by not regenerating it the first time someone does a create index on it, but is that really the best way? Why not a check on the parameters - does the create MATCH the automatically generated values?

Then there is the other issue - a 'bug' in Workbench, because the behavior of the sql engine should be known, etc. by the workbench. A workbench user is clobbered every time by this 'we told you so but we could not really explain it well' 'non-bug but.... well, it keeps giving me an error'. The workbench user cannot easily tell when this 'bug' will come out, because they run the same script over and over for other reasons, and it will fail on the second running under the scenario suggested by last commenter.

If there is no round trip ability in Workbench - to reverse engineer a schema and then to bring it back, then the workbench is not so great. This sort of 'expected behavior' is unsettling to a user, because it is either too restrictive or it is so far from what a designer might expect on another database.

Please redesign this so 1280 messages will not come up unless the user clearly screws up by intentionally using a different name for the same index as was generated, or redesign the workbench to generate a comment instead of the create index statement where a 1280 message would occur. Tall order to do the latter, so do the former.

Thx