Closed
Description
Describe the bug
We're getting the following error when connecting to our mysql 8 database with the standard collation utf8mb4_0900_ai_ci for utf8mb4:
Illegal mix of collations (utf8mb4_0900_ai_ci,IMPLICIT) and (utf8mb4_general_ci,IMPLICIT) for operation '='
To Reproduce
- Create a simple table with a varchar column on a standard mysql 8 database
- Run query:
set @test = 'test';
select * from table where col_name = @test;
This does work, but it shouldn't be necessary to specify the collation:
set @test = 'test' collate 'utf8mb4_0900_ai_ci';
select * from table where col_name = @test;
Execute this query using pymysql. Since the default charset used by pymysql is utf8mb4_general_ci, you'll get the error mentioned above.
Expected behavior
The query should either work because the default collation used by pymysql matches the default collation of mysql, or it should be possible to specify the collation (or encoding) when connecting.
The current behavior in the code is to use the default: https://github.com/PyMySQL/PyMySQL/blob/main/pymysql/connections.py#L307-L310
Environment
- Server and version: 8.0.27-18.1 - Percona XtraDB Cluster
- PyMySQL version: 0.7.4
Metadata
Metadata
Assignees
Labels
No labels