Bug #42266 Incorrect SQL produced using syncronization feature for datatype DOUBLE
Submitted: 22 Jan 2009 11:16 Modified: 1 Apr 2009 21:10
Reporter: Stuart Ramsay Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Workbench Severity:S3 (Non-critical)
Version:5.0.29SE Rev 3359 OS:Microsoft Windows (XP SP3)
Assigned to: CPU Architecture:Any
Tags: CHECKED, double, sql error, syncronization
Triage: Triaged: D3 (Medium) / R3 (Medium) / E3 (Medium)

[22 Jan 2009 11:16] Stuart Ramsay
Description:
When using the syncronization feature to syncronize the workbench model with a mysql database the SQL produced is incorrect and fields of datatype DOUBLE are scripted as DOUBLE(256) which will fail. This problem occurs for both new tables and tables that exist already. The copy SQL to clipboard feature does not have this problem.

Example:-

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 SCHEMA `test`  DEFAULT CHARACTER SET latin1  DEFAULT COLLATE latin1_swedish_ci ;

USE `test`;

CREATE  TABLE IF NOT EXISTS `test`.`table1` (
  `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT ,
  `testtext` VARCHAR(45) NULL DEFAULT NULL ,
  `testdouble` DOUBLE(256) NULL DEFAULT NULL ,
  PRIMARY KEY (`id`) )
ENGINE = InnoDB
DEFAULT CHARACTER SET = latin1
COLLATE = latin1_swedish_ci;

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

How to repeat:
Create a blank database called 'test'

Create a new workbench model (attached) containing a table with a field of datatype DOUBLE.

Try to syncronize the workbench model with the test database.

examine the script produced, it will be incorrect as the DOUBLE fields will be defined as DOUBLE(256). THis will produce a syntax error of executed.
[22 Jan 2009 11:17] Stuart Ramsay
Test Workbench Model

Attachment: testdouble.mwb (application/x-zip-compressed, text), 3.86 KiB.

[22 Jan 2009 16:04] Miguel Solorzano
Thank you for the bug report.
[13 Feb 2009 11:45] Frank Quosdorf
confirmed. This is especially annoying in conjunction with bug #37709. I decided to create a new Workbench project after it was impossible to get a reasonable alter script generated (caused by #37709). After creating the new Workbench project from the latest SQL schema file, all fields with DOUBLE(15,2) in the old project became DOUBLE in the new project. The issue became obvious when I added a field of type DOUBLE using the same (wrong) declaration as seen in other fields in the same table (could not remember that there was this manual (15,2) thing before). Applying the alteration failed with the result described by the bug reporter. With bug #37709 and this one (#42266) around, I have no choice but to manually add (15,2) to all existing fields of type DOUBLE to at least be able to alter the schema, but it will then again produce an alter script even though nothing changed in the model (#37709). This is really a dreadful combination of bugs in a production environment.

OS: Windows Vista
Workbench version: 5.0.29 OSS
[1 Apr 2009 21:10] Alfredo Kojima
marking as duplicate of bug #41290