Bug #65941 mysqldump should use single quote for escaping
Submitted: 18 Jul 2012 15:42 Modified: 18 Jul 2012 16:40
Reporter: Tomas Creemers Email Updates:
Status: Verified Impact on me:
Category:MySQL Server: mysqldump Command-line Client Severity:S3 (Non-critical)
Version:5.6.5-m8 OS:Any
Assigned to: CPU Architecture:Any
Tags: ansi, backslash, escaping, mysqldump, NO_BACKSLASH_ESCAPES, single quote

[18 Jul 2012 15:42] Tomas Creemers
mysqldump generates dumps in which strings are enclosed in single quotes and single quotes inside the string are escaped with a backslash. This is done regardless of the server's sql mode or mysqldump's compatibility setting. This method of escaping causes problems:
 - see bug #27552, bug #61656, bug #400
 - the individual insertion statements in the dump are not usable on any server with sql mode NO_BACKSLASH_ESCAPES (with or without replication)

The more standard double single quote escaping style works for servers with AND without that sql mode. The backslash escaping style only works for servers without sql mode NO_BACKSLASH_ESCAPES.

mysqldump does reset the sql mode at the top of the dump file, but dump files are often edited and used as a basis for a dataset. The stricter sql modes are useful to guard the database against faulty (manually edited) data, so the sql mode resetting at the top of the dump file is (and should be) often removed to enable this stricter checking. At this point, the dumped insertion queries generate errors.

How to repeat:
CREATE TABLE `quotingtest`(`name` VARCHAR(255));
INSERT INTO `quotingtest`(`name`) VALUES ('It''s Jim''s life.');

mysqldump --result-file=testtable.sql test quotingtest
mysql -Dtest < testtable.sql

Suggested fix:
Change mysqldump to use the more standard double single quote escaping style. This works on all servers regardless of sql mode, whereas the current escaping style (using backslashes) only works on servers with sql mode NO_BACKSLASH_ESCAPES disabled.
[18 Jul 2012 16:40] Valeriy Kravchuk
Sounds like a feature request that is worth considering.
[26 Nov 2014 15:10] Pavel Cibulka
Any progress on this? 

NO_BACKSLASH_ESCAPES should be true by default. Sadly it is not going to happen in 5.7.