Home / server / Questions / 52014
Accepted
SaveTheRbtz
Asked: 2009-08-10 11:32:02 +0800 CST

MySQL dump-restore replaces characters with 3Fh

  • 772

FreeBSD Server with Mysql and 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

I have mysql table with such structure

mysql> show create table phpbb_bt_torrents\G
*************************** 1. row ***************************
       Table: phpbb_bt_torrents
Create Table: CREATE TABLE `phpbb_bt_torrents` (
`info_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 at night with mysqldump --quote-names --add-drop-table db>db.sql

If I restore backup using mysql < db.sql on the same server then I'm getting not quite the same data, for example:

SELECT hex(info_hash) FROM phpbb_bt_torrents

8CB0701A0C8B7D97653888E8B959A6420AEC7679 becomes 8CB0701A0C8B7D9765383FE8B959A6420AEC7679 after restore

A9986D2E961E1F407E74E9D18D811DDB702924F5 becomes A93F6D2E961E1F407E74E9D18D811DDB702924F5 after restore

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

Is there a way to backup-restore data without corruption?

backup mysql freebsd restore corruption

2 Answers

  1. Can you try dumping with "mysqldump --opt ..." and then restoring it?

    It shouldn't make a difference when on the same server, just to make sure this cannot be some user-specific configuration.

    • 0
  2. Best Answer

    I've opened the ticket on Bugs.MySQL.com

    Workaround from there:

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

    • 0