MySQL Bugs: #57304: mysqldump fails to dump in latin-1 when table created in utf-8

Bug #57304	mysqldump fails to dump in latin-1 when table created in utf-8
Submitted:	7 Oct 2010 10:16	Modified:	12 Nov 2010 6:02
Reporter:	SaiKumar V	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server: mysqldump Command-line Client	Severity:	S3 (Non-critical)
Version:	5.1.52, 5.5.7	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	mysqldump

Description:
This defect was noticed during the analysis of bug :- http://bugs.mysql.com/bug.php?id=51984.

There are 2 issues mentioned in Bug 51984
   (a) there is a bug in the test harness mtr-test-run.pl
 & (b) there is a bug with mysqldump on widnows

Here issue (b) is been addresed:-

mysqldump fails to dump when the table is created as UTF-8, and dumped in latin-1 using --default-character-set, this is an exiting testcase in mysqldumo testsuite under mtr-test.

Errors noticed (during mtr test run):-
SET NAMES utf8;
CREATE TABLE `╫¢╫ô╫⌐╫Æ╫¢╫ù╫Ü╫£╫ô╫Æ╫¢╫ù╫⌐╫Ü╫ô╫Æ╫ù╫¢╫Ü╫£╫ô╫Æ╫¢` ( f1 INT );
main.new                                 [ fail ]
        Test ended at 2010-10-07 15:14:24

CURRENT_TEST: main.new
mysqldump: Got error: 1017: Can't find file: '.\test\@003f@003f@003f@003f@003f@0
03f@003f@003f@003f@003f@003f@003f@003f@003f@003f@003f@003f@003f@003f@003f@003f@0
03f@003f.frm' (errno: 22) when using LOCK TABLES
mysqltest: At line 7: command "$MYSQL_DUMP --character-sets-dir=$CHARSETSDIR --s
kip-comments --default-character-set=latin1 --compatible=mysql323 test" failed

the defect is noticed on Linux, however in the 003f are translated to ? there, but the dump file is still not created.

How to repeat:
##mtr-testcase below:-

SET NAMES utf8;
CREATE TABLE `×›×“×©×’×›×—×š×œ×“×’×›×—×©×š×“×’×—×›×š×œ×“×’×›` ( f1 INT );
--exec $MYSQL_DUMP --character-sets-dir=$CHARSETSDIR --skip-comments --default-character-set=latin1 --compatible=mysql323 test
DROP TABLE `×›×“×©×’×›×—×š×œ×“×’×›×—×©×š×“×’×—×›×š×œ×“×’×›`;
SET NAMES latin1

##Note the same testcase if available in main.mysqldump testsuite file.

##Also, attaching the testcase file which has the same testcase ( it can be place in the mysql-test\t\ folder and run to reproduce the defect.

mtr based test file

Attachment: new.txt (text/plain), 303 bytes.

I am not able to repeat this.

Here is my test:

DROP SCHEMA IF EXISTS bug57304;

CREATE SCHEMA bug57304 CHARSET utf8;
USE bug57304;

SET NAMES utf8;

CREATE TABLE täböll(i SERIAL, v VARCHAR(255));

INSERT INTO täböll(v) VALUES('Bär');
INSERT INTO täböll(v) VALUES('Änderung');
INSERT INTO täböll(v) VALUES('Übung');
INSERT INTO täböll(v) VALUES('Lüge');
INSERT INTO täböll(v) VALUES('Öffnung');
INSERT INTO täböll(v) VALUES('Möglichkeit');
INSERT INTO täböll(v) VALUES('Gården');
INSERT INTO täböll(v) VALUES('Maße');
INSERT INTO täböll(v) VALUES('Crêpes');
INSERT INTO täböll(v) VALUES('déjà vu');

$ mysqldump --default-character-set=latin1 bug57304 > bug57304.dump

$ file bug57304.dump bug57304.dump: ISO-8859 text

I also tried to restore:

$ mysql --default-character-set=latin1 < bug57304.dump

My table named "täböll" is totally correct utf8 encoded.

I tested with MySQL 5.1.52 on Ubuntu.

Which OS did you take?

Hi,

i have reproduced the bug( also with your testcase) on Linux :- Red Hat Enterprise Linux Server release 5.1 (Tikanga)

&

Windows XP Professional Editions (SP2).

Please let me know if you require and additional details,

Updated comments.

The bug is not reproducible on Linux, my test database seems to be corrupt, it was crashing the dump.

I am having difficutly with your testcase on Windows, can you please verify this at your end. (windows xp professioanl edition) (sp2). Orginally the problem appeared on Windows.

Update 2:

I have reproduced the defect with your testcase on Windows (XP Professional SP2).

There is no crash with your testcase on windows, but fails to create the mysqldump file for the utf-8 table.

Hello Saikumar,

how did you test it on Windows?

Windows is not using/supporting utf8.

To test this on Windows you need first to look which encoding your Windows is using.

You will figure out this by using:

CHCP

For creating the tables correct you need to use:
SET NAMES whatever_codepage_your_windows_is_using

For example when your Windows is using codepage 850 then you need to say:

SET NAMES cp850;

CREATE TABLE ...;

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