Missing characters in MySQL after backup restore (question marks).

[websafe]

Hello


We had an old DA (I guess 1.3.x) running on FreeBSD with MySQL 5.1.34, we did regular backups (Admin Backup/Transfer) and we restored those backups on a new server with DA 1.4.0 and MySQL 5.5.14.

Everything seems to be fine, but some characters in table-rows are missing. The strange thing is, this is not a "normal" character set problem, because most characters are OK (for example polish characters ąęźćśół) after restoring the backups, but there are some characters replaced by question marks (?), especially:

Ø Ø Ø Ø Capital O-slash
ø ø ø ø Lowercase O-slash
Euro sign


Source and destination is UTF8. As I said, most characters are OK, only a few were replaced (ø, — em dash, Euro sign).

I'm looking now into old backups, and I see these characters are replaced with question marks in the dumps too. Is this normal?

Is there a way to repair this?

I guess there is no way to repair this now, but how can I make sure, this will not happen again?

TIA

 

[zEitEr]

Hello,

I guess you should tweak /etc/my.cnf settings. I'm not sure about what are the polish character sets... but for russian I've got this in /etc/my.cnf:

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

or with utf8

init-connect="SET NAMES utf8"
character-set-server=utf8
collation-server=utf8_general_ci

Note, in some cases you might need to rebuild MySQL from sources on FreeBSD in order to make it to support your native charsets.

 

[websafe]

Thank Tou Alex for Your hints and sorry for my late answer.

Now I know, what caused the problem. Because it was not a global one, I checked those databases and noticed what follows:

1. mysqld is set to LATIN2 by default
2. those databases where LATIN2 but tables were in utf8

For example, PrestaShop databases, need to be utf8, users did not change the database encoding to UTF-8 just changed table encoding, and PrestaShop works fine, but a simple DA backup will verify that ;-)
I was thinking about switching to ut8 globally, but on this server most users use still LATIN2, I'll wait.

So we learned something new to check