| Bug #66480 | Myql comparison of curtime() with a constant in utf8 | ||
|---|---|---|---|
| Submitted: | 21 Aug 2012 9:04 | Modified: | 21 Aug 2012 11:15 |
| Reporter: | Nicolas Vogel | Email Updates: | |
| Status: | Verified | Impact on me: | |
| Category: | MySQL Server: Data Types | Severity: | S3 (Non-critical) |
| Version: | 5.5.27, 5.5.28 | OS: | Any (Squeeze) |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | comparison, curtime, time, utf8 | ||
[21 Aug 2012 9:04]
Nicolas Vogel
[21 Aug 2012 9:20]
Peter Laursen
Works as expected in MySQL 5.1.63 In both cases (5.1 and 5.5) I have 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 C:\Program Files\MySQL\MySQL Server 5.5\share\charsets\ */ Peter (not a MySQL/Oracle person)
[21 Aug 2012 11:15]
Valeriy Kravchuk
Same on Mac OS X: macbook-pro:5.5 openxs$ bin/mysql -uroot test Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 33 Server version: 5.5.28-debug-log Source distribution Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> select '00:00:00' < curtime(); ERROR 1267 (HY000): Illegal mix of collations (utf8_general_ci,COERCIBLE) and (latin1_swedish_ci,NUMERIC) for operation '<' mysql> select _latin1'00:00:00' < curtime(); +-------------------------------+ | _latin1'00:00:00' < curtime() | +-------------------------------+ | 1 | +-------------------------------+ 1 row in set (0.03 sec) mysql> show variables like 'char%'; +--------------------------+---------------------------------------+ | Variable_name | Value | +--------------------------+---------------------------------------+ | character_set_client | utf8 | | character_set_connection | utf8 | | character_set_database | latin1 | | character_set_filesystem | binary | | character_set_results | utf8 | | character_set_server | latin1 | | character_set_system | utf8 | | character_sets_dir | /Users/openxs/dbs/5.5/share/charsets/ | +--------------------------+---------------------------------------+ 8 rows in set (0.20 sec) mysql> select curtime() > '00:00:00'; +------------------------+ | curtime() > '00:00:00' | +------------------------+ | 1 | +------------------------+ 1 row in set (0.16 sec) This (one way it works the other it does not) is inconsistent and, thus, is a bug.
[13 Feb 2013 17:00]
Harm Geerts
This bug seems restricted to utf8_general_ci, with utf8_unicode_ci it works fine. mysql> select version(); +-----------------------------+ | version() | +-----------------------------+ | 5.5.29-0ubuntu0.12.10.1-log | +-----------------------------+ 1 row in set (0.03 sec) mysql> show variables like '%coll%'; +----------------------+-----------------+ | Variable_name | Value | +----------------------+-----------------+ | collation_connection | utf8_general_ci | | collation_database | utf8_general_ci | | collation_server | utf8_general_ci | +----------------------+-----------------+ 3 rows in set (0.00 sec) mysql> select '00:00:00' < curtime(); ERROR 1267 (HY000): Illegal mix of collations (utf8_general_ci,COERCIBLE) and (latin1_swedish_ci,NUMERIC) for operation '<' 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) mysql> select '00:00:00' < curtime(); +------------------------+ | '00:00:00' < curtime() | +------------------------+ | 1 | +------------------------+ 1 row in set (0.00 sec)
[26 May 2015 1:59]
B W
Ran into the same issue in MySQL version 5.6.23
[21 Mar 2016 17:48]
Sebastien Lemieux
mysql> SELECT '23:59:59' <= CURRENT_TIME() COLLATE utf8_unicode_ci; ERROR 1253 (42000): COLLATION 'utf8_unicode_ci' is not valid for CHARACTER SET 'latin1' Which is weird since I used this in my my.cnf init_connect='SET collation_connection = utf8_unicode_ci' init_connect='SET NAMES utf8' character-set-server=utf8 collation-server=utf8_unicode_ci skip-character-set-client-handshake And I have this : mysql> show variables like "%character%"; show variables like "%collation%"; +--------------------------+----------------------------+ | 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) +----------------------+-----------------+ | Variable_name | Value | +----------------------+-----------------+ | collation_connection | utf8_general_ci | | collation_database | utf8_general_ci | | collation_server | utf8_unicode_ci | +----------------------+-----------------+ 3 rows in set (0.00 sec) My guess : when we run mysql functions, it uses a different char set, and we doesn't seems to have access to it's configuration anymore.
[24 Nov 2018 8:37]
Ivan Huang
Have this problem resolved? I've just bumped into this problem. Here're my queries: > SELECT * FROM t_eoms_shift WHERE start_time <= curtime() AND end_time >= curtime(); [HY000][1267] Illegal mix of collations (utf8_general_ci,IMPLICIT) and (latin1_swedish_ci,NUMERIC) for operation '<=' > show variables like 'collation%'; collation_connection | utf8_general_ci collation_database | utf8_general_ci collation_server | utf8_general_ci > show variables like 'character%'; 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 | /data/app/mysql-5.7.22/share/charsets/ > SHOW VARIABLES LIKE '%version%'; innodb_version | 5.7.22 protocol_version | 10 slave_type_conversions | "" tls_version | "TLSv1TLSv1.1" version | 5.7.22-log version_comment | MySQL Community Server (GPL) version_compile_machine | x86_64 version_compile_os | linux-glibc2.12
