MySQL upgrade made unicode unreadable [Solved]

yaman666 · Last edited by yaman666 on Fri May 26, 2006 5:06 pm; edited 1 time in total

I recently did an emerge -u world and among other things that updated mysql to 5.0.21. Now all mysql data that was stored in utf8 looks like:

steveb · Advocate Joined: 18 Sep 2002 Posts: 4564

Did you dumped the data (before updating) with hexadecimal values for binary fields/data?
What collation did you used in your database/tables/fields before the 5.0.21 release?

cheers

SteveB

yaman666 · Posted: Wed May 24, 2006 9:44 pm Post subject:

Yes, I have backups. However, it seems that data is perfectly fine. It looks exactly like when you try to view unicode webpage in non-unicode encoding in a browser. In fact, I can decode it that way, by using a browser. So I am pretty sure the problem is not with corrupt data, but rather with it being output incorrectly. As if MySQL tries to output unicode in latin1. So I want to tell MySQL that the data is in fact unicode and to display it as unicode.

I am not quite sure what you mean by hexadecimal values and collation. If you mean column type - then it's varchar and text.

steveb · Advocate Joined: 18 Sep 2002 Posts: 4564

Okay... how do you know that the data is wrong? Where do you try to display that UTF8 data? In a webpage? In the console? Where?

yaman666 · Posted: Wed May 24, 2006 9:58 pm Post subject:

Both. In both webpage and console the data is displayed wrong. (You can see it at http://anna.ulyanov.net)

steveb · Advocate Joined: 18 Sep 2002 Posts: 4564

Do you have something like PHPMyAdmin? If so, then could you quickly look there if the data is as well in the same way as on your webpage?

steveb · Advocate Joined: 18 Sep 2002 Posts: 4564

btw: Phuuu... I can read cyrillic. It's long time that I don't write any more in that character set. But I can still read it. Funny... I guess, that some thing you learned in school when you was a kid, will never be forgotten.

yaman666 · Posted: Wed May 24, 2006 10:04 pm Post subject:

No, I prefer command line.

But like I said - everything was fine before upgrade, and now utf8 is not being properly identified. And I have a feeling it has something to do with the dissapearance of utf8 flag from portage and missing utf8.xml from /usr/share/mysql/charsets

yaman666 · Posted: Wed May 24, 2006 10:07 pm Post subject:

steveb · Advocate Joined: 18 Sep 2002 Posts: 4564

steveb · Advocate Joined: 18 Sep 2002 Posts: 4564

BTW: The character sets are now in a table inside MySQL. Look at the database "information_schema". There you will find utf8.

steveb · Advocate Joined: 18 Sep 2002 Posts: 4564

I just now posted a message on the Gentoo Serbia board with cyrillic characters.

When I try to display that message in the console, then I get this:

yaman666 · Posted: Wed May 24, 2006 11:57 pm Post subject:

Thanks a lot for the replies!

The thing is - blog displayed just fine before the upgrade, and now it won't display properly, which led me to believe this had something to do with mysql. I will play with it and try to manually extract data and see what happens. But it's good to know that my configuration "should" be working. I may try to restore backup as well just in case.

llongi · Posted: Thu May 25, 2006 11:34 am Post subject:

The fix should be easy: basically now MySQL defaults to UTF-8 (which is a good thing), at compile time and in my.cnf, that's why there is no more "utf8" USE flag (but there is a "latin1" USE flag if you want to switch back to the old charset, but that's not your case).
Now, PHP before used latin1: always. Now, with switching MySQL over to UTF-8 at compile time, it started using UTF-8 as charset, and here we have the problem:
before: MySQL --> UTF-8, PHP --> latin1
now: MySQL --> UTF-8, PHP --> UTF-8
and this, of course, breaks, at least on the webpages.
To fix this we added charset setting support to PHP's MySQL extensions (only distro that does that), so PHP now reads /etc/mysql/my.cnf too and sets the charset accordingly. So, to revert your situation to what it was before (with PHP being latin1), and fix the problem, just do the following:
a) emerge the newest dev-lang/php versions, 4.4.2-r2 for PHP4.X and 5.1.4 for PHP5.X
b) add the following to /etc/mysql/my.cnf:

