Closed Bug 374951 Opened 17 years ago Closed 16 years ago

Initial installation creates the mysql db using latin1 charset (utf8 illegal mix of collations)

Categories

(Bugzilla :: Installation & Upgrading, defect, P1)

defect

Tracking

()

RESOLVED FIXED
Bugzilla 3.0

People

(Reporter: gfrankliu, Assigned: mkanat)

References

Details

Attachments

(3 files, 1 obsolete file)

User-Agent:       Mozilla/5.0 (X11; U; Linux i686; en-US; rv:1.8.0.10) Gecko/20070226 Fedora/1.5.0.10-1.fc6 Firefox/1.5.0.10 pango-text
Build Identifier: 3.0rc1

1) untar the bugzilla 3.0rc1
2) run checksetup.pl
3) edit localconfig to add the MySQL db host/user/pass info.
4) run checksetup.pl again, so all the db tables are created, admin account is created.
5) login to mysql and check the tables. eg: show create table votes;
6) You will see those tables are created with CHARSET=latin1

Reproducible: Always

Steps to Reproduce:
1. see details above
2.
3.
Actual Results:  
tables are created in latin1 charset

Expected Results:  
tables are created in utf8 charset
I noticed that if I can "checksetup.pl" again, for whatever reason. It will detect that the db table charset is wrong, and ask me if I want to convert it to utf8.  This confirms that the initial db was created with wrong charset.
I can't reproduce this. I upgraded to the latest code, moved away my data directory (which is how Bugzilla knows it's a new installation--it has to create the data directory and data/params).

Probably what happened was you created your database by yourself, instead of letting Bugzilla create it. That makes the database latin1, which means that Bugzilla will make latin1 tables.

We could put information into the docs that if you do CREATE DATABASE yourself, you have to do CREATE DATABASE bugs CHARACTER SET utf8
Assignee: installation → documentation
Severity: major → normal
Status: UNCONFIRMED → NEW
Component: Installation & Upgrading → Documentation
Ever confirmed: true
OS: Linux → All
Hardware: PC → All
Indeed I created the db and db user by hand via dba account since the bugzilla db user doesn't have the create db privilege.

create database bugs;
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES ON bugs.* to 'bug_dbuser'@'%' identified by 'dbpassword';

It's interesting that a later run of "checksetup.pl" can fix/convert it. Why can't it do it the first time when all tables are created? eg:

