MySQL utf8 problems

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

Hi,

Scr�obh David O’Callaghan:

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).

In case this wasn’t clear, I didn’t deliberately avoid using
upgrade-mysql-schema.pl: I skipped it by accident and yet somehow the
system worked.

David

Hi,

Scríobh David O’Callaghan:

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).

In case this wasn’t clear, I didn’t deliberately avoid using
upgrade-mysql-schema.pl: I skipped it by accident and yet somehow the
system worked.

I believe that for 3.8.3, we’ve made RT a lot grumpier about starting if
you haven’t run it.