Bojan · Posted: Thu May 25, 2006 2:26 pm Post subject:

I have a very similar problem and I'd like to postpone transition to UTF-8 to some later time.

If I understand this correctly, emerging mysql with latin1 use flag set would give exactly the same result and there would be no need to edit my.cnf ???

Would that be OK ???

yaman666 · Posted: Thu May 25, 2006 9:09 pm Post subject:

wandra · n00b Joined: 24 May 2005 Posts: 25

for almost one year this has worked on my system,
but now, when i recompiled php i have the same problem as one year ago --
destroyed unicode-charaters in php-web- applications ...

Joe User · n00b Joined: 21 Mar 2004 Posts: 10 Location: Germany

Hi,

adding the following undocumented options to the php.ini (PHP-5.2.x) fixed the problem for our community:

llongi · Posted: Wed Mar 07, 2007 5:31 pm Post subject:

Indeed with the newest PHP releases the way this Gentoo-only addon-feature was handled changed, we now use the php configuration system to set the charsets for the MySQL<->PHP connection. That means any php.ini, or php_admin_value in Apache config, or even ini_set() in the script itself works now.
The three new options are:

Joe User · n00b Joined: 21 Mar 2004 Posts: 10 Location: Germany

Hi,

llongi · Posted: Thu Mar 08, 2007 10:35 pm Post subject:

vad3r · Posted: Fri Mar 09, 2007 1:30 pm Post subject:

Hi,

thanks for this Thread because i have the same UTF-8 encoding problem with auf PHP based website. I changed the configs according to this thread but it doesn't work for me

Do i have to compile MySQL or php in a specific way??

fisk · n00b Joined: 29 Mar 2004 Posts: 63

My entire database has become charset gimped, basically everything written in my own language comes out distorted like this:

stÃ¥r framfÃ¶r spegeln pÃ¥ morgonen fÃ¶r att fÃ¥fÃ¤ngt rÃ¤tta

Where it's supposed to be

"står framför spegeln på morgonen för att fåfängt rätta"

I suppose it was stupid of me not to back the database up before I ran a system update, but I didn't know it would botch my database this way.

I tried to edit the php.ini, and the my.cnf as suggested above, but nothing happened, I tried exporting the databases affected by it, and when I open them in an editor, they now have these weird characters instead of the ones that used to be there.

Is this reversible? Or do I now have to scrap everything? =/
_________________
-doh

llongi · Posted: Fri Mar 09, 2007 4:21 pm Post subject:

@vad3r: No, nothing needs to be compiled in some special way for this to work, and maybe it's better to talk here than in the bug.

@fisk: Huh? The PHP update only changed the way PHP<->MySQL connections are set charset-wise, it's impossible that it actually changed the data IN your database, unless you also made some major upgrade of MySQL or something like that...
The PHP update should really be no problem, it actually should add the right directives itself to php.ini, and it's very simple how to fix it yourself if it does not:
Before, PHP looked at my.cnf, first the [php-$SAPI] section in there, then the [client] section, if you had set any particular charset stuff in one of those two, then it would use it.
Now, PHP just uses the php.ini setting, so you just have to set the same charset you were using in my.cnf (if you were using any!), and then restart Apache, and it will work exactly like before.
If it doesn't, I'm pretty sure it's not the fault of the new PHP ebuilds.
_________________
Best regards, Luca.

Joe User · n00b Joined: 21 Mar 2004 Posts: 10 Location: Germany

Hi CHTEKK,

if i understand you correctly, i can drop the [php-$SAPI] sections from my.cnf without any sideeffects, because `emerge php` does not look at them anymore? And if so, will the now also unnecessary corresponding mysql-patches be removed too?

BTW: Thanks for unmasking PHP-5.2.x and Suhosin! No overlay/ebuild-hacking anymore...
_________________
PayPal.Me/JoeUser ● FreeBSD Remote Installation