| Bug #23676 | varchar field containing {dsfn TODATE(2006,1,1)} gives syntax error | ||
|---|---|---|---|
| Submitted: | 26 Oct 2006 17:33 | Modified: | 22 Nov 2006 12:28 |
| Reporter: | Mick Francis (Candidate Quality Contributor) | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Migration Toolkit | Severity: | S2 (Serious) |
| Version: | 1.1.4rc | OS: | Windows (WinXP) |
| Assigned to: | CPU Architecture: | Any | |
[26 Oct 2006 19:39]
MySQL Verification Team
Thank you for the bug report. I was unable to repeat with a table with
a single varchar column and I was able to migrate without problems:
Changed database context to 'dbtest'.
1> create table tbtest (col1 varchar(50))
2> go
1> insert into tbtest (col1) values ('{dsfn TODATE(2005,1,1)}')
2> go
(1 rows affected)
1> select * from tbtest
2> go
col1
--------------------------------------------------
{dsfn TODATE(2005,1,1)}
(1 rows affected)
Below how it was migrated:
mysql> desc tbtest;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| col1 | varchar(50) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
1 row in set (0.02 sec)
mysql> select * from tbtest;
+-------------------------+
| col1 |
+-------------------------+
| {dsfn TODATE(2005,1,1)} |
+-------------------------+
1 row in set (0.00 sec)
Any thing I missed? Could you please provide a table definition
sample with some insert data. Thanks in advance.
[27 Oct 2006 8:34]
Mick Francis
The attached file should be attached in SQL-Server 2000. There is a single table, SfnMeasureDesc. If you migrate this to MySQL you shold see the error in the log at the end.
[27 Oct 2006 12:06]
MySQL Verification Team
Thank you for the feedback. I was unable to attach to SQL Server 2005 because the log file *.LDF is missed. Could you provide it?. Thanks in advance.
[27 Oct 2006 12:12]
Mick Francis
Sorry - I shouild have explained: You just need to run the following from the master database in SQL-Server: EXEC sp_attach_single_file_db @dbname = 'MDF_TEMP', @physname = '...\MDF_TEMP.MDF'; where ... is the path to the folder containing the .MDF file.
[27 Oct 2006 14:02]
MySQL Verification Team
Thank you for the feedback.
2. Data Bulk Transfer
---------------------
`MDF_TEMP_dbo`.`SfnMeasureDesc`
-------------------------------
Syntax error for DATE escape sequence '{dsfn TODATE(2005,1,1)}'
0 row(s) transfered.
End of report.
[27 Oct 2006 15:43]
Mick Francis
I've managed to reproduce with a CREATE TABLE and import of a CSV file (but not, alas, with an INSERT - sorry). The CREATE TABLE is: CREATE TABLE [dbo].[SfnMeasureDesc]( [cMeasureId] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, [cDataTable] [varchar](128) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, [bDatumIsDate] [tinyint] NOT NULL, [cDatumExpSql] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, [cValueExpSql] [varchar](3000) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, [cDataFilterCondSql] [varchar](3000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [cParId] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, [cRedId] [varchar](16) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, [cChosenDimId] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [bMoreIsGood] [tinyint] NOT NULL, [bUpIsGood] [tinyint] NOT NULL, [cUnits] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, [nDecimalPlaces] [smallint] NOT NULL, [nMinPossVal] [float] NULL, [nMaxPossVal] [float] NULL, [bAutoCorrAdjust] [tinyint] NOT NULL, [bCreateDial] [tinyint] NOT NULL, [bAccumulate] [tinyint] NOT NULL, [bIgnoreCurrPeriod] [tinyint] NOT NULL, [nBmValues] [smallint] NOT NULL, [nPeriodsBack] [smallint] NULL, [nPeriodsForward] [smallint] NULL, [bActive] [tinyint] NOT NULL, [cMissingDataId] [varchar](16) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, [cTrendedGuideId] [varchar](16) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, CONSTRAINT [SfnMeasureDesc_PK] PRIMARY KEY CLUSTERED ( [cMeasureId] ASC ) ON [PRIMARY] ) ON [PRIMARY] CSV will be attached...
[27 Oct 2006 15:43]
Mick Francis
CSV to be loaded after CREATE TABLE
Attachment: SfnMeasureDesc.csv (application/vnd.ms-excel, text), 22.83 KiB.
[22 Nov 2006 12:28]
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
Description: The following error is given when transferring data from SQL-Server to MySQL: 2. Data Bulk Transfer --------------------- `SfnTest01_0006_dbo`.`SfnMeasureDesc` ------------------------------------- Syntax error for DATE escape sequence '{dsfn TODATE(2005,1,1)}' 0 row(s) transfered. The string is a perfectly good string, and not supposed to be interpreted as a date escape. How to repeat: Put '{dsfn TODATE(2005,1,1)}' into a varchar column in SQL-Server and try to migrate to MySQL. Suggested fix: Don't assume anything about contents of (var)char columns.