MySQL Bugs: #46622: MySQL dump-restore replaces characters with 3Fh

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

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

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;

Workaround: specify option --default-character-set=cp1251. But this workaround would not work with test case with mixed character sets like I provided.

fixed typos in tags

Fixed in 5.1.54.