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:
None 
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
Description:
Hello,

After upgrade mysql-server 8.0.21 package to 8.0.22 one at Ubuntu 18.04 I started getting errors in my Node.JS scripts (i use mysql2 package). At first I started thinking it was a mysql2 module problem. But I was unable to recreate this issue with the same module versions and all dependencies on the server where the 8.0.21 package version was (more precisely - mysql-server 8.0.21-1ubuntu18.04). Both my servers have repo "http://repo.mysql.com/apt/ubuntu/".

The "buggy" server has mysql-server 8.0.22-1ubuntu18.04 version.

I wrote test for mysql2 module which has a simple commands through API:

DROP TABLE IF EXISTS asciitest;
CREATE TABLE IF NOT EXISTS asciitest (a char (64) ascii not null, b int unsigned  not null) character set utf8;
INSERT INTO asciitest (a, b) VALUES ('test', 1);
INSERT INTO asciitest (a, b) VALUES ('super', 2);
SELECT a FROM asciitest WHERE a='test' AND b=2;

In 8.0.21 I will not get errors but in 8.0.22 I get the error:

Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8mb4_unicode_ci,COERCIBLE) for operation '='

This error should not happen because the Mysql has repertoire feature which is described here: https://dev.mysql.com/doc/refman/8.0/en/charset-repertoire.html

How to repeat:
1) To install mysql-server 8.0.22-1ubuntu18.04 version from repo "http://repo.mysql.com/apt/ubuntu/"

2) To install mysql2 patched by me module from my github: https://github.com/Perlover/node-mysql2/tree/perlover-ascii-collation (there is only one new test for this)

3) To do tests as described here:

https://github.com/sidorares/node-mysql2/blob/master/Contributing.md#running-tests

You will get error like here:

/home/perlover/tmp/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/perlover/tmp/node-mysql2/lib/packets/packet.js:712:17)
      at Execute.execute (/home/perlover/tmp/node-mysql2/lib/commands/command.js:28:26)
      at Connection.handlePacket (/home/perlover/tmp/node-mysql2/lib/connection.js:425:32)
      at PacketParser.onPacket (/home/perlover/tmp/node-mysql2/lib/connection.js:75:12)
      at PacketParser.executeStart (/home/perlover/tmp/node-mysql2/lib/packet_parser.js:75:16)
      at Socket.<anonymous> (/home/perlover/tmp/node-mysql2/lib/connection.js:82:25)
      at Socket.emit (events.js:314:20)
      at addChunk (_stream_readable.js:298:12)
      at readableAddChunk (_stream_readable.js:273:9)
      at Socket.Readable.push (_stream_readable.js:214: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 '='"
  }
[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).