MySQL Bugs: #64891: UTF-8 problem while backup

Bug #64891	UTF-8 problem while backup
Submitted:	6 Apr 2012 17:40	Modified:	11 Jun 2012 16:38
Reporter:	Toto Toto	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server: Backup	Severity:	S3 (Non-critical)
Version:	5.1.41	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
In my app, I create a Xml, gzip it and save like a lob in my table. I i request 
and unzip all is ok for utf-8.
Now I use MySql Query Browser to backup my database from Debian remote server and restore it on windows local machine. I request and unzip my lob some characters looks like 
that Ã©.

Server configuration :
[mysqld]
default-character-set=utf8
 
[client]
default-character-set=utf8

mysql> SHOW VARIABLES LIKE 'character_set%';

| character_set_client     | utf8                      
| character_set_connection | utf8                     
| character_set_database   | utf8                      
| character_set_filesystem | binary                      
| character_set_results    | utf8                     
| character_set_server     | utf8                      
| character_set_system     | utf8                        
| character_sets_dir       | /usr/share/mysql/charsets/

Restore config : utf8

What is wrong ?

How to repeat:
Create a Xml, gzip it and save like a lob in my table. I i request 
and unzip all is ok for utf-8.
Now I use MySql Query Browser to backup my database from Debian remote server and restore it on windows local machine. I request and unzip my lob some characters looks like 
that Ã©.

Please, send the output of SHOW CREATE TABLE for the table in question from both Linux and Windows. What exact server versions do you use on these systems?

MySql administrator 1.2.17
MySQL Client version 5.1.11

Windows Xp SP3 :
-MySQL 5.1.31-community
SHOW CREATE TABLE 
=> CREATE TABLE `content` (`id` varchar(255) NOT NULL,  `data` longblob NOT NULL,  PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8

Linux :
uname -a
Linux 2.6.32-21-server #32-Ubuntu SMP Fri Apr 16 09:17:34 UTC 2010 x86_64 GNU/Linux
MySQL 5.1.41-3ubuntu12.10
SHOW CREATE TABLE 
=> CREATE TABLE `content` (
  `id` varchar(255) NOT NULL,
  `data` longblob NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

Thank you for the feedback.

But MySQL Administrator is not supported anymore. Please try with MySQL Workbench or mysqldump command line utility and inform us if problem still exists.

I tried with MySql Workbench 5.2.39 CE and mysqldump command line and I have same problem.

Thank you for the feedback.

Could you please send us example of command you use to call mysqldump? Which exact version of MySQL server do you use?

MySQL 5.1.41-3ubuntu12.10

mysqldump -u root -p myschema > backup.sql

Thank you for the feedback.

I can not repeat described behavior. Additionally version 5.1.41 is very old. Please try with current version 5.1.63 and if problem still exists provide XML which causes problems.

Problem still exists

mysql --version
mysql  Ver 14.14 Distrib 5.1.62, for debian-linux-gnu (x86_64) using readline 6.1
                               
mysql  Ver 14.14 Distrib 5.1.63, for Win32 (ia32)

Backup & restore :
mysqldump -u root -p myschema > backup.sql
mysql -u root -p myschema < backup.sql

Xml before :

<batiment>
<comment>
<manager>les établissements privés&#13;
 </manager>
 </comment>
</batiment>

Xml after :

<batiment>
<comment>
<manager>les Ã©tablissements privÃ©s&#13;
</manager>
</comment>
</batiment>

Thank you for the feedback.

I still can not repeat described behavior. Please provide script you use to store file in database.

I use following statement:

mysql> insert into content values('foo', load_file('/home/sveta/build/mysql-5.1/mysql-test/var/mysqld.1/data/test/bug64891.xml.gz'));
Query OK, 1 row affected (0.09 sec)

Then select with:

php -r '$link = mysqli_connect("127.0.0.1","root","","test",13000); $res=mysqli_query($link, "select data from content"); $row = mysqli_fetch_row($res); echo $row[0];' >bug64891_result.gz

I use xsl to create xml ( had tried with  <xsl:output method="xml" omit-xml-declaration="no" indent="no" encoding="UTF-8"/> and <xsl:output method="xml" omit-xml-declaration="yes"/>
java.util.zip.GzipOutputstream and Jpa/hibernate

Thank you

I forgot xstream, traxsource, dom4jdriver, outputstreamwriter, javax.Transformer, Streamresult, ByteArrayOutputStream.

Thank you for the feedback.

But I don't ask about how you created XML. I am interesting in how do you store it in MySQL. Could you please provide query and/or application code you use?

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".