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: | |
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
[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.