MySQL Bugs: #72951: mysqldbcopy fails when copying text columns with single quotes.

Bug #72951	mysqldbcopy fails when copying text columns with single quotes.
Submitted:	11 Jun 2014 6:53	Modified:	9 Dec 2014 5:03
Reporter:	Eddie Ramirez	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Utilities	Severity:	S3 (Non-critical)
Version:	1.4.3	OS:	Linux (Ubuntu 14.04 64bits)
Assigned to:		CPU Architecture:	Any
Tags:	column, error, mysqldbcopy, quotes, syntax, text

Description:
mysqldbcopy is not able to copy table rows containing single quotes in text columns.

Tested on source and destination servers using 5.6.14.

How to repeat:
## source server
mysql> create table bug(txt TEXT(100));
mysql>INSERT INTO bug SET txt = 'I\' happy!';

$ mysqldbcopy --source=root:root@localhost:/tmp/mysql_3306.sock --destination=root:1234@localhost:/tmp/new_data/mysql.sock -vvv --drop-first test 
# Source on localhost: ... connected.
# Destination on localhost: ... connected.
# Copying database test 
# Dropping new object TABLE test.`bug`
# WARNING: Unable to drop `bug` from destination database (object may not exist): DROP TABLE `test`.`bug`
# Copying TABLE test.bug
CREATE TABLE `bug` (
  `txt` text
) ENGINE=InnoDB DEFAULT CHARSET=latin1
# Copying GRANTS from test
GRANT ALTER ON `test`.* TO ''@'%'
GRANT CREATE ON `test`.* TO ''@'%'
GRANT CREATE ROUTINE ON `test`.* TO ''@'%'
GRANT CREATE TEMPORARY TABLES ON `test`.* TO ''@'%'
GRANT CREATE VIEW ON `test`.* TO ''@'%'
GRANT DELETE ON `test`.* TO ''@'%'
GRANT DROP ON `test`.* TO ''@'%'
GRANT EVENT ON `test`.* TO ''@'%'
GRANT INDEX ON `test`.* TO ''@'%'
GRANT INSERT ON `test`.* TO ''@'%'
GRANT LOCK TABLES ON `test`.* TO ''@'%'
GRANT REFERENCES ON `test`.* TO ''@'%'
GRANT SELECT ON `test`.* TO ''@'%'
GRANT SHOW VIEW ON `test`.* TO ''@'%'
GRANT TRIGGER ON `test`.* TO ''@'%'
GRANT UPDATE ON `test`.* TO ''@'%'
# Copying data for TABLE test.bug
# Getting indexes for test.bug
ERROR: Problem updating blob field. Error = Query failed. 1064 (42000): 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 'm happy!')' at line 1

Suggested fix:
Scape special characters.

There's a type in the INSERT statement, should be somethink like this: 
mysql> INSERT INTO bug SET txt = 'I\'m happy!';
Query OK, 1 row affected (0.00 sec)

Hello Eddie,

Thank you for the bug report.
This could probably because of the diff sql_mode setting on source and target instance i.e sql mode set to NO_BACKSLASH_ESCAPES on target instance.

Workaround for now is to ensure target instance without NO_BACKSLASH_ESCAPES.

Thanks,
Umesh

//copy/paste steps for dev's

Source:

mysql> show variables like 'sql_mode';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| sql_mode      |       |
+---------------+-------+
1 row in set (0.00 sec)

use test;
Database changed
mysql> create table bug(txt TEXT(100));
Query OK, 0 rows affected (0.03 sec)

mysql> INSERT INTO bug SET txt = 'I\' happy!';
Query OK, 1 row affected (0.01 sec)

Target:

mysql> show variables like 'sql_mode';
+---------------+----------------------+
| Variable_name | Value                |
+---------------+----------------------+
| sql_mode      | NO_BACKSLASH_ESCAPES |
+---------------+----------------------+
1 row in set (0.00 sec)

// Call mysqlbugdb as shown below

