Bug #46622 MySQL dump-restore replaces characters with 3Fh
Submitted: 9 Aug 2009 20:01 Modified: 18 Jan 2018 13:14
Reporter: Alexey Ivanov Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Charsets Severity:S2 (Serious)
Version:5.0, 5.1, azalea bzr OS:Any (FreeBSD 6.3, Linux)
Assigned to: Assigned Account CPU Architecture:Any
Tags: 3f, 3Fh, charset, dump, restore

[9 Aug 2009 20:01] Alexey Ivanov
Description:
I have mysql table with such structure

CREATE TABLE `files` (
`hash` char(20) CHARACTER SET cp1251 COLLATE cp1251_bin NOT NULL DEFAULT '',
) ENGINE=InnoDB AUTO_INCREMENT=128511 DEFAULT CHARSET=cp1251

If I restore it on the same server then I'm getting not quite the same data, for example:

SELECT hex(hash) FROM files

8CB0701A0C8B7D976538**88**E8B959A6420AEC7679 after restore becomes 8CB0701A0C8B7D976538**3F**E8B959A6420AEC7679 

A9**98**6D2E961E1F407E74E9D18D811DDB702924F5 after restore becomes A9**3F**6D2E961E1F407E74E9D18D811DDB702924F5 

and so on... i've noticed only 88h and 98h characters corruption, but there can be more.

How to repeat:
FreeBSD Server with make.conf like that:

.if ${.CURDIR} == ${PORTSDIR}/databases/mysql50-server
WITH_CHARSET=cp1251
WITH_COLLATION=cp1251_bin
BUILD_OPTIMIZED=yes
BUILD_STATIC=yes
.endif
.if ${.CURDIR} == ${PORTSDIR}/databases/mysql50-client
WITH_CHARSET=cp1251
WITH_COLLATION=cp1251_bin
BUILD_OPTIMIZED=yes
.endif

my.cnf
[mysqld]
init-connect="SET NAMES cp1251"
character-set-server=cp1251
collation-server=cp1251_general_ci

mysql table:
mysql> show create table files\G
*************************** 1. row ***************************
       Table: files
Create Table: CREATE TABLE `files` (
`hash` char(20) CHARACTER SET cp1251 COLLATE cp1251_bin NOT NULL DEFAULT '',
) ENGINE=InnoDB AUTO_INCREMENT=128511 DEFAULT CHARSET=cp1251

I'm backing it up with:
mysqldump --quote-names --add-drop-table db>db.sql

Restore it using:
mysql < db.sql
[10 Aug 2009 6:02] Sveta Smirnova
Thank you for the report.

Verified as described.

Test case:

CREATE TABLE `files` (
`hash` char(20) CHARACTER SET cp1251 COLLATE cp1251_bin NOT NULL DEFAULT ''
) DEFAULT CHARSET=cp1251
;

insert into files values(0x88);
insert into files values(0x98);
insert into files values(0x3F);

SELECT hex(hash) FROM files;

CREATE TABLE `files1` (
`hash` char(20) CHARACTER SET cp1250 COLLATE cp1250_bin NOT NULL DEFAULT ''
) DEFAULT CHARSET=cp1250
;
insert into files1 values(0x81);
insert into files1 values(0x83);
insert into files1 values(0x88);
insert into files1 values(0x90);
insert into files1 values(0x98);

SELECT hex(hash) FROM files1;

--exec $MYSQL_DUMP  --quote-names --add-drop-table --default-character-set=utf8 test >$MYSQL_TEST_DIR/var/tmp/bug46622.sql
--exec $MYSQL test <$MYSQL_TEST_DIR/var/tmp/bug46622.sql

SELECT hex(hash) FROM files;
SELECT hex(hash) FROM files1;
[10 Aug 2009 6:04] Sveta Smirnova
Workaround: specify option --default-character-set=cp1251. But this workaround would not work with test case with mixed character sets like I provided.
[25 Aug 2009 20:50] Alexey Ivanov
fixed typos in tags
[18 Jan 2018 13:14] Erlend Dahl
Fixed in 5.1.54.