Re: Encoding problems

Jukka Zitting wrote:
> Hi,
> 
> On 1/26/06, Tarjei Huse <tarjei-y/[email protected]> wrote:
>> Hi, I get this error when trying out the latest and greatest midgard on
>> lampp:
>> midgard-core (pid:32722):(WARNING):  query failed: Illegal mix of
>> collations (latin1_swedish_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE)
>> for operation '='
> 
> It's most likely that your database is using latin1 encoding and the
> associated collation order. You can check this for example by running
> "SHOW CREATE DATABASE midgard". The end of the output will contain the
> default character encoding of the database. Likewise you can check the
> encoding of individual tables with "SHOW CREATE TABLE article". Note
> that even colums can have their own character encodings!
> 
> A usual case I've seen many times over is a ISO-8859-1 database that
> Midgard treats as a UTF-8 database. While this has worked fairly well
> so far, it is a disaster just waiting to be discovered by a correctly
> behaving Midgard version or by direct database integration.
> 
> I've found that the safest way to fix such a database is to export the
> database, fix the character encoding declarations, and recreate the
> entire database. Something like this:
> 
>     $ mysqldump midgard > midgard.sql              # Export the database
>     $ file midgard.sql                             # Check character encoding
>     midgard.sql: UTF-8 Unicode text
>     $ perl -i.bak -pe s/latin1/utf8/g midgard.sql  # Force correct encoding
>     $ diff midgard.sql midgard.sql.bak             # Just for safety...
>     [...]
>     $ mysql -e 'DROP DATABASE midgard'             # Drop the old database
>     $ mysql -e 'CREATE DATABASE midgard CHARACTER SET utf8' # Create a new one
>     $ mysql midgard < midgard.sql                  # Import the database
Hmm, would it be possible to make a simple commandline tool to run this 
process, checking the current charsets of midgard and mysql and making 
sure that they are correctly set and advising the user on what to do? I 
bet we'll see a lot of issues like this one in the coming year.

PS: The database was med using datagard, so I guess we need to look into 
dg as well.

Tarjei



> BR,
> 
> Jukka Zitting
> 
> --
> Yukatan - http://yukatan.fi/ - [email protected]
> Software craftmanship, JCR consulting, and Java development