MySQL Bugs: #61594: INSERT through TRIGGER fails in n-n relationship

Bug #61594	INSERT through TRIGGER fails in n-n relationship
Submitted:	22 Jun 2011 7:19	Modified:	11 Jun 2013 23:37
Reporter:	Luis Rodrigues	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Workbench: Modeling	Severity:	S4 (Feature request)
Version:	5.2.34 CE Rev 7780	OS:	Windows (7)
Assigned to:		CPU Architecture:	Any
Tags:	insert, n-n, relationship, trigger

Description:
A trigger meant to insert conections on an n-n relationship fails on insert due to wrong insert order in mai tables

How to repeat:
1 - create table a
2 - create table b
3 - insert data into table a
4 - insert data into table b
5 - establish n-n relationship between tables a and b (table a has b is created automaticly)
6 - create a trigger on table a for after insert on table a insert default relationship on table a_has_b (NEW key from a and first key from b)

fails on forward engeniering saying b_key on table a_has_b is foreign key for table b and can't be NULL

Suggested fix:
Make sure all tables with triggers are inserted to last

Another option is to create all triggers after all inserts are done - of course this has the drawback of not running the trigger for the inserts that are placed in the Workbench

Hello Luis.
Thanks a lot for your report.
Would you mind to provide us the following information:
- The output that you get from "Help->System Info" in Workbench.
- A copy of the model where you can reproduce this problem.
Thanks.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

Sorry about the late reply - have been busy on unrelated projects.

I don't have a sample at this time, but:
a) Following the instructions i left should create one for you.
b) I have actually found a solution for this

Solutio:

1 - Forward Engineer the database with Skip Foreign Key creation and Skip FK Indexes turned on.
2 - Synchroniz Model to add all Foreign Keys and FK Indexes

This procedure solves the problem so the solution to the issue is simply placing all Foreign Key's and FK Indexes creation statements at the end of the generated script when Forward Engeniering the database (or at least add this as an option)

i should also add i have since updated to version 5.2.35 CE Revision 7925 and i still need to do that procedure to get it working

Reproduced as reported.
Severity changed as Feature request.
Thanks for your report Luis.

Evidence

Attachment: test.mwb (application/octet-stream, text), 6.99 KiB.

Fixed as of the upcoming MySQL Workbench 6.0.2 release, and here's the changelog entry:

A trigger meant to insert connections on an n-n relationship would fail on
"INSERT" due to a wrong insert order produced during the Forward
Engineering step. 

A workaround was to forward engineer the database with
the "Skip Foreign Key creation" and "Skip FK Indexes" options enabled
while executing "Synchronize Model".

Thank you for the bug report.