Illegal mix of Collations when Reassinging Bug in Bugzilla 3.0

When i try to reassign bugs in Bugzilla 3.0 i received the error message
below. Iv just upgraded from Bugzilla 2.0 to Bugzilla 3.0 and had no
problems before. Im guessing its something to do with the encoding types of
the old database and it hasnt been converted proberly or something like
that. Iv tried using the recode.pl script and it works fine with no errors.
But i still receieved this;

Software error:

DBD::mysql::db do failed: Illegal mix of collations
(utf8_general_ci,CERCIBLE) and (latin1_swedish_ci,IMPLICIT) for operation
'case' at E:\Web\bugzilla\process_bug.cgi line 1689

For help, please send mail to this site's webmaster, giving this error
message and the time and date of the error.
[Thu May 17 12:06:20 2007] process_bug.cgi: DBD::mysql::db do failed:
Illegal mix of collations (utf8_general_ci,CERCIBLE) and
(latin1_swedish_ci,IMPLICIT) for operation 'case' at
E:\Web\bugzilla\process_bug.cgi line 1689


Any ideas / suggestions / fixes will be greatly appreiated!


support-bugzilla mailing list
support-bugzilla (AT) lists (DOT) mozilla.org

PLEASE put support-bugzilla (AT) lists (DOT) mozilla.org in the To: field when you reply.

I've just disaplayed the query to see why its going wrong, and it doesnt
seem to look correct;

UPDATE bugs SET
rep_platform = ?,
priority = ?,
bug_severity = ?,
bug_file_loc = ?,
short_desc = ?,
version = ?,
op_sys = ?,
cf_current_version = ?,
product_id = ?,
component_id = ?,
bug_status = CASE WHEN bug_status IN('NEW', 'REPENED', 'ASSIGNED',
'UNCNFIRMED') THEN (CASE WHEN everconfirmed = 1 THEN 'NEW' ELSE
'UNCNFIRMED' END) ELSE bug_status END,
assigned_to = ?,
estimated_time = ?,
remaining_time = ?,
deadline = NULL WHERE bug_id = ?" (perhaps you forgot to load "UPDATE
bugs SET
rep_platform = ?,
priority = ?,
bug_severity = ?,
bug_file_loc = ?,
short_desc = ?,
version = ?,
op_sys = ?,
cf_current_version = ?,
product_id = ?,
component_id = ?,
bug_status = CASE WHEN bug_status IN('NEW', 'REPENED', 'ASSIGNED',
'UNCNFIRMED') THEN (CASE WHEN everconfirmed = 1 THEN 'NEW' ELSE
'UNCNFIRMED' END) ELSE bug_status END,
assigned_to = ?,
estimated_time = ?,
remaining_time = ?,
deadline = NULL WHERE bug_id = ?"?) at E:\Web\bugzilla\process_bug.cgi
line 1688.


support-bugzilla mailing list
support-bugzilla (AT) lists (DOT) mozilla.org

PLEASE put support-bugzilla (AT) lists (DOT) mozilla.org in the To: field when you reply.

Thu, 17 May 2007 12:13:18 +0100 "Adam" <adam (AT) 84degrees (DOT) comwrote:
DBD::mysql::db do failed: Illegal mix of collations

You need to run checksetup.pl one more time. Just trust me. I
wrote it. :-) That's what you need to do. :-)

-Max
--

Competent, Friendly Bugzilla Services. And Everything Else, too.

support-bugzilla mailing list
support-bugzilla (AT) lists (DOT) mozilla.org

PLEASE put support-bugzilla (AT) lists (DOT) mozilla.org in the To: field when you reply.

"Max Kanat-Alexander" <mkanat (AT) bugzilla (DOT) orgwrote in message
@lists.mozilla.org
Thu, 17 May 2007 12:13:18 +0100 "Adam" <adam (AT) 84degrees (DOT) comwrote:
>DBD::mysql::db do failed: Illegal mix of collations
>
You need to run checksetup.pl one more time. Just trust me. I
wrote it. :-) That's what you need to do. :-)
>
-Max
--

Competent, Friendly Bugzilla Services. And Everything Else, too.

I re-ran checksetup.pl, per suggestion above, and am still getting this
error message. Any other suggestions??

-Roy



support-bugzilla mailing list
support-bugzilla (AT) lists (DOT) mozilla.org

PLEASE put support-bugzilla (AT) lists (DOT) mozilla.org in the To: field when you reply.

