MySQL Bugs: #9006: Collation conflict using IN

Bug #9006	Collation conflict using IN
Submitted:	7 Mar 2005 7:46	Modified:	10 Apr 2005 7:17
Reporter:	Emanuele Dolis	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Query Browser	Severity:	S3 (Non-critical)
Version:	1.1.5	OS:	Windows (Windows XP SP2)
Assigned to:		CPU Architecture:	Any

Description:
When using IN filter in order to test equality of a string field with a set of strings, MySQL browser throws out a conflict which doesn not appear using the same query in the command line client

How to repeat:

CREATE TABLE TMP (field1 CHAR(6))ENGINE=myIsam CHARSET=Latin1;
INSERT INTO tntna.tmp VALUES('tes'),('test')

Command line client:
mysql> SELECT field1 IN('tes') FROM tntna.tmp t;
+------------------+
| field1 IN('tes') |
+------------------+
|                1 |
|                0 |
+------------------+
2 rows in set (0.00 sec)

MySQL Query browser

Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE) for operation ' IN '

Please check the character set and collation setting in both clients:

SHOW VARIABLES LIKE 'char%'

SHOW VARIABLES LIKE 'coll%'

Woops... have I missed some configuration steps...?
The parameters are just the one set by the install itself (I have not modified them assuming that the installer provides a proper configuration for latin_swedish usage... ??)
Here is the info

from within MySQL QB:
SHOW VARIABLES LIKE 'char%'
'character_set_client', 'utf8'
'character_set_connection', 'utf8'
'character_set_database', 'latin1'
'character_set_results', 'utf8'
'character_set_server', 'latin1'
'character_set_system', 'utf8'
'character_sets_dir', 'C:\Programmi\MySQL\MySQL Server 4.1\share\charsets/'

From the command client:
mysql> SHOW VARIABLES LIKE 'char%';
+--------------------------+-----------------------------------------------------+
| Variable_name            | Value                                               |
+--------------------------+-----------------------------------------------------+
| character_set_client     | latin1                                              |
| character_set_connection | latin1                                              |
| character_set_database   | latin1                                              |
| character_set_results    | latin1                                              |
| character_set_server     | latin1                                              |
| character_set_system     | utf8                                                |
| character_sets_dir       | C:\Programmi\MySQL\MySQL Server 4.1\share\charsets/ |
+--------------------------+-----------------------------------------------------+
7 rows in set (0.39 sec)

from within MySQL QB:
SHOW VARIABLES LIKE 'coll%'

'collation_connection', 'utf8_general_ci'
'collation_database', 'latin1_swedish_ci'
'collation_server', 'latin1_swedish_ci'

from the command client:
mysql> SHOW VARIABLES LIKE 'coll%';
+----------------------+-------------------+
| Variable_name        | Value             |
+----------------------+-------------------+
| collation_connection | latin1_swedish_ci |
| collation_database   | latin1_swedish_ci |
| collation_server     | latin1_swedish_ci |
+----------------------+-------------------+
3 rows in set (0.00 sec)

If there is something I should have set up, please tell me...
Thanks

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

This was auto-suspended but appears to be an issue.  The commands "show variable like 'char%'" and "show variable like 'coll%'" show different values when issued from the command line and from QB.  This mismatch appears to be causing this error.  What is controlling those settings for the QB?

this bug is always present with QB 1.1.17.
How to change the default utf8 to latin1 in the QB ?

Bug #24273 was marked as duplicate of this one.

yes the same problem.

i think there should be a settings in QB preferences.

A little encoding fix

Attachment: MySQLBug9006.patch (text/x-patch), 1011 bytes.

i tested this patch:

OS : Linux minik 2.6.20-16-generic #2 SMP Sun Sep 23 19:50:39 UTC 2007 i686 GNU/Linux
MySQL Server: 5.0.38-Ubuntu_0ubuntu1-log

tested collations: utf8_turkish_ci, latin5_turkish_ci