Fixing MySQL collations/character sets

One of the small but slightly annoying errors that the TODB crops up occasionally is an error something like:

'Query failed: Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (latin1_general_cs,IMPLICIT) for operation '=''

Why does it do this?

Ok, apparently two columns being compared in a join (or whatever) are different collations. So, fair enough - you can't compare apples and oranges or (in this case) APPLES. Still, that is fair enough. But why are the chracter sets different? I do not think that the collation was specified (for any of the columns), so it seems strange that one of the columns should magically change by itself.

My private theory (unsubstantiated) is that if you enter a dodgy character (e.g. Excel likes to replace single quote characters with something special of its own that MySQL has trouble with), MySQL changes the collation to accommodate the funny character. That is all very well, but a complete pain if it breaks your queries.

Still, the short-term solution is:

1. List the collations for the all columns in the offending table:

select table_name, column_name, collation_name from information_schema.columns where table_schema = 'jbs_new' and table_name like 'units%' and collation_name is not null order by collation_name;

+---------------+-------------+-------------------+

| table_name | column_name | collation_name |

+---------------+-------------+-------------------+

| units_2007_08 | sgrps | latin1_general_cs |

| units_2008_09 | sgrps | latin1_general_cs |

| units_2009_10 | sgrps | latin1_general_cs |

| units_2006_07 | uname | latin1_swedish_ci |

| units_2006_07 | course | latin1_swedish_ci |

| units_2006_07 | sgrps | latin1_swedish_ci |

| units_2006_07 | name | latin1_swedish_ci |

| units_2006_07 | assessmode | latin1_swedish_ci |

| units_2006_07 | note | latin1_swedish_ci |

| units_2007_08 | uname | latin1_swedish_ci |

| units_2007_08 | course | latin1_swedish_ci |

| units_2007_08 | name | latin1_swedish_ci |

| units_2007_08 | assessmode | latin1_swedish_ci |

| units_2007_08 | note | latin1_swedish_ci |

| units_2008_09 | uname | latin1_swedish_ci |

| units_2008_09 | course | latin1_swedish_ci |

| units_2008_09 | name | latin1_swedish_ci |

| units_2008_09 | assessmode | latin1_swedish_ci |

| units_2008_09 | note | latin1_swedish_ci |

| units_2009_10 | uname | latin1_swedish_ci |

| units_2009_10 | course | latin1_swedish_ci |

| units_2009_10 | name | latin1_swedish_ci |

| units_2009_10 | assessmode | latin1_swedish_ci |

| units_2009_10 | note | latin1_swedish_ci |

+---------------+-------------+-------------------+

In the example above, some of the columns are latin1_general and others are latin1_swedish

2. Then, change the offending ones:

alter table units_2006_07 modify sgrps varchar(32) CHARACTER SET latin1 COLLATE latin1_swedish_ci;

...etc for the each of the tables and columns.

This sorts out the error... but for how long??