Illegal mix of collations with 4.1.7

Ever since we upgraded to 4.1.7, we've been seeing a lot of errors
similiar to this one:

ERROR 1267 (HY000): Illegal mix of collations (utf8_general_ci,IMPLICIT)
and (latin1_swedish_ci,COERCIBLE) for operation 'locate'

The query which generated this particular error is this:

SELECT COUNT(*) FROM holdsplaced WHERE timestampDatePlaced <=
20041130040000 AND INSTR( sPatronName, 'bubba' ) != 0;

But other queries have also been kicking this error out as well.

The server is being started with the following options (some altered to
protect the innocent):

mysqld would have been started with the following arguments:
--basedir=/dbs/tpp/mysql-4.1 --datadir=/dbs/tpp/mysql-4.1/data
--port= --socket=/dbs/tpp/mysql-4.1/mysql.sock
--user= --log-error=/dbs/tpp/mysql-4.1/logs/ping.err
--log=/dbs/tpp/mysql-4.1/logs/ping.log --default-character-set=utf8

The problem, I'm sure, is that --default-character-set=utf8 option, but
I don't know much beyond that. Google searches aren't helping much with
this one. Something is out of alignment somewhere, I'm just not sure
what it is.

Any help?

Thanks in advance,

--V

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql@m.gmane.org

Re: Illegal mix of collations with 4.1.7

Hello,
I think your tables have a collation different from the connection collation.

Open mysql client:

mysql> show variables like 'colla%';
+----------------------+-------------------+
| Variable_name | Value |
+----------------------+-------------------+
| collation_connection | latin1_swedish_ci |
| collation_database | latin1_swedish_ci |
| collation_server | latin1_swedish_ci |
+----------------------+-------------------+
3 rows in set (0.04 sec)

and if are different set the collation in your my.cnf file.
Santino

At 8:59 -0800 30-11-2004, V. M. Brasseur wrote:
>Ever since we upgraded to 4.1.7, we've been seeing a lot of errors
>similiar to this one:
>
>ERROR 1267 (HY000): Illegal mix of collations
>(utf8_general_ci,IMPLICIT) and (latin1_swedish_ci,COERCIBLE) for
>operation 'locate'
>
>The query which generated this particular error is this:
>
>SELECT COUNT(*) FROM holdsplaced WHERE timestampDatePlaced <=
>20041130040000 AND INSTR( sPatronName, 'bubba' ) != 0;
>
>But other queries have also been kicking this error out as well.
>
>The server is being started with the following options (some altered
>to protect the innocent):
>
>mysqld would have been started with the following arguments:
>--basedir=/dbs/tpp/mysql-4.1 --datadir=/dbs/tpp/mysql-4.1/data
>--port= --socket=/dbs/tpp/mysql-4.1/mysql.sock
>--user= --log-error=/dbs/tpp/mysql-4.1/logs/ping.err
>--log=/dbs/tpp/mysql-4.1/logs/ping.log --default-character-set=utf8
>
>The problem, I'm sure, is that --default-character-set=utf8 option,
>but I don't know much beyond that. Google searches aren't helping
>much with this one. Something is out of alignment somewhere, I'm
>just not sure what it is.
>
>Any help?
>
>Thanks in advance,
>
>--V
>
>--
>MySQL General Mailing List
>For list archives: http://lists.mysql.com/mysql
>To unsubscribe: http://lists.mysql.com/mysql?unsub=mc5422@mclink.it


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql@m.gmane.org

Re: Illegal mix of collations with 4.1.7

Hello.

The first impression is that you forgot to convert character
columns. See:
http://dev.mysql.com/doc/mysql/en/Upgrading-from-4.0.html
http://dev.mysql.com/doc/mysql/en/Charset-conversion.html


"V. M. Brasseur" wrote:
> Ever since we upgraded to 4.1.7, we've been seeing a lot of errors
> similiar to this one:
>
> ERROR 1267 (HY000): Illegal mix of collations (utf8_general_ci,IMPLICIT)
> and (latin1_swedish_ci,COERCIBLE) for operation 'locate'
>
> The query which generated this particular error is this:
>
> SELECT COUNT(*) FROM holdsplaced WHERE timestampDatePlaced <=
> 20041130040000 AND INSTR( sPatronName, 'bubba' ) != 0;
>
> But other queries have also been kicking this error out as well.
>
> The server is being started with the following options (some altered to
> protect the innocent):
>
> mysqld would have been started with the following arguments:
> --basedir=/dbs/tpp/mysql-4.1 --datadir=/dbs/tpp/mysql-4.1/data
> --port= --socket=/dbs/tpp/mysql-4.1/mysql.sock
> --user= --log-error=/dbs/tpp/mysql-4.1/logs/ping.err
> --log=/dbs/tpp/mysql-4.1/logs/ping.log --default-character-set=utf8
>
> The problem, I'm sure, is that --default-character-set=utf8 option, but
> I don't know much beyond that. Google searches aren't helping much with
> this one. Something is out of alignment somewhere, I'm just not sure
> what it is.
>
> Any help?
>
> Thanks in advance,
>
> --V
>


