MacLochlainns Weblog

Michael McLaughlin's Technical Blog

Site Admin

Bulk Transfer Works

with 9 comments

As many already know, I’ve been trying to get the MySQL Workbench migration feature working between Microsoft SQL Server 2012 and MySQL 5.5. There are a number of features added to the 5.2.43 point release, and one led me to believe that the Migration tool expects to find the data in a schema of its own, as opposed to the dbo schema. Having made that change in Microsoft SQL Server, it did appear to have a positive impact on the migration and when I corrected a character set mismatch it worked perfectly!

MySQL Workbench successfully migrated the schema and table but failed to migrate the data because of a character set mismatch. I updated Bug 66516 the log file from the character set mismatch before I retyped all 9 test rows to make sure they were in a latin1 character set.

I shortened the original log file because the actual log had over 2,000 blanks line :-(. That’s probably something that should be fixed in the code too.

Starting...
Prepare information for data copy...
Prepare information for data copy done
Create shell script for data copy...
Table copy script written to C:\Users\McLaughlinM\Desktop\copy_migrated_tables.cmd
Create shell script 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=SQL Server ODBC;DATABASE=;UID=sa --table [studentdb] [studentdb].[conquistador]
18:29:13 [INF][      copytable]: Opening ODBC connection to 'DSN=SQL Server ODBC;DATABASE=;UID=sa;PWD=XXX'
18:29:14 [INF][      copytable]: ODBC connection to 'DSN=SQL Server ODBC;DATABASE=;UID=sa;PWD=' opened
 
9 total rows in 1 tables need to be copied:
- [studentdb].[studentdb].[conquistador]: 9
Determine number of rows to copy finished
 
Copy data to target RDBMS....
 
Migrating data...
 
wbcopytables.exe --odbc-source=DSN=SQL Server ODBC;DATABASE=;UID=sa --target=student@mclaughlinsql:3306 --progress --passwords-from-stdin --thread-count=1 --table [studentdb] [studentdb].[conquistador] `studentdb` `conquistador` [conquistador_id], [conquistador], [actual_name], [nationality]
`studentdb`.`conquistador`:Copying 4 columns of 9 rows from table [studentdb].[studentdb].[conquistador]
 
ERROR: `studentdb`.`conquistador`:Inserting Batch: Incorrect string value: '\x9Acak' for column 'actual_name' at row 7
`studentdb`.`conquistador`:Finished copying 0 rows in 0m00s
 
29:15 [INF][      copytable]: Opening ODBC connection to 'DSN=SQL Server ODBC;DATABASE=;UID=sa;PWD=XXX'
29:16 [INF][      copytable]: ODBC connection to 'DSN=SQL Server ODBC;DATABASE=;UID=sa;PWD=' opened
29:16 [INF][      copytable]: Connecting to MySQL server at mclaughlinsql:3306 with user student
29:16 [INF][      copytable]: Connection to MySQL opened
 
Copy helper has finished
 
Data copy results:
- `studentdb`.`conquistador` has FAILED (0 of 9 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.

Originally, I thought the failure was due to the extended ASCII characters in the Microsoft SQL Server table. It still failed when I took all of the extended characters out. However, Alfredo suggested it was a character set issue, which is obvious when I looked more closely at the log – '\x9Acak' is clearly an incorrect string. I retyped the INSERT statement for the nine rows and it worked perfectly. Naturally, I’ve updated open Bug 66516 with the log file.

If you’re curious about the Microsoft SQL Server configuration check this post.

Written by maclochlainn

September 16th, 2012 at 6:55 pm

9 Responses to 'Bulk Transfer Works'

Subscribe to comments with RSS or TrackBack to 'Bulk Transfer Works'.

  1. Alfredo sent me an email in reply to mine earlier this evening and suggested that I’d missed the character set mismatch. He was right. I’ve updated the blog post with the change. Also, I change the title but not the underlying link because there were already tweets on it.

    maclochlainn

    16 Sep 12 at 9:20 pm

  2. […] resolved by development in MySQL Workbench 5.2.43 […]

    migrating data

    17 Sep 12 at 9:29 pm

  3. Hi,

    I am having some sort of similar issue. Having thousands of rows with these errors, and thus the migration doesn’t work.

    I don’t understand what you mean with Insert statement? Where and what?

    I am using mssql 2008 => mysql 5.5 (i guess, it’s the latest one)

    I am converting from latin1 to utf-8, as it seems. But don’t know where to change things to get the actual data import working.

    peter

    20 Oct 12 at 10:13 am

  4. Are you sure that the Microsoft SQL Server 2008 instance is all latin1? Did you create the MySQL database instance as utf-8? If so, could you post the error messages and the version of MySQL Workbench that you’re working with?

    maclochlainn

    20 Oct 12 at 12:22 pm

  5. Hi, I am facing the same issue, and I am getting the following in the debug log. I have lots of tables with thousands of rows, any help would be greatly appreciated.


    wbcopytables.exe --count-only --passwords-from-stdin --odbc-source=DSN=MSSQL-ODBC;DATABASE=;UID=sa;TDS_VERSION=7.1 --table-file=c:\users\welcome\appdata\local\temp\tmp7mtsgs
     
    22:54:58 [INF][      copytable]: --table [FSGG]	[dbo].[Accion]
     
    22:54:58 [INF][      copytable]: Opening ODBC connection to 'DSN=MSSQL-ODBC;DATABASE=;UID=sa;TDS_VERSION=7.1;PWD=XXX'
     
    22:54:58 [INF][      copytable]: ODBC connection to 'DSN=MSSQL-ODBC;DATABASE=;UID=sa;TDS_VERSION=7.1;PWD=' opened
     
    12 total rows in 1 tables need to be copied:
     
    - [FSGG].[dbo].[Accion]: 12
     
    Determine number of rows to copy finished
     
    Copy data to target RDBMS....
     
    Migrating data...
     
    wbcopytables.exe --odbc-source=DSN=MSSQL-ODBC;DATABASE=;UID=sa;TDS_VERSION=7.1 --target=root@127.0.0.1:3306 --force-utf8-for-source --progress --passwords-from-stdin --thread-count=1 --table-file=c:\users\welcome\appdata\local\temp\tmpafbxkt
     
    \users\welcome\appdata\local\temp\tmpafbxkt
     
    `dbo`.`Accion`:Copying 2 columns of 12 rows from table [FSGG].[dbo].[Accion]
     
    ERROR: `dbo`.`Accion`:Inserting Batch: Incorrect string value: '\xF3n en ...' for column 'Accion' at row 1
     
    `dbo`.`Accion`:Finished copying 0 rows in 0m00s
     
    54:59 [INF][      copytable]: --table [FSGG]	[dbo].[Accion]	`dbo`	`Accion`	[ID_Accion], [Accion]
     
    54:59 [INF][      copytable]: Opening ODBC connection to 'DSN=MSSQL-ODBC;DATABASE=;UID=sa;TDS_VERSION=7.1;PWD=XXX'
     
    54:59 [INF][      copytable]: ODBC connection to 'DSN=MSSQL-ODBC;DATABASE=;UID=sa;TDS_VERSION=7.1;PWD=' opened
     
    54:59 [INF][      copytable]: Connecting to MySQL server at 127.0.0.1:3306 with user root
     
    54:59 [INF][      copytable]: Connection to MySQL opened
     
    Copy helper has finished
     
    Data copy results:
     
    - `dbo`.`Accion` has FAILED (0 of 12 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.

    Karikalan

    6 Jan 13 at 10:30 am

  6. Karikalan, The error occurs when you try to import UTF8 character set data from Microsoft SQL Server into a Latin 1 MySQL database. Check that the character sets are equivalent.

    maclochlainn

    15 Jan 13 at 11:43 pm

  7. Hello Maclochlainn,

    I’ve been trying to migrate a MSSQL DB to MySQL.

    I’ve succeeded, partly though. Some tables were created, but the data not copied over. The error in the log was:

    2207:[Microsoft][ODBC SQL Server Driver][SQL Server]Invalid column name 'OrderID'.

    The message was the same for all the columns in the table.

    Any ideas?

    Terence

    22 Feb 13 at 4:39 am

  8. Check the character sets to ensure you’re not trying to migrate Unicode into a latin1 database.

    maclochlainn

    22 Feb 13 at 10:36 am

  9. I have the same problem. I developed a small program to copy the table by my self.

    TPC

    12 Jun 13 at 9:55 am

Leave a Reply