illegal mix of utf8_bin and utf8_general_ci collations

[Jim Cramer]

Hi,

With MySql 4.1.10a, I am using a commerial app (Advanced Query Tool)
to query and manage the server and databases in it.

While performing one of its functions, the app issues the query
" select * from msql.db where db=database()

This query give the error:
"HYT00(1267) Illegal mix of collations (utf8_bin,IMPLICIT) and
(utf8_general_ci,IMPLICIT) for operation '=' "

This is because the mysql database db table is set to utf8_bin collation but
the function "database()" returns a result that is in utf8_general_ci
collation,
and the comparison of them with the "=" operator is incompatible.

Can anybody tell me what to do to make this not happen?
How can I set the collation of information functions like database()
(in this case to utf_bin to match the mysql.db column)?

I have played around with having the client app issue
SET of connection_collation, server_collation, and some
other system variables. I don't know if this is even the right approach and
what to set which variable to.

Thanks for any advice you can give,

Jim Cramer
University of Iowa

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=myo...@freebsd.csie.nctu.edu.tw

[Vlad Shalnev]

Hi

Try this query

select * from mysql.db where db = database() collate utf8_bin;

Jim Cramer wrote:
> Hi,
>
> With MySql 4.1.10a, I am using a commerial app (Advanced Query Tool)
> to query and manage the server and databases in it.
>
> While performing one of its functions, the app issues the query
> " select * from msql.db where db=database()
>
> This query give the error:
> "HYT00(1267) Illegal mix of collations (utf8_bin,IMPLICIT) and
> (utf8_general_ci,IMPLICIT) for operation '=' "
>
> This is because the mysql database db table is set to utf8_bin collation but
> the function "database()" returns a result that is in utf8_general_ci
> collation,
> and the comparison of them with the "=" operator is incompatible.
>
> Can anybody tell me what to do to make this not happen?
> How can I set the collation of information functions like database()
> (in this case to utf_bin to match the mysql.db column)?
>
> I have played around with having the client app issue
> SET of connection_collation, server_collation, and some
> other system variables. I don't know if this is even the right approach and
> what to set which variable to.
>
> Thanks for any advice you can give,
>
> Jim Cramer
> University of Iowa
>
>
>
>

--
--------------------------------------------------------------------------------
Vlad A. Shalnev
E-mail: vl...@asv.ru

"Gravity can't be blamed
for someone
falling in love"

( Albert Einstein )

[Gleb Paharenko]

Hello.

I suggest you to switch to 4.1.11. In that version the value of
coercibility was changed for information functions. See:

http://dev.mysql.com/doc/mysql/en/charset-collate-tricky.html

Everything works for me on 4.1.11:

mysql> select * from mysql.db where db=database();
Empty set (0.00 sec)

mysql> show variables like '%coll%';
+----------------------+----------+
| Variable_name | Value |
+----------------------+----------+
| collation_connection | utf8_bin |
| collation_database | utf8_bin |
| collation_server | utf8_bin |
+----------------------+----------+
3 rows in set (0.01 sec)

"Jim Cramer" <jim-c...@uiowa.edu> wrote:
> Hi,
>
> With MySql 4.1.10a, I am using a commerial app (Advanced Query Tool)
> to query and manage the server and databases in it.
>
> While performing one of its functions, the app issues the query
> " select * from msql.db where db=database()
>
> This query give the error:
> "HYT00(1267) Illegal mix of collations (utf8_bin,IMPLICIT) and
> (utf8_general_ci,IMPLICIT) for operation '=' "
>
> This is because the mysql database db table is set to utf8_bin collation but
> the function "database()" returns a result that is in utf8_general_ci
> collation,
> and the comparison of them with the "=" operator is incompatible.
>
> Can anybody tell me what to do to make this not happen?
> How can I set the collation of information functions like database()
> (in this case to utf_bin to match the mysql.db column)?
>
> I have played around with having the client app issue
> SET of connection_collation, server_collation, and some
> other system variables. I don't know if this is even the right approach and
> what to set which variable to.
>
> Thanks for any advice you can give,
>
> Jim Cramer
> University of Iowa
>
>
>
>

--
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.Pa...@ensita.net
/_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET
<___/ www.mysql.com