Mon, 21 May 2007 12:35:19 -0500 "Roy" <richmond.roy (AT) cimcor (DOT) com>
wrote:
I re-ran checksetup.pl, per suggestion above, and am still getting
this error message. Any other suggestions??

Did it convert your database?

Are you perhaps running a utf-8 database without having the
utf8 parameter on in Bugzilla?

Usually the problem is the other way utf8 data with
a non-utf8 database, and running checksetup.pl fixes that. But maybe
it's the other way around for you?

-Max
--

Competent, Friendly Bugzilla Services. And Everything Else, too.

support-bugzilla mailing list
support-bugzilla (AT) lists (DOT) mozilla.org

PLEASE put support-bugzilla (AT) lists (DOT) mozilla.org in the To: field when you reply.

"Max Kanat-Alexander" <mkanat (AT) bugzilla (DOT) orgwrote in message
@lists.mozilla.org
Mon, 21 May 2007 12:35:19 -0500 "Roy" <richmond.roy (AT) cimcor (DOT) com>
wrote:
>I re-ran checksetup.pl, per suggestion above, and am still getting
>this error message. Any other suggestions??
>
Did it convert your database?
>
Are you perhaps running a utf-8 database without having the
utf8 parameter on in Bugzilla?
>
Usually the problem is the other way utf8 data with
a non-utf8 database, and running checksetup.pl fixes that. But maybe
it's the other way around for you?
>
-Max
--

Competent, Friendly Bugzilla Services. And Everything Else, too.

According to mysql, I am using UTF-8:

mysqlstatus;

mysql Ver 14.12 Distrib 5.0.33, for pc-linux-gnu (i686) using readline 5.0

Connection id: 2
Current database: bugs
Current user: root@localhost
SSL: Not in use
Current pager: stdout
Using outfile: ''
Using delimiter: ;
Server version: 5.0.33 Source distribution
Protocol version: 10
Connection: Localhost via UNIX socket
Server characterset: utf8
Db characterset: utf8
Client characterset: utf8
Conn. characterset: utf8
UNIX socket: /srv/mysql/mysql.sock
Uptime: 2 min 28 sec

Threads: 1 Questions: 63 Slow queries: 0 : 66 Flush tables: 1
tables: 60 Queries per second avg: 0.426



And I have checked the parameters in bugzilla: UTF-8 is set to on.

When I set this up, I performed a clean bugzilla install, backed-up the
mysql database, and then restored it into the clean install. Could this be
the issue??

-Roy



support-bugzilla mailing list
support-bugzilla (AT) lists (DOT) mozilla.org

PLEASE put support-bugzilla (AT) lists (DOT) mozilla.org in the To: field when you reply.

May 21, 8:15 pm, "Roy" <richmond (AT) cimcor (DOT) comwrote:
"Max Kanat-Alexander" <mka (AT) bugzilla (DOT) orgwrote in message
>
@lists.mozilla.org
>
>
>
Mon, 21 May 2007 12:35:19 -0500 "Roy" <richmond (AT) cimcor (DOT) com>
wrote:
>I re-ran checksetup.pl, per suggestion above, and am still getting
>this error message. Any other suggestions??
>
Did it convert your database?
>
Are you perhaps running a utf-8 database without having the
utf8 parameter on in Bugzilla?
>
Usually the problem is the other way utf8 data with
a non-utf8 database, and running checksetup.pl fixes that. But maybe
it's the other way around for you?
>
-Max
--
>
Competent, Friendly Bugzilla Services. And Everything Else, too.
>
According to mysql, I am using UTF-8:
>
mysqlstatus;

mysql Ver 14.12 Distrib 5.0.33, for pc-linux-gnu (i686) using readline 5.0
>
Connection id: 2
Current database: bugs
Current user: root@localhost
SSL: Not in use
Current pager: stdout
Using outfile: ''
Using delimiter: ;
Server version: 5.0.33 Source distribution
Protocol version: 10
Connection: Localhost via UNIX socket
Server characterset: utf8
Db characterset: utf8
Client characterset: utf8
Conn. characterset: utf8
UNIX socket: /srv/mysql/mysql.sock
Uptime: 2 min 28 sec
>
Threads: 1 Questions: 63 Slow queries: 0 : 66 Flush tables: 1
tables: 60 Queries per second avg: 0.426

>
And I have checked the parameters in bugzilla: UTF-8 is set to on.
>
When I set this up, I performed a clean bugzilla install, backed-up the
mysql database, and then restored it into the clean install. Could this be
the issue??
>
-Roy

