Bug #63956 WB inconsistenty handles index and FK names for certain MySQL server versions
Submitted: 7 Jan 2012 14:07 Modified: 9 Jan 2015 16:17
Reporter: Karsten Wutzke Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Workbench: Modeling Severity:S1 (Critical)
Version:5.2.37, 5.2.38 OS:Any
Assigned to: CPU Architecture:Any

[7 Jan 2012 14:07] Karsten Wutzke
Description:
The policy for foreign key and index names has changed somewhere between MySQL 5.1 and MySQL 5.5 servers. See here:

http://forums.mysql.com/read.php?10,507796,507796

For earlier 5.1 versions the same FK and index names were allowed, so the code in that forum post worked for earlier 5.1 versions, but no longer works for later 5.1 versions and especially version 5.5. (exactly when the policy changed is for you to find out).

There are two problem areas in WB:

1. WB doesn't create different FK and index names when modeling relationships
2. Forward engineering creates erroneous DDL scripts (they'll fail when executed on newer servers)

Because of 1. I'll mark this one as critical.

Please also revamp WB so that it doesn't create and export redundant indexes (again see forum post)! phpMyAdmin usually warns me about unnecessary indexes that might be deleted.

How to repeat:
see desc

Suggested fix:
Quickly!
[7 Jan 2012 15:25] Valeriy Kravchuk
Yes, Workbench gives the same name for indexes and constraints, but I fail to see any error as a result. Look:

macbook-pro:5.5 openxs$ bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 16
Server version: 5.5.20-debug Source distribution

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> drop database mydb;
Query OK, 4 rows affected (2.54 sec)

Here I forward engineered simple model with 2 tables and FK:

mysql> use mydb;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show create table table2\G
*************************** 1. row ***************************
       Table: table2
Create Table: CREATE TABLE `table2` (
  `id` int(11) NOT NULL,
  `table1_id` int(11) NOT NULL,
  PRIMARY KEY (`id`,`table1_id`),
  KEY `fk_table2_table1` (`table1_id`),
  CONSTRAINT `fk_table2_table1` FOREIGN KEY (`table1_id`) REFERENCES `table1` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.05 sec)

So, what exact server versions do NOT allow you to create foreign key with the same name as index?
[8 Jan 2012 18:58] Karsten Wutzke
Have you tried the snippet in the forum post I mentioned?

My MySQL version is 5.5.19
[8 Jan 2012 19:10] Valeriy Kravchuk
Indeed, your snippet does not work with 5.5.19 and old 5.5.20 snapshot I have, but it works in 5.1.61:

macbook-pro:5.1 openxs$ bin/mysql -uroot test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.1.61-debug Source distribution

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> CREATE  TABLE IF NOT EXISTS `GeoAreas`
    -> (
    ->   `id` INT(11)  NOT NULL AUTO_INCREMENT ,
    ->   `parent_id` INT(11)  NULL ,
    ->   `name` VARCHAR(50) NOT NULL ,
    ->   PRIMARY KEY (`id`) ,
    ->   CONSTRAINT `geoareas_self_fk` FOREIGN KEY (`parent_id` ) REFERENCES `GeoAreas` (`id` ) ON DELETE NO ACTION ON UPDATE CASCADE
    -> );
Query OK, 0 rows affected (0.05 sec)

mysql> CREATE INDEX `geoareas_self_fk` ON `GeoAreas` (`parent_id` ASC) ;
Query OK, 0 rows affected (0.08 sec)
Records: 0  Duplicates: 0  Warnings: 0

For Workbench problem starts when you generate separate CREATE INDEX statements,so we may have a valid workaround here... I still need to find out what exact change in 5.5 leads to this. Some time next week maybe.
[8 Jan 2012 19:15] Valeriy Kravchuk
Sorry, my previous test with 5.1.61 was wrong, as there table was created as MyISAM by default, thus no foreign key and no index before explicit CREATE INDEX... So, yes, Workbench should NOT give the same names to indexes as for foreign keys to be safe. 

Even better, it should NOT add explicit index at all if InnoDB table with FOREIGN KEY is created.
[23 Mar 2012 7:17] Valeriy Kravchuk
Bug #64722 was marked as a duplicate of this one.
[21 Aug 2012 18:33] Luke Stevens
Duplicate of Bug #60705
[11 Sep 2012 21:48] David Dykstra
@Valeriy Kravchuk
The reference http://dev.mysql.com/doc/refman/5.5/en/innodb-foreign-key-constraints.html states though that:
InnoDB requires indexes on foreign keys and referenced keys so that foreign key checks can be fast and not require a table scan. 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 index might be silently dropped later, if you create another index that can be used to enforce the foreign key constraint. index_name, if given, is used as described previously.