MySQL Bugs: #42266: Incorrect SQL produced using syncronization feature for datatype DOUBLE

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:	Windows (XP SP3)
Assigned to:		CPU Architecture:	Any
Tags:	CHECKED, double, sql error, syncronization

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.

Test Workbench Model

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

Thank you for the bug report.

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

marking as duplicate of bug #41290