Find *actual* character encoding of data in MySQL DB: UTF8 Latin1 illegal collation problem

Question

Spent hours on this now and could use some help! Our website queries our db - table columns are set to Latin1 collation, website has set names to UTF8 for queries.

The data is French and when we do a search for a string including accented characters we get the "*Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE) for operation 'like'*" error.

If you navigate to a page which loads the data completely it shows accented characters no problem, it is just when using the search function that it breaks.

We have tried a number of methods including ALTER TABLE t1 CHANGE c1 c1 BLOB; ALTER TABLE t1 CHANGE c1 c1 TEXT CHARACTER SET utf8;

but this damages the data: the text before the first accented character in each field is fine, but then the rest of the text in the field is completely dropped: 'Métal' becomes 'M'. I am using phpMyAdmin to try and fix this BTW. Not sure if that's a problem.

So is the data UTF8 encoded? If so, why does the ALTER TABLE not work, I've seen it mentioned as THE way to fix this problem on so many webpages! If the fact it doesn't work means that the data is not UTF8 encoded, how do I find out what it is?

Thanks in advance, this is driving me insane!

Answer 1

Having a different encoding between your website and your database is not a very good idea.... to avoid this problem, it's better to have everything in utf8. Though, it should be possible to convert the encoding of your tables playing with collations.