Bug #39579 mysqldump --compatible=postgresql not escaped string like postgres E'

• Submitted: 22 Sep 2008 8:54
• Modified: 23 Sep 2008 9:58
• Reporter: Alex Dereka
• Status: Verified
• Category: MySQL Server: mysqldump Command-line Client
• Severity: S3 (Non-critical)
• Version: 5.0.51, 4.1, 5.0, 5.1, 6.0 bzr
• OS: Any (Linux, Windows)
• CPU Architecture: Any

[22 Sep 2008 8:54] Alex Dereka

Description:
mysqldump  --compatible=postgresql not escaped string like postgres E'
and have trables with utf8 and russian lang (have in dump ? ??????????? ????????? ????????? ????????? ???????. ?, ??????? ?????, ???? ?? ????) :(
But not have trables with utf8 and russian lang where using 
mysqldump --user=Mysql --password=secret db > outputfile.sql

How to repeat:
mysqldump --user=mysql --password=secret --compatible=postgresql db > outputfile.sql

[22 Sep 2008 9:08] Sveta Smirnova

Thank you for the report.

But version 5.0.51 is a bit old. Please try current version 5.0.67 and if problem still exists provide repeatable test case and/or  outputfile.sql

[22 Sep 2008 12:32] Alex Dereka

i'm update to 5.0.67 server and problem exist.

DROP TABLE IF EXISTS "locales_creature";
CREATE TABLE "locales_creature" (
  "entry" mediumint(8) unsigned NOT NULL default '0',
  "name_loc1" varchar(100) NOT NULL default '',
  "name_loc2" varchar(100) NOT NULL default '',
  "name_loc3" varchar(100) NOT NULL default '',
  "name_loc4" varchar(100) NOT NULL default '',
  "name_loc5" varchar(100) NOT NULL default '',
  "name_loc6" varchar(100) NOT NULL default '',
  "name_loc7" varchar(100) NOT NULL default '',
  "name_loc8" varchar(100) NOT NULL default '',
  "subname_loc1" varchar(100) default NULL,
  "subname_loc2" varchar(100) default NULL,
  "subname_loc3" varchar(100) default NULL,
  "subname_loc4" varchar(100) default NULL,
  "subname_loc5" varchar(100) default NULL,
  "subname_loc6" varchar(100) default NULL,
  "subname_loc7" varchar(100) default NULL,
  "subname_loc8" varchar(100) default NULL,
  PRIMARY KEY  ("entry")
);

INSERT INTO "locales_creature" VALUES (3,'','','','','','','','???????')

(???) in dump :(. But where are using mysqldump  with out (--compatible=postgresql) option not have this problem

[22 Sep 2008 20:12] Sveta Smirnova

Thank you for the report.

Verified as described.

Test case:

DROP TABLE IF EXISTS locales_creature;
CREATE TABLE locales_creature (
  entry mediumint(8) unsigned NOT NULL default '0',
  name_loc1 varchar(100) NOT NULL default '',
  PRIMARY KEY  (entry)
) default charset=utf8;

set names utf8;

insert into locales_creature(entry, name_loc1) values(1, 'Света');

--exec $MYSQL_DUMP test

--exec $MYSQL_DUMP  --compatible=postgresql test

[23 Sep 2008 9:58] Alex Dereka

--exec $MYSQL_DUMP test

LOCK TABLES `locales_creature` WRITE;
/*!40000 ALTER TABLE `locales_creature` DISABLE KEYS */;
INSERT INTO `locales_creature` VALUES (1,'Света');
/*!40000 ALTER TABLE `locales_creature` ENABLE KEYS */;
UNLOCK TABLES;

--exec $MYSQL_DUMP  --compatible=postgresql test

LOCK TABLES "locales_creature" WRITE;
/*!40000 ALTER TABLE "locales_creature" DISABLE KEYS */;
INSERT INTO "locales_creature" VALUES (1,'?????');
/*!40000 ALTER TABLE "locales_creature" ENABLE KEYS */;
UNLOCK TABLES;

[26 Apr 2010 19:29] Mikhail T

We are suffering from this problem too. Server version 5.0.90. Our entire database (a phpBB forum) is in UTF8.

Could MySQL, at least, provide the ETA for a fix? Or a work-around -- some sort of post-processing for the results of the dump?