[MySQL] Character set problemen

[Robbeke]

Beste Tweakers,

Ik onderhoud al een lange tijd een aantal websites in gehost op Apache/Linux en gebaseerd op PHP / MySQL. Een kleine maand geleden zijn we van hosting provider gewisseld en sindsdien zijn de problemen ontstaan.

Er is duidelijk een character set probleem maar ik krijg het niet opgelost. Ik heb me al suf gezocht en onder andere en aantal GoT Topics doorgenomen en tips op deze websites doorgelezen : Linkje1 Linkje2

Wat zijn de symptomen:
- Tekens in phpMyAdmin worden correct getoond (PHPMyAdmin instelling English - UTF8)
- Tekens in phpMyAdmin worden in enkele tabellen foutief getoond
- Tekens in resultaten van queries via mysql command line worden correct getoond
- Wanneer ik een webpagina maak die bvb een gebruiker opzoekt in een tabel op basis van emailadres, dan werkt deze correct behalve wanneer er speciale karakters in één van de velden staan die moeten worden geretourneerd. Dan krijg ik in m'n apache error log het volgende:
- Ik zie veel de volgende fout in apache error log, maar weet niet of het hieraan gerelateerd is

code:

 [warn] (103)Software caused connection abort: mod_fcgid: ap_pass_brigade failed in handle_request function

- ik maak gebruik van PHPExcel om exports te maken naar Excel. Records met speciale tekens ontbreken en in de apache error log krijg ik de volgende regel:

code:

PHP Notice:  iconv_substr(): Detected an illegal character in input string in PHPExcel/Shared/String.php on line 561

Alle tabellen/velden hebben collation 'latin1_swedish_ci', zowel voor de database op de oude webhost als op de nieuwe webhost.
Zowel de nieuwe als de oude webhost hebben de volgende instellingen m.b.t. charsets:

code:

mysql> show variables LIKE '%char%';
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | latin1                     |
| character_set_connection | latin1                     |
| character_set_database   | latin1                     |
| character_set_filesystem | binary                     |
| character_set_results    | latin1                     |
| character_set_server     | latin1                     |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)

Wanneer ik dezelfde query uitvoer in een PHP script krijg ik dezelfde instellingen terug.

De nieuwe webhost: Server version: 5.0.51a-24+lenny4 (Debian)
De oude webhost: Server version: 5.0.45-Debian_1ubuntu3.4-log Debian etch distribution

Het overzetten van de database heb ik als volgt uitgevoerd:
- export met mysqldump -u xxxx -p per database
- dumpfile bewerkt en het commando USE Database; toegevoegd. Hier weet ik echter niet meer juist of ik deze aanpassing via nano op linux heb uitgevoerd of via een text editor onder windows.
- import met mysql -u xxxx -p < dumpfile.sql

Nog een duidelijk verschil is dat op de nieuwe webhost Apache geconfigureerd is via fastcgi, de oude host was een klassieke apache + PHP configuratie.

Ik vermoed zelf dat het een probleem is met FastCGI of een character set instelling die toch ergens mis is, maar ik kan het probleem niet vinden. Weet iemand raad?

[Voor 3% gewijzigd door Robbeke op 19-10-2010 12:38. Reden: aanpassen informatie na aantal tests]

[HuHu]

Staan je HTTP headers wel goed?

[Robbeke]

HuHu schreef op dinsdag 19 oktober 2010 @ 12:27:
Staan je HTTP headers wel goed?

Hoe bedoel je?

Ik heb het script in kwestie geprobeerd met

code:

<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />

en

code:

<meta http-equiv="Content-Type" content="text/html; charset=utf8" />

[RobIII]

Robbeke schreef op dinsdag 19 oktober 2010 @ 12:33:
[...]

Hoe bedoel je?

Wikipedia: Hypertext Transfer Protocol
Wikipedia: List of HTTP header fields
header()

[Voor 68% gewijzigd door RobIII op 19-10-2010 13:06]

[pedorus]

Robbeke schreef op dinsdag 19 oktober 2010 @ 12:00:
- ik maak gebruik van PHPExcel om exports te maken naar Excel. Records met speciale tekens ontbreken en in de apache error log krijg ik de volgende regel:

code:

PHP Notice:  iconv_substr(): Detected an illegal character in input string in PHPExcel/Shared/String.php on line 561

Gokje: PHPExcel verwacht utf-8 maar je voert het latin1?

Het overzetten van de database heb ik als volgt uitgevoerd:
- export met mysqldump -u xxxx -p per database
- dumpfile bewerkt en het commando USE Database; toegevoegd. Hier weet ik echter niet meer juist of ik deze aanpassing via nano op linux heb uitgevoerd of via een text editor onder windows.
- import met mysql -u xxxx -p < dumpfile.sql

Met welke versies van mysql? Dat maakt uit voor de charsets. Latere versies gebruiken utf-8.

Robbeke schreef op dinsdag 19 oktober 2010 @ 12:33:
Ik heb het script in kwestie geprobeerd met

code:

<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />

en

code:

