Bug #65820 Generated SQL script does not reflect model changes anymore.
Submitted: 5 Jul 2012 9:53 Modified: 7 Aug 2012 18:26
Reporter: David P Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Workbench: Modeling Severity:S3 (Non-critical)
Version:5.2.40 OS:Any (XP SP3, Mac OS X)
Assigned to: CPU Architecture:Any

[5 Jul 2012 9:53] David P
Description:
Table B references table A with an identifying FK.

I changed the PK type in table A from INT to SMALLINT UNSIGNED, and altered table B accordingly.

Now, the SQL script won't reflect any change in table B, even if I add a new column that is not a FK. So, not sure if it is related to FKs. The model is corrupted. Restarts and reboots won't help.

How to repeat:
Don't know.
[5 Jul 2012 9:55] David P
my model

Attachment: pdd2.mwb (application/octet-stream, text), 26.88 KiB.

[5 Jul 2012 10:03] David P
Same on Ubuntu 12.04, with same model file.
[5 Jul 2012 10:08] David P
In the attached model file, the error is about the relationship between Data and DataType.
[5 Jul 2012 10:31] David P
Workaround : make a copy of corrupted table, delete original table.
[5 Jul 2012 10:35] David P
Also, I chose to recover model after a crash before the error occurred. That might well be the cause.
[5 Jul 2012 11:48] Valeriy Kravchuk
This is what I get when trying to forward engineer your model from .mwb file uploaded to MySQL server 5.5.26:

Executing SQL script in server
ERROR: Error 1005: Can't create table 'pdd2.data' (errno: 150)

CREATE  TABLE IF NOT EXISTS `pdd2`.`data` (
  `stime` DATETIME NOT NULL COMMENT 'Sampling time.' ,
  `idInstrument` SMALLINT(5) UNSIGNED NOT NULL ,
  `DataType_idDataType` INT(11) NOT NULL ,
  `value` FLOAT(11) NULL DEFAULT NULL COMMENT 'Value of the data point.' ,
  `flag` INT(11) NULL DEFAULT NULL COMMENT 'Data quality assurance flag, if data point is an instrument zero, etc.' ,
  PRIMARY KEY (`stime`, `idInstrument`, `DataType_idDataType`) ,
  INDEX `fk_Data_Instrument1` (`idInstrument` ASC) ,
  INDEX `fk_Data_DataType1` (`DataType_idDataType` ASC) ,
  CONSTRAINT `fk_Data_DataType1`
    FOREIGN KEY (`DataType_idDataType` )
    REFERENCES `pdd2`.`datatype` (`idDataType` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
DEFAULT CHARACTER SET = latin1
COLLATE = latin1_swedish_ci
COMMENT = 'A generic raw data point.'

While on the diagram I see only column named idDataType of type SMALLINT (instead of `DataType_idDataType` INT(11)).

Is this a corruption of model that you are talking about? Please, confirm.
[5 Jul 2012 11:50] Valeriy Kravchuk
In any case, any attempt to add column to that Data table are NOT reflected in the SQL generated. So, there is a bug here.
[5 Jul 2012 12:00] David P
The error arises because of a type mismatch in the FK definition, 3rd column (INT instead of unsigned SMALL INT). If you look at the model, you'll they do match.
[7 Aug 2012 17:25] Alfredo Kojima
Why do you have multiple tables with the same name and difference in case? While that is supposed to work, it is not a very good idea.
[7 Aug 2012 17:37] Alfredo Kojima
There are 2 problems with this model:
- the data, datatype and calibration tables contain empty FK definitions. They should be deleted
- there are multiple tables with same name and difference in case only. In Windows and Mac, you cannot create tables with difference only in case. If I rename the duplicate table names the remaining errors are gone.