SQLx CLI error: Using @variable in WHERE statement causes "Illegal mix of collations"

[LucHayward]

Bug Description

When using the sqlx CLI to run migrations on MariaDB, using variables in where statements results in a mixed collation error. This does not occur if you run the sql queries "manually".

Using the minimal example below you will see: error: while executing migrations: error returned from database: 1267 (HY000): Illegal mix of collations (utf8mb4_general_ci,IMPLICIT) and (utf8mb4_unicode_ci,IMPLICIT) for operation '='

This can be resolved by forcing the collation to be utf8mb4_unicode_ci for the given statement.

Minimal Reproduction

Place these two files into ./migrations/ 1_collate_example.up.sql

CREATE TABLE users
(
    `id`                     BIGINT AUTO_INCREMENT,
    `username`               VARCHAR(128) NOT NULL,
    PRIMARY KEY (id)
);

2_collate_example.up.sql

SET @myvar := '[email protected]';
select id from users where username = @myvar;

Assuming you have a mysql/MariaDB server setup with a user, the following will create and run our migrations.

export DATABASE_URL=mysql://zulzi:zulzi@localhost/collate-example
sqlx database drop -y
sqlx database create
sqlx migrate run

A working example for 2_collate_example.up.sql would be

SET @myvar := '[email protected]';
select id from users where username = @myvar COLLATE utf8mb4_unicode_ci;

OR

select id from users where username = '[email protected]';

Info

• SQLx version: sqlx-cli 0.7.4
• SQLx features enabled: Don't think this applies
• Database server and version: mysql from 11.3.2-MariaDB, client 15.2 for osx10.19 (arm64) using EditLine wrapper
• Operating system: macOS 14.4.1 (23E224)
• rustc --version: rustc 1.77.1 (7cf61ebde 2024-03-27)