Bug #67683 WB claims it corrected problems in mwb file but same message re-appears
Submitted: 23 Nov 2012 9:27 Modified: 2 Jan 2013 22:13
Reporter: Ingo Korndoerfer Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Workbench: Modeling Severity:S3 (Non-critical)
Version:5.2.44 OS:Windows (XP)
Assigned to: CPU Architecture:Any

[23 Nov 2012 9:27] Ingo Korndoerfer
Description:
Upon loading an .mwb file a window pops up:

"Corrected Model File"

The model file ... contained problems which were succesfully recovered .....
the reference was removed from the index ... etc ...

The log file shows the following.

10:21:27 [WRN][               ]: C:\Dokumente und Einstellungen\Korndoerfer\Anwendungsdaten\MySQL\Workbench/CrystalTrain_230712b.mwbd/document.mwb.xml:55624: link '{878082A6-223F-4C38-B7EB-7A59F8545AA5}' <object db.mysql.Table> key=referencedTable could not be resolved
10:21:27 [WRN][               ]: C:\Dokumente und Einstellungen\Korndoerfer\Anwendungsdaten\MySQL\Workbench/CrystalTrain_230712b.mwbd/document.mwb.xml:55626: link '{6B9EE206-4875-4E9D-BF91-ED664945A375}' <object > key= could not be resolved
10:21:27 [WRN][               ]: C:\Dokumente und Einstellungen\Korndoerfer\Anwendungsdaten\MySQL\Workbench/CrystalTrain_230712b.mwbd/document.mwb.xml: skipping element 'link' in unserialized document, line 55626
10:21:27 [WRN][               ]: C:\Dokumente und Einstellungen\Korndoerfer\Anwendungsdaten\MySQL\Workbench/CrystalTrain_230712b.mwbd/document.mwb.xml:55630: link '{ED782394-541C-4D40-8BDD-B901D3D554DD}' <object db.Index> key=index could not be resolved
10:21:27 [WRN][               ]: C:\Dokumente und Einstellungen\Korndoerfer\Anwendungsdaten\MySQL\Workbench/CrystalTrain_230712b.mwbd/document.mwb.xml:55636: link '{73489AA8-D937-4F46-9E3E-47343CDE6B5A}' <object > key= could not be resolved
10:21:27 [WRN][               ]: C:\Dokumente und Einstellungen\Korndoerfer\Anwendungsdaten\MySQL\Workbench/CrystalTrain_230712b.mwbd/document.mwb.xml: skipping element 'link' in unserialized document, line 55636
10:21:27 [WRN][               ]: C:\Dokumente und Einstellungen\Korndoerfer\Anwendungsdaten\MySQL\Workbench/CrystalTrain_230712b.mwbd/document.mwb.xml:55645: link '{8AAC33A2-863D-4CED-9160-25597F041979}' <object db.mysql.Table> key=referencedTable could not be resolved
10:21:27 [WRN][               ]: C:\Dokumente und Einstellungen\Korndoerfer\Anwendungsdaten\MySQL\Workbench/CrystalTrain_230712b.mwbd/document.mwb.xml:55647: link '{F2D8198B-30F3-4866-ACED-7CE68167CB01}' <object > key= could not be resolved
10:21:27 [WRN][               ]: C:\Dokumente und Einstellungen\Korndoerfer\Anwendungsdaten\MySQL\Workbench/CrystalTrain_230712b.mwbd/document.mwb.xml: skipping element 'link' in unserialized document, line 55647
10:21:27 [WRN][               ]: C:\Dokumente und Einstellungen\Korndoerfer\Anwendungsdaten\MySQL\Workbench/CrystalTrain_230712b.mwbd/document.mwb.xml:55651: link '{38901F3D-26E6-4CA6-9AB2-274B7B5B6422}' <object db.Index> key=index could not be resolved
10:21:27 [WRN][               ]: C:\Dokumente und Einstellungen\Korndoerfer\Anwendungsdaten\MySQL\Workbench/CrystalTrain_230712b.mwbd/document.mwb.xml:55657: link '{DF8BB0EA-E33D-4375-AE47-3FD86F18091C}' <object > key= could not be resolved
10:21:27 [WRN][               ]: C:\Dokumente und Einstellungen\Korndoerfer\Anwendungsdaten\MySQL\Workbench/CrystalTrain_230712b.mwbd/document.mwb.xml: skipping element 'link' in unserialized document, line 55657
10:21:27 [WRN][               ]: C:\Dokumente und Einstellungen\Korndoerfer\Anwendungsdaten\MySQL\Workbench/CrystalTrain_230712b.mwbd/document.mwb.xml:55765: link '{F2D8198B-30F3-4866-ACED-7CE68167CB01}' <object db.Column> key=referencedColumn could not be resolved
10:21:27 [WRN][               ]: C:\Dokumente und Einstellungen\Korndoerfer\Anwendungsdaten\MySQL\Workbench/CrystalTrain_230712b.mwbd/document.mwb.xml:55788: link '{6B9EE206-4875-4E9D-BF91-ED664945A375}' <object db.Column> key=referencedColumn could not be resolved

