Description:
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 DATABASE IF NOT EXISTS `test`;
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.
