0

I know how to escape apostrophe. The question is different. How to add data with manually escaped apostrophe during steps in Workbench wizard?

I have the same question as in SQL Server to Mysql migration (using Mysql Workbench) data transfer error

How can I manually add escape to apostrophe (') and then run the last step in workbench wizard for DATA MIGRATION?

My error is different: Statement execution failed: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 's Homestead','Regina Murphy','Sales Representative','707 Oxford Rd.','Ann Arbor'' at line 1:

INSERT INTO `Northwind`.`Suppliers` (`SupplierID`, `CompanyName`, `ContactName`, `ContactTitle`, `Address`, `City`, `Region`, `PostalCode`, `Country`, `Phone`, `Fax`, `HomePage`)

VALUES

(3,'Grandma Kelly's Homestead','Regina Murphy','Sales Representative','707 Oxford Rd.','Ann Arbor','MI','48104','USA','(313) 555-5735','(313) 555-3349',NULL),

It looks like apostrophe in word "Kelly's" produce the problem.

Community
  • 1
  • 1
фымышонок
  • 1,362
  • 16
  • 22
  • I know how to escape apostrophe. The question is different. How to add data with manually escaped apostrophe during steps in Workbench wizard? Please remove the duplication tag. – фымышонок Oct 24 '16 at 18:05
  • Fair enough. Reopened :p – Drew Oct 24 '16 at 18:12
  • There is only one way to fix that, you must fix source code and rebuild. I can tell you what exactly needs to be done if you know how to bulild Workbench from scratch. In meantime please fill a bug report at http://bugs.mysql.com/ – Miłosz Bodzek Oct 25 '16 at 11:20
  • It looks like not a mysql bug but absence of options in workbench wizard to manually change data during migration. I used Northwind database as example. I used instnwnd.sql file from https://www.microsoft.com/en-us/download/details.aspx?id=23654 – фымышонок Oct 25 '16 at 15:12
  • Thank you Milosz Bodzek. It looks like the only existing option is to copy database -> make changes in this database (escape strings) -> perform migration with workbench wizard. – фымышонок Oct 25 '16 at 15:18

3 Answers3

1

I'm working on the same issue. As far as I can tell you need to run

Replace(string_column, '''', '''''');

or 

Replace(string_column, '''', '&apos');

on the source DB before import. It might be possible to run it as part of the import script, I'm not sure. My scripting abilities are somewhat limited.

Edit:

This worked for me on a MSSQL Server:

BEGIN TRANSACTION;
  UPDATE [dbo].[Table]
  SET Field = REPLACE(Field, '''', '''''');
COMMIT TRANSACTION;
Topplestack
  • 11
  • 3
0

It is Milosz Bodzek's answer.

I only reformulated it.

It looks like the only existing option is to copy database -> make changes in this database (escape strings) -> perform migration with workbench wizard.

фымышонок
  • 1,362
  • 16
  • 22
0

It is a bug in MySQL Workbench(version 6.3.8). Here's the bug page: http://bugs.mysql.com/bug.php?id=83616

Edit:

As it turns out, when migrating with Migration Wizard in Workbench, if string in the source column(could be text or varchar) has apostrophe character in it, you get that error because Workbench does not escape the apostrophe.

I have encountered the same issue and used Topplestack's workaround. I also replaced apostrophe back in MySQL after migration in order to avoid changing source data.

Edit 2:

They have fixed it in MySQL Workbech 6.3.9. This is entry is from bug fixes part of the changelog:

  • Having a single quotation mark (') in the text field of a table caused the MySQL Schema Transfer wizard to fail. (Bug #24978403, Bug #83616)
Community
  • 1
  • 1
Omer
  • 11
  • 2