After loading of the mwb is completed, the .mwb is saved, wb restarted and the .mwb file loaded again, the same message pops up again, although the error was supposedly fixed.

How to repeat:
See above.
[23 Nov 2012 13:52] Ingo Korndoerfer
I have all day been trying to rescue my .mwb file, repairing table by table. I did notice now, that after repairing one table, i.e. doing all changes in the model by hand, one, by one, until it was synchronized with the database, this error did appear again after re-opening the .mwb file. Subsequently, all the manual synchronization work was undone again. So it seems, rather than correcting one problem with foreign keys upon startup, workbench makes the situation worse.
[24 Nov 2012 10:21] Ingo Korndoerfer
I have continued to work on my db an managed to narrow the problem down further. Attached is the current .mwb. The following changes are suggested from the synchronizer:

###############################################################################

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,ALLOW_INVALID_DATES';

ALTER TABLE `CrystalTrain`.`tbl_purificationlots` DROP FOREIGN KEY `tbl_purificationtypestbl_purificationlots` , DROP FOREIGN KEY `tbl_purificationlotpuritypeakstbl_purificationlots` , DROP FOREIGN KEY `tbl_purificationlotpuritygradestbl_purificationlots` , DROP FOREIGN KEY `tbl_purificationlotpuritybandstbl_purificationlots` , DROP FOREIGN KEY `tbl_expressionlotstbl_purificationlots1` , DROP FOREIGN KEY `tbl_expressionlotstbl_purificationlots` ;

ALTER TABLE `CrystalTrain`.`tbl_expressionflasks` DROP FOREIGN KEY `tbl_compoundstbl_expressionflasks_define` ;

ALTER TABLE `CrystalTrain`.`tbl_purificationexperimentdocs` DROP FOREIGN KEY `fk_tbl_puriexp_SDSGel_Elutionprofile_tbl_puriexps` ;

ALTER TABLE `CrystalTrain`.`tbl_purificationexperiments` DROP FOREIGN KEY `tbl_purificationlotstbl_purificationexperiments` , DROP FOREIGN KEY `tbl_experimenttypes_purificationexperiments` ;

ALTER TABLE `CrystalTrain`.`tbl_purificationlots` 
  ADD CONSTRAINT `fk_tbl_expressionlots_tbl_purificationlots1`
  FOREIGN KEY (`ExpressionLotID` )
  REFERENCES `CrystalTrain`.`tbl_expressionlots` (`ExpressionLotID` )
  ON DELETE NO ACTION
  ON UPDATE CASCADE, 
  ADD CONSTRAINT `fk_tbl_expressionlots_tbl_purificationlots2`
  FOREIGN KEY (`ExpressionLot2ID` )
  REFERENCES `CrystalTrain`.`tbl_expressionlots` (`ExpressionLotID` )
  ON DELETE NO ACTION
  ON UPDATE CASCADE, 
  ADD CONSTRAINT `fk_tbl_purificationlotpuritybands_tbl_purificationlots`
  FOREIGN KEY (`PurificationLotPurityBands` )
  REFERENCES `CrystalTrain`.`tbl_purificationlotpuritybands` (`PurificationLotPurityBandsID` )
  ON DELETE NO ACTION
  ON UPDATE CASCADE, 
  ADD CONSTRAINT `fg_tbl_purificationlotpuritygrades_tbl_purificationlots`
  FOREIGN KEY (`PurificationLotPurity%` )
  REFERENCES `CrystalTrain`.`tbl_purificationlotpuritygrades` (`PurificationLotPurityGradesID` )
  ON DELETE NO ACTION
  ON UPDATE CASCADE, 
  ADD CONSTRAINT `fk_tbl_purificationlotpuritypeaks_tbl_purificationlots`
  FOREIGN KEY (`PurificationLotPurityPeaks` )
  REFERENCES `CrystalTrain`.`tbl_purificationlotpuritypeaks` (`PurificationLotPurityPeaksID` )
  ON DELETE NO ACTION
  ON UPDATE CASCADE, 
  ADD CONSTRAINT `fk_tbl_purificationtypes_tbl_purificationlots`
  FOREIGN KEY (`PurificationTypeID` )
  REFERENCES `CrystalTrain`.`tbl_purificationtypes` (`PurificationTypeID` )
  ON DELETE NO ACTION
  ON UPDATE CASCADE
