Bug #26308 Incorrect syntax for default values (parenthesis)
Submitted: 13 Feb 2007 9:08 Modified: 5 Feb 2013 14:23
Reporter: Martin Kirchner Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Workbench Severity:S2 (Serious)
Version:1.1.9, 1.1.11, 1.1.12, 1.1.17 OS:Microsoft Windows (WinXP)
Assigned to:
Triage: Triaged: D2 (Serious) / R3 (Medium) / E3 (Medium)

[13 Feb 2007 9:08] Martin Kirchner
Description:
When a column with an integer default value is migrated the resulting script is s.th. like
`ColumnName` TINYINT NOT NULL DEFAULT (0)

The parenthesis are not accepted by the MySQL server.

How to repeat:
Migrate a column with an integer or bit value that has a default value set.

Suggested fix:
Strip off parenthesis
[13 Feb 2007 9:14] Martin Kirchner
I was migrating from MS SQL Server 2005
[20 Feb 2007 14:55] Valerii Kravchuk
Thank you for a problem report. Please, send a complete CREATE TABLE statement for you original table in MS SQL Server.
[20 Feb 2007 15:28] Martin Kirchner
Example schema:

MS SQL:

CREATE TABLE [dbo].[MyTable](
	[GGUID] [binary](16) NOT NULL,
	[AllowCustomMandatory] [bit] NOT NULL DEFAULT ((0)),
	[IsDuplicatable] [bit] NOT NULL DEFAULT ((1)),
 CONSTRAINT [PK_GUIDSTR_SEC] PRIMARY KEY NONCLUSTERED
(
	[GGUID] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

The Migration Tool generates:

CREATE TABLE `EIM2_dbo`.`MyTable` (
  `GGUID` BINARY(16) NOT NULL,
  `AllowCustomMandatory` TINYINT NOT NULL DEFAULT (0),
  `IsDuplicatable` TINYINT NOT NULL DEFAULT (1),
  PRIMARY KEY (`GGUID`)
)
ENGINE = INNODB;
[21 Feb 2007 12:31] Sveta Smirnova
Thank you for the report.

Verified as described.
[21 Feb 2007 12:33] Sveta Smirnova
Thank you for the report.

Verified as described.
[23 Feb 2007 17:47] Michael G. Zinner
Thank you for your bug report. This issue has been committed to our source repository of that product and will be incorporated into the next release.

If necessary, you can access the source repository and build the latest available version, including the bug fix. More information about accessing the source trees is available at

    http://dev.mysql.com/doc/en/installing-source.html
[9 Apr 2007 13:34] Sveta Smirnova
Bug exists in 1.1.11

Also Bug #27688 was marked as duplicate of this one.
[30 Apr 2007 9:20] Michael G. Zinner
Thank you for your bug report. This issue has been committed to our source repository of that product and will be incorporated into the next release.

If necessary, you can access the source repository and build the latest available version, including the bug fix. More information about accessing the source trees is available at

    http://dev.mysql.com/doc/en/installing-source.html

May I ask why you are using double parenthesis in your CREATE TABLE statement? I am now filtering those out as well.
[5 Nov 2007 1:20] Eval User
The 'DEFAULT (0)' vs. 'DEFAULT 0' for the 'TINYINT NULL' for the CREATE TABLE statement in version 1.1.12 of the MySQL Migration Toolkit persists.
[17 Feb 2008 16:23] Valerii Kravchuk
Bug #32090 was marked as a duplicate of this one.
[3 Sep 2008 20:33] Fredrik
I'm having this very same problem, and i see that it's over a year since this bug was confirmed. Is it still not fixed?

From MS SQL 2005 to MySQL using Migration Toolkit version 1.1.12.
[30 Mar 2009 6:37] Bogdan Degtyariov
When migrating from MSSQL to MySQL the problem is still repeatable for some data types. Migration Toolkit version 1.1.17 treats well double parentheses for INT columns in DEFAULT, but BIT default values remain not properly converted:

`BitColumn` TINYINT NOT NULL DEFAULT (0)

When I retain the same definition of the constraint in MSSQL as DEFAULT ((0)) FOR [BitColumn], but change the column type to TINYINT the conversion generates the correct column definition for MySQL:

`BitColumn` TINYINT NOT NULL DEFAULT 0

Because the patch for other types has been exploited, adding BIT to the list of types to remove parentheses should not be a big problem.
[7 Oct 2009 6:54] Richard Penwell
May we remind MySQL that this issue has yet to be resolved in the latest (1.1.17) version.

This bug is two years old.

This is a simple fix.

This makes me have to edit every Microsoft SQL migration we do with default constraints.

When if ever can resolution be expected?
[15 Oct 2009 7:58] Susanne Ebrecht
We are on the way to implement full functionality from Migration Toolkit into MySQL Workbench.

This needs fixing in Workbench.
[15 Oct 2009 9:08] Susanne Ebrecht
Bug #48038 is set as duplicate of this bug here.
[16 Nov 2010 6:43] Roel Van de Paar
Any updates?
[5 Feb 2013 14:23] Alfredo Kojima
This bug is not present in the Workbench migration wizard.