Last Comment Bug 374951 - Initial installation creates the mysql db using latin1 charset (utf8 illegal mix of collations)
: Initial installation creates the mysql db using latin1 charset (utf8 illegal ...
Status: RESOLVED FIXED
:
Product: Bugzilla
Classification: Server Software
Component: Installation & Upgrading (show other bugs)
: unspecified
: All All
: P1 normal with 1 vote (vote)
: Bugzilla 3.0
Assigned To: Max Kanat-Alexander
: default-qa
:
Mentors:
: 375257 381425 387397 396183 396591 (view as bug list)
Depends on:
Blocks:
  Show dependency treegraph
 
Reported: 2007-03-22 11:22 PDT by Frank Liu
Modified: 2008-10-14 17:50 PDT (History)
9 users (show)
mkanat: approval+
mkanat: blocking3.2+
mkanat: approval3.0+
mkanat: blocking3.0.4+
See Also:
QA Whiteboard:
Iteration: ---
Points: ---


Attachments
v1 (5.09 KB, patch)
2008-02-03 17:29 PST, Max Kanat-Alexander
no flags Details | Diff | Splinter Review
v2 (3.99 KB, patch)
2008-02-03 17:34 PST, Max Kanat-Alexander
LpSolit: review+
Details | Diff | Splinter Review
v2 (3.0) (3.99 KB, patch)
2008-02-12 12:04 PST, Max Kanat-Alexander
LpSolit: review+
Details | Diff | Splinter Review
installing the bugzilla, the error messege picture (171.41 KB, image/pjpeg)
2008-10-14 17:50 PDT, lemon
no flags Details

Description Frank Liu 2007-03-22 11:22:37 PDT
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
Comment 1 Frank Liu 2007-03-22 11:26:20 PDT
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.
Comment 2 Max Kanat-Alexander 2007-03-22 12:52:18 PDT
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
Comment 3 Frank Liu 2007-03-22 13:17:43 PDT
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;



Comment 4 Max Kanat-Alexander 2007-03-22 13:29:34 PDT
(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.
Comment 5 Frank Liu 2007-03-22 13:48:10 PDT
> 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.

Comment 6 Frank Liu 2007-03-23 09:18:32 PDT
I agree we should just put some info in the installation document.
Any other changes at this stage aren't really worthwhile.
Comment 7 Max Kanat-Alexander 2007-03-25 17:00:19 PDT
*** Bug 375257 has been marked as a duplicate of this bug. ***
Comment 8 Max Kanat-Alexander 2007-06-19 14:26:37 PDT
*** Bug 315661 has been marked as a duplicate of this bug. ***
Comment 9 Max Kanat-Alexander 2007-06-19 14:26:43 PDT
*** Bug 381425 has been marked as a duplicate of this bug. ***
Comment 10 Frédéric Buclin 2007-07-09 08:36:51 PDT
*** Bug 387397 has been marked as a duplicate of this bug. ***
Comment 11 Frédéric Buclin 2007-09-14 11:20:42 PDT
*** Bug 396183 has been marked as a duplicate of this bug. ***
Comment 12 Frédéric Buclin 2007-09-18 13:16:11 PDT
*** Bug 396591 has been marked as a duplicate of this bug. ***
Comment 13 Colin Ogilvie [:cso] 2007-09-18 13:26:21 PDT
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...
Comment 14 Max Kanat-Alexander 2007-09-18 14:10:31 PDT
Yes, we should probably just do the check twice in checksetup. I think it can be done.
Comment 15 Tom Adams 2007-09-19 06:59:01 PDT
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
Comment 16 Tom Adams 2007-09-19 07:07:09 PDT
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;
Comment 17 Max Kanat-Alexander 2007-09-19 15:04:38 PDT
Tom: Search the archives of the mozilla.support.bugzilla newsgroup for the answer to your question. (I've answered it there in a thread.)
Comment 18 Max Kanat-Alexander 2008-02-03 17:29:35 PST
Created attachment 301194 [details] [diff] [review]
v1

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.
Comment 19 Max Kanat-Alexander 2008-02-03 17:34:45 PST
Created attachment 301195 [details] [diff] [review]
v2

The first one had an unrelated patch in it.
Comment 20 Max Kanat-Alexander 2008-02-03 17:35:30 PST
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.
Comment 21 Frédéric Buclin 2008-02-04 09:39:47 PST
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?
Comment 22 Max Kanat-Alexander 2008-02-04 16:00:02 PST
(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.
Comment 23 Frédéric Buclin 2008-02-06 10:54:00 PST
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. :)
Comment 24 Max Kanat-Alexander 2008-02-06 14:56:55 PST
(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.
Comment 25 Frank Liu 2008-02-06 15:17:51 PST
(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 26 Frédéric Buclin 2008-02-08 17:04:58 PST
Comment on attachment 301195 [details] [diff] [review]
v2

Looks good. r=LpSolit
This patch needs a backport for 3.0.
Comment 27 Max Kanat-Alexander 2008-02-12 12:04:53 PST
Created attachment 302859 [details] [diff] [review]
v2 (3.0)

Okay, here's the 3.0 version.
Comment 28 Frédéric Buclin 2008-02-12 14:17:54 PST
Comment on attachment 302859 [details] [diff] [review]
v2 (3.0)

r=LpSolit
Comment 29 Max Kanat-Alexander 2008-02-13 22:16:41 PST
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
Comment 30 lemon 2008-10-14 17:50:32 PDT
Created attachment 343145 [details]
installing the bugzilla, the error messege picture

I wonder if this bug will bring another items when we use the bugzilla for some day.

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