MySQL Bugs: #38710: Foreign keys dropped after attempting to change cascade.

Bug #38710	Foreign keys dropped after attempting to change cascade.
Submitted:	11 Aug 2008 8:29	Modified:	13 Oct 2008 8:11
Reporter:	David Sanders (Candidate Quality Contributor)	Email Updates:
Status:	Won't fix	Impact on me:	None
Category:	MySQL Query Browser	Severity:	S3 (Non-critical)
Version:	1.2.12	OS:	Windows (XP SP3)
Assigned to:	Mike Lischke	CPU Architecture:	Any

Description:
After attempting to change the "On Delete" to "Set Null" for a "Not Null" foreign key, you'll correctly receive an error.  After clicking ok on the error dialog and then clicking "Discard Changes", the foreign key is actually dropped (erroneously).

Before applying the changes in "How to repeat" below, the discard changes does actually work correctly.  It's only after the apply changes that discard deletes the FK.

How to repeat:
1. Create a table 'foo' with a single primary key

2. Create a table 'bar' with a primary key and a foreign key referencing the primary key of foo that is 'Not Null' and has both 'Restrict' for 'On Delete' and 'On Cascade' (and then apply changes and close the create table dialog)

3. Reopen the Edit Table window for the table 'bar'

4. Change 'On Delete' to 'Set Null'

5. Click 'Apply Changes'

6. Click 'Execute'

-> An error should display: "Error while executing query......."

7. Click 'OK'

8. Click 'Discard Changes'

-> Confirmation dialog appears

9. Click 'Yes'

-> The foreign key disappears and any attempt to reopen the 'Edit Table' window does not show the foreign key, so it appears to have actually been dropped.

This may be related to Bug #28885

Thank you for the bug report.

I'm afraid currently we can't do anything about this. The problem here is that we'd either need something like a syntax check by the server before applying any change (so we would not drop anything if there is an error) or have transactional DDL or know the reverse operation to "undo" the drop, which is necessary before creating the new FK. Unfortunately, none of them are available to QB so when you decide to execute the SQL you get shown in the popup when you click "Apply Changes" then two commands are actually executed: a drop for the old key and the alter command for the new key. Hence if the second command fails the first one is still already done and the key is gone. So check carefully what gets executed. QB is not a tool to limit a user but can do some serious damage if used wrongly.

Since the QB features will be included into MySQL Workbench at a later point in time and we have much better parsing capabilities there, we can then at least "undo" the drop operation.

This bug is not scheduled to be fixed at this time.