Bug #71350 Receiving "Invalid timestamp literal detected" on database migration
Submitted: 11 Jan 2014 22:40 Modified: 19 Feb 2015 0:13
Reporter: Jonathan Gordon Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Workbench: Migration Severity:S2 (Serious)
Version:6.0.8 OS:Windows (MS Windows Server 2003 SP2)
Assigned to: CPU Architecture:Any

[11 Jan 2014 22:40] Jonathan Gordon
Description:
I am attempting to migrate a SQL Server 2005 database to MySQL 5.0.95. When I run the data transfer, tables only, I receive a flood (thousands) of log messages that all say:

[WRN][      copytable]: Invalid timestamp literal detected:

There's actually some non-ascii boxes at the end of the line. These scroll by quickly. The migration tool eventually becomes unresponsive and Windows shows it as "Not Responding" in Task Manager and I have to quit.

I followed the advice in this post:

http://www.abstractnew.com/2013/04/migrating-sqlserver-to-mysql-using.html

I wrote alter table statements so that all my source columns are smalldatetime rather than datetime, but that hasn't fixed the issue.

How to repeat:
This happens every time. See above.
[13 Jan 2014 16:29] Alfredo Kojima
Can you provide the data from some of these timestamp rows?
[13 Jan 2014 23:18] Jonathan Gordon
I just ran many tests. Here's what I discovered:

Copying a simple 4-column table with 51 rows which looks like this:
int, varchar, smalldatetime, smalldatetime

And has nulls in both date fields for every record yields 102 errors that look like this:

40:30 [WRN][      copytable]: Invalid timestamp literal detected: '���K'

And ends like this:

Data copy results:

- `mailing_analysis_test`.`WebSubjects` has FAILED (0 of 51 rows copied)

0 tables of 1 were fully copied

All 51 records appear to have been copied appropriately. Previously, the smalldatetime fields were datetime fields but I switched them over in hopes of avoiding this error. I'm attaching a csv file that has the results of exec sp_columns on this table.

I've tried to recreate the issue by creating a new table with dateime and smalldatetime fields, with permutations of nulls and non-null values but I'm not able to reproduce the error.

I've also copied tables that have datetime fields (which I converted to smalldatetime fields) that have a mix of null and non-null values and receive a mixed number of error messages.

It appears I'm not the only one with this issue:

https://www.google.com/search?q=Invalid+timestamp+literal+detected

From looking at the various posts, it appears that sometimes this happens for tables that don't have any columns relating to time at all.
[13 Jan 2014 23:20] Jonathan Gordon
Results of  exec sp_columns

Attachment: bad-table.csv (text/csv), 509 bytes.

[14 Jan 2014 21:16] Jonathan Gordon
SQL Server Dump File

Attachment: mysql_test.bak.tgz (application/octet-stream, text), 236.26 KiB.

[14 Jan 2014 21:18] Jonathan Gordon
I just added an MSSQL backup file of the db that you should be able to use to reproduce the error. Please let me know if you can recreate the problem.
[16 Jan 2014 9:59] Jonathan Gordon
Is anyone looking at this? 

I spent a lot of effort to create a reproducible use case. Unzip and install the SQL Server dump file and attempt to transfer the database to a MySQL server instance and you should be able to reproduce the error.
[20 Jan 2014 18:20] Alfredo Kojima
BTW have you tried newer versions of MySQL than 5.0?
[20 Jan 2014 18:32] Jonathan Gordon
I was able to reproduce the error running on "Server version: 5.6.14-1+debphp.org~precise+1 (Ubuntu)"
[21 Jan 2014 13:01] Milosz Bodzek
I was trying reproduce error with several scenarios but I can't. I was trying on WB community edition 6.0.8 but also on 6.0.9 and 6.1, on MySQL 5.0.95 and 5.5.34.
Can you attach script generated on WB migration wizard to copy data? It is on page 'Data Transfer Setup', checkbox 'Create shell script to copy data from outside Workbench'.
[21 Jan 2014 20:41] Jonathan Gordon
Data copy script

Attachment: copy_migrated_tables.sh (application/octet-stream, text), 1.25 KiB.

[21 Jan 2014 20:48] Jonathan Gordon
I was just able to reproduce the problem running MySQL Workbench on my local OSX machine. I'm using OpenLink's Universal Data Access Drivers. I've uploaded the copy_migrated_tables.sh script.

