MySQL: rust enum not compatible with SQL ENUM

[alfonso-eusebio]

Hi,

I've hit an issue with ENUMs in MySQL and, after reviewing docs and issues here, I can't find anything that helps with this particular problem. Basically, the error says that type 'ENUM' is not compatible with type 'ENUM':

error occurred while decoding column \"kind\": mismatched types; Rust type `...::IssueKind` (as SQL type `ENUM`) is not compatible with SQL type `ENUM`

The relevant (abridged) code is this:

#[derive(sqlx::FromRow)]
pub struct Issue {
    pub id: IssueId,
    pub env_id: EnvId,
    pub date: Date,
    pub kind: IssueKind,
}


#[derive(sqlx::Type)]
#[sqlx(rename_all = "snake_case")]
pub enum IssueKind {
    TypeOne,
    TypeTwo,
    TypeThree,
}


pub async fn find_by_env_date(env_id: EnvId, date: Date) -> Result<Vec<Issue>> {
    Ok(
        sqlx::query_as::<_, Issue>("SELECT * FROM `issue` WHERE `env_id` = ? AND `date` = ?")
            .bind(&env_id)
            .bind(&date)
            .fetch_all(&ConnPool::clone())
            .await?,
    )
}

The DB schema looks like this (again, abridged):

CREATE TABLE `issue` (
  `id` int(11) UNSIGNED NOT NULL,
  `env_id` int(11) UNSIGNED NOT NULL,
  `date` date NOT NULL,
  `kind` enum('type_one','type_two','type_three') NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

This was happening with v0.5.1 and is still happening after upgrade to v.0.5.4

I'm guessing that I have hit a fairly obscure issue, or that I'm missing something pretty basic.

Any help would be appreciated.

Regards,
Alfonso

[alfonso-eusebio]

Ok, I think I know where the issue comes from.

In sqlx the ENUM type is defined as having char_set 63 (binary), which is what the compatible() method is testing against.
My ENUMs have a charset/collation of utf8mb4_general_ci, the database default; and that's what I think is causing the compatibility check to fail.

However, if I change the column's collation to binary (type still ENUM) then the error is still there, but different:

Rust type `...::IssueKind` (as SQL type `ENUM`) is not compatible with SQL type `BINARY`

I'm using MariaDB 10.5.10 - not sure if that's the reason for sqlx no getting the data it expects.

I hope this helps somewhat.

Regards,
Alfonso

[euphbriggs]

I ran into a similar error message last week. The solution to my issue was to specify the Rust enum type in the query. My column was also named kind. Would something similar work for you?

SELECT
    actual_start_time    `actual_start_time: chrono::DateTime<chrono::Utc>`,
    business_date,
    end_time             `end_time: chrono::DateTime<chrono::Utc>`,
    failed_stores,
    initiated_by,
    kind                 `kind: crate::tables::batch::Kind`,
    scheduled_start_time `scheduled_start_time: chrono::DateTime<chrono::Utc>`,
    scheduled_stores,
    status,
    system_comment,
    uid                  `uid: u64`,
    user_comment
FROM batches
WHERE end_time IS NULL
  AND initiated_by != ?;

Edit: Including the definition of Kind in case it's helpful.

#[derive(Clone, Debug, Deserialize, PartialEq, Serialize, sqlx::Type)]
pub enum Kind {
    #[sqlx(rename = "closed")]
    Closed,
    #[sqlx(rename = "custom")]
    Custom,
    #[sqlx(rename = "open")]
    Open,
    #[sqlx(rename = "process")]
    Process,
}

[alfonso-eusebio]

Thanks for your input @euphbriggs!
However, I believe that syntax is only usable in query_as!() macro, not so much in the method.

From the errors that I'm getting it seems to me that sqlx is identifying the rust type fine, but there is a mismatch between the ENUM config for that type and the one for the column coming from the DB.

[alfonso-eusebio]

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

[amitavaghosh1]

Also faced similar issue:

 ColumnDecode { index: "\"template_type\"", source: "mismatched types; Rust type `template::app::model::TemplateType` (as SQL type `ENUM`) is not compatible with SQL type `ENUM`" }

#[derive(Serialize, Deserialize, Clone, Debug, EnumString, ToString, Type)]
// #[sqlx(rename="template_type", rename_all = "snake_case")] 
pub enum TemplateType {
    Email,
    Pns
}


#[derive(Serialize, Deserialize, Clone, Debug)]
pub struct Template {
    pub tname: String,
    pub template_type: TemplateType,
    pub created_at: DateTime::<Utc>
}

One way to solve this is to overwrite the FromRow trait as such.

impl <'r>FromRow<'r, MySqlRow> for Template {
    fn from_row(row: &'r MySqlRow) -> Result<Self, MysqlError> {
        let tt: String = row.try_get("template_type")?;

        let template_type = match TemplateType::from_str(&tt) {
                Ok(t) => t,
                Err(_) => TemplateType::Email,
        };

        return Ok(Template{
            tname: row.try_get("tname")?,
            template_type: template_type,
            created_at: row.try_get("created_at")?
        });
    }
}

The to_string and from_str are methods from strum package, which converts enums to string and vice versa.

[alfonso-eusebio]

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

[amitavaghosh1]

Right, but the problem with using query_as is it's dependent on one DATABASE_URL , but most of the time there are multiple database connections. Encoding type could be a issue, Not sure.
Once I write a library such as this, probably will understand the issue better enough to contribute

[kraigher]

I think I am running into the same problem when I am using query_as (The function not the macro).
I tried to use derive(sqlx::Type) on my MySQL ENUM type but got errors at runtime such as:

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

Is this supposed to work or am I doing something wrong? I would be really convenient if it worked as it avoids a lot of boilerplate for manual trait implementation.

[ittorchbearer]

Following the in code comments saying this page was followed for ColumnType, the page points out that the charset is dynamic and not hard coded to binary 63 but that page is misleading around what ColumnType will be sent if you follow the linked page shows that even thought enum is it's own type, behind the scene the enum is mapped to the string protocol before being sent to sqlx. That lead to sqlx receiving r@#type of ColummType::String for enums. Here the docs outline that Enums will have the Enum flag as well.

I pulled the repo, and made the following changes then it started working just fine for me.

sqlx/sqlx-core/src/mysql/type_info.rs new hard coded values to match my database

#[doc(hidden)]
    pub const fn __enum() -> Self {
        Self {
            r#type: ColumnType::String, //[I belive this is always correct](https://dev.mysql.com/doc/internals/en/binary-protocol-value.html)
            flags: ColumnFlags::ENUM, //[I belive this is always correct ](https://dev.mysql.com/doc/c-api/8.0/en/c-api-data-structures.html)
            char_set: 224,   //[I belive this may need to be done dynamically, or change db charset](https://dev.mysql.com/doc/refman/8.0/en/charset-mysql.html)
            max_size: None,
        }
    }

Cargo.toml path may differ

[patch.crates-io]
sqlx = { path = '_sqlx_' }

As a secondary note for others trying to make enums work, I had to force the Enum conversion for query_as to work: status as `status: InboxStatus with status being the column and InboxStatus being the Enum. Best docs I found on it were in the tests here.

reproduction: docker image: mysql:8.0.21 with sqlx 0.5.9

[khumps]

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?

[ilyvion]

@khumps Considering that this issue has received no input from the crate maintainers in the two years it's been here, I have a feeling it's not a priority to get this particular thing working right.

EDIT: That's not meant as criticism, btw; I realize that a popular project with ~400 open issues has to prioritize. 😄

[sampullman]

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?

[botahamec]

I think this is the issue I just had. I kept getting this error:

Database(MySqlDatabaseError { code: Some("HY000"), number: 1210, message: "Incorrect arguments to mysqld_stmt_execute" })', src\api\clients.rs:242:51

And after at least an hour of debugging, I narrowed it down to one of my enums not being properly converted into a string. The workaround I went with was to implement Display on this enum, and call to_string on it.

query!(
	r"INSERT INTO clients (id, alias, type, secret_hash, secret_salt, secret_version, allowed_scopes, default_scopes, trusted)
	               VALUES ( ?,     ?,    ?,           ?,           ?,              ?,              ?,              ?,       ?)",
	client.id(),
	client.alias(),
	client.client_type().to_string(),
	client.secret_hash(),
	client.secret_salt(),
	client.secret_version(),
	client.allowed_scopes(),
	client.default_scopes(),
	client.is_trusted()
)
.execute(transaction.as_mut())
.await?;

/// There are two types of clients, based on their ability to maintain the
/// security of their client credentials.
#[derive(Debug, Clone, Copy, PartialEq, Eq, Hash, Serialize, Deserialize, sqlx::Type)]
#[sqlx(rename_all = "lowercase")]
#[serde(rename_all = "SCREAMING_SNAKE_CASE")]
pub enum ClientType {
	/// A client that is capable of maintaining the confidentiality of their
	/// credentials, or capable of secure client authentication using other
	/// means. An example would be a secure server with restricted access to
	/// the client credentials.
	Confidential,
	/// A client that is incapable of maintaining the confidentiality of their
	/// credentials and cannot authenticate securely by any other means, such
	/// as an installed application, or a web-browser based application.
	Public,
}

impl Display for ClientType {
	fn fmt(&self, f: &mut std::fmt::Formatter<'_>) -> std::fmt::Result {
		f.write_str(match self {
			Self::Confidential => "confidential",
			Self::Public => "public",
		})
	}
}

[alu]

@botahamec

I have a similar problem.

I think the problem releated to uncompartible change of mysql 8.0.22 SEE.
I've tested mysql 8.0.21 and 8.0.22 and had no issues with 8.0.21.

The problem have happen on several liblary such as node-mysql2, luadbi, and This discusstion is same.

They say a parameter used in a prepared statement has its type determined when the statement is first prepared but we cannot know what the determined type is.
Currently all we can do is convert it to string before bind.

Another idea would be to implement sqlx::Type on an enum, but since all the fields of MySqlTypeInfo are private, it is not possible to implement type_info().

[botahamec]

@alu That is useful information. I did eventually find a way to do it without calling the to_string method, which was needed to pull the data out from the database, but it's very verbose. If it helps, here's what I did. I hope I don't ever have to do this again though.

/// There are two types of clients, based on their ability to maintain the
/// security of their client credentials.
#[derive(Debug, Clone, Copy, PartialEq, Eq, Hash, Serialize, Deserialize)]
#[serde(rename_all = "SCREAMING_SNAKE_CASE")]
pub enum ClientType {
	/// A client that is capable of maintaining the confidentiality of their
	/// credentials, or capable of secure client authentication using other
	/// means. An example would be a secure server with restricted access to
	/// the client credentials.
	Confidential,
	/// A client that is incapable of maintaining the confidentiality of their
	/// credentials and cannot authenticate securely by any other means, such
	/// as an installed application, or a web-browser based application.
	Public,
}

impl Display for ClientType {
	fn fmt(&self, f: &mut std::fmt::Formatter<'_>) -> std::fmt::Result {
		f.write_str(match self {
			Self::Confidential => "confidential",
			Self::Public => "public",
		})
	}
}

impl FromStr for ClientType {
	type Err = ();

	fn from_str(s: &str) -> Result<Self, Self::Err> {
		match s {
			"confidential" => Ok(Self::Confidential),
			"public" => Ok(Self::Public),
			_ => Err(()),
		}
	}
}

impl sqlx::Type<MySql> for ClientType {
	fn type_info() -> MySqlTypeInfo {
		<str as sqlx::Type<MySql>>::type_info()
	}
}

impl sqlx::Encode<'_, MySql> for ClientType {
	fn encode_by_ref(
		&self,
		buf: &mut <MySql as sqlx::database::HasArguments<'_>>::ArgumentBuffer,
	) -> sqlx::encode::IsNull {
		<String as sqlx::Encode<MySql>>::encode_by_ref(&self.to_string(), buf)
	}
}

impl sqlx::Decode<'_, MySql> for ClientType {
	fn decode(
		value: <MySql as sqlx::database::HasValueRef<'_>>::ValueRef,
	) -> Result<Self, sqlx::error::BoxDynError> {
		<&str as sqlx::Decode<MySql>>::decode(value).map(|s| s.parse().unwrap())
	}
}

impl From<String> for ClientType {
	fn from(value: String) -> Self {
		// TODO banish this abomination back to the shadows from whence it came
		value.parse().unwrap()
	}
}

[alu]

@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);

[jvliwanag]

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")"

[callumbirks]

If anyone else is still struggling with this, I made a macro which implements the same things #[derive(sqlx::Type)] does, using std::mem::transmute:

/// Implement `sqlx::Type`, `sqlx::Encode` and `sqlx::Decode` for values that can be safely
/// transmuted to another `sqlx::Type`, but can't use `#[derive(sqlx::Type)]` i.e. Nested enums.
#[macro_export]
macro_rules! impl_sqlx_type {
    (<$db:ty> $in_ty:ty as $out_ty:ty) => {
        impl sqlx::Type<$db> for $in_ty {
            fn type_info() -> <$db as sqlx::Database>::TypeInfo {
                <$out_ty as sqlx::Type<$db>>::type_info()
            }

            fn compatible(ty: &<$db as sqlx::Database>::TypeInfo) -> bool {
                <$out_ty as sqlx::Type<$db>>::compatible(ty)
            }
        }

        impl sqlx::Encode<'_, $db> for $in_ty {
            fn encode_by_ref(&self, buf: &mut <$db as HasArguments<'_>>::ArgumentBuffer) -> IsNull {
                #[allow(clippy::transmute_ptr_to_ptr)]
                let out: &$out_ty = unsafe { std::mem::transmute(self) };
                <$out_ty as sqlx::Encode<$db>>::encode_by_ref(out, buf)
            }
        }

        impl sqlx::Decode<'_, $db> for $in_ty {
            fn decode(value: <$db as HasValueRef<'_>>::ValueRef) -> Result<Self, BoxDynError> {
                <$out_ty as sqlx::Decode<$db>>::decode(value)
                    .map(|v| unsafe { std::mem::transmute(v) })
            }
        }
    };
}

Usage is i.e.:

impl_sqlx_type!(<Sqlite> EventType as u32);

In this case, EventType is a nested enum:

#[derive(Debug, Copy, Clone, Eq, PartialEq)]
#[repr(u16)]
pub enum EventType {
    Common(CommonEvent),
    DB(DBEvent),
}

Where the types nested within it are also enums:

#[derive(sqlx::Type, Debug, Copy, Clone, Eq, PartialEq)]
#[repr(u16)]
pub enum CommonEvent {
    Created,
    Destroyed,
}

This is only useful if your type can be safely transmuted to another type which already implements sqlx::Type.