Procedure in mysqldb fails Illegal mix of collations (utf8mb4_general_ci,IMPLICIT) and (utf8mb4_unicode_ci,IMPLICIT) for operation 'like'

Question

I am running the procedure in mysql but it fails due to the below error

ERROR 1267 (HY000): Illegal mix of collations (utf8mb4_general_ci,IMPLICIT) and (utf8mb4_unicode_ci,IMPLICIT) for operation 'like'

mysql> show PROCEDURE STATUS where name like 'PRE_PROCESSING';

+-----------------+----------------+-----------+-------------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
| Db | Name | Type | Definer | Modified | Created | Security_type | Comment | character_set_client | collation_connection | Database Collation |
+-----------------+----------------+-----------+-------------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
| main_model | PRE_PROCESSING | PROCEDURE | main_model@% | 2020-09-18 20:30:18 | 2020-09-18 20:30:18 | DEFINER | | utf8mb4 | utf8mb4_general_ci | utf8mb4_unicode_ci |
+-----------------+----------------+-----------+-------------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
    
mysql> show TABLE STATUS where name like 'Customer'; +-----------+--------+---------+------------+-------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+--------------------+----------+----------------+---------+ | 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 | +-----------+--------+---------+------------+-------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+--------------------+----------+----------------+---------+ | Customer | InnoDB |      10 | Dynamic    | 14174 |            338 |     4800512 |               0 |      3735552 |   2097152 |           NULL | 2020-09-18 20:30:17 | NULL        | NULL       | utf8mb4_unicode_ci |     NULL |                |         | +-----------+--------+---------+------------+-------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+--------------------+----------+----------------+---------+ 1 row in set (0.00 sec)
    
show variables like "%collation%";
+----------------------+--------------------+
| Variable_name | Value |
+----------------------+--------------------+
| collation_connection | utf8mb4_unicode_ci |
| collation_database | utf8mb4_unicode_ci |
| collation_server | utf8mb4_unicode_ci |
+----------------------+--------------------+

I'd use explicit collation specifying. Show the statement which causes the problem. — Akina, Nov 11 '20 at 19:02
please show the output of `show create procedure PRE_PROCESSING \G` and `show create table Customer \G` and the request you are making that causes the error. — ysth, Nov 11 '20 at 19:08

score 0 · Answer 1 · answered Nov 11 '20 at 20:28

DROP PROCEDURE ...
SET NAMES ...
CREATE PROCEDURE ...

That is, alter the procedure to be using the desired charset and COLLATION.

If this fails, then see Troubleshooting "Illegal mix of collations" error in mysql , which was previously linked as a "duplicate".

Procedure in mysqldb fails Illegal mix of collations (utf8mb4_general_ci,IMPLICIT) and (utf8mb4_unicode_ci,IMPLICIT) for operation 'like'

1 Answers1