, ADD INDEX `fk_tbl_purificationlotpuritybands_tbl_purificationlots` (`PurificationLotPurityBands` ASC) 
, ADD INDEX `fg_tbl_purificationlotpuritygrades_tbl_purificationlots` (`PurificationLotPurity%` ASC) 
, ADD INDEX `fk_tbl_purificationlotpuritypeaks_tbl_purificationlots` (`PurificationLotPurityPeaks` ASC) 
, DROP INDEX `tbl_purificationlotpuritypeakstbl_purificationlots` 
, DROP INDEX `tbl_purificationlotpuritygradestbl_purificationlots` 
, DROP INDEX `tbl_purificationlotpuritybandstbl_purificationlots` ;

ALTER TABLE `CrystalTrain`.`tbl_expressionflasks` CHANGE COLUMN `CompoundID` `CompoundID` INT(11) NULL DEFAULT NULL  
, DROP INDEX `tbl_compoundstbl_expressionflasks_define` ;

ALTER TABLE `CrystalTrain`.`tbl_purificationexperimentdocs` 
  ADD CONSTRAINT `fk_tbl_puriexp_SDSGel_Elutionprofile_tbl_puriexps`
  FOREIGN KEY ()
  REFERENCES `CrystalTrain`.`tbl_purificationexperiments` ()
  ON DELETE CASCADE
  ON UPDATE CASCADE;

###############################################################################

I assume the solution would be to simply apply the suggested changes to the database and thus correct any non-canonical naming in the db itself. The db, however, is functional, non-canonical key names or not. 

In any case, if I apply any of the changes to the model, I will completely corrupt the model again.

Create statement for one of the corresponding tables below:

###############################################################################

delimiter $$

