508181 - UTF-8 table conversion will fail if there are FKs on the column or on related columns

Status: RESOLVED FIXED

(Bugzilla :: Installation & Upgrading, defect)

Description

[Max Kanat-Alexander]

If there is an foreign key on a string column in MySQL, you basically can't change its character set unless you drop the FK and re-create it when you're all done. I guess that string columns with FKs can't have different character sets on the two tables, which makes sense.

(We get into this situation if somebody turns on the utf8 parameter after they complete the upgrade, and then run checksetup.pl again.)

Comment 1

[Max Kanat-Alexander]

Attachment: v1

This drops all relevant fks on a column before converting it to UTF-8.

Comment 2

[Max Kanat-Alexander]

Here's how you can reproduce the issue:

1) Create a DB with UTF-8 off.
2) Add a multi-select field, give it some legal values, and set them on at least one bug.
3) Turn UTF-8 on and try to run checksetup.pl.

Comment 3

[Max Kanat-Alexander]

Attachment: v2

Previous patch was buggy--this one is correct.

Comment 4

[Frédéric Buclin]

I couldn't reproduce the issue. Here is what I did:

1) install 2.22.7
2) turn off "utf8"
3.1) upgrade to 3.4.1 (including running checksetup.pl)
3.2) make sure "utf8" is still "off" (it is)
4) create a multi-select field, add some values to it, and add them to a bug
5.1) run recode.pl
5.2) turn on "utf8"
5.3) run checksetup.pl

I got no error after step 5.3 (and my strings are all messed up, but that's another story).

Comment 5

[Max Kanat-Alexander]

When you created your database for 2.22.7, are you sure that it had a latin1 character set?

Comment 6

[Frédéric Buclin]

Comment on attachment 392423 [details] [diff] [review]
v2

Tested on 3.4.1. Works fine, and looks good (as far as I understand it). r=LpSolit

Comment 7

[Max Kanat-Alexander]

tip:

Checking in Bugzilla/DB.pm;
/cvsroot/mozilla/webtools/bugzilla/Bugzilla/DB.pm,v  <--  DB.pm
new revision: 1.126; previous revision: 1.125
done
Checking in Bugzilla/DB/Mysql.pm;
/cvsroot/mozilla/webtools/bugzilla/Bugzilla/DB/Mysql.pm,v  <--  Mysql.pm
new revision: 1.74; previous revision: 1.73
done

3.4:

Checking in Bugzilla/DB.pm;
/cvsroot/mozilla/webtools/bugzilla/Bugzilla/DB.pm,v  <--  DB.pm
new revision: 1.124.2.2; previous revision: 1.124.2.1
done
Checking in Bugzilla/DB/Mysql.pm;
/cvsroot/mozilla/webtools/bugzilla/Bugzilla/DB/Mysql.pm,v  <--  Mysql.pm
new revision: 1.72.2.1; previous revision: 1.72
done

Comment 10

[Frédéric Buclin]

These dupes make me think we shouldn't wait too long before releasing 3.4.2. Max, is this bug a blocker for someone upgrading from < 3.2 to 3.4.1?

Comment 11

[Charles Gillet]

IMHO, this is a blocker if you're actively switching over to UTF-8 as part of the upgrade.  Max's patch worked nicely for me.

My experience was that checksetup.pl was very informative about running recode.pl, etc., but not about going into data/params and turning UTF-8 on (I know, tell me to RTFM better).  I just happened to notice that UTF-8 was not turned on in params, so I turned it on, ran checksetup.pl again, then ran into this bug.

Comment 12

[Max Kanat-Alexander]

(In reply to comment #10)
> These dupes make me think we shouldn't wait too long before releasing 3.4.2.
> Max, is this bug a blocker for someone upgrading from < 3.2 to 3.4.1?

  Only if they had custom multi-select fields.

(In reply to comment #11)
> My experience was that checksetup.pl was very informative about running
> recode.pl, etc., but not about going into data/params and turning UTF-8 on (I
> know, tell me to RTFM better).

  FWIW, checksetup.pl would never have told you to run recode if the utf8 parameter was off.

Comment 13

[Charles Gillet]

I did not have custom multi-select fields, in fact, my instance has no custom fields at all.  Our instance has been in production since version 2.18 if that's important.  The database conversion to UTF-8 failed on the "settings" table, which I believe is part of the stock schema.

UTF-8 was most certainly OFF when I first ran the upgrade from 3.0.6 to 3.4.1.  Checksetup DID tell me to run recode.pl, probably because that's the storage format for new installations?

Perhaps my bug should not have been marked as duplicate?  But the fix worked the same, so I'm happy.

Comment 14

[Max Kanat-Alexander]

(In reply to comment #13)
> UTF-8 was most certainly OFF when I first ran the upgrade from 3.0.6 to 3.4.1. 

  Ah, if you didn't retain your old data/ directory from your old installation, checksetup assumed you were basically doing a new installation and automatically set utf8 on. If you then copied over your data/ directory later (or just the params file from the old installation) utf8 would have then been off.

> Checksetup DID tell me to run recode.pl, probably because that's the storage
> format for new installations?

  Nope. I'm the checksetup author, and I wrote that code, and I can assure you that it does not run unless the utf8 parameter is enabled:

  http://mxr.mozilla.org/mozilla/source/webtools/bugzilla/Bugzilla/DB/Mysql.pm#679

> Perhaps my bug should not have been marked as duplicate?  But the fix worked
> the same, so I'm happy.

  Cool. :-)

Comment 15

[Denis Roy]

I've hit this too...  I'm planning a 3.0.x -> 3.4 upgrade + UTF-8.  I'll just apply the patch.