[root@cluster-repo mysql-5.6.17]# mysqldbcopy --source=root@localhost:/tmp/master/mysqld.sock --destination=root@localhost:/tmp/slave/mysqld.sock -vvv --drop-first test
# Source on localhost: ... connected.
# Destination on localhost: ... connected.
# WARNING: A partial copy from a server that has GTIDs enabled will by default include the GTIDs of all transactions, even those that changed suppressed parts of the database. If you don't want to generate the GTID statement, use the --skip-gtid option. To export all databases, use the --all option and do not specify a list of databases.
# WARNING: The destination server does not support GTIDs yet the source server does support GTIDs. To suppress this warning, use the --skip-gtid option when copying to a non-GTID enabled server.
# Copying database test
# Dropping new object TABLE test.`bug`
# WARNING: Unable to drop `bug` from destination database (object may not exist): DROP TABLE `test`.`bug`
# Copying TABLE test.bug
CREATE TABLE `bug` (
  `txt` tinytext
) ENGINE=InnoDB DEFAULT CHARSET=latin1
# Copying GRANTS from test
GRANT ALTER ON `test`.* TO ''@'%'
GRANT CREATE ON `test`.* TO ''@'%'
GRANT CREATE ROUTINE ON `test`.* TO ''@'%'
GRANT CREATE TEMPORARY TABLES ON `test`.* TO ''@'%'
GRANT CREATE VIEW ON `test`.* TO ''@'%'
GRANT DELETE ON `test`.* TO ''@'%'
GRANT DROP ON `test`.* TO ''@'%'
GRANT EVENT ON `test`.* TO ''@'%'
GRANT INDEX ON `test`.* TO ''@'%'
GRANT INSERT ON `test`.* TO ''@'%'
GRANT LOCK TABLES ON `test`.* TO ''@'%'
GRANT REFERENCES ON `test`.* TO ''@'%'
GRANT SELECT ON `test`.* TO ''@'%'
GRANT SHOW VIEW ON `test`.* TO ''@'%'
GRANT TRIGGER ON `test`.* TO ''@'%'
GRANT UPDATE ON `test`.* TO ''@'%'
# Copying data for TABLE test.bug
# Getting indexes for test.bug
ERROR: Problem updating blob field. Error = Query failed. 1064 (42000): 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 'm happy!')' at line 1

Posted by developer:
 
Fixed as of the upcoming MySQL Utilities 1.6.1 release, and here's the changelog entry:

The "mysqldbcopy" utility was not able to copy table rows that contained
single quotes in text columns.

Thank you for the bug report.

1. Any idea when 1.6.1 is going to be released or at least made alpha?
2. The change log here https://dev.mysql.com/doc/relnotes/mysql-utilities/en/wb-utils-news-1-6-1.html does not have any entries in it yet

This appears to still be around in 1.6.2.

ERROR: Problem updating blob field. Error = Query failed. 1064 (42000): 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 Bother: How to Protect Knowledge Online' AND `slug` = 'bradburys-bother-how-pr' at line 1

Problem still exists.

$ mysqldbcopy --version
MySQL Utilities mysqldbcopy version 1.6.4 
License type: GPLv2

ERROR: Problem updating blob field. Error = Query failed. 1064 (42000): 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 Your Move' AND `email` = 'carrie@synotoboxes.com' AND `url` = 'www.saynotoboxe' at line 1

Is there a workaround?

Ok, this has to be the worst possible bug still out there!

It was fixed in 2014 and still not working in this version
MySQL Utilities mysqldbcopy.exe version 1.6.5
License type: GPLv2

ERROR: Problem updating blob field. Error = Query failed. 1064 (42000): 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 this quote' at line 1

It looks like single quotes are still not being escaped with a \

Is there any workaround for this?

1.6.5 still has this bug:

ERROR copying data for table 'tab': Problem updating blob field. Error = Query failed. 1064 (42000): 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 'Yano Bar' AND `cross_road` = '' AND `address` = '' at line 1

1.6.5 still has this bug:
ERROR: Problem updating blob field. Error = Query failed. 1064 (42000): 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 '6227000012620104511' AND `BankAccountType` = '1' AND `BankProvince` = '北京市' at line 1

Bug #93988 marked as duplicate of this one