LITTLEBLACKDOG.COM

I recently upgraded MySQL from 4 to 5.0.51a. All is well except for one of my user's accounts. The DB works fine, but whenever I try to back it up using Navicat, I get this error:

COLLATION 'utf8_bin' is not valid for CHARACTER SET 'latin1'

Initially I thought it might be the DB, but even if I delete it and create a blank one, I still get the error.

A closer look revealed that it was the "information_schema" DB that automatically gets created that is causing the problems. However, I can't seem to be able to change, delete or rebuild this DB.

I've tried searching Google with no results.

Any help?

Posted: Fri Mar 07, 2008 11:12 am Post subject:

The information_schema contains a mapping of collations to applicable character sets. You need to change the character set or the collation on whatever entity it is choking on because you can't use utf8 collations on latin1 character sets and vice versa. Consult the MySQL manual on how to do this because I can't remember the alter syntax off the top of my head.

Posted: Fri Mar 07, 2008 11:16 am Post subject:

CMTG wrote:
You need to change the character set or the collation on whatever entity it is choking on because you can't use utf8 collations on latin1 character sets and vice versa. Consult the MySQL manual on how to do this because I can't remember the alter syntax off the top of my head.

I've tried that. I have even deleted all databases and created a brand new, empty database. Still get the same error.

Posted: Fri Mar 07, 2008 12:19 pm Post subject:

Have you tried setting the character-set-server and collation-server parameters in the my.cnf file?

Posted: Fri Mar 07, 2008 1:39 pm Post subject:

Haven't tried that. I'm pretty sure I don't have one.

Frankly I'm afraid to create a new config file as all 50 of my other accounts work just fine, except this one.

Posted: Fri Mar 07, 2008 1:53 pm Post subject:

That's odd, are you sure you don't have one? It might not be called exactly my.cnf, but in all (both happy8

) my MySQL installations there is a config file that tells the service where its unix socket is and what user to run as.

Posted: Fri Mar 07, 2008 2:05 pm Post subject:

I'm pretty sure it's an optional file (you only use it if you want to optimize your install for specific server loads)...but I may be wrong on that one.