Last Comment Bug 508181 - UTF-8 table conversion will fail if there are FKs on the column or on related columns
: UTF-8 table conversion will fail if there are FKs on the column or on related...
Status: RESOLVED FIXED
[es-gnome]
:
Product: Bugzilla
Classification: Server Software
Component: Installation & Upgrading (show other bugs)
: 3.4
: All All
: -- normal (vote)
: Bugzilla 3.4
Assigned To: Max Kanat-Alexander
: default-qa
:
Mentors:
: 509548 509777 515194 (view as bug list)
Depends on:
Blocks: 508186
  Show dependency treegraph
 
Reported: 2009-08-03 20:24 PDT by Max Kanat-Alexander
Modified: 2010-02-28 10:52 PST (History)
6 users (show)
mkanat: approval+
mkanat: approval3.4+
See Also:
QA Whiteboard:
Iteration: ---
Points: ---


Attachments
v1 (2.47 KB, patch)
2009-08-03 20:53 PDT, Max Kanat-Alexander
no flags Details | Diff | Splinter Review
v2 (2.87 KB, patch)
2009-08-03 21:50 PDT, Max Kanat-Alexander
LpSolit: review+
Details | Diff | Splinter Review

Description Max Kanat-Alexander 2009-08-03 20:24:46 PDT
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 2009-08-03 20:53:16 PDT
Created attachment 392415 [details] [diff] [review]
v1

This drops all relevant fks on a column before converting it to UTF-8.
Comment 2 Max Kanat-Alexander 2009-08-03 21:45:59 PDT
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 2009-08-03 21:50:21 PDT
Created attachment 392423 [details] [diff] [review]
v2

Previous patch was buggy--this one is correct.
Comment 4 Frédéric Buclin 2009-08-04 07:56:45 PDT
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 2009-08-04 16:52:39 PDT
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 2009-08-06 04:15:27 PDT
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 2009-08-06 07:54:34 PDT
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 8 Max Kanat-Alexander 2009-08-10 14:19:55 PDT
*** Bug 509548 has been marked as a duplicate of this bug. ***
Comment 9 Max Kanat-Alexander 2009-08-11 13:21:16 PDT
*** Bug 509777 has been marked as a duplicate of this bug. ***
Comment 10 Frédéric Buclin 2009-08-11 15:45:34 PDT
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 2009-08-11 15:58:19 PDT
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 2009-08-11 18:33:30 PDT
(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 2009-08-12 13:12:30 PDT
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 2009-08-12 15:38:01 PDT
(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 2009-08-18 12:54:13 PDT
I've hit this too...  I'm planning a 3.0.x -> 3.4 upgrade + UTF-8.  I'll just apply the patch.
Comment 16 Max Kanat-Alexander 2009-09-08 11:38:39 PDT
*** Bug 515194 has been marked as a duplicate of this bug. ***

Note You need to log in before you can comment on or make changes to this bug.