Smeće u bazi, po ko zna koji put…
Standardna početnička greška (ne setovanje enkodinga konekcije) vrlo često rezultuje velikim problemom zvanim smeće u bazi. Primer problema je standardna PHP greška
1. aplikacija misli da radi sa utf8 (forsiran je utf8 enkoding u hederu html-a, web enkodira karatktere kao utf8 i očekuje ih kao utf8)
2. baza čuva podatke kao utf8
3. NIKO NIJE SETOVAO ENKODING KONEKCIJE što u php slučaju znači da je enkoding defaultni – LATIN1.
Šta se desi kada u ovom slučaju pošaljemo utf8 karakter Č u tabelu. Kako je php glup on to pošalje kako ga ima u memoriji, dakle on mysql drajveru pošalje ta dva bajta od kojih je karakter Č sastavljen. Pošto je enkoding konekcije LATIN1 koji ima jednobajtne karaktere, mysql ta dva bajta vidi kao DVA latin1 karaktera, i svaki od njih upiše kao jedan trobajtni utf8 karakter. Sada u polju na mysql-u imam 6 bajtova tj dva karaktera umesto jedan. Kada sledeci put pročitate taj podatak, mysql će prekodirati ta dva utf8 karaktera u 2 jednobajtna latin1 karaktera i vratiti ih php-u koji će ta dva karaktera poslati web browseru koji će pošto očekuje utf8 to pogrešno interpretirati kao utf8 karakter i prikazati slovo Č.
Dakle poslali smo Č, pročitali smo Č – gde je problem? Problem je u tome što u bazi ne piše Č, što sortiranje tog stringa neće biti kao da se na tom mestu nalazi Č, što poredjenje neće raditi, što računanje dužine neće raditi, što zauzima duplo više mesta u bazi…. i najzad, što ako probate da pročitate taj podatak VALIDNO – videćete da je u bazi SMEĆE.
Kako rešiti ovaj problem kada ga zateknemo?
Dakle ako imamo tabelu:
create table `smece` ( `id` int auto_increment primary key not null, `djubre` varchar(100) character set utf8 ) engine=myisam;
i imamo aplikaciju sa početničkom greškom:
< !DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
<body>
<form action="djubre.php" method="post">
<input name="q" type="text" />
<input type="submit" />
</form>
< ?php
mysql_connect(':/tmp/mysql.sock', 'root', '') or die(mysql_error());
mysql_select_db('test') or die (mysql_error());
$res = mysql_query('select djubre from smece');
while ($row=mysql_fetch_row($res)) echo $row[0].'<br/>';
@mysql_free_result($res);
if ( isset( $_POST['q'] ) ) {
$djubre = $_POST['q'];
mysql_query("insert into smece (djubre) values ('$djubre')");
mysql_close();
}
?>
</body>
</html>
na web-u se vidi:
dšdšdšd dasdad dšdšdšd dššdšdšdš ccccccccccc šššššššššš ccccccccccccc
ali ako pogledamo u bazi vidimo smeće:
mysql> select * from smece; +----+-------------------------------------------------------------------+ | id | djubre | +----+-------------------------------------------------------------------+ | 1 | ÄÅ¡ÄÅ¡ÄÅ¡Ä | | 2 | dasdad | | 3 | ÄÅ¡ÄÅ¡ÄÅ¡Ä | | 4 | ÄÅ¡Å¡ÄÅ¡ÄÅ¡ÄÅ¡ | | 5 | ÄÄÄÄÄÄÄÄÄÄÄ | | 6 | Å¡Å¡Å¡Å¡Å¡Å¡Å¡Å¡Å¡Å¡ | | 7 | ÄÄÄÄÄÄÄÄÄÄÄÄÄ | +----+-------------------------------------------------------------------+ 7 rows in set (0.00 sec) mysql> select hex(djubre) from smece; +------------------------------------------------------------------------------------------------------------------------------------+ | hex(djubre) | +------------------------------------------------------------------------------------------------------------------------------------+ | C384E28098C385C2A1C384E28098C385C2A1C384E28098C385C2A1C384E28098 | | 646173646164 | | C384E28098C385C2A1C384E28098C385C2A1C384E28098C385C2A1C384E28098 | | C384E28098C385C2A1C385C2A1C384E28098C385C2A1C384E28098C385C2A1C384E28098C385C2A1 | | C384C28DC384C28DC384C28DC384C28DC384C28DC384C28DC384C28DC384C28DC384C28DC384C28DC384C28D | | C385C2A1C385C2A1C385C2A1C385C2A1C385C2A1C385C2A1C385C2A1C385C2A1C385C2A1C385C2A1 | | C384E280A1C384E280A1C384E280A1C384E280A1C384E280A1C384E280A1C384E280A1C384E280A1C384E280A1C384E280A1C384E280A1C384E280A1C384E280A1 | +------------------------------------------------------------------------------------------------------------------------------------+ 7 rows in set (0.00 sec)
Ovo smeće se na webu i dalje vidi kako treba !!!
sada da bi ovo smeće prebacili u ispravan zapis:
mysql> alter table smece change djubre djubre varchar(100) character set latin1; Query OK, 7 rows affected (0.00 sec) Records: 7 Duplicates: 0 Warnings: 0 mysql> alter table smece change djubre djubre blob; Query OK, 7 rows affected (0.00 sec) Records: 7 Duplicates: 0 Warnings: 0 mysql> alter table smece change djubre djubre varchar(100) character set utf8; Query OK, 7 rows affected (0.01 sec) Records: 7 Duplicates: 0 Warnings: 0 mysql> select * from smece; +----+----------------------------+ | id | djubre | +----+----------------------------+ | 1 | dšdšdšd | | 2 | dasdad | | 3 | dšdšdšd | | 4 | dššdšdšdš | | 5 | ccccccccccc | | 6 | šššššššššš | | 7 | ccccccccccccc | +----+----------------------------+ 7 rows in set (0.00 sec) mysql> select hex(djubre) from smece; +------------------------------------------------------+ | hex(djubre) | +------------------------------------------------------+ | C491C5A1C491C5A1C491C5A1C491 | | 646173646164 | | C491C5A1C491C5A1C491C5A1C491 | | C491C5A1C5A1C491C5A1C491C5A1C491C5A1 | | C48DC48DC48DC48DC48DC48DC48DC48DC48DC48DC48D | | C5A1C5A1C5A1C5A1C5A1C5A1C5A1C5A1C5A1C5A1 | | C487C487C487C487C487C487C487C487C487C487C487C487C487 | +------------------------------------------------------+ 7 rows in set (0.00 sec)
i voila, u bazi vise nije smeće nego validan sadržaj. Ako se sad vratimo na aplikaciju, na webu se sad vide neke nove kuke i kvake:
?�?�?�? dasdad ?�?�?�? ?��?�?�?� ??????????? ���������� ?????????????
sve što treba da uradimo je da setujemo pravilno enkoding u aplikaciji
< !DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
<body>
<form action="djubre.php" method="post">
<input name="q" type="text" />
<input type="submit" />
</form>
< ?php
mysql_connect(':/tmp/mysql.sock', 'root', '') or die(mysql_error());
mysql_select_db('test') or die (mysql_error());
$res = mysql_query(“set names ‘utf8′”);
$res = mysql_query('select djubre from smece');
while ($row=mysql_fetch_row($res)) echo $row[0].'<br/>';
@mysql_free_result($res);
if ( isset( $_POST['q'] ) ) {
$djubre = $_POST['q'];
mysql_query("insert into smece (djubre) values ('$djubre')");
mysql_close();
}
?>
</body>
</html>
i problem rešen.
27 Responses to “Smeće u bazi, po ko zna koji put…”
zoran - July 7, 2010
E ovo ti je do jaja post da znas to me muci odavno… nego hajmo opet back to bazis posto smo mi poceli da pravimo svoje satove davnig 1999, 2002, 2004… pa upgrade radili a utf8 se ‘sire’ pojavio kasnije imamo starih tabela koje su latin1 i sada bi sve da prepakujemo u utf8… kako da uradimo to a da nemamo smece u bazi?
Posebno me zanima to zbog ruskog i slicnih jezika jer imam upravo to ako gledam odakle god smetlje je u bazi samo sto na sajtu ‘izgleda OK’
Bogdan Kecman - July 7, 2010
isto je – nema razlike, ako si imao tabelu na 3.x mysql-u koji nije imao karakter set podrsku i provugao je sve do 5.5 i sada imas unutra smece …
cilj je da u polju imas pravilno rasporedjene bajtove – jedan po jedan, za validnu UTF8 nisku.
U slucaju da si kroz latin1 konekciju tukao utf8 karaktere u utf8 polje problem je sto sada u tom polju nemas pravilnu nisku bajtova “fizicki u polju” koja cini utf8 vec imas za svaki utf8 bajt 3 neka bajta koja ga pretstavljaju. Zato konvertujes to polje iz utf8 u latin1
ovaj korak:
alter table smece change djubre djubre varchar(100) character set latin1;
i sada posle tog koraka imas fizicki u samom polju pravilnu nisku bajtova koja je validan utf8 string.
Ako si ti imao 3.x pa 4.x pa 5.x i sada ti je to LATIN1 tabela (dakle nisi je prebacivao u utf8, nego si je ostavio default) ti se vec nalazis na ovoj poziciji kada imas validnu nisku bajtova u polju.
sada uradis:
alter table smece change djubre djubre blob;
ovde mysql bukvalno samo iskopira tu nisku bajtova takve kakvi su u blob i ne pokusava da ih interpretira uopste.
i na kraju:
alter table smece change djubre djubre varchar(100) character set utf8;
sada kazes mysql-u da procita tu nisku bajtova iz blob-a i interpretira ih kao utf8 nisku.
Dakle cela poenta je da dodjes u situaciju da je u polju niska bajtova validan utf8 izraz, onda bacis to u binarni pa u utf8 i sredio si podatke. Taj prvi korak je najvazniji posto ako si recimo ti tukao sa LATIN2 enkodingom data u bazu onda prvi korak nije
alter table smece change djubre djubre varchar(100) character set latin1;
vec prvi korak treba da bude:
alter table smece change djubre djubre varchar(100) character set latin2;
dakle cela fora je da se napravi niska bajtova u polju da bude ista kao ono sto si ti utukao kroz konekciju.
jasno?
Никола - July 7, 2010
Више пута сам имао прилике да се са овим проблемом сретнем, и ово је изузетно ризично радити. Наиме, приликом оваквих пребацивања, у зависности од разноразних комбинација кодних распореда колоне, табеле, базе, везе, распореда планета и сличних фактора, редовно се дешава да се нешто забрља. Проблем праве бајтови у УТФ-8 тексту који су уствари контролни карактери у ИСО8859-1 а што резултује у разним занимљивим и мање занимљивим појавама…
Углавном, пре сваке операције ове врсте, бекап.
Bogdan Kecman - July 7, 2010
Bekap je majka, naravno… no sto se “brljanja” tice, brljanje se desava uglavnom samo ako se ne pogodi enkoding, ili sto je vrlo cest slucaj na zalost, kada je pola tabele jedan enkoding a druga polovina drugi (npr pola tabele je latin1 – default, a onda se neko napravio pametan i zbog nase latinice dodao latin2) tu onda nastaje haos koji ne moze lako da se pocisti…
Ono sto je tu vazno za primetiti je da ti problemi koji mogu da se jave u 99.9% slucajeva postoje vec i u trenutnom sistemu, dakle i u trenutnom sistemu se vide kao kuke i kvake.
Sasa Kostic - July 7, 2010
Hvala za info, Kecmane :)
Bogdan Kecman - July 7, 2010
Nema na cemu, izgleda da je ova tema trebala ranije, ja sve mislim da ljudi znaju posto je standardan zez al .. ima vise hitova na ovu stranu u zadnjih 24h nego na ceo blog zadnjih 7 dana :D
Vanja Tesin - July 8, 2010
Ono presipanje iz latin1 u utf8 je zaista zgodno :)
Hvala za tip!
Damir - July 10, 2010
Prije svega, pun pogodak za clanak, hvala ti.
Kada pravim pretragu u mysql-u (…LIKE ‘%nešto%’…), a dobijem rezultat u kojem se nalazi “nesto” i “nešto”, da li je to znak “smeca” u bazi.
Bogdan Kecman - July 12, 2010
ne, naprotiv, to znaci da je mysql prepoznao š kao validan karakter. jednacenje izmedju š i s radi kolacija.. ide uskoro jedan clanak na tu temu, samo da uvatim 10min vremena
MySQL » Sortiranje po azbuci (azbuka i mysql) - July 23, 2010
[...] пакета са пуном подршком за наш, Српски, језик. Поред “смећа у бази”, врло чест проблем везан за карактер сетове у MySQL-у и [...]
Draško - September 1, 2010
Ovaj problem se pojavio u novijim verzijama e107 CMS-a, ali nije otklonjen više od godinu dana, iz meni nepoznatih razloga.
Pokušavao sam da se izborim sa ovim nekoliko dana, dok nisam došao do Vašeg članka koji tako jednostavno objašnjava problem, kao i rešenje. Obavestio sam razvojni tim e107 o propustu, a da li će, i kad će reagovati, ostaje da se vidi.
Hvala za trud!
Bogdan Kecman - September 1, 2010
Drasko, nemam pojma sta je e107 cms ali drago mi je da ce clanak pomoci (doduse vidim da je clanak dobio ukupno jedan glas i to negativan, al sta je tu je)
Боки - November 18, 2010
Богдане, да ли би могао да ми помогнеш око практично истог проблема? Наиме, када унесем текст ћирилицом у базу користећи PHPMyAdmin, текст је на страници приказан са упитницима, док ако преко сајта унесем податке у исту базу, користећи PHPMyAdmin могу да видим само неке чудне знакове типа аÑдаÑдаÑ. Текст који је тако унет када се прикаже на страници нормално изгледа. Подесио сам encoding и collation на utf_8_general_ci, storage engine је MYISAM.
Bogdan Kecman - November 18, 2010
da li iz php-a kada napravis konekciju radis “SET NAMES” ??
pogledaj boldovan deo poslednjeg primera
$res = mysql_query(“set names ‘utf8′”);
dakle moras da setujes konekciju na utf8 (sa set names) u php-u da bi ti to radilo kako treba
Боки - November 18, 2010
Пробао сам и то али нема промене. Ево и кода:
<?php
echo "”; echo “”; echo “”;
echo “”;
$result = mysql_query(“SET NAMES ‘utf-8′”);
$result = mysql_query(“SELECT * from boki”);
//While there are rows to display
while($row = mysql_fetch_array($result)){
//Display the results from the current row and a line break
echo $row['prezime'] . ” – ” . $row['ime'] . “”;
}
echo “”;
?>
Боки - November 18, 2010
Испробао сам код који си ти оставио. Он ради међутим када кликнем на дугме submit рачунар нешто ради неколико секунди и онда се појави прозор где пише Apache HTTP Server has encountered a problem and needs to close… Након тога када урадим Refresh подаци које сам проследио се појављују на екрану. Ако одем у PHPMyAdmin могу нормално да видим податке које сам унео.
Уколико те мрзи да ми објашњаваш постави неки линк где бих могао да погледам како да решим овај проблем.
Bogdan Kecman - November 18, 2010
bez tvog koda ti pricam napament, ali samo nadji gde radis mysql_connect(…) i odma posle toga uradi:
mysql_query(“SET NAMES ‘utf-8′”);
(navodnik, apostrof, apostrof, navodnik – iz nekog razloga wordpress is prebacuje u prednji/zadnji navodnik i neke cudne apostrofe – nemoj dakle copy/paste nego prekucaj)
javi dal radi
Боки - November 18, 2010
Нема промене, али како објашњаваш то да када сам испробао твој код прекине се веза са сервером а подаци се уписују у базу?
Боки - November 18, 2010
Отклонио сам овај проблем са падом сервера када сам обрисао команду:
mysql_close();
Сада мислим да неће бити проблема јер твој скрипт ради како треба а ја ћу се потрудити да прилагодим мој код.
Хвала.
Bogdan Kecman - November 18, 2010
hm … treba ti mysql_close() na kraju skripta .. e sad ako radis neke includeove moras da pazis da ne zatvoris konekciju pre nego su svi zavrsili sa njom ..
dodatno sa php-om postoji smor sa persistentnim konekcijama, za pocetak – savet je da ne koristis persistentne konekcije
Боки - November 20, 2010
Радим са “инклудовима” али због чега ми је потребан mysql_close() на крају скрипта и шта су то персистентне конекције?
Bogdan Kecman - November 20, 2010
mysql_close() je potreban da bi explicitno zatvorio konekciju, ako implicitno zatvaras konekcije (tako sto samo zavrsis php skript i ocekujes da se konekcija samozatvori) onda se na mysql-u gomilaju konekcije posto budu zatvorene tek kada istekne neki timeout … dakle trosis resurse na serveru
persistentne konekcije je kada koristis mysql_pconnect ili kada u php.ini stavis da ti tretira obicne konekcije kao persistent (da se mysql_connect tretira kao mysql_pconnect – ne secam se koji je parametar), u tom slucaju kada uradis mysql_close() ta konekcija se ne zatvara “stvarno” nego sledeci put kada uradis mysql_connect() dobijes neku od tih “zatvorenih” konekcija – ovo moze da dovede do mnogo problema ako ne znas “tacno” sta radis
Марко - February 26, 2012
Одличан текст. Много ми је помогло.
Такође бих препоручио следећи линк као обавезни штиво на тему енкодинга:
http://www.joelonsoftware.com/articles/Unicode.html
Bogdan Kecman - February 27, 2012
Ima mnogo tekstova, dobrih i losih, o UNICODE-u kao takvom, ovaj bas i nema nikakve veze sa MySQL-om (niti nekim drugim RDBMS-om)
jebac - April 11, 2012
mnogo mi pomoglo ovo upustvo, u suštini baza mi je bio vec u utf8_general_ci nego zaboravio sam kod queryja da ubacim mysql_query(“set names ‘utf8′”);. JOS JEDNOM HVALA!
Novak - October 20, 2012
Ne znam da li je ova tema jos aktivna, nadam se da jeste.
Radio sam kao sto je navedeno u primeru ali kad uradim select * from smece dobijam neke znakove pitanja umesto slova č,ć,ž,ž.
Dok vi dobijate:
mysql> select * from smece;
+—-+——————————————————————-+
| id | djubre |
+—-+——————————————————————-+
| 1 | ÄÅ¡ÄÅ¡ÄÅ¡Ä |
| 2 | dasdad |
| 3 | ÄÅ¡ÄÅ¡ÄÅ¡Ä |
| 4 | ÄÅ¡Å¡ÄÅ¡ÄÅ¡ÄÅ¡ |
| 5 | ÄÄÄÄÄÄÄÄÄÄÄ |
| 6 | Å¡Å¡Å¡Å¡Å¡Å¡Å¡Å¡Å¡Å¡ |
| 7 | ÄÄÄÄÄÄÄÄÄÄÄÄÄ |
Da li možda znate u čemu može biti problem?
Bogdan Kecman - November 1, 2012
zavisi od tvog klijenta da li ume i sta da prikaze kako treba. Ako tvoj klijent ne ume pravilno da se konektuje na bazu ili ako nije utf ili … zgodno je recimo da koristis phpmyadmin on se konektuje kako treba, ili recimo workbench
Leave a Reply