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

RESOLVED FIXED in Bugzilla 3.4

Status

()

defect
RESOLVED FIXED
10 years ago
9 years ago

People

(Reporter: mkanat, Assigned: mkanat)

Tracking

Bugzilla 3.4
Dependency tree / graph
Bug Flags:
approval +
approval3.4 +

Details

(Whiteboard: [es-gnome])

Attachments

(1 attachment, 1 obsolete attachment)

Assignee

Description

10 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

10 years ago
Posted 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)
Assignee

Updated

10 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

10 years ago
Blocks: 508186
Assignee

Comment 2

10 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

10 years ago
Posted 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)

Comment 4

10 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

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

Comment 6

10 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

10 years ago
Flags: approval3.4+
Flags: approval+
Assignee

Comment 7

10 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
Closed: 10 years ago
Resolution: --- → FIXED
Assignee

Updated

10 years ago
Duplicate of this bug: 509548
Assignee

Updated

10 years ago
Whiteboard: [es-gnome]
Assignee

Updated

10 years ago
Duplicate of this bug: 509777

Comment 10

10 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

10 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

10 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

10 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

10 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

10 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

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