WHERE without COLLATE does not work
Posted by: Martin Wyss
Date: June 13, 2012 02:51AM

I have a SELECT ... WHERE problem when not using COLLATE.

mysql> SET NAMES 'utf8';
Query OK, 0 rows affected (0.00 sec)

mysql> SET ONE_SHOT collation_connection = utf8_unicode_ci;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT sysname FROM tbl_system WHERE sysname LIKE '%mysql%';
Empty set (0.01 sec)

mysql> SELECT sysname FROM tbl_system WHERE sysname LIKE '%mysql%' COLLATE utf8_unicode_ci;
+-------------------------+
| sysname |
+-------------------------+
| mysqldata01.domain.tld |
| mysqldata02.domain.tld |
| mysqlsrv01.domain.tld |
| mysqlsrv02.domain.tld |
| mysqlsrv03.domain.tld |
| mysqldata03.domain.tld |
+-------------------------+
6 rows in set (0.02 sec)

The server variables are set to utf8:
mysql> SHOW VARIABLES LIKE '%char%';
+--------------------------+----------------------------+
| Variable_name | Value |
+--------------------------+----------------------------+
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character_set_database | utf8 |
| character_set_filesystem | binary |
| character_set_results | utf8 |
| character_set_server | utf8 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)

mysql> SHOW VARIABLES LIKE '%coll%';
+----------------------+-----------------+
| Variable_name | Value |
+----------------------+-----------------+
| collation_connection | utf8_unicode_ci |
| collation_database | utf8_unicode_ci |
| collation_server | utf8_unicode_ci |
+----------------------+-----------------+
3 rows in set (0.00 sec)

Also the db/table/colums are utf8:
mysql> SHOW TABLE STATUS WHERE Name = 'tbl_system';
+------------+------------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+-------------+-------------+------------+-----------------+----------+----------------+---------+
| Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment |
+------------+------------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+-------------+-------------+------------+-----------------+----------+----------------+---------+
| tbl_system | ndbcluster | 10 | Dynamic | 1766 | 60 | 360448 | 0 | 0 | 0 | 1767 | NULL | NULL | NULL | utf8_unicode_ci | NULL | | |
+------------+------------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+-------------+-------------+------------+-----------------+----------+----------------+---------+
1 row in set (0.01 sec)

mysql> SHOW FULL COLUMNS FROM tbl_system WHERE Field='sysname';
+---------+--------------+-----------------+------+-----+---------+-------+----------------------+---------+
| Field | Type | Collation | Null | Key | Default | Extra | Privileges | Comment |
+---------+--------------+-----------------+------+-----+---------+-------+----------------------+---------+
| sysname | varchar(255) | utf8_unicode_ci | NO | MUL | NULL | | select,insert,update | |
+---------+--------------+-----------------+------+-----+---------+-------+----------------------+---------+
1 row in set (0.01 sec)

Can I check something more, or is it not possible to use a SELECT without COLLATE in this case? The database runs on a mysql cluster:

mysql> SHOW VARIABLES LIKE '%version%';
+-------------------------+-----------------------------------+
| Variable_name | Value |
+-------------------------+-----------------------------------+
| ndbinfo_version | 459027 |
| protocol_version | 10 |
| slave_type_conversions | |
| version | 5.1.56-ndb-7.1.19-cluster-gpl-log |
| version_comment | MySQL Cluster Server (GPL) |
| version_compile_machine | x86_64 |
| version_compile_os | unknown-linux-gnu |
+-------------------------+-----------------------------------+
7 rows in set (0.00 sec)

Options: ReplyQuote


Subject
Views
Written By
Posted
WHERE without COLLATE does not work
3322
June 13, 2012 02:51AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.