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??

Merging branches with SVN

'Subversion' (SVN) is used to maintain the various branches of the TODB. As features and functionality are developed in one branch, is often necessary to propogate some of these changes to other branches.

Every time I do this, I seem to have to start from first principles, figuring out the correct svn commands and so on.

Here I have decided to blog on what actually worked!

Example: bringing the Divinity TODB branch largely up-to-date with Trunk (the main development = 'generic version' branch):

1. Create a new (temp) working directory for Divinity: svn checkout https://source.caret.cam.ac.uk/TODB/branches/divinity
2. Merge the differences between Divinity and Trunk into the local working directory: svn merge https://source.caret.cam.ac.uk/TODB/branches/divinity https://source.caret.cam.ac.uk/TODB/trunk
3. Run 'svn status' to list changes (i.e. files that need attention, the ones that have changed)
4. Run 'svn diff' for each of these files to get a list of the changes in that file (or using the TortoiseSVN Explorer contextual menu: SVN -> Diff)
5. When the files have been edited to satisfaction, indicate that the conflicts are resolved (if possible, otherwise continue to the next step) (using the TortoiseSVN right-click command).
6. When all file conflicts are resolved, commit (using the TortoiseSVN)
7. Go back to the original branch working directory, and update (using Tortoise or the command prompt). This will update the usual Divinity working directory.

This only applies to the PHP files, of course - the database schema and data need to be updated manually.