Bug #63408 Generated SQL inserts in wrong order (failing PK requirements)
Submitted: 23 Nov 2011 20:48 Modified: 30 May 2012 22:10
Reporter: Javier Ortiz Email Updates:
Status: Not a Bug Impact on me:
Category:MySQL Workbench: Modeling Severity:S2 (Serious)
Version:5.2.35 OS:Any
Assigned to: CPU Architecture:Any

[23 Nov 2011 20:48] Javier Ortiz
Tries to insert rows in tables with references to other tables not populated yet.

How to repeat:
1) Generate the sql file via Database>Forward Engineer of the attached model.
2) Execute the script in the database
[23 Nov 2011 20:49] Javier Ortiz
Model for test

Attachment: Xinco.mwb (application/x-zip-compressed, text), 62.82 KiB.

[23 Nov 2011 20:50] Javier Ortiz
The issue showed on 5.2.32, saw there was an update and retried on the new version with the same result.
[23 Nov 2011 20:58] Javier Ortiz
Updated model, previous had an error.

Attachment: Xinco.mwb (application/x-zip-compressed, text), 62.82 KiB.

[24 Nov 2011 10:57] Valeriy Kravchuk
Verified just as described with your .mwb file (after fixing several syntax errors in file generated by Workbench, like comma (',') before COMMENT clause). 

Looks like INSERTs are generated with foreign keys in place just in the alphabetical order of table names, without any regard to foreign key contraints present.
[30 May 2012 19:50] Oleksandr Musienko
The model attached contains duplicated table name xinco_core_user (looks like one of them missing _t) with 1st table holding inserts and 2nd constraints. Thus leading to table with constraints being created in correct order, but inserts are generated in regards to 2nd table that doesn't have any constraints and thus haven't any INSERTS ordering.
[30 May 2012 21:53] Javier Ortiz
The tables are not duplicate. They are different and on purpose. The one with the _T is an audit trail table that basically holds all the data in the original one and some additional data in related tables. I still believe is a bug.
[30 May 2012 22:10] Alfredo Kojima
I see 2 tables called xinco_core_user in your model.
[30 May 2012 22:12] Alfredo Kojima
Clarifying, you have 2 tables called xinco_core_user and an extra xinco_core_user_t.
Check int the Overview table list or the Catalog tree.