--
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.NET http://www.ensita.net/
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Gleb Paharenko
/ /|_/ / // /\ \/ /_/ / /__ Gleb.Paharenko@ensita.net
/_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET
<___/ www.mysql.com




--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql@m.gmane.org

Re: Illegal mix of collations with 4.1.7

Ah! Many thanks. That appears to be our problem here:

mysql> show variables like 'colla%';
+----------------------+-------------------+
| Variable_name | Value |
+----------------------+-------------------+
| collation_connection | latin1_swedish_ci |
| collation_database | utf8_general_ci |
| collation_server | utf8_general_ci |
+----------------------+-------------------+

I'll work on getting all the connections onto the same collation page.

Very many thanks again!

--V

Santino wrote:
> Hello,
> I think your tables have a collation different from the connection
> collation.
>
> Open mysql client:
>
> mysql> show variables like 'colla%';
> +----------------------+-------------------+
> | Variable_name | Value |
> +----------------------+-------------------+
> | collation_connection | latin1_swedish_ci |
> | collation_database | latin1_swedish_ci |
> | collation_server | latin1_swedish_ci |
> +----------------------+-------------------+
> 3 rows in set (0.04 sec)
>
> and if are different set the collation in your my.cnf file.
> Santino
>
> At 8:59 -0800 30-11-2004, V. M. Brasseur wrote:
>
>> Ever since we upgraded to 4.1.7, we've been seeing a lot of errors
>> similiar to this one:
>>
>> ERROR 1267 (HY000): Illegal mix of collations
>> (utf8_general_ci,IMPLICIT) and (latin1_swedish_ci,COERCIBLE) for
>> operation 'locate'
>>
>> The query which generated this particular error is this:
>>
>> SELECT COUNT(*) FROM holdsplaced WHERE timestampDatePlaced <=
>> 20041130040000 AND INSTR( sPatronName, 'bubba' ) != 0;
>>
>> But other queries have also been kicking this error out as well.
>>
>> The server is being started with the following options (some altered
>> to protect the innocent):
>>
>> mysqld would have been started with the following arguments:
>> --basedir=/dbs/tpp/mysql-4.1 --datadir=/dbs/tpp/mysql-4.1/data
>> --port= --socket=/dbs/tpp/mysql-4.1/mysql.sock
>> --user= --log-error=/dbs/tpp/mysql-4.1/logs/ping.err
>> --log=/dbs/tpp/mysql-4.1/logs/ping.log --default-character-set=utf8
>>
>> The problem, I'm sure, is that --default-character-set=utf8 option,
>> but I don't know much beyond that. Google searches aren't helping
>> much with this one. Something is out of alignment somewhere, I'm just
>> not sure what it is.
>>
>> Any help?
>>
>> Thanks in advance,
>>
>> --V
>>
>> --
>> MySQL General Mailing List
>> For list archives: http://lists.mysql.com/mysql
>> To unsubscribe: http://lists.mysql.com/mysql?unsub=mc5422@mclink.it
>
>
>
>

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql@m.gmane.org

Re: Illegal mix of collations with 4.1.7

On Tue, 30 Nov 2004 19:24:05 +0200, Gleb Paharenko
wrote:
> The first impression is that you forgot to convert character
> columns. See:
>
> http://dev.mysql.com/doc/mysql/en/Upgrading-from-4.0.html
> http://dev.mysql.com/doc/mysql/en/Charset-conversion.html

Once the Character Sets are set up (everything is utf8 now on my
installation), how can it be achieved to convert *ALL* columns in
*ALL* tables to the same, new COLLATION value?

Changing them by hand would lead to admin's fun for, say, weeks ;)

Thanks in advance,
Fred

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql@m.gmane.org

Re: Illegal mix of collations with 4.1.7

Hello.

Use Perl, DBI :)


Frederic Wenzel wrote:
> On Tue, 30 Nov 2004 19:24:05 +0200, Gleb Paharenko
> wrote:
>> The first impression is that you forgot to convert character
>> columns. See:
>>
>> http://dev.mysql.com/doc/mysql/en/Upgrading-from-4.0.html
>> http://dev.mysql.com/doc/mysql/en/Charset-conversion.html
>
> Once the Character Sets are set up (everything is utf8 now on my
> installation), how can it be achieved to convert *ALL* columns in
> *ALL* tables to the same, new COLLATION value?
>
> Changing them by hand would lead to admin's fun for, say, weeks ;)
>
> Thanks in advance,
> Fred
>


--
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.NET http://www.ensita.net/
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Gleb Paharenko
/ /|_/ / // /\ \/ /_/ / /__ Gleb.Paharenko@ensita.net
/_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET
<___/ www.mysql.com




--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql@m.gmane.org