MySQL COLLATE inconsistent database errors

Question

I have two physically separate MySQL databases on which I have to run a single query.

The query has an SQL section that looks like this:

and foo_table.bar_column like concat('%', rules.pattern, '%') COLLATE utf8_general_ci 

It works fine in database A, but in database B I get this error:

 ERROR 1253 (42000): COLLATION 'utf8_general_ci' is not valid for CHARACTER SET 'latin1' 

If I remove the sort, it works fine in database B, but in database A I get this error:

 ERROR 1267 (HY000): Illegal mix of collations (utf8_general_ci,IMPLICIT) and (utf8_unicode_ci,IMPLICIT) for operation 'like' 

Is there a version of the query that will run in both databases?

Or is there a configuration that I can change in any database to make the query happy in both places?

Update:

Database A is version 5.1.38, Database B is version 5.1.34

Answer 1

Some promising information here .

UPDATE: the suggestions in this link solved my problem, although the syntax is a bit outdated and you no longer need to convert each column, just a table.

 ALTER TABLE the_latin_one CONVERT TO CHARACTER SET utf8;