Bug #41138 Index created automatically by FOREIGN KEY not named correctly
Submitted: 30 Nov 2008 16:41 Modified: 9 Apr 2019 18:00
Reporter: Jaka Jančar Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: Parser Severity:S3 (Non-critical)
Version:5.1.30 OS:Any
Assigned to: CPU Architecture:Any

[30 Nov 2008 16:41] Jaka Jančar
Description:
http://dev.mysql.com/doc/refman/5.1/en/innodb-foreign-key-constraints.html says:

> index_name represents a foreign key ID. If given, this is ignored if an index for the foreign key is defined explicitly. Otherwise, if InnoDB creates an index for the foreign key, it uses index_name for the index name.

This is however not true if the constraint symbol is specified too, in which case index_name is ignored and the name of the constraint is used.

If constraint symbol is not specified, then the index name is used correctly (but the constraint name is auto-generated).

How to repeat:
Example:

mysql> CREATE TABLE test (
    ->     foo int,
    ->     CONSTRAINT test_foo_fk_constraint FOREIGN KEY test_foo_fk_index (foo) REFERENCES test(foo) ON DELETE CASCADE
    -> ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.02 sec)

mysql> SHOW CREATE TABLE test;
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                                             |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| test  | CREATE TABLE `test` (
  `foo` int(11) DEFAULT NULL,
  KEY `test_foo_fk_constraint` (`foo`),
  CONSTRAINT `test_foo_fk_constraint` FOREIGN KEY (`foo`) REFERENCES `test` (`foo`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1 | 
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

The key is erroneously named test_foo_fk_constraint instead of test_foo_fk_index.

The expected result would be the same as when the index is specified explicitly:
mysql> CREATE TABLE test (
    ->     foo int,
    ->     UNIQUE KEY test_foo_fk_index (foo),
    ->     CONSTRAINT test_foo_fk_constraint FOREIGN KEY (foo) REFERENCES test(foo) ON DELETE CASCADE
    -> ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.02 sec)

mysql> SHOW CREATE TABLE test;
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                                               |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| test  | CREATE TABLE `test` (
  `foo` int(11) DEFAULT NULL,
  UNIQUE KEY `test_foo_fk_index` (`foo`),
  CONSTRAINT `test_foo_fk_constraint` FOREIGN KEY (`foo`) REFERENCES `test` (`foo`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1 | 
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

If constraint name is not specified, it works as expected:
mysql> CREATE TABLE test (
    ->     foo int,
    ->     CONSTRAINT FOREIGN KEY test_foo_fk_index (foo) REFERENCES test(foo) ON DELETE CASCADE
    -> ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.02 sec)

mysql> SHOW CREATE TABLE test;
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                             |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| test  | CREATE TABLE `test` (
  `foo` int(11) DEFAULT NULL,
  KEY `test_foo_fk_index` (`foo`),
  CONSTRAINT `test_ibfk_1` FOREIGN KEY (`foo`) REFERENCES `test` (`foo`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1 | 
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
[30 Nov 2008 18:09] Valeriy Kravchuk
Thank you for a reasonable documentation request. I think that manual page should explain that constraint name is used in case it is explicitely set and there is no explicitely created index.
[30 Nov 2008 19:00] Jaka Jančar
Are you sure this is a docu problem and not a bug?

The behavior the docs describe seems logical to me.

Why would index_name work only if the constraint symbol was NOT specified? Shouldn't it work always when there's an index being implicitly generated?

So:
 - constraint symbol: if set, the supplied one is used, otherwise automatically generated
 - index_name: if already exists do nothing, otherwise create index with name index_name
One completely independent of the other.

What do you think?
[1 Dec 2008 15:30] Stefan Hinz
As the bug reporter has indicated, this doesn't look like a doc bug to me. Please clarify why this is not a server bug.
[1 Dec 2008 21:57] Valeriy Kravchuk
Based on reasoning from similar bug #35523, I'd say that this is a bug in parser (MySQL server), if not InnoDB bug. Server should give an error message (or, at least, a warning) in cases like this.
[1 Dec 2008 22:27] Jaka Jančar
As far as I understand databases, keys (indexes) and constraints are two conceptually different things. MySQL (and probably many others), however, mangles them together somehow.

For example, you could, theoretically, have a unique constraint WITHOUT using a unique index (I think Oracle supports this for deferred uniqueness checks). Heck, you could even enforce a unique constraint without any index at all by always doing a full table scan.

I see no reason (but that doesn't mean there isn't one ;)) why the parser should give any warnings or errors when you use a different name for the constraint than for the key. Also keep in mind that indexes' names must be unique in the table while constraints' names must be unique in the entire database.

But most importantly:
If I can create and name an index, and then create the constraint with a different name in another statement, why wouldn't doing these exact two things using the short (non-standard) syntax work?

So I think this indeed is a problem in the parser, but it should not be changed to give warnings/errors but instead just behave as expected and documented (KEY ...; CONSTRAINT ...; == CONSTRAINT ... KEY)
[5 Dec 2008 16:50] Mikhail Izioumtchenko
my opinion it's a doc bug. In the first example if I say
CONSTRAINT foo, I expect CREATE TABLE to say CONSTRAINT foo. 
On the other hand subsequent discussion shows it's not that obvious.
But my point is it looks more like a parser bug to me. 
Could you try it with another engine? Falcon supports foreign keys,
doesn't it?
[5 Dec 2008 17:53] Valeriy Kravchuk
With Falcon I've got:

C:\Program Files\MySQL\MySQL Server 5.0\bin>mysql -uroot -proot -P3311 test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 6.0.7-alpha-community MySQL Community Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> CREATE TABLE test (
    -> foo int,
    -> CONSTRAINT test_foo_fk_constraint FOREIGN KEY test_foo_fk_index (foo)
    -> REFERENCES test(foo) ON DELETE CASCADE
    -> ) ENGINE=Falcon;
Query OK, 0 rows affected (0.81 sec)

mysql> show create table test\G
*************************** 1. row ***************************
       Table: test
Create Table: CREATE TABLE `test` (
  `foo` int(11) DEFAULT NULL,
  KEY `test_foo_fk_constraint` (`foo`)
) ENGINE=Falcon DEFAULT CHARSET=utf8
1 row in set (0.13 sec)

So, the result (as well as foreign keys support itself) depends on storage engine. Doesn't InnoDB parse FOREIGN KEY syntax itself? 

Engine-independent foreign keys support is work in progress for 6.1, as far as I know (see bug #35523 on how this problem is currently solved there).
[5 Dec 2008 18:14] Mikhail Izioumtchenko
Valeriy, thanks for checking it.
Vasil is our foreign key master.
[5 Dec 2008 18:15] Jaka Jančar
Valeriy,

So if I understand it correctly, the FOREIGN KEY constraint isn't present in that SHOW CREATE TABLE, because MySQL doesn't support them universally yet, and Falcon (unlike InnoDB) doesn't (and won't) support them on their own? This makes sense...

Still, how is this relevant to this issue? My point is that the KEY (index, not FK constraint) should still be named using the provided KEY name, not the CONSTRAINT name. And your example demonstrates that Falcon behaves exactly like InnoDB in that regard. So how is this an InnoDB and not a parser/generic MySQL problem?

Sorry for repeating myself, but I think this is the heart of the problem and I'd be grateful if you could answer me:

Do you think that fk constraint name should always be equal to the index name?

If no, why shouldn't the user be able to specify different names using the non-standard syntax "CONSTRAINT ... KEY ..." just as he's able to do it with two separate clauses: "KEY ... , CONSTRAINT ..."? The syntax already supports it.

If yes, won't that prevent having an index with the same name in two tables? (since constraints must be unique within the database)
[25 Aug 2009 22:27] Jimmy Yang
This does seem to an innodb issue.

The index name is parse and set by mysql layer, and passed to the innodb. Innodb create the index with the name passed in.

The call stack is: 
mysql_create_table->mysql_create_table_no_lock->
rea_create_table->ha_create_table->ha_create->create->create_index

The index name for the index to be created is actually instantiated in the mysql layer, and passed into innodb create index functions.

mysql_create_table->mysql_create_table_no_lock->
rea_create_table->ha_create_table -> open_table_def -> open_binary_frm  

The index name is set in the table->key_info. From following debug output, we can see the share->key_info is instantiated in the open_binary_frm. The name is set to "test_foo_fk_constraint":

(gdb) n
631         error= open_binary_frm(thd, share, head, file);
2: share->key_info = (KEY *) 0x0
(gdb) n
632         *root_ptr= old_root;
2: share->key_info = (KEY *) 0x1607d8b8
(gdb) print *share->key_info
$25 = {key_length = 5, flags = 8256, key_parts = 1, extra_length = 1,
  usable_key_parts = 1, block_size = 0, algorithm = HA_KEY_ALG_UNDEF, {
    parser = 0x0, parser_name = 0x0}, key_part = 0x1607d910,
  name = 0x1607d931 "test_foo_fk_constraint", rec_per_key = 0x1607d950,
  handler = {bdb_return_if_eq = 0}, table = 0x0}
(gdb) where
#0  open_table_def (thd=0x1600a018, share=0x44a1b900, db_flags=0)
    at table.cc:632
#1  0x00000000007497c1 in ha_create_table (thd=0x1600a018,
    path=0x44a1c1a0 "./test/test", db=0x16067108 "test",
    table_name=0x16066da0 "test", create_info=0x44a1c740,
    update_create_info=false) at handler.cc:3569

/*
  Read data from a binary .frm file from MySQL 3.23 - 5.0 into TABLE_SHARE
*/

static int open_binary_frm(THD *thd, TABLE_SHARE *share, uchar *head,
                           File file)

I guess a bit more digging will get to the bottom where this name got set wrong, however, just from what we have here, more likely this bug should be addressed in the mysql codeline.

thanks
Jimmy
[25 Aug 2009 23:23] Jimmy Yang
In the previous comments, the first sentence should be:

"This does NOT seem to be an innodb issue.".
           ^^^
[9 Apr 2019 18:00] Dmitry Lenev
Posted by developer:
 
Hello!

Corresponding part of our documentation looks a bit differently for modern MySQL
versions (8.0, 5.7).
For example, https://dev.mysql.com/doc/refman/8.0/en/create-table-foreign-keys.html
says:

===
...
... The index_name value is ignored if there is already an explicitly defined index on the child table that can support the foreign key. Otherwise, MySQL implicitly creates a foreign key index that is named according to the following rules:

    * If defined, the CONSTRAINT symbol value is used. Otherwise, the FOREIGN KEY index_name value is used.

    * If neither a CONSTRAINT symbol or FOREIGN KEY index_name is defined, the foreign key index name is generated using the name of the referencing foreign key column. 
...
===

So the fact that index_name is ignored if CONSTRAINT clause is
present is reflected in current documentation. And this is
correctly describes current behavior of MySQL Server (both
5.7.27-git and 8.0.17-git return similar results):

===

CREATE TABLE parent (pk INT PRIMARY KEY);
CREATE TABLE child (fk1 INT, fk2 INT, fk3 INT, fk4 INT,
FOREIGN KEY (fk1) REFERENCES parent (pk),
FOREIGN KEY idx2 (fk2) REFERENCES parent (pk),
CONSTRAINT con3 FOREIGN KEY (fk3) REFERENCES parent (pk),
CONSTRAINT con4 FOREIGN KEY idx4 (fk4) REFERENCES parent (pk));

SHOW CREATE TABLE child;
#	Table   Create Table
#	child   CREATE TABLE `child` (
#		  `fk1` int(11) DEFAULT NULL,
#		  `fk2` int(11) DEFAULT NULL,
#		  `fk3` int(11) DEFAULT NULL,
#		  `fk4` int(11) DEFAULT NULL,
#		  KEY `fk1` (`fk1`),
#		  KEY `idx2` (`fk2`),
#		  KEY `con3` (`fk3`),
#		  KEY `con4` (`fk4`),
#		  CONSTRAINT `child_ibfk_1` FOREIGN KEY (`fk1`) REFERENCES `parent` (`pk`),
#		  CONSTRAINT `child_ibfk_2` FOREIGN KEY (`fk2`) REFERENCES `parent` (`pk`),
#		  CONSTRAINT `con3` FOREIGN KEY (`fk3`) REFERENCES `parent` (`pk`),
#		  CONSTRAINT `con4` FOREIGN KEY (`fk4`) REFERENCES `parent` (`pk`)
#		) ENGINE=InnoDB DEFAULT CHARSET=latin1

===

So this is no longer can be considered as a code or documentation issue.

In theory using index_name as name for automagically generated index for
a foreign key even if "CONSTRAINT symbol" clause is present can be treated
as feature request.

However, since:
1) there is a simple and more standard workaround - one can simply create
   supporting index with necessary name explicitly.
2) from the past experience we know that any such change of behavior that was
   around for long time is likely to cause issue for some user.
this is feature request is unlikely to be implemented without strong additional
arguments.

Taking into account all the above I am closing this report as "Not a bug"
(alternatively we can close it as "Fixed Documentation bug").