sqlx: MySQL: rust enum not compatible with SQL ENUM

Running into this issue in 2023:

    mismatched types; Rust type `DataType` (as SQL type `ENUM`) is not compatible with SQL type `ENUM`

Version: 0.6.2

Has any progress been made on this issue?

@mitchrmiller 's discovery inspired me to come up with a relatively quiet workaround.

#[derive(Debug, sqlx::Decode, sqlx::Encode)]
#[sqlx(rename_all = "lowercase")]
enum Size {
    Small,
    Medium,
    Large,
}

impl sqlx::Type<sqlx::MySql> for Size {
    fn type_info() -> <sqlx::MySql as sqlx::Database>::TypeInfo {
        <str as sqlx::Type<sqlx::MySql>>::type_info()
    }

    fn compatible(ty: &<sqlx::MySql as sqlx::Database>::TypeInfo) -> bool {
        <str as sqlx::Type<sqlx::MySql>>::compatible(ty)
    }
}

It could also be a macro.

macro_rules! impl_enum_type {
    ($ty:ty) => {
        impl sqlx::Type<sqlx::MySql> for $ty {
            fn type_info() -> <sqlx::MySql as sqlx::Database>::TypeInfo {
                <str as sqlx::Type<sqlx::MySql>>::type_info()
            }

            fn compatible(ty: &<sqlx::MySql as sqlx::Database>::TypeInfo) -> bool {
                <str as sqlx::Type<sqlx::MySql>>::compatible(ty)
            }
        }
    };
}

impl_enum_type!(Size);

I did some more digging and this is what I can see.

The DB server (or at least my version) is sending a ColumnDefinition for the ENUM column with type=String, enum_flag=true and char_set=utf8 - the default for DB/table (these are not actual field names). In sqlx, the reference definition of an Enum type (in MySqlTypeInfo::__enum()) is type=Enum, binary_flag=true, char_set=63 (binary). Obviously, when they are compared for compatibility it fails and throws the error.

The reason that the error says “ENUM is not compatible with ENUM” is that the method that translates the column type into its name (ColumnType::name()) returns “ENUM” if type=Enum OR if type=String and enum_flag=true. So both the reference Enum column type and the one coming from the server yield “ENUM” as the name, even though they have different definitions.

Unfortunately, the method that decides if the two types are compatible checks if the two type fields are the same, which in this case are not. So the two types have a different definition but the same name. The compatibility check fails - though it shouldn’t - and it prints that two types with the same name are not compatible.

I hope this helps somebody decide what’s the correct fix for this issue. Unless my server is a very peculiar version of MariaDB, I would be surprised if this use case is working for anybody.

Cheers, Alfonso

Thank you for the workaround @amitavaghosh1

In fact, if you use the macro version (query_as!), and set the right types for your enums, the error doesn’t come up. My guess is that it uses a different logic to check compatibility between data from DB and rust types - since the check is at compile time.

However, the issue remains that, for the query_as() method, sqlx fails to correctly detect that the data coming from the DB is an enum and that it matches the type in the struct.

Regards, Alfonso

Another workaround is just to cast it as signed:

#[derive(sqlx::Type)]
#[repr(i32)]
pub enum Size {
    Small = 1,
    Medium = 2,
    Large = 3,
}

#[derive(sqlx::FromRow)]
struct Shirt {
    size: Size
}

sqlx::query_as::<_, Shirt>("select cast(size as signed) size from shirts")"

It’s not a great solution, but we use a mapper function with try_get_unchecked to get around this, and rely on integration tests to catch mistakes 😬

For example:

fn map_user_entity(row: PgRow) -> Result<UserEntity, sqlx::Error> {
    Ok(UserEntity {
        id: row.try_get("id")?,
        email: row.try_get("email")?,
        user_status: row.try_get_unchecked("user_status")?,
    })
}

let user = query
    .try_map(map_user_entity)
    .fetch_one(&self.db)
    .await?