Bug #15285 mysqldump
Submitted: 28 Nov 2005 13:16 Modified: 23 Feb 2006 16:13
Reporter: Nico Schottelius Email Updates:
Status: No Feedback Impact on me:
None 
Category:MySQL Server: mysqldump Command-line Client Severity:S2 (Serious)
Version:mysqldump Ver 10.9 Distrib 4.1.14, for OS:Linux (Linux)
Assigned to: CPU Architecture:Any

[28 Nov 2005 13:16] Nico Schottelius
Description:
The umlauts (ae, oe, ue, sz) are broken in the dump, if I omit --opt (I use --skip-opt, explanation below) and specify all parameters --opt would set, but omit --extended-insert, my umlauts are broken.

I tried to use -c to fix it, but that does not work either.

I omit -e, because we put the dumps into git, so we can diff them.

The next thing I tried is to use --default-character-set=utf8 (btw, shouldn't it be UTF-8 or
at least utf-8 instead of utf8?). But this does not help either.

How to repeat:
# from a shell skript:
DB=$1

# other things tried: 
#    --default-character-set=utf8 \
# --opt \
#    -c                \
mysqldump            \
   --complete-insert \
   --skip-opt        \
   --add-drop-table  \
   --add-locks       \
   --all             \
   --quick           \
   --lock-tables     \
   -h schwein        \
   -u root           \
   -p                \
   "$DB"             > ${DB}-db.sql

Suggested fix:
Either add an option --one-insert-per-line, so one can diff it or fix the standard dump.
[28 Nov 2005 13:19] Nico Schottelius
The version on the server is different:

[root@srsyg03 nico]# mysqld --version
mysqld  Ver 4.1.9-standard-log for pc-linux-gnu on i686 (Official MySQL RPM)

We cannot update that, because the mandrake server has some serious issues.

How I verified that the umlauts are broken: I copied it with scp to our production server
(Version:mysqld  Ver 4.1.14-Debian_6-log for pc-linux-gnu on i486 (Source distribution))
and read it in with mysql:

mysql --password=\"$RPWD\" \"$DB\" < \"$DB_FILENAME\"
[28 Nov 2005 14:10] Nico Schottelius
It works, if I use an older mysqldump version:

[15:07] srsyg01:wConvert% cat dump-db.sh 
#!/bin/sh
# Nico, Wed Oct 12 17:42:35 CEST 2005

if [ $# -ne 1 ]; then
   echo "$(basename $0): DB"
   exit 1
fi

DB=$1

# NICHT NUTZEN!
#   --opt             \
# Zum Testen
# --default-character-set=utf8 \
# geht nur auf srsyg01
#   --skip-opt        \
   
mysqldump            \
   --add-drop-table  \
   --add-locks       \
   --all             \
   --quick           \
   --lock-tables     \
   -c                \
   -h schwein        \
   -u root           \
   -p                \
   "$DB"             > ${DB}-db.sql
[15:08] srsyg01:wConvert% mysqldump --version
mysqldump  Ver 10.9 Distrib 4.1.14, for pc-linux-gnu (i486)
[28 Nov 2005 14:33] Valeriy Kravchuk
Thank you for a problem report. Please, specify what exect version of mysqldump shows you this problem. Is Ver. 10.9 working OK or some older version (which one) is working OK? Please, send the my.cnf files content from the (4.1.9?) server you are dumping from and 4.1.14 server you are using to restore the dump.
[29 Nov 2005 7:41] Nico Schottelius
So here's the big version description:

This version is the problematic one:
--------------------------------------------------------------
[8:27] srsyg01:diverse-skripte% mysqldump --version
mysqldump  Ver 10.9 Distrib 4.1.14, for pc-linux-gnu (i486)
--------------------------------------------------------------
The one that runs 'fine' is the following one:
--------------------------------------------------------------
[17:52] srsyg03:wConvert% mysqldump --version
mysqldump  Ver 9.10 Distrib 4.0.18, for mandrake-linux-gnu (i586)
--------------------------------------------------------------
I am dumping from the following server:
--------------------------------------------------------------
[8:39] srsyg03:wConvert% /usr/sbin/mysqld --version
/usr/sbin/mysqld  Ver 4.1.9-standard-log for pc-linux-gnu on i686 (Official MySQL RPM)
--------------------------------------------------------------
I am restoring to the following server:
--------------------------------------------------------------
[8:34] srsyg01:diverse-skripte% /usr/sbin/mysqld --version
/usr/sbin/mysqld  Ver 4.1.14-Debian_6-log for pc-linux-gnu on i486 (Source distribution)
--------------------------------------------------------------
[30 Nov 2005 18:19] Valeriy Kravchuk
So, Ver. 10.9 is NOT working OK for you.

Please, send the my.cnf files content from the (4.1.9) server you are dumping from and 4.1.14 server you are using to restore the dump. 

Can you upload the problematic dump to the report using the File tab? I hope you do not need anything large to demonstrate the problem.
[1 Jan 2006 0:00] Bugs System
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".
[17 Jan 2006 14:49] Nico Schottelius
my.cnf files content from the (4.1.9) server dumping from

Attachment: server-my.cnf (application/octet-stream, text), 1.97 KiB.

[17 Jan 2006 14:50] Nico Schottelius
My.cnf from the server we read the dump into

Attachment: server-readin-my.cnf (application/octet-stream, text), 2.62 KiB.

[17 Jan 2006 15:02] Nico Schottelius
I cannot send the dump, because it contains sensitive customer information (adresses, Billing info). 

And now we upgraded to mysqldump  Ver 10.9 Distrib 4.1.15, for pc-linux-gnu (i486).

I did not test this versions dump yet, and we will not do many dumps in future anymore, but if you need the information, I'll manually try to verify it.
[23 Jan 2006 16:13] Valeriy Kravchuk
I reviewed your .cnf files and found nothing in them that could cause problems. Anyway, I still do not know how you created your database and tables, so, I need either a small, repeatable sequence of statements to create that problematic database (one table with one row of characters, including umlauts, should be enough, no need for customer data), or a problematic dump data uploaded.
[24 Feb 2006 0:00] Bugs System
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".