| Bug #101346 | "Illegal mix of collations" errors when matching ascii field and unicode | ||
|---|---|---|---|
| Submitted: | 27 Oct 2020 16:05 | Modified: | 30 Oct 2020 17:28 |
| Reporter: | Perlover Perlovovich | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: Prepared statements | Severity: | S3 (Non-critical) |
| Version: | 8.0.22 | OS: | Ubuntu (18.04) |
| Assigned to: | CPU Architecture: | x86 | |
| Tags: | ASCII, collation, repertoire, Unicode, utf8, utf8mb4 | ||
[27 Oct 2020 16:05]
Perlover Perlovovich
[27 Oct 2020 16:26]
Perlover Perlovovich
P.S. This problem is reproducible only through the mysql2 module (to be more precise - I cannot reproduce it through the mysql CLI for example) And more precisely, for cloning the repository: git clone -b perlover-ascii-collation 'https://github.com/Perlover/node-mysql2.git'
[27 Oct 2020 22:04]
MySQL Verification Team
Hi,
I'd normally not verify this as
1. executing this trough mysql CLI works ok
2. executing this trough few other clients works ok
But, since your example does pass on the .21 and does not on .22 there might be something in .22 that's a problem so I'll let our connector's team check this out. I believe it has more to do with connectors than with character sets.
all best
Bogdan
[0:00:22 2 31/108 30.6% node test/integration/connection/test-execute-bind-ascii.js]
/home/arhimed/bug101346/x/node-mysql2/test/integration/connection/test-execute-bind-ascii.js:21
throw err;
^
Error: Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8mb4_unicode_ci,COERCIBLE) for operation '='
at Packet.asError (/home/arhimed/bug101346/x/node-mysql2/lib/packets/packet.js:712:17)
at Execute.execute (/home/arhimed/bug101346/x/node-mysql2/lib/commands/command.js:28:26)
at Connection.handlePacket (/home/arhimed/bug101346/x/node-mysql2/lib/connection.js:425:32)
at PacketParser.onPacket (/home/arhimed/bug101346/x/node-mysql2/lib/connection.js:75:12)
at PacketParser.executeStart (/home/arhimed/bug101346/x/node-mysql2/lib/packet_parser.js:75:16)
at Socket.<anonymous> (/home/arhimed/bug101346/x/node-mysql2/lib/connection.js:82:25)
at Socket.emit (events.js:310:20)
at addChunk (_stream_readable.js:286:12)
at readableAddChunk (_stream_readable.js:268:9)
at Socket.Readable.push (_stream_readable.js:209:10) {
code: 'ER_CANT_AGGREGATE_2COLLATIONS',
errno: 1267,
sqlState: 'HY000',
sqlMessage: "Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8mb4_unicode_ci,COERCIBLE) for operation '='"
}
[28 Oct 2020 0:59]
Perlover Perlovovich
And I noticed that this error happens in the "execute" but a "query" methods. A difference is: https://stackoverflow.com/a/53219297 I think this problem is one of MySQL not node module and related with prepare statements at server side.
[28 Oct 2020 10:01]
MySQL Verification Team
Just one update to understand things properly - this connector is community project, not MySQL made - this connector uses classic mysql protocol but native nodejs, not using mysqlclient library - the MySQL's nodejs connector uses X protocol only Using other mysql clients we cannot reproduce this, so it is def. a bug in the node-mysql2 connector (not our project). We do want to help and will try to find out why this fails with .22 but the mysql as is works flawlesly. all best Bogdan
[28 Oct 2020 13:32]
Perlover Perlovovich
Let's say this is a Node.JS module problem. But the error (ER_CANT_AGGREGATE_2COLLATIONS) is being thrown by your server, and as far as I understand, it shouldn't have happened because string comparison of ascii charset and unicode/ascii strings will give TRUE/FALSE result in any cases due to "Repertoire" (https://dev.mysql.com/doc/refman/8.0/en/charset-repertoire.html). Am I right?
[28 Oct 2020 13:37]
Perlover Perlovovich
Changes from 8.0.21 to 8.0.22 were related with prepare statements (i see this problem only when MySQL's prepare statements are used by node-mysql2 module) https://dev.mysql.com/doc/relnotes/mysql/8.0/en/news-8-0-22.html#mysqld-8-0-22-optimizer It seems that the prepares statments turn off the Repertoire mechanism, which, as I understand it, should work inside of MySQL and is not regulated in any way by the "client" throught optoins/varaiables and etc.
[28 Oct 2020 14:04]
MySQL Verification Team
Hi, A lot of changes between .21 and .22 and our team is already inspecting what could cause this, but, I do a test with CAPI with server side prepare statement and I don't see a bug. I do prepare statement from nodejs connector made by us and I don't see a bug, so it's not that simple, it is not only PS on server. There's a combination that's a problem. Now, I can't prejudice what's going on exactly but we have both server team and nodejs connector team checking this out so I assume we'll soon come to some resolution :) all best Bogdan
[28 Oct 2020 14:21]
MySQL Verification Team
Hi, closing the bug on the mysql side as we tested all other connectors and every single one works ok (c, python, java, nodejs..) so there's something node2 is missing that might be passable before but is not any more. Our team will engage the original project maintainer trough https://github.com/sidorares/node-mysql2/issues/1237 and https://github.com/sidorares/node-mysql2/pull/1235 to see if we can help, but this is def. not a bug on our side all best Bogdan
[28 Oct 2020 19:33]
MySQL Verification Team
Hi,
One note to be sure you understand about the create you are using here:
> CREATE TABLE IF NOT EXISTS asciitest (a char (64) ascii not null, b int unsigned not null) character set utf8
the "a char (64) ascii"
is not same as "a char (64) character set ascii"
the "a char (64) ascii" is same as "a char (64) character set latin1"
This is remnance from the past that can be rather confusing.
e.g.
mysql [localhost:8022] {root} (test) > CREATE TABLE IF NOT
-> EXISTS asciitest (a char (64) ascii not null, b int unsigned not null)
-> character set utf8;
Query OK, 0 rows affected, 1 warning (0.01 sec)
mysql [localhost:8022] {root} (test) > show create table asciitest\G
*************************** 1. row ***************************
Table: asciitest
Create Table: CREATE TABLE `asciitest` (
`a` char(64) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL,
`b` int unsigned NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
mysql [localhost:8022] {root} (test) >
[28 Oct 2020 19:35]
MySQL Verification Team
or even better example:
mysql [localhost:8022] {root} (test) > create table t1 (c1 varchar(10) ascii, c2 varchar(10) character set ascii);
Query OK, 0 rows affected (0.01 sec)
mysql [localhost:8022] {root} (test) > show create table t1\G
*************************** 1. row ***************************
Table: t1
Create Table: CREATE TABLE `t1` (
`c1` varchar(10) CHARACTER SET latin1 COLLATE latin1_swedish_ci DEFAULT NULL,
`c2` varchar(10) CHARACTER SET ascii COLLATE ascii_general_ci DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)
mysql [localhost:8022] {root} (test) >
[28 Oct 2020 23:45]
MySQL Verification Team
minimal test case
Attachment: bug101346.tar.xz (application/octet-stream, text), 1.05 KiB.
[29 Oct 2020 11:15]
MySQL Verification Team
Hi, Reopening this bug, it is not connector related. mysql> DROP TABLE IF EXISTS t1; Query OK, 0 rows affected (0.01 sec) mysql> CREATE TABLE t1 (a char (64) character set latin1, b int unsigned); Query OK, 0 rows affected (0.02 sec) mysql> PREPARE s1 FROM 'SELECT a FROM t1 WHERE a=? AND b=?'; Query OK, 0 rows affected (0.00 sec) Statement prepared mysql> SET @a = 'test'; Query OK, 0 rows affected (0.00 sec) mysql> SET @b = 2; Query OK, 0 rows affected (0.00 sec) mysql> EXECUTE s1 USING @a, @b; ERROR 1267 (HY000): Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8mb4_0900_ai_ci,COERCIBLE) for operation '=' kind regards Bogdan
[29 Oct 2020 11:26]
Perlover Perlovovich
Hi, > the "a char (64) ascii" > is not same as "a char (64) character set ascii" > the "a char (64) ascii" is same as "a char (64) character set latin1" Yes, it's my mistake. The original my code where I first time caught this error had: field_name CHAR (2) CHARSET ascii NOT NULL In such a random way, it turned out that my typo did not greatly affect the result and quality of the test - the bug will still be detected by it. Thanks for pointing out this inaccuracy - I did not know about this feature! ;-) From Mysql 8.0 docs: "...The ASCII attribute is shorthand for CHARACTER SET latin1..."
[29 Oct 2020 11:29]
Perlover Perlovovich
I'm glad the problem was reproduced. Hope to fix it as soon as possible. True, I have rolled back to MySQL 5.7 so far, but when the problem will be fixed - I'm ready to return to MySQL 8.0.22. Unfortunately I couldn't rollback to 8.0.21 because the official MySQL repository did not have version 8.0.21 after upgrading to 8.0.22. :(
[30 Oct 2020 14:41]
Steven Azzopardi
OS: Centos 7 & 8 Verified issue is not present in 8.0.21 but introduced in 8.0.22 with changes for prepared statements, replicated this issue via following steps: DROP TABLE testtable; CREATE TABLE testtable(id INT PRIMARY KEY, testcolumn VARCHAR(5)) DEFAULT CHARSET=latin1; INSERT INTO testtable(id, testcolumn) VALUES(1, 'abc'), (2, 'bca'), (3, 'bcd'); PREPARE stmt1 FROM 'select count(*) from testtable where (? is null or testcolumn = ?)'; SET @a = 0; SET @b = 'bca'; EXECUTE stmt1 USING @a,@b; PREPARE stmt1 FROM 'select count(*) from testtable where (? is null or testcolumn = ?)'; SET @a = b'0'; SET @b = 'bca'; EXECUTE stmt1 USING @a,@b; PREPARE stmt1 FROM 'select count(*) from testtable where (? is null or testcolumn = ?)'; SET @a = 'bcd'; SET @b = 'bca'; EXECUTE stmt1 USING @a,@b; --------------- Query: create table testtable(id int primary key, testcolumn varchar(5)) default charset=latin1 0 row(s) affected Execution Time : 0.021 sec Transfer Time : 1.007 sec Total Time : 1.028 sec -------------------------------------------------- Query: insert into testtable(id, testcolumn) values(1, 'abc'), (2, 'bca'), (3, 'bcd') 3 row(s) affected Execution Time : 0.009 sec Transfer Time : 0.004 sec Total Time : 0.013 sec -------------------------------------------------- Query: prepare stmt1 from 'select count(*) from testtable where (? is null or testcolumn = ?)' 0 row(s) affected Execution Time : 0.010 sec Transfer Time : 0.002 sec Total Time : 0.012 sec -------------------------------------------------- Query: set @a = 0 0 row(s) affected Execution Time : 0.011 sec Transfer Time : 0.002 sec Total Time : 0.013 sec -------------------------------------------------- Query: set @b = 'bca' 0 row(s) affected Execution Time : 0.009 sec Transfer Time : 0.002 sec Total Time : 0.011 sec -------------------------------------------------- Query: execute stmt1 using @a,@b Error Code: 1267 Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE) for operation '=' Execution Time : 0 sec Transfer Time : 0 sec Total Time : 0.009 sec -------------------------------------------------- Query: PREPARE stmt1 FROM 'select count(*) from testtable where (? is null or testcolumn = ?)' 0 row(s) affected Execution Time : 0.016 sec Transfer Time : 0.003 sec Total Time : 0.019 sec -------------------------------------------------- Query: SET @a = b'0' 0 row(s) affected Execution Time : 0.010 sec Transfer Time : 0.002 sec Total Time : 0.013 sec -------------------------------------------------- Query: SET @b = 'bca' 0 row(s) affected Execution Time : 0.008 sec Transfer Time : 0.002 sec Total Time : 0.010 sec -------------------------------------------------- Query: EXECUTE stmt1 USING @a,@b 1 row(s) affected Execution Time : 0.010 sec Transfer Time : 0.002 sec Total Time : 0.013 sec -------------------------------------------------- Query: PREPARE stmt1 FROM 'select count(*) from testtable where (? is null or testcolumn = ?)' 0 row(s) affected Execution Time : 0.007 sec Transfer Time : 0.002 sec Total Time : 0.010 sec -------------------------------------------------- Query: SET @a = 'bcd' 0 row(s) affected Execution Time : 0.008 sec Transfer Time : 0.002 sec Total Time : 0.010 sec -------------------------------------------------- Query: SET @b = 'bca' 0 row(s) affected Execution Time : 0.007 sec Transfer Time : 0.003 sec Total Time : 0.011 sec -------------------------------------------------- Query: EXECUTE stmt1 USING @a,@b 1 row(s) affected Execution Time : 0.009 sec Transfer Time : 0.003 sec Total Time : 0.012 sec
[30 Oct 2020 17:28]
Paul DuBois
Posted by developer: Fixed in 8.0.23. For prepared statements, "illegal mix of collations" errors could occur for legal collation mixes.
[11 Mar 2021 16:19]
Uziel Sulkies
I'm not sure I understand the last comment: Is it still expected to happen for prepared statements on 8.0.23, after the fix (I experience it)? Is there a workaround for the problem?
[11 Mar 2021 19:12]
Roy Lyseng
You may have encountered an unrelated problem. Do you have a test case for it?
[11 Mar 2021 19:42]
Trey Raymond
could be. reproduce on any install via system db with a function like `select * from mysql.user u where u.host=SUBSTRING_INDEX(CURRENT_USER(),'@',-1);`
[12 Mar 2021 0:01]
Roy Lyseng
This is a different problem, it will be fixed in the next release.
There is a workaround possible: wrap the ASCII expression in a CAST, like:
select * from mysql.user u
where CAST(u.host AS CHAR CHARACTER SET utf8) =
SUBSTRING_INDEX(CURRENT_USER(),'@',-1);
[12 Mar 2021 17:03]
Trey Raymond
any idea on a workaround server side, which would solve it for all queries? I haven't found one yet, so we're stuck on .21 for now. failing that, you mentioned it'll be fixed in .24, got a git link? maybe we can backport the patch
[15 Apr 2021 20:08]
Eimantas Jatkonis
Does this error has other Bug#? mysql> select * from mysql.user u where u.host=SUBSTRING_INDEX(CURRENT_USER(),'@',-1); ERROR 1267 (HY000): Illegal mix of collations (ascii_general_ci,IMPLICIT) and (utf8_general_ci,SYSCONST) for operation '=' Have similar problem with latin1 table and SUBSTRING(CURRENT_USER()...) comparison.
[25 Nov 2021 5:38]
Aye Thinzar Oo
"code":"ER_CANT_AGGREGATE_NCOLLATIONS","errno":1271,"sqlState":"HY000","sqlMessage":"Illegal mix of collations for operation 'UNION'","sql":" and ER_CANT_AGGREGATE_2COLLATIONS","errno":1267,"sqlState":"HY000","sqlMessage":"Illegal mix of collations (utf8mb4_0900_ai_ci,COERCIBLE) and (utf8mb4_unicode_ci,COERCIBLE) for operation how to fixed it? when call from server side and api now using mysql server -8.022.
[25 Nov 2021 7:49]
Roy Lyseng
Posted by developer: This particular problem is fixed in 8.0.23 (which now also is becoming quite old).
