Pro webhosting nejpoužívanější databáze MySQL přidává od verze 4.1 podporu jazyků. Tato nová funkce však způsobuje nemalé probémy při převodu dat z předchozí verze 4.0. Článek popisuje jednu z možností, jak provést upgrade databáze MySQL 4.0 na 4.1 se zachováním správné diakritiky.
Situace
Před tím, než si popíšeme postup migrace, popíšeme si důvod, proč jsem musel udělat migraci na vyšší verzi MySQL.
Potřeboval jsem k redakčnímu systému webu přidat další aplikaci, která měla zakladací skripty, které byly napsané pro MySQL 4.1 a vyšší – obsahovaly COLLATE cp1250_czech_cs. Redakční systém běžel zatím na verzi MySQL 4.0, která nemá podporu jazyků a tudíž nezná COLLATE.
Řešením by mohlo být vymazat COLLATE ze zakládacích skriptů nebo migrovat na MySQL 4.1. První řešení by bylo jen provizorní, protože povýšení MySQL bychom se do budoucna asi jen stěží vyhnuli. Zvolil jsem tedy povýšení MySQL ze 4.0 na 4.1. Hosting nabízí obě verze databáze. Při překopírování dat však došlo k rozhození češtiny.
Jak funguje jazyková popora
Až od verze 4.0 MySQL ukládá databáze data tak, jak přišla a neřeší v jakém kódování jsou data uložena. Od verze 4.1 byla přidáná podpora jazyků. Data jsou interně ukládána v UTF-8. Navenek komunikace ale může probíhat v libovolném jiném kódování. Kódování lze definovat až na úrovni jednotlivých sloupců (atributů) tabulky. Při komunikaci dochází k převodu, což může být kámen úrazu.
Co je nutné, aby čeština fungovala správně:
- U sloupců mít správně nastavené COLLATE, např. COLLATE cp1250_czech_cs.
- Po připojení správně nastavit SET CHARACTER SET, např. SET CHARACTER SET cp1250 nebo SET NAMES, např. SET NAMES cp1250.
Nabízí se otázka, jaké kódování zvolit. Interně si databáze ukládá data v UTF-8. My můžeme komunikovat s jiným kódování. Zpravidla použijeme takové, které máme použito u webové prezentace. Tzn. máme-li prezentaci v CP1250 (Windows), použijeme CP1250. Máme-li prezentaci v UTF-8 (např. celá psaná na Linuxu), použijeme UTF-8.
Postup migrace
Zkoušel jsem několik různých návodů, ale nakonec jsem zvolil následující řešení:
1. Na původní MySQL 4.0 provést export do SQL
K původní databázi (MySQL 4.0) jsem měl přístup pouze pomocí phpMyAdmina, což byl trošku problém, protože databáze měla 80 MB. První selhání nastalo při exportu a zazipování souboru. Nakonec se podařilo úspěšně exportovat, ale bez komprese souboru. Jelikož jsem měl v databázi uložena binární data, ješte bych doporučil vypnout volbu „
2. Doplnit COLLATE
Protože MySQL 4.0 neumí podporu jazyků, není v exportu v zakládacím skriptu použito slovo COLLATE, které je ovšem potřebné při importu do MySQL 4.1. Proto do přikazů CREATE TABLE doplníme COLLATE dle příkladu. Dle situace samozřejmě použijeme odpovídající kódování.
CREATE TABLE `akce` (
`id` int(11) NOT NULL auto_increment,
`nadpis` varchar(50) collate cp1250_czech_cs default NULL,
`popis` tinytext collate cp1250_czech_cs ,
`text` text collate cp1250_czech_cs ,
`datum` date default NULL,
`obrazek` varchar(100) collate cp1250_czech_cs default NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `id` (`id`)
) TYPE=MyISAM AUTO_INCREMENT=392 ;
3. Na nové MySQL 4.1. provést import SQL dumpu
Nyní by se mohlo zdát, že již provedeme import pomocí phpMyAdmina a je hotovo. Není to tak jednoduché. Máme-li databázi rozumně malou, tj asi do 8 MB, není problém. Jelikož moje databáze je kolem 80 MB velká, nelze import provést pomocí phpMyAdmina, který je limitovaný velikostí uploadovaného souboru (z pravidla 2-8 MB) a také dobou běhu PHP skriptu (zpravidla 30 s).
K importu mohu doporučit aplikaci SQLyog (zdarma), která běží na klientovi (na vašem PC) a připojuje se přímo na MySQL databázi (zpravidla na portu 3306). Podmínka ovšem je, že tuto možnost připojení hosting nabízí. SQLyog není nijak omezen velikosti databáze ani délkou importu.
Před importem je ještě nutné do SQL souboru přidat
SET NAMES cp1250;
aby databáze věděla, v jakém kódování jsou data v souboru.
Závěr
Jelikož jsem nikde nanašel postup migrace, který by mi vyhovoval a nakonec jsem si nějaký způsob našel, sepsal jsem tento návod pro ty, kteří se budou muset trápit stejně jako já. Snad jim pomůže :-). Všem problematiky více znalým se omlouvám za nepřesnosti a nedokonalosti.
Dobry den,
predem bych chtel podekovat za prvni relativne uplny soupis, co presne udelat a jak postupovat pri migraci. Internet je plny ruznych polovicatych reseni, dotazy na upresneni obvykle konci odkazem „najdi si to na googlu“, a tak clovek stale tape.
Mel bych k Vam ale nekolik dotazu ci mozna doplneni. Stojim pred ukolem premigrovat radove 30 mysql 4.0.26 databazi do nejake verze 5.0. Chci si odpustit mezikrok migrace do 4.1, protoze vse se bude odehravat na produkcnim prostredi a je proto potreba minimalizovat dobu vypadku.
Z vami uvedeneho popisu vyplyva, ze jste mel moznost pouzit jen phpMyAdmin. Ja mohu pracovat na urovni operacniho systemu, a tedy misto dumpovani pres web pouzit mysqldump (ono by to ani jinak neslo, databaze maji nekolik Gb). Zde jsem resil otazku kodovani. Mysql 4.0 uklada data skutecne tak, jak ji prijdou, a proto abych mel jistotu, ze pracuji vsude se stejnou kodovou strankou, udelal jsem tyto dva dotazy :
//zaloha
mysqldump –default-character-set=win1250 -d -u xxxx -p databaze > databaze_structure.sql
// jenom struktury
mysqldump –default-character-set=win1250 -t -u xxxx -p databaze > databaze_data.sql
//jenom data
tato dvojice vyexportuje nejprve strukturu vsech tabulek v databazi do jednoho souboru, a nasledne INSERT skripty pro vsechna data do druheho souboru. Vsechna data jsou v WIN1250 (CP1250 chcete li) kodovani.
Nyni otazka :
je opravdu nutne davat ke kazdemu sloupci to collate ? budu migrovat stovky tabulek, neda se toto resit nejakym „defaultnim“ nastavenim nekde na zacatku toho SQL skriptu zakladani tabulek ?
pak jen staci starou databazi vypnout, nainstalovat novou, a za pomoci
mysql -u xxxx -p databaze < databaze_structure.sql
nahrat nejprve strukturu, a potom
mysql -u xxxx -p databaze < databaze_data.sql
i data. Do souboru s daty je treba na zacatek doplnit jeste to
SET NAMES cp1250;
Bude takto premigrovana mysql 5.0 databaze fungovat pro puvodni PHP skripty bez nutnosti jejich zmeny / upravy, co se kodovani tyce ? V soucasnosti vsichni pouzivaji CP1250 pro zapis do databazi, a v tomto smeru bych take nastavoval kodovani tabulek i collate sloupcu.
Dekuji
Dobrý den
Děkuji za kladné hodnocení článku.
K Vašim dotazům…
> Z vami uvedeneho popisu vyplyva, ze jste mel moznost pouzit jen
phpMyAdmin. Ja mohu pracovat na urovni operacniho systemu, a tedy misto dumpovani pres web pouzit mysqldump (ono by to ani jinak neslo, databaze maji nekolik Gb). Zde jsem resil otazku kodovani. Mysql 4.0 uklada data skutecne tak, jak ji prijdou, a proto abych mel jistotu, ze pracuji vsude se stejnou kodovou strankou, udelal jsem tyto dva dotazy :
Já to řešil phpmyadminem. Pokud jsou DB velké, osvědčil se mi SQLyog, který se připojuje přímo na MySQL (musí to umožňovat hosting), takže na velikosti nezáleží. mysqldump jsem ještě ani nezkoušel 🙂
> je opravdu nutne davat ke kazdemu sloupci to collate ? budu migrovat
stovky tabulek, neda se toto resit nejakym „defaultnim“ nastavenim nekde na zacatku toho SQL skriptu zakladani tabulek ?
Na tohle nevím přesnou odpověď, ale myslím že ano, COLLATE musíste zadat.
Ale když se teď dívám do manuálu mysql, http://dev.mysql.com/doc/refman/5.0/en/create-database.html
CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name
[create_specification] …
create_specification:
[DEFAULT] CHARACTER SET [=] charset_name
| [DEFAULT] COLLATE [=] collation_name
…takže teoreticky by to moholo stačit zde. Vyzkoušejte…
> Chci si odpustit mezikrok migrace do 4.1, protoze vse se bude
> odehravat na
produkcnim prostredi a je proto potreba minimalizovat dobu vypadku.
Myslím, že co se týče češtiny problém na vyšší verzi než 4.1 určitě nebude.
4.1 tedy můžete přeskočit 🙂
> SET NAMES cp1250;
Bude takto premigrovana mysql 5.0 databaze fungovat pro puvodni PHP skripty bez nutnosti jejich zmeny / upravy, co se kodovani tyce ? V soucasnosti vsichni pouzivaji CP1250 pro zapis do databazi, a v tomto smeru bych take nastavoval kodovani tabulek i collate sloupcu.
Při importu dumpu do verze 4.1 či vyšší SET NAMES cp1250 uvést musíte, aby databáze věděla, v jakém kódování data importujete. Interně budou v UFT-8.
Ve skriptech opět stačí uvést SET NAMES takové, v jakém kódování jsou psané stránky. Jsou-li v CP1250, tak SET NAMES cp1250, jsou-li v UTF-8, tak SET NAMES UTF-8. Do nedávna jsem tvořil web v CP1250, teď dělám v UTF-8 s pomocí PSPADu a bez problémů.
Pokud provozujete nějaký menší hosting, tak bych spíš doporučil běžet současně obě verze mysql (buď na jiném stroji nebo na jiném portu), tedy 4.0 a novější. A převod udělat postupně a v klidu. Když se něco změní, měníte jen nastavení připojení k DB ve skriptu. Co používám hostingy, tak si můžu zvolit verzi mysql. Spousta jich běží ještě na „staré mysql“.
dekuji za odpovedi.
mam svuj vlastni server, kde me do migrace nuti nejvice urcita nestabilita „stare“ mysql 4.0, a nedari se mi rozchodit na jednom stroji soubezne dve verze (ackoliv vim, ze to funguje a jde to, proste nejsem schopen to z nejakeho duvodu takto rozebehnout). Proto jsem zvolil cestu uplne migrace.
jeste jsem zjistoval tu upravu PHP skriptu, a da se nastavit my.cnf jedna z promennych, ktera rika, ze defaultne komunikace bezi treba v CP1250, a pokud pripojeni nepouzije prikaz SET NAMES a nezmeni si to, vse bezi v CP1250. Bohuzel mam na serveru mix databazi (nabizim prostor pro weby podobneho zamereni jako je muj primarni, takze tam mam vicero uzivatelu) v obou kodovanich, takze ten prechod bude jiste zajimavy 🙂
kazdopadne diky za clanek i doplneni, kdyztak sem dam vedet dalsi poznatky z prubehu migrace 🙂