Bug #67304 mySQL workbench is dropping foreign keys durring sycronize with DB when updating
Submitted: 19 Oct 2012 20:54 Modified: 23 Nov 2012 16:42
Reporter: Shaun Clarkson Email Updates:
Status: No Feedback Impact on me:
None 
Category:MySQL Workbench: Modeling Severity:S3 (Non-critical)
Version:5.2.44 win32.msi OS:Windows
Assigned to: CPU Architecture:Any

[19 Oct 2012 20:54] Shaun Clarkson 
Description:
Connecting to 
Server version: 5.0.95

With an original create table like this.

CREATE TABLE `ps_suk_bli_xref_sbx` (
`id_bli_sbx` int(10) unsigned NOT NULL,
`id_suk_sbx` int(10) unsigned NOT NULL,
FOREIGN KEY (`id_bli_sbx`) REFERENCES `ps_billing_line_item_bli` (`id_bli` ),
FOREIGN KEY (`id_suk_sbx`) REFERENCES `ps_system_user_key_suk` (`id_suk` )
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

When showing a create for a table on the command line for a table with valid foreign keys, it looks like this.

 CREATE TABLE `ps_suk_bli_xref_sbx` (
  `id_bli_sbx` int(10) unsigned NOT NULL,
  `id_suk_sbx` int(10) unsigned NOT NULL,
  KEY `id_bli_sbx` (`id_bli_sbx`),
  KEY `id_suk_sbx` (`id_suk_sbx`),
  CONSTRAINT `ps_suk_bli_xref_sbx_ibfk_1` FOREIGN KEY (`id_bli_sbx`) REFERENCES `ps_billing_line_item_bli` (`id_bli`),
  CONSTRAINT `ps_suk_bli_xref_sbx_ibfk_2` FOREIGN KEY (`id_suk_sbx`) REFERENCES `ps_system_user_key_suk` (`id_suk`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

When imported into mysqlworkbench and requesting a change to the model from the original DB, this is the change that is wanting to execute.

ALTER TABLE `con_ps_permission_services`.`ps_suk_bli_xref_sbx` DROP FOREIGN KEY `ps_suk_bli_xref_sbx_ibfk_1` , DROP FOREIGN KEY `ps_suk_bli_xref_sbx_ibfk_2` 

ALTER TABLE `con_ps_permission_services`.`ps_suk_bli_xref_sbx` 

  ADD CONSTRAINT `ps_suk_bli_xref_sbx_ibfk_1`

  FOREIGN KEY ()

  REFERENCES `con_ps_permission_services`.`ps_billing_line_item_bli` (), 

  ADD CONSTRAINT `ps_suk_bli_xref_sbx_ibfk_2`

  FOREIGN KEY ()

  REFERENCES `con_ps_permission_services`.`ps_system_user_key_suk` ()

, ADD UNIQUE INDEX `single_xref_check` (`id_bli_sbx` ASC, `id_suk_sbx` ASC) 

, ADD INDEX `id_bli_sbx_p` () 

, ADD INDEX `id_suk_sbx_p` () 

, DROP INDEX `id_suk_sbx` 

, DROP INDEX `id_bli_sbx` 

At this point all the FOREIGN KEY () is empty, and when updating model the constraints fail.

Repeated attempts to rebuild the DB from dumps and ground up have not worked. 

After update a second sync causes a Type mismatch: expected object if type db.mysql.Table but got db.mysql.ForeignKey.

How to repeat:
Connecting to 
Server version: 5.0.95

With an original create table like this.

CREATE TABLE `ps_suk_bli_xref_sbx` (
`id_bli_sbx` int(10) unsigned NOT NULL,
`id_suk_sbx` int(10) unsigned NOT NULL,
FOREIGN KEY (`id_bli_sbx`) REFERENCES `ps_billing_line_item_bli` (`id_bli` ),
FOREIGN KEY (`id_suk_sbx`) REFERENCES `ps_system_user_key_suk` (`id_suk` )
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

When showing a create for a table on the command line for a table with valid foreign keys, it looks like this.

 CREATE TABLE `ps_suk_bli_xref_sbx` (
  `id_bli_sbx` int(10) unsigned NOT NULL,
  `id_suk_sbx` int(10) unsigned NOT NULL,
  KEY `id_bli_sbx` (`id_bli_sbx`),
  KEY `id_suk_sbx` (`id_suk_sbx`),
  CONSTRAINT `ps_suk_bli_xref_sbx_ibfk_1` FOREIGN KEY (`id_bli_sbx`) REFERENCES `ps_billing_line_item_bli` (`id_bli`),
  CONSTRAINT `ps_suk_bli_xref_sbx_ibfk_2` FOREIGN KEY (`id_suk_sbx`) REFERENCES `ps_system_user_key_suk` (`id_suk`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

When imported into mysqlworkbench and requesting a change to the model from the original DB, this is the change that is wanting to execute.

ALTER TABLE `con_ps_permission_services`.`ps_suk_bli_xref_sbx` DROP FOREIGN KEY `ps_suk_bli_xref_sbx_ibfk_1` , DROP FOREIGN KEY `ps_suk_bli_xref_sbx_ibfk_2` 

ALTER TABLE `con_ps_permission_services`.`ps_suk_bli_xref_sbx` 

  ADD CONSTRAINT `ps_suk_bli_xref_sbx_ibfk_1`

  FOREIGN KEY ()

  REFERENCES `con_ps_permission_services`.`ps_billing_line_item_bli` (), 

  ADD CONSTRAINT `ps_suk_bli_xref_sbx_ibfk_2`

  FOREIGN KEY ()

  REFERENCES `con_ps_permission_services`.`ps_system_user_key_suk` ()

, ADD UNIQUE INDEX `single_xref_check` (`id_bli_sbx` ASC, `id_suk_sbx` ASC) 

, ADD INDEX `id_bli_sbx_p` () 

, ADD INDEX `id_suk_sbx_p` () 

, DROP INDEX `id_suk_sbx` 

, DROP INDEX `id_bli_sbx` 

At this point all the FOREIGN KEY () is empty, and when updating model the constraints fail.

Repeated attempts to rebuild the DB from dumps and ground up have not worked. 

After update a second sync causes a Type mismatch: expected object if type db.mysql.Table but got db.mysql.ForeignKey.
[19 Oct 2012 20:59] Shaun Clarkson 
Of note is that the indexes 

, ADD INDEX `id_bli_sbx_p` () 

, ADD INDEX `id_suk_sbx_p` () 

were added to a create in an attempt to rename the INDEX values so that the INDEX and the FOREIGN KEY would have different values.
[19 Oct 2012 21:05] Shaun Clarkson 
Another note, after giving up on my existing model and re-starting, it seems that the model now shows the correct Key Constraints.  This problem therefore appears to happen when you update tables with keys on the DB, and then try to synchronize back to the workbench. Workbench appears to hang on or not fully clean out keys when doing this.
[23 Oct 2012 16:42] MySQL Verification Team 
Thank you for the bug report. If I understood the issue happens when changes is done in the live server and then synchronize with WorkBench model? .
[19 Nov 2012 15:55] MySQL Verification Team 
http://bugs.mysql.com/bug.php?id=67624 duplicate of this one.
[24 Nov 2012 1:00] Bugs System 
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".