Bug #99299 Synchronize model *always* want to alter foreign keys
Submitted: 18 Apr 2020 23:18 Modified: 25 May 2020 13:53
Reporter: Steven Pearce Email Updates:
Status: Verified Impact on me:
Category:MySQL Workbench: Modeling Severity:S3 (Non-critical)
Version:8.0.19 OS:Any
Assigned to: CPU Architecture:x86
Tags: alter, sync

[18 Apr 2020 23:18] Steven Pearce
Symptoms seem very similiar to https://bugs.mysql.com/bug.php?id=60230, which was fixed many moons ago.
I don't know if this is a regression or a different problem where the symptoms are the same.

I have a MWB file that has 3 linked tables. everytime it is syncronized to a MySQL 8.0.18 DB, it will drop and re-create the foreign keys between the tables.

Can be executed multiple times but will always identify the same changes as required.

Through the general Log I can see it executes
SHOW CREATE TABLE `test`.`response_attempt`;

and gets

CREATE TABLE `response_attempt` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `response_id` bigint(20) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_attempts_response1_idx` (`response_id`),
  CONSTRAINT `fk_attempts_response1` FOREIGN KEY (`response_id`) REFERENCES `response` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

The then executes
ALTER TABLE `test`.`response_attempt` 
DROP FOREIGN KEY `fk_attempts_response1`


ALTER TABLE `test`.`response_attempt` 
ADD CONSTRAINT `fk_attempts_response1`
  FOREIGN KEY (`response_id`)
  REFERENCES `test`.`response` (`id`)

And then on further resyncs will peform the same actions.

How to repeat:
I have a test MWB that I will attach.
[18 Apr 2020 23:20] Steven Pearce
Simple 3 Table Test Case to demonstrate the problem

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

[19 Apr 2020 6:55] MySQL Verification Team
Hello Steven,

Thank you for the report and feedback.
Verified as described with provided model file on Win10 with WB 8.0.19.

[25 May 2020 13:53] Steven Pearce
verified on Win10 so change OS to Any
[13 Aug 2020 14:51] Oracle Oracle
It appears that MySQL 8.0 will convert a NO ACTION foreign key referential action into a RESTRICT, whereas 5.7 would leave it as NO ACTION. So if your foreign key actions are set to NO ACTION, the server will convert these to RESTRICT (as they are identical in MySQL), but Workbench sees the difference between the model (NO ACTION) and the server (RESTRICT) and thinks it needs to recreate them.

Updating your model's keys to RESTRICT instead of NO ACTION seems to be a workaround. If you have a large model, then you can extract the .xml from the .mwb (which is just a .zip) and find / replace each NO ACTION with RESTRICT, then put it back into the .mwb.