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

RESOLVED FIXED in Bugzilla 3.4

Status

()

Bugzilla
Installation & Upgrading
RESOLVED FIXED
8 years ago
8 years ago

People

(Reporter: Max Kanat-Alexander, Assigned: Max Kanat-Alexander)

Tracking

Bugzilla 3.4
Bug Flags:
approval +
approval3.4 +

Details

(Whiteboard: [es-gnome])

Attachments

(1 attachment, 1 obsolete attachment)

v2
2.87 KB, patch
Frédéric Buclin
: review+
Details | Diff | Splinter Review
(Assignee)

Description

8 years ago
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.)
(Assignee)

Comment 1

8 years ago
Created attachment 392415 [details] [diff] [review]
v1

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)
(Assignee)

Updated

8 years ago
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
(Assignee)

Updated

8 years ago
Blocks: 508186
(Assignee)

Comment 2

8 years ago
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.
(Assignee)

Comment 3

8 years ago
Created attachment 392423 [details] [diff] [review]
v2

Previous patch was buggy--this one is correct.
Attachment #392415 - Attachment is obsolete: true
Attachment #392423 - Flags: review?(LpSolit)
Attachment #392415 - Flags: review?(LpSolit)

Comment 4

8 years ago
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).
(Assignee)

Comment 5

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

Comment 6

8 years ago
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+
(Assignee)

Updated

8 years ago
Flags: approval3.4+
Flags: approval+
(Assignee)

Comment 7

8 years ago
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
Last Resolved: 8 years ago
Resolution: --- → FIXED
(Assignee)

Updated

8 years ago
Duplicate of this bug: 509548
(Assignee)

Updated

8 years ago
Whiteboard: [es-gnome]
(Assignee)

Updated

8 years ago
Duplicate of this bug: 509777

Comment 10

8 years ago
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

8 years ago
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.
(Assignee)

Comment 12

8 years ago
(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

8 years ago
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.
(Assignee)

Comment 14

8 years ago
(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

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

Updated

8 years ago
Duplicate of this bug: 515194
You need to log in before you can comment on or make changes to this bug.