i reran the checksetup.pl and i still receive the error. iv the same
thing as roy, transfered from an older bugzilla installation. i have
UTF-8 support turned on, if i try to turn it off in the bugzilla
settings i receive this error;

The new value for utf8 is invalid: You cannot disable UTF-8 support,
because your MySQL database is encoded in UTF-8.

I've ran recode.pl a few times but it doesnt seem to be doing
anything, and im not sure if its because im specifying something wrong
or its just my database.


All the help is appreciated and sorry i took awhile to reply!


support-bugzilla mailing list
support-bugzilla (AT) lists (DOT) mozilla.org

PLEASE put support-bugzilla (AT) lists (DOT) mozilla.org in the To: field when you reply.

30 May 2007 03:22:04 -0700 adam (AT) 84degrees (DOT) com wrote:
The new value for utf8 is invalid: You cannot disable UTF-8 support,
because your MySQL database is encoded in UTF-8.

Here's what you have to do.

1. Back up your DB.

2. Go into MySQL, and do:

ALTER DATABASE bugs CHARACTER SET latin1;

3. Re-run checksetup.pl

If you are missing any data after #3, then you should run
recode.pl again after #2.

-Max
--

Competent, Friendly Bugzilla Services. And Everything Else, too.

support-bugzilla mailing list
support-bugzilla (AT) lists (DOT) mozilla.org

PLEASE put support-bugzilla (AT) lists (DOT) mozilla.org in the To: field when you reply.

30 May 2007 03:32:05 -0700 adam (AT) 84degrees (DOT) com wrote:
go to the params file in the data directory, look for 'utf8' =and
then change the value from 1 to 0 or from 0 to 1

This should solve your the problem!

And you absolutely should *not* do it. You could corrupt the
data in your database, particularly if any of it is already UTF-8.

-Max
--

Competent, Friendly Bugzilla Services. And Everything Else, too.

support-bugzilla mailing list
support-bugzilla (AT) lists (DOT) mozilla.org

PLEASE put support-bugzilla (AT) lists (DOT) mozilla.org in the To: field when you reply.

May 30, 6:22 am, a (AT) 84degrees (DOT) com wrote:
May 21, 8:15 pm, "Roy" <richmond (AT) cimcor (DOT) comwrote:
>
>
>
"Max Kanat-Alexander" <mka (AT) bugzilla (DOT) orgwrote in message
>
>@lists.mozilla.org
>
Mon, 21 May 2007 12:35:19 -0500 "Roy" <richmond (AT) cimcor (DOT) com>
wrote:
>I re-ran checksetup.pl, per suggestion above, and am still getting
>this error message. Any other suggestions??
>
Did it convert your database?
>
Are you perhaps running a utf-8 database without having the
utf8 parameter on in Bugzilla?
>
Usually the problem is the other way utf8 data with
a non-utf8 database, and running checksetup.pl fixes that. But maybe
it's the other way around for you?
>
-Max
--
>
Competent, Friendly Bugzilla Services. And Everything Else, too.
>
According to mysql, I am using UTF-8:
>
mysqlstatus;

mysql Ver 14.12 Distrib 5.0.33, for pc-linux-gnu (i686) using readline 5.0
>
Connection id: 2
Current database: bugs
Current user: root@localhost
SSL: Not in use
Current pager: stdout
Using outfile: ''
Using delimiter: ;
Server version: 5.0.33 Source distribution
Protocol version: 10
Connection: Localhost via UNIX socket
Server characterset: utf8
Db characterset: utf8
Client characterset: utf8
Conn. characterset: utf8
UNIX socket: /srv/mysql/mysql.sock
Uptime: 2 min 28 sec
>
Threads: 1 Questions: 63 Slow queries: 0 : 66 Flush tables: 1
tables: 60 Queries per second avg: 0.426

>
And I have checked the parameters in bugzilla: UTF-8 is set to on.
>
When I set this up, I performed a clean bugzilla install, backed-up the
mysql database, and then restored it into the clean install. Could this be
the issue??
>
-Roy
>
i reran the checksetup.pl and i still receive the error. iv the same
thing as roy, transfered from an older bugzilla installation. i have
UTF-8 support turned on, if i try to turn it off in the bugzilla
settings i receive this error;
>
The new value for utf8 is invalid: You cannot disable UTF-8 support,
because your MySQL database is encoded in UTF-8.
>
I've ran recode.pl a few times but it doesnt seem to be doing
anything, and im not sure if its because im specifying something wrong
or its just my database.
>
All the help is appreciated and sorry i took awhile to reply!