So it seems the problem occurs run MySQL Workbench on both Windows and OSX. I'm guessing the problem has to do with the data source itself, which is SQL Server 2005 running on Microsoft Windows Server 2003, SP2.

What OS/Server versions are you running your tests from?
[22 Jan 2014 14:40] Milosz Bodzek
First I tried on Ubuntu, I thought that the bug relates to the Workbench and is independent of the platform. Now I can confirm that on Windows and can be repeated.
[22 Jan 2014 16:34] Milosz Bodzek
confirm, can be repeated on windows
[28 Jan 2014 22:52] Jonathan Gordon
Can anyone set my expectations on when/if this might be addressed? I'm eager to migrate away from SQL Server.
[10 Feb 2014 16:14] Milosz Bodzek
Be patient, we're working on fixing it. 
Please do not use mysql 5.0 or 5.1 (they're EOL) - recommended version is 5.6.
[12 Feb 2014 15:42] Milosz Bodzek
It seems that bug is specific to the MS odbc driver. So in the meantime, you can try migrating from linux using freetds.
[13 Feb 2014 1:40] Jonathan Gordon
As I wrote on "21 Jan 20:48", I was also able to reproduce this error running MySQL Workbench on OSX, copying data from a remote SQL Server database to a local MySQL database. The ODBC driver I was using was "OpenLink ODBC for SQLServer (Express Edition)(Unicode) v6.0". I don't believe this is a Microsoft ODBC driver.

So, to recap, I've received this problem when running MySQL Workbench on the SQL Server Windows machine using the MS ODBC driver as well as running MySQL Workbench on OSX using the OpenLink ODBC driver.
[6 Mar 2014 21:35] Jonathan Gordon
Is there anything else I can do to help resolve this issue?
[28 Apr 2014 15:52] Jason Walley
Hello, Jonathan.  I saw this bug as I was having the same exact issue trying to migrate from SQL Server 2005 to MySQL, encountering all of the invalid timestamp literal errors.  I finally was able to figure out that MySQL will not allow NULL values within a DATETIME column to migrate.  I went through my tables, changed all the NULLs to a safe default value of '1901-01-01 00:00:00', and then successfully migrated all of my data.  
It's also worth noting that MySQL will not allow milliseconds when migrating, but I think it may do a better job porting those over and dropping them than ignoring NULLs.  Hope that this helps!
[8 May 2014 23:25] Jonathan Gordon
Thanks Jason. Clearing out the NULL values allowed me to work around this bug. In case it's helpful to others, here's the sql scripts I used to automate the process:

-- Perform this query and execute the results in the source database
select 'update ' + TABLE_NAME + ' set ' + COLUMN_NAME + '=''1970-01-01'' where '+COLUMN_NAME+ ' IS NULL;'
from INFORMATION_SCHEMA.COLUMNS
WHERE DATA_TYPE = 'SMALLDATETIME'
and TABLE_NAME IN (SELECT NAME FROM SYS.TABLES)

-- Perform this query in the source database and execute the results in the target database
select 'update ' + TABLE_NAME + ' set ' + COLUMN_NAME + '= null where '+COLUMN_NAME+ '=''1970-01-01'';'
from INFORMATION_SCHEMA.COLUMNS
WHERE DATA_TYPE = 'SMALLDATETIME'
and TABLE_NAME IN (SELECT NAME FROM SYS.TABLES)
[19 Feb 2015 0:13] Philip Olson
Posted by developer:
 
Fixed as of the upcoming MySQL Workbench 6.2.5 release, and here's the changelog entry:

Migrating from SQL Server 2005 to MySQL would emit "copytable: Invalid
timestamp literal detected" errors if the source database contained a
DATETIME column with one or more NULL values.

Thank you for the bug report.
[26 Feb 2015 15:26] Lee Fairbanks
This is also happening with MsSQL 2014
[26 Feb 2015 15:30] Lee Fairbanks
This was working not too long ago.
[26 Feb 2015 15:30] Lee Fairbanks
Oh, when is 6.2.5 being released?
[6 May 2015 4:27] Philip Olson
6.2.5 was released on 2015-03-03, and 6.3.x is also stable.