MySQL Bugs: #15285: mysqldump

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

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.

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\"

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)

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.

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)
--------------------------------------------------------------

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.

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".

my.cnf files content from the (4.1.9) server dumping from

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

My.cnf from the server we read the dump into

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

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.

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.

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".