MySQL Bugs: #16762: Invalid SQL generated during migration

Bug #16762	Invalid SQL generated during migration
Submitted:	25 Jan 2006 0:16	Modified:	14 Jun 2006 13:21
Reporter:	Ilya Tsymbal	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Migration Toolkit	Severity:	S3 (Non-critical)
Version:	1.0.22 rc	OS:	Windows (Windows XP)
Assigned to:	Michael G. Zinner	CPU Architecture:	Any

Description:
During creation of objects, a table fails to create, because translation generates an invalid syntax - migrating from MS SQL Server 2005 Express to MySQL 4.2

How to repeat:
Here is the SQL to create MS SQL Table:
CREATE TABLE [dbo].[TABLE5](
	[VIEWABLE] [bit] NOT NULL CONSTRAINT [DF_TABLE5_VIEWABLE]  DEFAULT (0)
) ON [PRIMARY]

Here is what the migration toolkit generates:

DROP TABLE IF EXISTS `ISIS3_dbo`.`TABLE5`;
CREATE TABLE `ISIS3_dbo`.`TABLE5` (
  `VIEWABLE` TINYINT NOT NULL DEFAULT ((0))
)
ENGINE = INNODB;
 
This is invalid syntax; DEFAULT((0)) should be DEFAULT '0'

Suggested fix:
A workaround would be to drop the contstraint from the source table, migrate and then add it to the newly created table.

I reproduced this bug using the supplied create table statement under SQL2005 and MT 1.0.22

"Migration from SQL Server:  numeric default values invalid due to parenthesis" might be a better synopses for this issue.

The issue also affects under the following config:
Migration Toolkit v1.0.25 on Window XP SP2
SQL Server 2005 Enterprise Edition on Window Server 2003

All numeric default values in the generated CREATE TABLE statements are wrapped in parenthesis.

Workaround:  The easiest work-around is editing the parenthesis out of the default values (in the Creation Results if using Create Objects Online or in the script file if running from a generated file).

Fix:  I'd guess that this is caused by MT not removing the parenthesis from the value pulled from MS SQL Server....possibly not removing enough parenthesis.  In SQL Server Management Studio, the values are displayed in doubled-parenthesis, e.g. ((1)).

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 bugfix, yourself. More information 
about accessing the source trees is available at
    http://www.mysql.com/doc/en/Installing_source_tree.html