[RECEPTA] Błędne kodowanie bazy danych MySQL – złe znaki i krzaki po migracji

Dzisiejszego dnia wykonywałem dla klienta migrację na nasz hosting i kolejny raz trafiłem na problem, który zainspirował mnie do dzisiejszego wpisu. Problem może być już Tobie znany, ponieważ powraca jak bumerang, a związany jest często z niedbałością, brakiem wiedzy lub niekonsekwencją wśród niektórych administratorów. Jeżeli po migracji bazy danych masz problem z wyświetlaniem polskich znaków to być może dzięki temu wpisowi szybko poradzisz sobie z problemem…

UTF-8 powstał jako swoiste remedium na problemy z zapisem znaków diakrytycznych. Dotychczasowe tablice takie jak Windows-1250, czy ISO-8859-2 (latin2) pozwalały co prawda na zapis naszych polskich ogonków z wykorzystaniem tylko 1 bajta na znak, jednak wybrana strona kodowa ograniczała nas do konkretnych – znajdujących się w niej – znaków. UTF-8 wykorzystuje do zapisu znaków diakrytycznych 2 bajty jednak pozwala nam korzystać – jednocześnie – z wszystkich liter różnych alfabetów.

Skoro UTF-8 jest taki wspaniały, nie dziwi fakt, że administratorzy i webmasterzy chcą z niego korzystać. Niestety, czasami chcąc dobrze zabierają się za to źle, co generuje problemy, które nieraz ujawniają się dopiero przy migracji na inny – już poprawnie skonfigurowany – serwer.

Na czym najczęściej polega problem?

Bardzo często spotykam się z sytuację w której baza i tabele owszem oznaczone są i pracują jako UTF-8, ale źle skompilowana biblioteka lub zmienne środowiskowe powodują, że MySQL client zapisuje w bazie dane tak, jakby kodowaniem miało być latin1. Problemu oczywiście na początku nie widać. UTF ma szerszy zakres i bez problemu zapisuje i odczytuje te znaki samemu jednak ich poprawnie nie interpretując – jeśli można tak napisać. Dlaczego to działa? Sytuację można porównać do polaka który pisze list po polsku i następnie przekazuje go chińczykowi w kopercie z napisem „po francusku”. Chińczyk próbując przeczytać list z użyciem słownika francuskiego nic nie zrozumie, jednak gdy już ten sam polak wróci po czasie i zignoruje swój zapis „po francusku” na kopercie z łatwością przeczyta treść.

Co można zrobić?

Po pierwsze zakładam, że masz poprawie skonfigurowany serwer i konsolę, tj. z bazą UTF-8 „rozmawiasz” jako UTF-8 (lub przynajmniej informujesz klienta mysql jakim kodowaniem z nim rozmawiasz) i jednocześnie terminal na serwerze i ew. klient SSH (np. putty) również jest prawidłowo ustawiony, jednym zdaniem zakładam, że gdy z konsoli uruchomisz klienta mysql i zrobisz SELECT dla którejś z poprawnie skonfigurowanych i działających swoich baz widzisz bez problemu właściwe znaki. Jeżeli wszystko jest OK, wykonaj następujące kroki:

Krok 1: Zaimportuj bazę danych

Krok 2: Podłącz się pod bazę z konsoli:

# mysql -u user -p baza
Enter password: 

Krok 3: Wykonaj zapytanie SELECT zwracające jakieś dane z polskimi znakami:

mysql> SELECT nazwisko FROM klienci limit 10;

Krok 4: Znajdź prawidłowe kodowanie:

mysql> SET NAMES latin1;
mysql> SELECT nazwisko FROM klienci limit 10;
mysql> SET NAMES latin2;
mysql> SELECT nazwisko FROM klienci limit 10;
mysql> [...] 

Krok 5: Wykonaj zrzut bazy danych definiując kodowanie ustalone w kroku 4:

# mysqldump -u user -p --default-character-set=latin1 baza > baza.sql

Krok 6: Edytuj plik baza.sql i popraw linijkę zawierającą „SET NAMES kodowanie” tak aby przedstawiała się kodowaniem UTF-8, u mnie wygląda to tak:

/*!40101 SET NAMES utf8 */;

Krok 7: Ponownie zaimportuj poprawioną bazę z pliku baza.sql, np tak:

# mysql -u root -p
Enter password:

mysql> DROP DATABASE baza;
Query OK, 29 rows affected (0,02 sec)

mysql> CREATE DATABASE baza;
Query OK, 1 row affected (0,00 sec)

mysql> CONNECT baza;
Connection id:    53639
Current database: baza

mysql> SOURCE baza.sql;
[...]

Od tej chwili zapytania powinny już zwracać prawidłowo kodowane wyniki. Jeżeli Twój problem jest bardziej nietypowy to do rozeznania w sytuacji polecam Ci sprawdzić wynik poniższego polecenia:

mysql> SHOW VARIABLES like '%character%';
+--------------------------+----------------------------------+
| Variable_name            | Value                            |
+--------------------------+----------------------------------+
| character_set_client     | utf8                             |
| character_set_connection | utf8                             |
| character_set_database   | utf8                             |
| character_set_filesystem | binary                           |
| character_set_results    | utf8                             |
| character_set_server     | utf8                             |
| character_set_system     | utf8                             |
| character_sets_dir       | /usr/local/share/mysql/charsets/ |
+--------------------------+----------------------------------+
8 rows in set (0,00 sec)

HINT: Do zmiany kodowania pliku bezpośrednio na serwerze możesz też użyć narzędzia iconv:

# iconv
Usage:  iconv [-cs] -f <from_code> -t <to_code> [file ...]
        iconv -f <from_code> [-cs] [-t <to_code>] [file ...]
        iconv -t <to_code> [-cs] [-f <from_code>] [file ...]
        iconv -l

Niniejszy wpis nie wyczerpuje tematu. Myślę, że temat kodowania baz danych jest na tyle ciekawy, że w przyszłości konfiguracji baz danych poświęcę kolejny wpis.

Autor

Rafał Wilk

Administrator systemów Unixowych z 15 letnim doświadczeniem, specjalista od FreeBSD, Windows Admin, inżynier sieci i przedsiębiorca, a prywatnie myśliwy, niedzielny gracz i tata pięcioletniej Nadii.

Dodaj komentarz

Twój adres email nie zostanie opublikowany. Pola, których wypełnienie jest wymagane, są oznaczone symbolem *