Re: Encoding problems

Hi,

On 1/26/06, Tarjei Huse <[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

BR,

Jukka Zitting

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