MySQL Bugs: #66359: wbcopytables.exe crashes on NULL datetime columns

Bug #66359	wbcopytables.exe crashes on NULL datetime columns
Submitted:	13 Aug 2012 18:57	Modified:	12 Sep 2012 6:28
Reporter:	Sergey Latkin	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Workbench: Migration	Severity:	S1 (Critical)
Version:	5.2.41	OS:	Windows (Microsoft Windows XP Professional Service Pack 3 (build 2600))
Assigned to:		CPU Architecture:	Any
Tags:	WBBugReporter

Description:
BaseConverter::convert_date_time does not check for NULL values and raises an out-of-range exception upon encountering fist NULL value in a table.
The main routine at copytable.cpp lines 642-643 checks for NULL value only after attempted conversion, it has to check it before doing it.

----- Developer Notes -----

MySQL Workbench CE for Windows version 5.2.41  revision 9724
Configuration Directory: C:\Documents and Settings\slatkin.PHG\Application Data\MySQL\Workbench
Data Directory: C:\Program Files\MySQL\MySQL Workbench CE 5.2.41
Cairo Version: 1.8.8
OS: Microsoft Windows XP Professional Service Pack 3 (build 2600)
CPU: 2x Intel(R) Pentium(R) 4 CPU 3.40GHz, 3.5 GiB RAM
Active video adapter (0): NVIDIA Quadro FX 540
Installed video RAM: 128 MB
Current video mode: 2560 x 1024 x 4294967296 colors
Used bit depth: 32
Driver version: 6.14.10.8426
Installed display drivers: nv4_disp.dll
Current user language: English (United States)

How to repeat:
Migrate any MS SQL table containing both NULL and non-null values in DATETIME columns

Log file, NULL value at row 109

Attachment: log-66359-1.txt (text/plain), 3.09 KiB.

Another log, null value at row 1098

Attachment: log-66359-2.txt (text/plain), 2.84 KiB.

Hello Sergey,

can you please add a step by step list in order to reproduce you problem?

Thanks in advance!

reproduction step-by-step list:

0) you need working MS SQL Server and MySQL servers, and their connection info
1) on MS SQL Server, execute the script
--- CUT HERE ---

CREATE TABLE [dbo].[Table1] (
	[id] [int] NOT NULL ,
	[stamp] [datetime] NULL 
) ON [PRIMARY]
GO

INSERT INTO [dbo].[Table1] VALUES (1, '2010-01-01')
INSERT INTO [dbo].[Table1] VALUES (2, '2011-01-01')
INSERT INTO [dbo].[Table1] VALUES (3, NULL)
INSERT INTO [dbo].[Table1] VALUES (4, '2012-01-01')
GO
--- CUT HERE ---

2) Migrate this table to MySQL server (expand all tables list and pick only this Table1), and follow the prompts clicking Next without changing anything else
3) Let online data copy to run and review the log, similar to this one:

--- CUT HERE ---
Starting...
Prepare information for data copy...
Prepare information for data copy done
Determine number of rows to copy....
Counting number of rows in tables...
wbcopytables.exe --count-only --passwords-from-stdin --odbc-source=DSN=DATA-NEW;DATABASE=tempdb;UID=sa --table [tempdb] [dbo].[Table1]
11:25:40 [INF][      copytable]: Opening ODBC connection to 'DSN=DATA-NEW;DATABASE=tempdb;UID=sa;PWD=XXX'
11:25:40 [INF][      copytable]: ODBC connection to 'DSN=DATA-NEW;DATABASE=tempdb;UID=sa;PWD=' opened
4 total rows in 1 tables need to be copied:
- [tempdb].[dbo].[Table1]: 4
Determine number of rows to copy finished
Copy data to target RDBMS....
Migrating data...
wbcopytables.exe --odbc-source=DSN=DATA-NEW;DATABASE=tempdb;UID=sa --target=***@***:3306 --progress --passwords-from-stdin --thread-count=1 --table [tempdb] [dbo].[Table1] `dbo` `Table1`
`dbo`.`Table1`:Copying 2 columns of 4 rows from table [tempdb].[dbo].[Table1]

*** ERROR: `dbo`.`Table1`:invalid string position

`dbo`.`Table1`:Finished copying 0 rows in 0m00s
Copy helper has finished

Data copy results:
- `dbo`.`Table1` has FAILED (0 of 4 rows copied)
0 tables of 1 were fully copied
Copy data to target RDBMS finished

Tasks finished with warnings and/or errors, view the logs for details
Finished performing tasks.

--- CUT HERE ---

4) enjoy

I finally managed to recompile wbcopytables with my corrected converter.cpp
I'm attaching the file, feel free to use under LGPL.
It is working fine now, at least in DATETIME type part.

correct version with added checks for NULL value and range

Attachment: converter.cpp (text/plain), 4.62 KiB.

Thanks Sergey for your thorough report.

Fixed as of the upcoming Workbench 5.2.43, and here's the changelog entry:

 Migrating a Microsoft SQL Server table that contained both
 "NULL" and non-null values in a "DATETIME" column would fail 
 to import properly.

Thank you for the report.