| Bug #66516 | Fail on Bulk Data Transfer | ||
|---|---|---|---|
| Submitted: | 23 Aug 2012 15:31 | Modified: | 14 Jun 2013 1:46 |
| Reporter: | Andre Bechara | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Workbench: Migration | Severity: | S2 (Serious) |
| Version: | 5.2.42 CE | OS: | Windows (Windows 7 Home 64Bits) |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | migration, SQLServer | ||
[23 Aug 2012 15:31]
Andre Bechara
[23 Aug 2012 15:32]
Andre Bechara
Screenshot on the fail moment
Attachment: screenshot.png (image/png, text), 74.75 KiB.
[23 Aug 2012 15:36]
Andre Bechara
Migration Report
Attachment: MigrationReport.txt (text/plain), 103.64 KiB.
[23 Aug 2012 15:38]
Andre Bechara
Workbench Log
Attachment: wb.log (text/plain), 98.50 KiB.
[23 Aug 2012 23:50]
MySQL Verification Team
Thank you for the bug report.
[26 Aug 2012 14:14]
Alfredo Kojima
Andre, can you enable the creation of a script for the data migration and after editing it for password and enabling debugging output, run it from the command line? What happens and what does it print?
[27 Aug 2012 19:29]
Andre Bechara
wbcopytables result
Attachment: screenshot-wbcopytables.png (image/png, text), 184.05 KiB.
[27 Aug 2012 19:31]
Andre Bechara
Hi Alfredo, I submited the screenshot of wbcopytables result.
[13 Sep 2012 7:38]
Michael McLaughlin
Alfredo, I've posted blog entries for the SQL Server 2012, Windows SQL Server DSN, and the error location and the generated script file. Please let me know if you require anything else. http://blog.mclaughlinsoftware.com/2012/09/13/trying-to-migrating-data/ http://blog.mclaughlinsoftware.com/2012/09/12/sql-server-odbc-osn/ http://blog.mclaughlinsoftware.com/2012/09/11/sql-server-2012-install/
[17 Sep 2012 0:34]
Michael McLaughlin
Upgrading to MySQL 5.2.43 CE fails, and I simplified the source by creating a STUDENTDB database schema and STUDENT user in MS SQL Server 2012. The migration succeeds in replacing the table but fails on copying the data, as shown in the log file: 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.
[17 Sep 2012 3:07]
Michael McLaughlin
Alfredo, Per your email, I retyped the INSERT statement to the Microsoft SQL Server and it successfully migrated the rows. Like you thought, it appears to have been a character set mismatch. Is it possible to raise a character set mismatch error in the log file?
[23 Jan 2013 19:43]
M N
Same issue here on a SQL Server 2k migration. I've got some tables that have non-english characters here and there. Those tables fail the migration with errors like "Incorrect string value '\xEF' for column 'title' at row 100'. The problem is pretty much exactly as described above and in this thread: http://forums.mysql.com/read.php?60,136566,136566#msg-136566 I tried editing the db create statement to use other collations but so far no luck. The simplest solution seems to be to use the "Data consistency / multilanguage" option in the "Object Mapping" screen of the migration toolkit (per a PDF instruction manual I found), but I can't find that feature has been deprecated. Here's a SQL dump of a sample table structure: if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tblProfanity]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[tblProfanity] GO CREATE TABLE [dbo].[tblProfanity] ( [word_id] [bigint] IDENTITY (1, 1) NOT NULL , [word] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [replacement] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ) ON [PRIMARY] GO And here is a copy of the data from one of the offending rows (apologies to the Finnish): word_id word replacement 100 kyrpä <NULL> I see that the previous reporter found a workaround via an email from Alfredo, but he doesn't mention what exactly it entailed. Details would be appreciated if possible. :)
[13 Feb 2013 17:44]
Erik Dzul
Hi everybody, I was having the same issue when migrating from MSSQL, in my particular situation the columns throwing the error were those in VARCHAR data type in MSSQL, I changed them to NVARCHAR so they can handle the characters as unicode, in one of the columns a couldn't changed the type so I created a temporal column as NVARCHAR copy from the original column, drop the orignal column and rename the temporal one, probably this could be a lot of work to do but it's only needed to change the columns data type in those having not only english characters. Hope this helps. Greetings
[14 Jun 2013 1:46]
Philip Olson
Fixed as of the upcoming MySQL Workbench 6.0.2 public beta release, and here's the changelog entry: When migration SQL Server to MySQL, CHAR/VARCHAR/TEXT fields containing international text would fail to transfer. These fields are now converted to NCHAR/NVARCHAR/NTEXT. Thank you for the bug report.