Did your database have any special characters in the name that could
interfere with a MySQL query? I was having this issue, apparently I
missed that the checksetup.pl had encountered an error when I was
upgrading my existing DB with the 3.0rc1 release. My database was
named "something-bugs", and the hyphen was causing an error in the
MySQL query performed in .\Bugzilla\DB\Mysql.pm on line 646:

$self->do("ALTER DATABASE $db_name CHARACTER SET utf8");

By changing the $db_name to be surrounded by `backticks` like so:

$self->do("ALTER DATABASE `$db_name` CHARACTER SET utf8");

You can then re-run checksetup.pl and have it succeed (if this was
your problem.)


support-bugzilla mailing list
support-bugzilla (AT) lists (DOT) mozilla.org

PLEASE put support-bugzilla (AT) lists (DOT) mozilla.org in the To: field when you reply.

I have changed the value of 'utf8' from 1 to 0 and it has worked for
me too !

My situation was a bit different, because my SQL data was already
written in Latin1 charset (the result of 'status' in MySQL shell was
'Latin1', not 'utf-8'). I had tried to solve it with recode.pl and
checksetup.pl but it didn't work, the special characters were all
wrong, so I tried as a last resort to change this 'utf8' value in
Bugzilla 'params' file and it works !

It is _VERY_ important that you make a backup of your MySQL data
before doing those kind of changes!!!

- Xavi

May 30, 11:12 pm, Max Kanat-Alexander <mka (AT) bugzilla (DOT) orgwrote:
30 May 2007 03:32:05 -0700 a (AT) 84degrees (DOT) com wrote:
>
go to the params file in the data directory, look for 'utf8' =and
then change the value from 1 to 0 or from 0 to 1
>
This should solve your the problem!
>
And you absolutely should *not* do it. You could corrupt the
data in your database, particularly if any of it is already UTF-8.
>
-Max
//www.everythingsolved.com/
Competent, Friendly Bugzilla Services. And Everything Else, too.



support-bugzilla mailing list
support-bugzilla (AT) lists (DOT) mozilla.org

PLEASE put support-bugzilla (AT) lists (DOT) mozilla.org in the To: field when you reply.

I hit this error recently after a fresh install. Thank God google is
here to find the answers to all my problems!

I just re-ran checksetup.pl as Max recommended and it resolved my
issue. Given that he warns against changing the value of 'utf8' from
1 to 0 manually, I would suggest trying this option first.
--
Bill


support-bugzilla mailing list
support-bugzilla (AT) lists (DOT) mozilla.org

PLEASE put support-bugzilla (AT) lists (DOT) mozilla.org in the To: field when you reply.

DBD::mysql::db do failed:Illegalmixofcollations
>
You need to run checksetup.pl one more time. Just trust me. I
wrote it. :-) That's what you need to do. :-)
>
-Max
//www.everythingsolved.com/

I followed all the suggestions provided for by this thread and I am
still getting this error.

This was a clean install of Bugzilla 3.0, mySQL, and Perl, but it had
older Bugzilla data imported. The import had no problems until now.
MySQL is dedicated to Bugzilla, there are no other apps connecting to
it

Here are the particulars:
C:\bugzilla-3.0>perl checksetup.pl
* This is Bugzilla 3.0 on perl 5.8.8
* Running on Win2003 Build 3790
Checking for MySQL (v4.1.2) ok: found v5.0.41-community-nt

C:\bugzilla-3.0>ver
Microsoft Windows [Version 5.2.3790]

C:\bugzilla-3.0>perl
This is perl, v5.8.8 built for MSWin32-x86-multi-thread

mysqlstatus;

mysql Ver 14.12 Distrib 5.0.41, for Win32 (ia32)

Connection id: 9
Current database: bugs
Current user: root@localhost
SSL: Not in use
Using delimiter: ;
Server version: 5.0.41-community-nt MySQL Community Edition
(GPL)
Protocol version: 10
Connection: localhost via TCP/IP
Server characterset: utf8
Db characterset: utf8
Client characterset: utf8
Conn. characterset: utf8
TCP port: 3306


support-bugzilla mailing list
support-bugzilla (AT) lists (DOT) mozilla.org

PLEASE put support-bugzilla (AT) lists (DOT) mozilla.org in the To: field when you reply.

Tue, 26 Jun 2007 11:17:48 -0700 javaseeds (AT) gmail (DOT) com wrote:
Db characterset: utf8

