MySQL Migration error: Invalid timestamp literal detected

Question

I am trying to migrate a MS SQL 2008 R2 database to MySQL 5.6 CE. I am using the MySQL WorkBench 5.2. The migration finished with tons of errors.

Most of the errors are:

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

This error message makes no sense as many table does not have a DateTime column. For example it was trying to migrate 4 rows of data from this table:

/****** Object:  Table [dbo].[defResidentialStatus]    Script Date: 07/11/2013 14:33:47 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[defResidentialStatus](
    [idResStatusKey] [int] IDENTITY(1,1) NOT NULL,
    [desc1] [nvarchar](50) NOT NULL,
    [desc2] [nvarchar](50) NOT NULL,
    [active] [bit] NOT NULL,
 CONSTRAINT [PK_defResidentialStatus] PRIMARY KEY CLUSTERED 
(
    [idResStatusKey] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[defResidentialStatus] ADD  CONSTRAINT [DF_defResidentialStatus_active]  DEFAULT ((1)) FOR [active]
GO

The log is this:

TestDB.defResidentialStatus:Copying 4 columns of 4 rows from table [TestDB].[dbo].[defResidentialStatus]

''

04:33 [WRN][ copytable]: Invalid timestamp literal detected: ''

04:33 [WRN][ copytable]: Invalid timestamp literal detected: ''

04:33 [WRN][ copytable]: Invalid timestamp literal detected: ''

04:33 [WRN][ copytable]: Invalid timestamp literal detected: ''

04:33 [WRN][ copytable]: Invalid timestamp literal detected: ''

<<< Repeat the same error message about 40 times, not included to save space >>>

04:34 [WRN][ copyPROGRESS:TestDB.defResidentialStatus:4:4 ............. TestDB.defResidentialStatus has FAILED (0 of 4 rows copied)

I have no idea what's going on. This is a very simple table with 4 columns and 4 rows. This is not the only table returning this type of error, but it is one of the simplest.

Data in the table:

1   Pending     Pending     1    
2   Arrived     Arrived     1    
3   Cancelled   Cancelled   1    
4   Departed    Departed    1

Here is a site I found that might help you answer your question. http://www.databasejournal.com/features/mysql/mapping-data-types-between-mysql-and-sql-server.html — SQL Taylor, Apr 27 '15 at 19:08

Wes Palmer · Answer 1 · 2015-10-02T14:55:51.030

Datetime - In MYSQL datetime does not contain milliseconds. The SQL Server datetime datatype contains milliseconds. Error: “Invalid timestamp literal detected” error. Solution: Convert the datetime type to a smalldatetime in SQL Server if you do not mind losing the milliseconds.

Here is some code for the conversion:

SELECT CONVERT(smalldatetime,<columnName>);

After the converison you shouldn't have any trouple importing it.

MySQL Migration error: Invalid timestamp literal detected

1 Answers1