[rt-users] MySQL utf8 problems

[David O'Callaghan]

Hi,

Some time ago I upgraded from 3.2.x to 3.8.2 and from MySQL 3.x to
5.0.45. This went smoothly but I somehow managed to do it *without*
using the infamous upgrade-mysql-schema.pl. I *did* run
`rt-setup-database --dba root --prompt-for-dba-password --action
upgrade` (also without problems).

So I tried upgrading from 3.8.2 to 3.8.3 and on startup got an error
message in the logs saying:

[Fri Jun 05 11:57:03 2009] [error] RT since version 3.8 has new schema
for MySQL
 versions after 4.1.0\nFollow instructions in the UPGRADING.mysql file.

After backing up the DB I attempted to run the schema upgrade. The
script outputted a large number of queries, mostly modifying the content
type of columns. The queries looked reasonable and I didn't get any
errors when applying them to the DB.

However, this gave content encoding problems with tickets. For example,
in tickets with an í character in the subject the subject is truncated
before the í and the content of the ticket is not visible (the ticket
appears to be empty). In other tickets non-ASCII characters appear
incorrectly.

I suspect that some of the content in my (pre-upgrade) DB may have been
utf8 stored in latin1 fields.

To restore a "working" system I cheated and modified my pre-schema
upgrade backup (which worked with 3.8.2) so that it would pass the DB
compatibility check and then restored. The encoding problems have
disappeared.


So I would like to know:
 * do the queries from upgrade-mysql-schema actually try to convert
latin1 encoded text to utf8?;
 * what happens to text that is already utf8?;
 * am I likely to have problems if I stay with the old schema; and
 * is there a way for me to upgrade without messing up the encoding?

Thanks,

David