Closed Bug 508181 Opened 11 years ago Closed 11 years ago

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

Categories

(Bugzilla :: Installation & Upgrading, defect)

defect
Not set
normal

Tracking

()

RESOLVED FIXED
Bugzilla 3.4

People

(Reporter: mkanat, Assigned: mkanat)

References

Details

(Whiteboard: [es-gnome])

Attachments

(1 file, 1 obsolete file)

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.)
Attached patch v1 (obsolete) — Splinter Review
This drops all relevant fks on a column before converting it to UTF-8.
Assignee: installation → mkanat
Status: NEW → ASSIGNED
Attachment #392415 - Flags: review?(LpSolit)
Summary: UTF-8 table conversion will fail if there are FKs on the column → UTF-8 table conversion will fail if there are FKs on the column or on related columns
Blocks: 508186
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.
Attached patch v2Splinter Review
Previous patch was buggy--this one is correct.
Attachment #392415 - Attachment is obsolete: true
Attachment #392423 - Flags: review?(LpSolit)
Attachment #392415 - Flags: review?(LpSolit)
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).
When you created your database for 2.22.7, are you sure that it had a latin1 character set?
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
Attachment #392423 - Flags: review?(LpSolit) → review+
Flags: approval3.4+
Flags: approval+
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
Status: ASSIGNED → RESOLVED
Closed: 11 years ago
Resolution: --- → FIXED
Duplicate of this bug: 509548
Whiteboard: [es-gnome]
Duplicate of this bug: 509777
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?
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.
(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.
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.
(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. :-)
I've hit this too...  I'm planning a 3.0.x -> 3.4 upgrade + UTF-8.  I'll just apply the patch.
Duplicate of this bug: 515194
You need to log in before you can comment on or make changes to this bug.