Bug #58556 Foreign keys not added after changing storage engine from MyISAM to InnoDB

• Submitted: 29 Nov 2010 12:20
• Modified: 29 Dec 2012 1:07
• Reporter: Artur Stępień
• Status: Can't repeat
• Category: MySQL Workbench: Modeling
• Severity: S3 (Non-critical)
• Version: 5.2.30 CE, 5.2.31
• OS: Any ( Windows 7 x64 Ultimate, Mac OS X)
• CPU Architecture: Any
• Tags: change, innodb, keys, myisam, storage engine

[29 Nov 2010 12:20] Artur Stępień

Description:
I've created db model with storage engine set to MyISAM. Then added foreign keys to improve readability of relations (with ON UPDATE and ON DELETE set for every key).
After changing store engine to InnoDB (and synchronize with db), WB didn't added foregin keys.

How to repeat:
- Create tables with Store Engine set to MyISAM
- Add some Foreign Keys
- Synchronize model with DB
- Change Store Engine to InnoDB
- Synchronize model with DB

[29 Nov 2010 12:20] Artur Stępień

DB model before

Attachment: test before.mwb (application/x-zip-compressed, text), 10.36 KiB.

[29 Nov 2010 12:21] Artur Stępień

DB model after

Attachment: test after.mwb (application/x-zip-compressed, text), 10.37 KiB.

[28 Dec 2010 14:46] MySQL Verification Team

Editing the tables of test_before project crash the current 5.2.31 Workbench.

[28 Dec 2010 14:50] MySQL Verification Team

Crash of 5.2.31

Attachment: beforeCrash.png (image/png, text), 162.63 KiB.

[1 Jan 2011 12:55] Valeriy Kravchuk

Verified as described initially with 5.2.31 on Mac OS X. This is the code generated after changing storage engine for all tables to InnoDB:

SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL';

ALTER TABLE `kina_testing_db`.`jos_kina` ENGINE = InnoDB ;

ALTER TABLE `kina_testing_db`.`jos_kina_filmy` ENGINE = InnoDB ;

ALTER TABLE `kina_testing_db`.`jos_kina_seanse` ENGINE = InnoDB ;

ALTER TABLE `kina_testing_db`.`jos_kina_miasta` ENGINE = InnoDB ;

ALTER TABLE `kina_testing_db`.`jos_kina_seanse_filmy` ENGINE = InnoDB ;

SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;

So, foreign keys are not created.

[29 Dec 2012 1:07] Alfredo Kojima

This bug no longer occurs as of WB 5.2.45