<meta http-equiv="Content-Type" content="text/html; charset=utf8" />

Dit is puur gokwerk. Waar haal je utf-8 vandaan als je alles in latin1 doet?

Voor de rest is dit misschien een goed moment om alles om te zetten naar utf-8.

[Robbeke]

Leuk, zoveel en snelle reacties

pedorus schreef op dinsdag 19 oktober 2010 @ 13:27:
Gokje: PHPExcel verwacht utf-8 maar je voert het latin1?

Dat lijkt te kloppen, ondanks dat PHPExcel hiervoor zelf conversies voorziet dacht ik.
Ik gebruik nu utf8_encode() wanneer ik een celwaarde instel en dat lijkt beter te werken. De iconv fouten krijg ik nog steeds in het apache log maar de data is nu wel aanwezig in de excel sheet.

pedorus schreef op dinsdag 19 oktober 2010 @ 13:27:
Met welke versies van mysql? Dat maakt uit voor de charsets. Latere versies gebruiken utf-8.

De versies staan bij in openingspost. Is er een verschil tussen beiden?

pedorus schreef op dinsdag 19 oktober 2010 @ 13:27:
Dit is puur gokwerk. Waar haal je utf-8 vandaan als je alles in latin1 doet?
Voor de rest is dit misschien een goed moment om alles om te zetten naar utf-8.

Na al heel wat gelezen te hebben is het misschien geen slecht idee om alles in te stellen op UTF-8. Hier zal ik me eens op toeleggen.


Ondertussen heb ik gemerkt dat de foutieve weergave van speciale karakters enkel voorkomt voor velden met het type TEXT. Ik heb dit nu kunnen oplossen voor deze TEXT velden met ze in volgorde om te zetten naar BLOB, TEXT (utf8_general_ci) en TEXT (latin1). Als ik het goed begrijp wijst dit erop dat de export in UTF8 gebeurde en de import met latin1, waardoor er UTF-8 karakters in latin1 gecodeerde velden zaten?
Ik begrijp wel niet waarom dit dan niet hetzelfde is voor VARCHAR velden, deze zouden toch dezelfde symptomen moeten hebben? Toch lijken ze in orde want dezelfde procedure zorgt ervoor dat de speciale karakters en alles erna wegvallen bij omzetting.

[Voor 3% gewijzigd door Robbeke op 19-10-2010 14:22]

[NMe]

Robbeke schreef op dinsdag 19 oktober 2010 @ 12:33:
[...]

Hoe bedoel je?

Ik heb het script in kwestie geprobeerd met

code:

<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />

en

code:

<meta http-equiv="Content-Type" content="text/html; charset=utf8" />

http://nl.php.net/manual/en/function.header.php

Wat betreft je probleem: run eens deze query in je applicatie direct nadat je de verbinding met je database tot stand hebt gebracht:

SQL:

SET NAMES utf8

Spul dat al verkeerd in de database staat ga je daar niet mee fixen, maar probeer eens of alles dat je daarna invoert wel werkt.

Dit topic heeft trouwens niks met het ontwerpen van software te maken maar met het programmeren ervan. Zie ook Waar hoort mijn topic?

SEA>>PRG

[Voor 10% gewijzigd door NMe op 19-10-2010 14:40]

[pedorus]

Robbeke schreef op dinsdag 19 oktober 2010 @ 14:11:
De versies staan bij in openingspost. Is er een verschil tussen beiden?

Oh wacht, toevallig zijn ook Debian en PHP bij v5, dus ik had het even verkeerd gezien. Volgens mij maakt een mysqldump v5 zonder aparte opties altijd een 'set names utf8' statement aan in de dump en is deze utf8. Ik zou zelf even checken met een editor die de tekst als utf8 ziet of dit zo is en of de data daar goed leesbaar is (of dat er dus daarvoor al iets mis zat).

NMe schreef op dinsdag 19 oktober 2010 @ 14:39:
Wat betreft je probleem: run eens deze query in je applicatie direct nadat je de verbinding met je database tot stand hebt gebracht:

SQL:

SET NAMES utf8

Spul dat al verkeerd in de database staat ga je daar niet mee fixen, maar probeer eens of alles dat je daarna invoert wel werkt.

NB: dat is alleen om te testen. Ala:

quote: http://php.net/manual/en/mysqli.set-charset.php

This is the preferred way to change the charset. Using mysqli::query() to execute SET NAMES .. is not recommended.

set names kan zorgen voor verkeerde escaping, en in het ergste geval zelfs voor sql-injectie.

[Robbeke]

Ik ben van plan om via my.cnf de UTF8 instellingen uit te voeren. Heb het reeds getest en de output van de character variabels geeft daarna UTF8 over de hele lijn

Verder heb ik ondertussen uitgevonden hoe het zit met die speciale karakters. Het is slechts in 1 tabel in 1 database die ik moeten terugzetten heb uit backup. Kennelijk is daar iets mis gegaan maar dat is ondertussen dus alweer rechtgezet.

Nu alles omzetten naar UTF8 en het zit allemaal weer goed denk ik