# MySQL 4.1.14 charset update disaster [no longer SOLVED]

## joehni

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.Last edited by joehni on Sat Apr 22, 2006 1:23 pm; edited 2 times in total

----------

## Quincy

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 Evil: 

----------

## joehni

 *Quincy wrote:*   

> 
> 
> 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.
> 
> 

 

Unfortunately this did not work for me either (I also already tried it). If I convert the SQL file and import with latin1 setting in my.cnf the import will again do an automatic conversion and the character is even more garbled. If I have utf8 as default, they are stored/retrieved in the converted form. The only combination where I can at least retrieve the values from the command line is latin1 in my.cnf with the "original" backup file, but this does not work for the PHP-based clients or knoda (they display converted and cannot save).

----------

## Quincy

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

----------

## joehni

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

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

See https://bugs.gentoo.org/show_bug.cgi?id=129761 and 

https://forums.gentoo.org/viewtopic-t-436439.html

Maybe the workaround of setting 

 *Quote:*   

> init-connect='SET NAMES latin1'

  in the [mysqld] section of you my.cnf works for you.

adapt latin1 to whatever charset you used to have.

----------

## magic919

 *joehni wrote:*   

> 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!!!

 

Wouldn't it be worth setting your own use flags for MySQL to protect against this happening?

----------

## joehni

 *magic919 wrote:*   

>  *joehni wrote:*   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!!! 
> 
> Wouldn't it be worth setting your own use flags for MySQL to protect against this happening?

 

When I say dropped, I mean it! If an ebuild no longer uses it, I can set it to what and where I want - it will not change the build.

----------

## magic919

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

----------

## joehni

 *s|mon wrote:*   

> Maybe the workaround of setting 
> 
>  *Quote:*   init-connect='SET NAMES latin1'  in the [mysqld] section of you my.cnf works for you.
> 
> adapt latin1 to whatever charset you used to have.

 

No, it does not help.

----------

## joehni

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

----------

## s|mon

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

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

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...  :Sad: 

----------

## llongi

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.

----------

## joehni

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

 *s|mon wrote:*   

> See https://bugs.gentoo.org/show_bug.cgi?id=129761 and 
> 
> https://forums.gentoo.org/viewtopic-t-436439.html
> 
> Maybe the workaround of setting 
> ...

 

This worked perfectly for me. I used a mysql version of 5.0.18 though. ^^

----------

