Bug #101575 "Illegal mix of collations" against a string
Submitted: 12 Nov 2020 8:03 Modified: 18 Nov 2020 19:26
Reporter: Marko Todorovski Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Prepared statements Severity:S3 (Non-critical)
Version:8.0.22 OS:Ubuntu (20.04)
Assigned to: CPU Architecture:Any
Tags: collation, regression

[12 Nov 2020 8:03] Marko Todorovski 
Description:
After upgrade to 8.0.22, we are getting the error
"ERROR 1267 (HY000): Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8mb4_0900_ai_ci,COERCIBLE) for operation 'like'" when comparing a string against a "latin1_swedish_ci" column for prepared statements.

Versions prior to 8.0.22 have no problems.

The problem occurs when using:
- mysql client Ver 8.0.22-0ubuntu0.20.04.2 for Linux on x86_64 ((Ubuntu));
- phpmyadmin v4.9.5;
- PHP v7.4, when "PDO::ATTR_EMULATE_PREPARES" is "false" when establishing a database connection using PDO.

How to repeat:
CREATE DATABASE db COLLATE latin1_swedish_ci;
USE db;
CREATE TABLE `tb` (
  `id` int UNSIGNED NOT NULL,
  `col1` varchar(100) NOT NULL,
  `col2` varchar(200) NOT NULL COLLATE utf8_general_ci
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
PREPARE stmt1 FROM 'SELECT * from `tb` WHERE `col1` like ? OR `col2` LIKE ?';
[12 Nov 2020 9:10] MySQL Verification Team 
Hello Marko Todorovski,

Thank you for the report and feedback.

regards,
Umesh
[18 Nov 2020 19:26] Paul DuBois 
Posted by developer:
 
Fixed in 8.0.23.

For prepared statements, "illegal mix of collations" errors could
occur for legal collation mixes.