MySQL Bugs: #71350: Receiving "Invalid timestamp literal detected" on database migration

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

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.

Can you provide the data from some of these timestamp rows?

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.

Results of  exec sp_columns

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

SQL Server Dump File

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

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.

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.

BTW have you tried newer versions of MySQL than 5.0?

I was able to reproduce the error running on "Server version: 5.6.14-1+debphp.org~precise+1 (Ubuntu)"

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'.

Data copy script

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

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?

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.

confirm, can be repeated on windows

Can anyone set my expectations on when/if this might be addressed? I'm eager to migrate away from SQL Server.

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.

It seems that bug is specific to the MS odbc driver. So in the meantime, you can try migrating from linux using freetds.

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.

Is there anything else I can do to help resolve this issue?

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!

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)

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.

This is also happening with MsSQL 2014

This was working not too long ago.

Oh, when is 6.2.5 being released?

6.2.5 was released on 2015-03-03, and 6.3.x is also stable.