alphagov/transition — pull request #316 by jamiecobbett (2014-06-11 16:43:07+00:00)
Title: Fix collation for some columns

These columns were created whilst their tables had the utf8_general_ci
collation, and had preserved this setting on the columns even after we
had manually altered the collation at the table level. The migration here
alters the text and varchar columns on those tables to be consistent with
the other InnoDB tables.

The history of how we got into this state is in this commit message:

https://github.gds/gds/puppet/commit/310eb6370c893a849a5a0944e4243a031ff93cba

That change was deployed a few weeks ago, and since then the
whitelisted_hosts table has been correctly created with utf8_unicode_ci,
so collation for new tables should be fine in future. The following tables
had been created with utf8_general_ci and were manually altered to utf8_unicode_ci:

- mappings_batch_entries
- mappings_batches
- organisations_sites
- sessions

Those tables now have the correct table-level collation, but text and
varchar columns in those tables still had the wrong collation at the
column level. A new varchar column created on one of those tables since
the table collation was fixed does have the correct collation (type on
mappings_batches).

Collation in itself doesn't make any difference for these four tables:
we don't sort by any of their text columns and even if we did, most of
them store ASCII-only text which would be sorted identically between the
two collations in question anyway. We only want this fixed so that we can
have a predictable and consistent db/structure.sql, and the update to that
file here should put us back into a world of happiness and consistency.