CREATE TABLE `votes` (
  `who` mediumint(9) NOT NULL,
  `bug_id` mediumint(9) NOT NULL,
  `vote_count` smallint(6) NOT NULL,
  KEY `votes_who_idx` (`who`),
  KEY `votes_bug_id_idx` (`bug_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;



(In reply to comment #3)
> Indeed I created the db and db user by hand via dba account since the bugzilla
> db user doesn't have the create db privilege.

  It has CREATE below, and it works on all my installations.

> It's interesting that a later run of "checksetup.pl" can fix/convert it. Why
> can't it do it the first time when all tables are created? eg:

  It does do it, if your database is UTF8. We don't create tables as UTF-8 unless your DB charset is UTF8. By default Bugzilla creates a database as UTF-8.

  It only *converts* a database to utf8 if the "utf8" parameter is on. However, when the database is originally created, parameters don't even exist! So it doesn't happen then.
> It has CREATE below, and it works on all my installations.

You are right. I just pasted from the doc, and didn't "see" the CREATE :)

> We don't create tables as UTF-8 unless your DB charset is UTF8.

Guess this is chicken/egg. Since this is a fresh new installation, do we have to honor the default DB charset? We can just force utf8 for new installs, or if we really want to honor the DB charset, we should probably populate the tables in a way so that the parameter's page has the utf8 off if the db charset isn't utf8. Since we are already forcing utf8 on the parameter's page for an initial install, we should probably force tables to be utf8 too, so that they match.

I agree we should just put some info in the installation document.
Any other changes at this stage aren't really worthwhile.
Summary: Initial installation creates the mysql db using latin1 charset → Initial installation creates the mysql db using latin1 charset (utf8 collation problem)
Summary: Initial installation creates the mysql db using latin1 charset (utf8 collation problem) → Initial installation creates the mysql db using latin1 charset (utf8 illegal mix of collations)
What exactly is it that is proposed to adding to the documentation about this?

Shouldn't we be catching the error in a nicer way given the number of people that seem to be having an issue with this stuff...
Yes, we should probably just do the check twice in checksetup. I think it can be done.
Assignee: documentation → installation
Component: Documentation → Installation & Upgrading
Priority: -- → P1
Target Milestone: --- → Bugzilla 3.0
Frederic has marked Bug 396591 – Unable to change bug ownership after 2.17 to 3.01 upgrade (edit)  as a duplicate.

I'm not sure it is.  Checksetup does not complain nor ask for recode.pl to be run.
If I remove the bugs database an recreate it, I see that the default charset is latin.

I'd like a simple explanation of how to fix this.  I've been blocking a production environment for days.  Everything seems to work expect reassign as per Bug 396591
I notice that if I drop my database and recreate it using checksetup.pl it's in latin1 format.  I run recode.pl it changes to utf8. If I then reload my data it reverts to latin 1, and subsequent executions of recode.pl don't change it back.

I'm determining encoding using show create table votes;
Tom: Search the archives of the mozilla.support.bugzilla newsgroup for the answer to your question. (I've answered it there in a thread.)
Attached patch v1 (obsolete) — Splinter Review
Okay, this handles all of the different situations that can cause this bug. In particular, it handles the following situations:

1) When creating a database yourself (instead of letting checksetup do it) 
   tables would default to latin1 instead of UTF8, and then you'd get the big
   WARNING needlessly.

2) Sometimes you could get a situation where some tables were utf8 and some
   were latin1 (I had this on a DB on landfill).

3) Sometimes you could get a situation where all the tables were utf8,
   but the DB was latin1.

I'm hoping that this will fully handle the "Illegal mix of collations" error for almost all users.
Assignee: installation → mkanat
Status: NEW → ASSIGNED
Attachment #301194 - Flags: review?(LpSolit)
Attached patch v2Splinter Review
The first one had an unrelated patch in it.
Attachment #301194 - Attachment is obsolete: true
Attachment #301195 - Flags: review?(LpSolit)
Attachment #301194 - Flags: review?(LpSolit)
Now that this has a patch, I'd like to see it in 3.0.4 so that this error can be fully handled for people.
Flags: blocking3.0.4+
Is this patch for 3.0 only? Else why isn't it blocking 3.2 as well?

Also, can Pg be affected by this problem?
(In reply to comment #21)
> Is this patch for 3.0 only? Else why isn't it blocking 3.2 as well?

  It's now blocking3.2 as well.

> Also, can Pg be affected by this problem?

  No, we don't do any table conversion there yet.
Flags: blocking3.2+
I'm unable to make bugzilla to fail with the illegal mix of collations error, without the patch. Max, could you tell me how to do so? Else it's hard to test a fix which doesn't fix something broken. :)
(In reply to comment #23)
> I'm unable to make bugzilla to fail with the illegal mix of collations error,
> without the patch. Max, could you tell me how to do so? Else it's hard to test
> a fix which doesn't fix something broken. :)

  Um, it's somewhat difficult to cause this intentionally. Try cloning an old DB, say 2.22 or 2.20, then on the clone do: "ALTER DATABASE <db_name> CHARACTER SET utf8" and then run checksetup. All of checksetup's new tables will be utf8 but the old tables will be latin1, and then searches on the old tables will fail. Oh, and you have to turn the utf8 parameter on before running checksetup, of course.
(In reply to comment #23)
> I'm unable to make bugzilla to fail with the illegal mix of collations error,
> without the patch. Max, could you tell me how to do so? Else it's hard to test
> a fix which doesn't fix something broken. :)
> 

Check comment #3 on how to reproduce it.

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

Looks good. r=LpSolit
This patch needs a backport for 3.0.
Attachment #301195 - Flags: review?(LpSolit) → review+
Attached patch v2 (3.0)Splinter Review
Okay, here's the 3.0 version.
Attachment #302859 - Flags: review?(LpSolit)
Comment on attachment 302859 [details] [diff] [review]
v2 (3.0)

r=LpSolit
Attachment #302859 - Flags: review?(LpSolit) → review+
Flags: approval?
Flags: approval3.0?
Flags: approval?
Flags: approval3.0?
Flags: approval3.0+
Flags: approval+
tip:

Checking in Bugzilla/DB/Mysql.pm;
/cvsroot/mozilla/webtools/bugzilla/Bugzilla/DB/Mysql.pm,v  <--  Mysql.pm
new revision: 1.57; previous revision: 1.56
done

3.0:

Checking in Bugzilla/DB/Mysql.pm;
/cvsroot/mozilla/webtools/bugzilla/Bugzilla/DB/Mysql.pm,v  <--  Mysql.pm
new revision: 1.49.2.1; previous revision: 1.49
done
Status: ASSIGNED → RESOLVED
Closed: 16 years ago
Resolution: --- → FIXED
I wonder if this bug will bring another items when we use the bugzilla for some day.
You need to log in before you can comment on or make changes to this bug.