MySQL Bugs: #48837: mysqldump --all-databases: german äöü destroyd

Bug #48837	mysqldump --all-databases: german äöü destroyd
Submitted:	17 Nov 2009 12:46	Modified:	21 Nov 2009 6:51
Reporter:	Harald Reindl	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Charsets	Severity:	S2 (Serious)
Version:	5.1.41	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
We are using the following backup-method to step back if anyone has made a big mistake in the last two hours, but "öäü" are destroyed in the target-database (both machines are cloned vmware-images)
______________

SOURCE-MACHINE:

mysqldump --ignore-table=mysql.slow_log --comments=FALSE --compact=TRUE --lock-tables=TRUE --add-locks=TRUE --add-drop-database=TRUE --routines=TRUE --triggers=TRUE --create-options=TRUE --extended-insert=FALSE --hex-blob=TRUE --quote-names=TRUE --disable-keys=TRUE -u backupuser -p****** --all-databases > /Volumes/dune/mysqldump.sql

rsync --quiet --password-file=/etc/rsync/password --compress --compress-level=3 --perms --owner --group --inplace /Volumes/dune/mysqldump.sql rsync://backupuser@$TARGET_MACHINE/dune/mysqldump.sql
______________

TARGET-MACHINE:
mysql -f -e "source /Volumes/dune/mysqldump.sql" -u backupuser -p****
______________

BEGIN OF THE DUMP:

/*!40000 DROP DATABASE IF EXISTS `aaeenergy`*/;
CREATE DATABASE /*!32312 IF NOT EXISTS*/ `aaeenergy` /*!40100 DEFAULT CHARACTER SET latin1 COLLATE latin1_german1_ci */;
USE `aaeenergy`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `cms1_anfragen` (

How to repeat:
* make a dump of all databases
* copy the dump with rsync to the target-machine
* import the dump with "mysql -f -e"

Hello Harald,

this isn't a bug at all but I want to help you anyway because this is kind of tricky.

I need some things from you:

1) Version of "old" mysql server
2) Operating system of "old" database
3) on which OS did you store the dump
4) part of dump file where i can see text with umlaut
5) To which version and which OS did you restore it?

MySQL-Spec for our binaries

Attachment: mysql.spec (application/octet-stream, text), 31.75 KiB.

As said both are the same versions because we speak about vmware-clones with central update-repo for self-built-packages and both machines are using nearly the same configuration (only buffer/cache-sizes are changed)

I have attached our "mysql-spec" also

2.6.30.9-96.fc11.x86_64 #1 SMP Wed Nov 4 00:02:04 EST 2009 

rpm -qa | grep mysql
mysqlreport-3.5-3.fc11.noarch
qt-mysql-4.5.3-9.fc11.x86_64
rsyslog-mysql-3.22.1-1.fc11.x86_64
mysql-5.1.40-2.fc11.rh.x86_64
php-mysql-5.2.11-16.fc11.rh.x86_64
mysql-administrator-5.0r12-11.fc11.x86_64
mysqltuner-1.0.0-3.fc11.rh.noarch
mysqlclient15-5.0.86-1.fc11.rh.x86_64
mysql-gui-common-5.0r12-11.fc11.x86_64
mysql-bench-5.1.40-2.fc11.rh.x86_64
mysql-libs-5.1.40-2.fc11.rh.x86_64
mysql-server-5.1.40-2.fc11.rh.x86_64

Harald,

you totally misunderstood me.

I need example output from you.

Also I need to know where you see it. Terminal? Editor? Console?

You are using linux, so output of

$ locale

would be interesting too.

I have changed our backup-scenario 

* MySQL-Replication
* Backup-Machine has to mysqld-instances
* replication-slave on 3307 and backup on 3306
* every hour cron stops both mysqld, rsync of the data and restart
____________

Anyways, if mysqldump generates a full dump of all databases there should never be data changed after importing because the dump is useless :-(
[root@arrakis:~]$ locale
LANG=de_DE.UTF-8
LC_CTYPE="de_DE.UTF-8"
LC_NUMERIC="de_DE.UTF-8"
LC_TIME="de_DE.UTF-8"
LC_COLLATE="de_DE.UTF-8"
LC_MONETARY="de_DE.UTF-8"
LC_MESSAGES="de_DE.UTF-8"
LC_PAPER="de_DE.UTF-8"
LC_NAME="de_DE.UTF-8"
LC_ADDRESS="de_DE.UTF-8"
LC_TELEPHONE="de_DE.UTF-8"
LC_MEASUREMENT="de_DE.UTF-8"
LC_IDENTIFICATION="de_DE.UTF-8"
LC_ALL=

I see the change in the browser
pagetitle in our CMS
this is a varchar(255) and all special chars are destroyed

Thank you for the feedback.

If I understood correctly you did dump of version 5.1.30 and restore with 5.1.40?

Does it help if you add option --default-character-set=utf8 to both mysqldump and mysql calls?

> If I understood correctly you did dump of version 5.1.30 
> and restore with 5.1.40?

No everytime the same version, now 5.1.41
All our machines are vmware-images from the same clone
For every production-machine runs one copy on another host
Cronscripts are making backups of docroot, databases and configs every hour

> Does it help if you add option --default-character-set=utf8 to both
> mysqldump and mysql calls?

Of course it does!
Tried it on two test-machines a few minutes ago
This is why i think there is a bug because if both machines running the same mysql-version, the same os and identical settings there should be no need to give any argument making sure there are no data changed

Thank you!

Thank you for the feedback.

You have option --compact=TRUE in mysqldump call which is shorthand to --skip-add-drop-table, --skip-add-locks, --skip-comments, --skip-disable-keys --skip-set-charset  

With --skip-set-charset  final dump has no charset information, so it is expected what you have to define it during restore.