MySQL Bugs: #91072: default_collation_for_utf8mb4 leads ERROR 1267 (HY000): Illegal mix of collation

Bug #91072	default_collation_for_utf8mb4 leads ERROR 1267 (HY000): Illegal mix of collation
Submitted:	30 May 2018 8:52	Modified:	10 May 2019 7:43
Reporter:	Tsubasa Tanaka (OCA)	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: Charsets	Severity:	S3 (Non-critical)
Version:	8.0.11	OS:	CentOS (7.4)
Assigned to:		CPU Architecture:	x86

Description:
Can't query sys.statement_analysis, which is defined under utf8mb4_0900_ai_ci in initializing, when setting default_collation_for_utf8mb4 = 'utf8mb4_general_ci'.

How to repeat:
Can reproduce by using official docker-image.

$ docker run -d -e MYSQL_ALLOW_EMPTY_PASSWORD=1 -e MYSQL_ROOT_PASSWORD="" -e MYSQL_ROOT_HOST="%" docker.io/mysql/mysql-server:8.0

$ mysql -h172.17.0.3 -uroot

mysql> SET SESSION default_collation_for_utf8mb4 = 'utf8mb4_general_ci';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> SELECT * FROM sys.statement_analysis;
ERROR 1267 (HY000): Illegal mix of collations (utf8mb4_0900_ai_ci,IMPLICIT) and (utf8mb4_general_ci,IMPLICIT) for operation '='

Hello tanaka-San,

Thank you for the report!

Thanks,
Umesh

Posted by developer:
 
Hi Tsubasa,

Are you seeing this issue in a replication context?

Hello Ståle,

> Are you seeing this issue in a replication context?

No.
I just ran docker-container freshly and connect it.

I found the SELECT query succeed when

1. Querying before SET default_collation_for_utf8mb4 (Of cource, success)
2. SET SESSION default_collation_for_utf8mb4 = 'utf8mb4_general_ci'
3. SELECT * FROM sys.statement_analysis (Success, no problem)

I can reproduce the problem by,

1. Just execute "SET SESSION default_collation_for_utf8mb4 = 'utf8mb4_general_ci'" or "SET [GLOBAL|PERSIST] default_collation_for_utf8mb4 = 'utf8mb4_general_ci'" and reconnect a client.
2. SELECT * FROM sys.statement_analysis (Failed)

Test case for 91072. (*) I confirm the code being submitted is offered under the terms of the OCA, and that I am authorized to contribute it.

Contribution: bug91072.test (application/octet-stream, text), 762 bytes.

A reproduction of the behavior not using system tables:

drop table if exists foo;
drop procedure if exists p;
set session default_collation_for_utf8mb4=utf8mb4_0900_ai_ci;
create table foo (v varchar(10));
delimiter $$
create procedure p(arg varchar(10))
deterministic
BEGIN
    select v from foo where v = arg;
END$$
delimiter ;
set session default_collation_for_utf8mb4=utf8mb4_general_ci;
call p('x');

Posted by developer:
 
The problem seem not to be related to the variable at all:

For procedures/functions to work in all contexts, the arguments need explicit character set and collation.

mysql> drop table if exists foo;
Query OK, 0 rows affected (0.01 sec)

mysql> drop procedure if exists p1;
Query OK, 0 rows affected (0.00 sec)

mysql> drop procedure if exists p2;
Query OK, 0 rows affected (0.00 sec)

mysql> create table foo (v varchar(10) collate utf8mb4_general_ci);
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter $$
mysql> create procedure p1(arg varchar(10))
    -> deterministic
    -> BEGIN
    ->     select v from foo where v = arg;
    -> END$$
Query OK, 0 rows affected (0.00 sec)

mysql> create procedure p2(arg varchar(10) charset utf8mb4 collate utf8mb4_general_ci)
    -> deterministic
    -> BEGIN
    ->     select v from foo where v = arg;
    -> END$$
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;
mysql> call p1('x');
ERROR 1267 (HY000): Illegal mix of collations (utf8mb4_general_ci,IMPLICIT) and (utf8mb4_0900_ai_ci,IMPLICIT) for operation '='
mysql> call p1('x' collate utf8mb4_general_ci);
ERROR 1267 (HY000): Illegal mix of collations (utf8mb4_general_ci,IMPLICIT) and (utf8mb4_0900_ai_ci,IMPLICIT) for operation '='
mysql> call p2('x');
Empty set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> call p2('x' collate utf8mb4_0900_ai_ci);
Empty set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> call p2('x' collate utf8mb4_general_ci);
Empty set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

The conclusion is that stored procedures/functions provided by us need to have explicit character set and collation for argumnets (and possibly for results). Some more thorough investigation is needed on this.

Posted by developer:
 
The root cause of this issue is reported as bug #28948954. But, in the interest of keeping the server going, the SYS stored programs will be fixed to include explicit collation of their attributes and return values. 
This whole "fix" will have to be reverted back when bug #28948954 is fixed.

Posted by developer:
 
Spoke too soon: since the SYS lives in a separate git repo its .sql files are reused across mysqld versions. The effort to merge this tree into the mysqld git repo is comparable to making CREATE PROCEDURE store the default collation. 
Thus closing this bug as a duplicate of the root cause: bug#28948954

Duplicate of

Bug#93256 CREATE PROCEDURE/FUNCTION should store the collation for its arguments