ERROR 1267 (HY000): Illegal mix of collations

Question

I'm using the Percona mysql client to connect to a database, mysql Ver 8.0.15-5 for Linux on x86_64 (Source distribution)

mysql> SHOW SESSION VARIABLES LIKE 'collation_connection';
Variable_name   Value
collation_connection    utf8mb4_general_ci
mysql> call unit_create_with_dummy_users;
ERROR 1267 (HY000): Illegal mix of collations (utf8mb4_unicode_520_ci,IMPLICIT) and (utf8mb4_general_ci,IMPLICIT) for operation '='
mysql>

Unlike other error messages like Illegal mix of collations (utf8mb4_unicode_ci,IMPLICIT) and (utf8mb4_general_ci,IMPLICIT) for operation '=' I believe all the tables are of the same collation, but my collation_connection isn't? Or perhaps I have misunderstood?

Answer 1

• The CHARACTER SET and COLLATION for that Stored Procedure were established when the proc was created. See SHOW CREATE PROCEDURE unit_create_with_dummy_users.
• The charset and collation of the connection is the other factor. It is apparently different.
• The charset and collation of the table is a third factor.

Without seeing the actual statement, inside the proc, that caused the "illegal mix", we cannot advise you on the simplest way to avoid the problem.