SOLVED executing INSERT commands with ascii 8216 and 8217 (smart quotes) made using mysqldump

[Andy Nicastro]

Hi,

Given:
drop table x;
create table x (fragment text);
INSERT INTO `x` (`fragment`)
VALUES
('‘abc def’\r\n');
That last line is (single quote, i.e. ascii 39 , followed by single left quotation mark, i.e. ascii 8216 , followed by abc def, followed by single right quotation mark, i.e. ascii 8217, followed by \r\n followed by single quote, i.e. ascii 39).

The code works on Server version: 5.1.39 MySQL Community Server (GPL) (my local development server):
Result:
mysql> INSERT INTO `x` (`fragment`)
-> VALUES
-> ('‘abc def’\r\n');
Query OK, 1 row affected (0.00 sec)
=================================================

On Server version: 5.0.51a-24+lenny5 (Debian) (a remote test server), it produces an error:
Result:
mysql> INSERT INTO `x` (`fragment`)
-> VALUES
-> abc \r\n');def('
ERROR 1044 (42000): Access denied for user 'tobivroegh'@'%' to database 'n');def(''
'>

while on the same server, the same code without the ascii 8216 and 8217 quotes works:
mysql> INSERT INTO `x` (`fragment`)
-> VALUES
-> ('abc def\r\n');
Query OK, 1 row affected (0.00 sec)
============================================================

Any ideas of how I can easily work around this problem, a mysql configuration parameter setting or command line switch?

It's about an SQL file made using mysqldump on a live production server. The file contains many INSERT commands that are very long and complex with a lot of text containing ascii 8216 and ascii 8217 characters.

Thanks for your help.
Andynic



Edited 1 time(s). Last edit at 04/28/2012 06:01AM by Andy Nicastro.