Bug #63642 MySQL Workbench EER Diagram allows you to use duplicate foreign key names

• Submitted: 7 Dec 2011 7:31
• Modified: 23 Jul 2012 17:22
• Reporter: Mark Holmes
• Status: Closed
• Category: MySQL Workbench: Modeling
• Severity: S2 (Serious)
• Version: 5.2.36
• OS: Windows (Windows 7 x64)
• CPU Architecture: Any
• Tags: dupliciate, foreign key, modeling, name

[7 Dec 2011 7:31] Mark Holmes

Description:
The GUI MySQL Workbench EER Diagram Designer allows to you add multiple foreign keys across seperate tables with the same foreign key name. When you then attempt to Synchronize your model with a live MySQL server, MySQL Server produces a cryptic error message as follows:

Error Code: 1005. Can't create table './<dbname>/#sql-25c2_13bf8.frm' (errno: 121)

Where <dbname> is the name of your volume.

How to repeat:
Add three tables, table1, table2, table3.

Add a foreign key constraint to table1, linking it to table2, with foreign key name "my_foreign_key".

Add a foreign key constraint to table3 linking it to table2, with foreign key name "my_foreign_key".

Synchronize your model to a MySQL server and you will receive an error as the SQL executes in the final stages of synchonization.

Suggested fix:
The GUI should enforce unique foreign key names across the entire volume.

Additionally it would be nice if MySQL provided an easier to understand error message.

[7 Dec 2011 7:55] Valeriy Kravchuk

Please, check if the same problem still happens with a newer version, 5.2.36. It does NOT allow me to have 2 foreign keys with the same name defined for one table, and having foreign keys with the same name defined on different tables does not lead to any errors (while working with MySQL 5.1.58) as far as I can see.

Maybe I do not get your exact point, so, please, upload simple .mwb file that demonstrates it, if 5.2.36 is still affected.

[7 Dec 2011 11:11] Mark Holmes

Thank you, I will try to replicate from scratch with a simple new model in the current version and then open the same model in a new version and see if it corrects the problem. If not I'll upload the model files.

Cheers
Mark

[7 Dec 2011 11:33] Mark Holmes

Have verified this bug exists in 5.2.36 as well

[7 Dec 2011 11:34] Mark Holmes

The schema SQL that MySQL Workbench generates when Synchonizing

Attachment: schema.txt (text/plain), 1.40 KiB.

[7 Dec 2011 11:34] Mark Holmes

The error log that is produced after Synchonizing

Attachment: error-log.txt (text/plain), 732 bytes.

[7 Dec 2011 11:35] Mark Holmes

Other information

MySQL Server running on Linux VPS
Connection via TCP over SSH
MySQL Server version 5.0.77

[7 Dec 2011 12:31] Valeriy Kravchuk

OK, now I got it. I tried to Forward engineer model, not to Syncronize it. Got the same error as yours with your .mwb file while working with 5.1.58.

[7 Dec 2011 12:34] Mark Holmes

Thanks, this is no big deal for me as I obviously will use unique foreign key names from now on however it was a bit confusing for a while there so should probably be fixed in future versions.

By the way, this software is amazingly good. Can't believe I was still using MySQL Query Browser & Administrator!

[23 Jul 2012 17:22] Philip Olson

This has been fixed as of the soon-to-be-released Workbench 5.2.41, and 
here's the changelog entry:

MySQL Workbench would allow the creation of multiple foreign keys across
multiple tables but with the same foreign key name. Synchronization
would then emit a "Can't create table" error.

[17 Jul 2013 11:03] risman hidayat

I still have this issue in version 5.2.47 Revision 10398
This is not patched

[17 Jul 2013 11:30] risman hidayat

I'm working on Mac OSX So I guess I should open a new bug report?

[17 Jul 2013 13:13] MySQL Verification Team

Bug #69764 Marked as duplicate of this.