MySQL Bugs: #26308: Incorrect syntax for default values (parenthesis)

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:	Windows (WinXP)
Assigned to:		CPU Architecture:	Any

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

I was migrating from MS SQL Server 2005

Thank you for a problem report. Please, send a complete CREATE TABLE statement for you original table in MS SQL Server.

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;

Thank you for the report.

Verified as described.

Thank you for the report.

Verified as described.

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

Bug exists in 1.1.11

Also Bug #27688 was marked as duplicate of this one.

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.

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.

Bug #32090 was marked as a duplicate of this one.

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.

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.

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?

We are on the way to implement full functionality from Migration Toolkit into MySQL Workbench.

This needs fixing in Workbench.

Bug #48038 is set as duplicate of this bug here.

Any updates?

This bug is not present in the Workbench migration wizard.