In MySQL, do:

ALTER DATABASE SET CHARACTER SET = 'latin1';

And then re-run checksetup.

If you've always had the "utf8" parameter on, you don't need to
run recode.pl. you may have to.

-Max
--

Competent, Friendly Bugzilla Services. And Everything Else, too.

support-bugzilla mailing list
support-bugzilla (AT) lists (DOT) mozilla.org

PLEASE put support-bugzilla (AT) lists (DOT) mozilla.org in the To: field when you reply.

It finally worked - thank you very much Max.

Tue, 26 Jun 2007 11:17:48 -0700 javase (AT) gmail (DOT) com wrote:
In MySQL, do:
ALTER DATABASE SET CHARACTER SET = 'latin1';
And then re-run checksetup.
>
If you've always had the "utf8" parameter on, you don't need to
run recode.pl. you may have to.
Competent, Friendly Bugzilla Services. And Everything Else, too.



support-bugzilla mailing list
support-bugzilla (AT) lists (DOT) mozilla.org

PLEASE put support-bugzilla (AT) lists (DOT) mozilla.org in the To: field when you reply.

Hi Adam,
Thanks a lot,
It worked for me also.

Murli.

Adam-136 wrote:

iv managed to fix the problem!

heres a quick fix;

go to the params file in the data directory, look for 'utf8' =and
then change the value from 1 to 0 or from 0 to 1

This should solve your the problem!



ad



May 21, 8:15 pm, "Roy" <richmond (AT) cimcor (DOT) comwrote:
>"Max Kanat-Alexander" <mka (AT) bugzilla (DOT) orgwrote in message
>>
>@lists.mozilla.org
>>
>>
>>
>Mon, 21 May 2007 12:35:19 -0500 "Roy" <richmond (AT) cimcor (DOT) com>
>wrote:
>>I re-ran checksetup.pl, per suggestion above, and am still getting
>>this error message. Any other suggestions??
>>
>Did it convert your database?
>>
>Are you perhaps running a utf-8 database without having the
>utf8 parameter on in Bugzilla?
>>
>Usually the problem is the other way utf8 data with
>a non-utf8 database, and running checksetup.pl fixes that. But maybe
>it's the other way around for you?
>>
>-Max
>--
>>
>Competent, Friendly Bugzilla Services. And Everything Else, too.
>>
>According to mysql, I am using UTF-8:
>>
>mysqlstatus;
>
>mysql Ver 14.12 Distrib 5.0.33, for pc-linux-gnu (i686) using readline
>5.0
>>
>Connection id: 2
>Current database: bugs
>Current user: root@localhost
>SSL: Not in use
>Current pager: stdout
>Using outfile: ''
>Using delimiter: ;
>Server version: 5.0.33 Source distribution
>Protocol version: 10
>Connection: Localhost via UNIX socket
>Server characterset: utf8
>Db characterset: utf8
>Client characterset: utf8
>Conn. characterset: utf8
>UNIX socket: /srv/mysql/mysql.sock
>Uptime: 2 min 28 sec
>>
>Threads: 1 Questions: 63 Slow queries: 0 : 66 Flush tables: 1
>
>tables: 60 Queries per second avg: 0.426
>
>>
>And I have checked the parameters in bugzilla: UTF-8 is set to on.
>>
>When I set this up, I performed a clean bugzilla install, backed-up the
>mysql database, and then restored it into the clean install. Could this
>be
>the issue??
>>
>-Roy





support-bugzilla mailing list
support-bugzilla (AT) lists (DOT) mozilla.org

PLEASE put support-bugzilla (AT) lists (DOT) mozilla.org in the To: field when you
reply.



--
View this message in context: #a11360888
Sent from the Bugzilla - Users mailing list archive at Nabble.com.


support-bugzilla mailing list
support-bugzilla (AT) lists (DOT) mozilla.org

PLEASE put support-bugzilla (AT) lists (DOT) mozilla.org in the To: field when you reply.

Max Kanat-Alexander wrote:
You need to run checksetup.pl one more time. Just trust me. I
wrote it. :-) That's what you need to do. :-)

Works like a charm, now I'm able to reassign, the mail never appears
but I think my company's exchange server is acting up.

Much appreciated
Michael

support-bugzilla mailing list
support-bugzilla (AT) lists (DOT) mozilla.org

PLEASE put support-bugzilla (AT) lists (DOT) mozilla.org in the To: field when you reply.