CREATE TABLE `tbl_purificationlots` (
  `PurificationLotID` int(11) NOT NULL AUTO_INCREMENT,
  `PurificationLotName` varchar(50) NOT NULL,
  `PurificationLotul` int(11) DEFAULT NULL,
  `PurificationLotConcentration` double DEFAULT NULL,
  `PurificationLotDescription` longtext,
  `PurificationTypeID` int(11) DEFAULT NULL,
  `KamatoExperimentID` varchar(50) DEFAULT NULL,
  `PurificationLotDate` datetime NOT NULL,
  `ExpressionLotID` int(11) NOT NULL,
  `ExpressionLotPelletUsed` int(11) DEFAULT NULL,
  `InhibitorID` int(11) DEFAULT NULL,
  `CompoundAliquotID` int(11) DEFAULT NULL,
  `OperatorID` int(11) NOT NULL,
  `TagIsCleaved` tinyint(4) NOT NULL,
  `ExpressionLot2ID` int(11) DEFAULT NULL,
  `ExpressionLot2PelletUsed` int(11) DEFAULT NULL,
  `PurificationLotPurity%` int(11) DEFAULT NULL,
  `PurificationLotPurityBands` int(11) DEFAULT NULL,
  `PurificationLotPurityPeaks` int(11) DEFAULT NULL,
  `PurificationLotMSComment` longtext,
  `PurificationLotIsPEModified` tinyint(4) DEFAULT NULL,
  `PurificationLotBuffer` longtext,
  `PurificationLotQuantityMGBeforeConcentration` double DEFAULT NULL,
  `PurificationLotWasConcentrated` int(11) DEFAULT '-1',
  `AUTO_PurificationLotOutcome` varchar(50) DEFAULT NULL,
  `AUTO_CrystalsFromThisLot` longtext,
  `AUTO_UsableCompounds` longtext,
  `timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `DateTime_ODBCWriteConflict_Fix` datetime DEFAULT NULL,
  PRIMARY KEY (`PurificationLotID`),
  UNIQUE KEY `PurificationLotName` (`PurificationLotName`),
  KEY `ExpressionLotId` (`ExpressionLotID`),
  KEY `ExpressionLotId1` (`ExpressionLot2ID`),
  KEY `InhibitorId1` (`InhibitorID`),
  KEY `KamatoExperimentId` (`KamatoExperimentID`),
  KEY `OperatorId` (`OperatorID`),
  KEY `PurificationLotId` (`PurificationLotID`),
  KEY `PurificationTypeId` (`PurificationTypeID`),
  KEY `tbl_purificationlotpuritybandstbl_purificationlots` (`PurificationLotPurityBands`),
  KEY `tbl_purificationlotpuritygradestbl_purificationlots` (`PurificationLotPurity%`),
  KEY `tbl_purificationlotpuritypeakstbl_purificationlots` (`PurificationLotPurityPeaks`),
  CONSTRAINT `tbl_expressionlotstbl_purificationlots` FOREIGN KEY (`ExpressionLotID`) REFERENCES `tbl_expressionlots` (`ExpressionLotID`) ON DELETE NO ACTION ON UPDATE CASCADE,
  CONSTRAINT `tbl_expressionlotstbl_purificationlots1` FOREIGN KEY (`ExpressionLot2ID`) REFERENCES `tbl_expressionlots` (`ExpressionLotID`) ON DELETE NO ACTION ON UPDATE CASCADE,
  CONSTRAINT `tbl_purificationlotpuritybandstbl_purificationlots` FOREIGN KEY (`PurificationLotPurityBands`) REFERENCES `tbl_purificationlotpuritybands` (`PurificationLotPurityBandsID`) ON DELETE NO ACTION ON UPDATE CASCADE,
  CONSTRAINT `tbl_purificationlotpuritygradestbl_purificationlots` FOREIGN KEY (`PurificationLotPurity%`) REFERENCES `tbl_purificationlotpuritygrades` (`PurificationLotPurityGradesID`) ON DELETE NO ACTION ON UPDATE CASCADE,
  CONSTRAINT `tbl_purificationlotpuritypeakstbl_purificationlots` FOREIGN KEY (`PurificationLotPurityPeaks`) REFERENCES `tbl_purificationlotpuritypeaks` (`PurificationLotPurityPeaksID`) ON DELETE NO ACTION ON UPDATE CASCADE,
  CONSTRAINT `tbl_purificationtypestbl_purificationlots` FOREIGN KEY (`PurificationTypeID`) REFERENCES `tbl_purificationtypes` (`PurificationTypeID`) ON DELETE NO ACTION ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=1861 DEFAULT CHARSET=utf8$$

###############################################################################
[24 Nov 2012 10:24] Ingo Korndoerfer
Updated .mwb, all uncritical issues resolved

Attachment: CrystalTrain_140612a.mwb (application/vnd.mysql-workbench-model, text), 185.34 KiB.

[25 Nov 2012 2:41] MySQL Verification Team
Thank you for the bug report. So the issue is resolved?. Thanks.
[25 Nov 2012 7:39] Ingo Korndoerfer
Issue is not resolved at all. The most recent .mwb was only cleaned up manually and all items that could be resolved manually resolved one by one. If I let the workbench touch any of the remaining items as given by the synchronizer .sql code, the problem will come up. Issue actually same under Windows and Ubuntu 10.04-
[2 Jan 2013 22:13] Ingo Korndoerfer
After narrowing down the problem to what was really the core of the problem, I think this was all caused by the more specific bug registered as #67970.
Hopefully fixed in 5.2.45 (but still testing).