MySQL 4.1.14 charset update disaster [no longer SOLVED]

joehni · Apprentice Joined: 27 Jul 2003 Posts: 175

Hello folks,

I don't know how the update from MySQL 4.0.25 to 4.1.14 worked for you, but for me it is an absolute disaster. I have a lot of entries in my DB containing extended characters from latin1 and those cannot be handled with 4.1.14 at all.

By default the ebuild comes with utf8 in my.cnf. Importing the old data with this setting, all extended characters are replaced by '?'. Exchanging utf8 in my.cnf with latin1 works better. I can select rows from the command line and the content is printed all right. But using PHP-based web clients or Knoda the characters are displayed garbled again. Trying to update content that contains an extended character will turn it again into a simple questionmark in the DB.

I really tried a lot of possibilities (and yes, I did a revdep-rebuild) and restarted the server between settings changes and explicitly set the charset of the tables, but nothing. I never got a combination where I could save those characters. For me 4.1.14 us not usable at all.

If somebody had more success with characters like the German Umlauts or accented characters, let me know.

Quincy · Posted: Mon Oct 24, 2005 12:59 am Post subject:

I had strange errors with updating mysql, too. Same problem: latin1 charset doesn't import very well with UTF8 setting.

First error:
Every post (in a forum) containing an "Umlaut" stopped at this point with the rest of information getting lost.

Solution:
Do the export as mentioned in the update instructions and the run "iconv -f ISO8859-15 -t UTF-8" on the your SQL export file.

Second error:
Duplicate entry in a column with an index containing a text.

Solution:
Because of --hex-blob option in the export an unfortunatily set binary attribute for this column text was exported as hex and could not be converted by iconv. Import gives same result as above (shortened entrys) and two of them had teh same beginning causing the duplicate.

Did i already say?

I hate converting charsets... :twisted:

joehni · Apprentice Joined: 27 Jul 2003 Posts: 175

Quincy · Posted: Mon Oct 24, 2005 4:26 pm Post subject:

You have to store the data with latin1 charset? Because importing converted SQL File with Standard UTF-8 Setting is the right combination...

joehni · Apprentice Joined: 27 Jul 2003 Posts: 175

Found the culprit. I had enabled utf8 support in make.conf some time ago and that causes the libmysql to expect strings in utf8 and no longer in latin1. After recompiling mysql without the utf8 flag, I could import the iconv-converted BACKUP (with utf8 settings in my.cnf). The iconv conversion corrupted one DB though, since the client (Amarok) already saves utf8 strings on its own. This can be solved though by letting Amarok recreating its collection.

Now access from Knoda and from the PHP-clients work as expected. Single issue left though is the command line. Requesting data from it will show the result "iconverted"...

joehni · Apprentice Joined: 27 Jul 2003 Posts: 175

Whom do I have to thank? Ebuild for 4.1.14-r1 dropped the utf8 use flag available ein 4.1.14 and all the character conversion problems are back and by DB got corrupted!!!

s|mon · Posted: Sat Apr 22, 2006 11:25 am Post subject:

See https://bugs.gentoo.org/show_bug.cgi?id=129761 and
https://forums.gentoo.org/viewtopic-t-436439.html

Maybe the workaround of setting

magic919

joehni · Apprentice Joined: 27 Jul 2003 Posts: 175

magic919 · Posted: Sat Apr 22, 2006 11:50 am Post subject:

Yes, I see what you mean now. Dropped as in no longer exists for the purposes of the ebuild. Not very helpful then :-(

joehni · Apprentice Joined: 27 Jul 2003 Posts: 175

joehni · Apprentice Joined: 27 Jul 2003 Posts: 175

And this carries it simply to the extreme: https://bugs.gentoo.org/show_bug.cgi?id=130814

s|mon · Posted: Sat Apr 22, 2006 1:16 pm Post subject:

Well first you could unmask 4.1.14 and use this until a better solution is found. But you should lookup why it's masked (maybe important)

joehni · Apprentice Joined: 27 Jul 2003 Posts: 175

That's what I have done. The masking was because of security issues. But this DB and none of its clients is exposed to the internet, so I don't care. Nevertheless I ask my self what is worse - waiting for some attacker corrupting my data or have a version that does it by default.

itti · n00b Joined: 30 Jun 2004 Posts: 40

oh man iÂ´ve upgraded mysql on a production server, not knowing that it will make all my latin-1 databases useless... what can i do now? i need this server up and running as soon as possible. why is such a serious thing not mentioned in an emerge warning? i update my software with emerge on a regular basis...

llongi · Posted: Sun Apr 23, 2006 11:17 am Post subject:

The latest updates won't make your dbs useless or damage them in any way, the problem is PHP here, which switches to UTF8 regardless of what you may set in my.cnf. This was fixed with dev-lang/php-4.4.2-r1 and 5.1.2-r1, so just emerge that PHP version and change your /etc/mysql/my.cnf so that the [client] section includes the line "default-character-set=latin1". That will revert the behaviour of PHP to what it was before: use latin1 as default charset.
_________________
Best regards, Luca.

joehni · Apprentice Joined: 27 Jul 2003 Posts: 175

My problem is not PHP, but Knoda. It simply does not matter what I put into my.cnf, the DB is wrong starting with 4.1.14-r1.

uppe · n00b Joined: 04 Jul 2004 Posts: 18 Location: Sweden