Incorrect conversion from MySQL UUID string to Rust UUID struct.

[aazev]

Bug Description

Hello, I'm experiencing an issue with conversion of a UUID field when using the sqlx crate with a MySQL database.

Here's my struct:

#[derive(Debug, Serialize, Deserialize, FromRow)]
pub struct UserModel {
    pub id: u64,
    pub api_token: Uuid,
    pub name: String,
    pub email: String,
    #[serde(skip_serializing)]
    pub password: String,
    pub created_at: DateTime<Utc>,
    pub updated_at: Option<DateTime<Utc>>,
}

As you can see, api_token is of type Uuid (doesn't matter if uuid::Uuid or sqlx::types::Uuid). In MySQL, this column is stored as a string.
When retrieved using the fetch_all() method, the resulting JSON contains the same api_token value for all entries, even though they're unique in the database.

[
    {
        "id": 1,
        "api_token": "6170695f-746f-6b65-6e21-3a2055756964",
        "name": "Naruto Uzumaki",
        "email": "ramenlover_naruto@leafvillage.com",
        "created_at": "2023-05-04T00:39:12Z",
        "updated_at": null
    },
    {
        "id": 2,
        "api_token": "6170695f-746f-6b65-6e21-3a2055756964",
        "name": "Sasuke Uchiha",
        "email": "emoavenger_sasuke@shariganshadow.net",
        "created_at": "2023-05-04T01:27:34Z",
        "updated_at": null
    }
]

When changing the api_token field in my struct to String, the resulting JSON reflects the database records:

[
    {
        "id": 1,
        "api_token": "6170695f-746f-6b65-6e21-3a2055756964",
        "name": "Naruto Uzumaki",
        "email": "ramenlover_naruto@leafvillage.com",
        "created_at": "2023-05-04T00:39:12Z",
        "updated_at": null
    },
    {
        "id": 2,
        "api_token": "42387d54-8b93-4e5d-8e49-799f426d1194",
        "name": "Sasuke Uchiha",
        "email": "emoavenger_sasuke@shariganshadow.net",
        "created_at": "2023-05-04T01:27:34Z",
        "updated_at": null
    }
]

Please let me know if you need any further information or if I can assist with this issue in any way.

Thank you for your attention to this matter.

Minimal Reproduction

A small code snippet or a link to a Github repo or Gist, with instructions on reproducing the bug.
I've created a repo which replicates the issue, despite using a different struct:

#[derive(Debug, FromRow)]
pub struct UserModel {
    pub id: i32,
    pub api_token: Uuid,
    pub name: String,
    pub email: String,
}

Info

• SQLx version: 0.6.3
• SQLx features enabled: [
  "bigdecimal",
  "macros",
  "runtime-tokio-rustls",
  "json",
  "chrono",
  "decimal",
  "uuid",
  "mysql",
  "migrate",
  "tls",
  "offline",
  "ipnetwork",
  ]
• uuid version: 1.3.2
• Database server and version: MySql 8.0
• Operating system: Ubuntu 22.04
• rustc --version: rustc 1.69.0 (84c898d65 2023-04-16)

[abonander]

Part of the issue, which is not really explained at all in the documentation, is we encode the Uuid type in binary by default: https://github.com/launchbadge/sqlx/blob/v0.6.3/sqlx-core/src/mysql/types/uuid.rs#L22

And we expect it to decode the same way: https://github.com/launchbadge/sqlx/blob/main/sqlx-mysql/src/types/uuid.rs#L34

You want to use uuid::fmt::Hyphenated to encode to and decode from text. SQLx implements the appropriate traits for that type so you can just use it directly.

I'm not exactly happy with this situation as it clearly can lead to a lot of confusion, but I was not responsible for that design decision. Given the opportunity, I would probably switch this around to always encode UUIDs as text and provide a binary encoding as a deliberate alternative choice to save on storage.

But what's really weird is that I would expect this to return an error, not an arbitrary UUID. It would get a text-encoded UUID as a 36-byte slice and pass it to Uuid::from_slice(), but that returns an error if the slice is anything other than 16 bytes: https://docs.rs/uuid/1.3.2/uuid/struct.Uuid.html#method.from_slice

So I tried your repro, which, by the way, has a bug. "characters" has a double A here: https://github.com/aazev/uuid_sqlx_bug/blob/main/insert.sql#LL9C13-L9C24

On fixing that, it does reproduce the issue, but I will ask that you please make sure things like this work as-is in the future.

Anyway, I set a breakpoint at line 34 in src/sqlx-core/mysql/types/uuid.rs and was absolutely baffled to find that it was, in fact, getting a 16 byte slice:

And was further baffled upon checking the actual contents of that slice:

This sent me down a rollercoaster of emotions. Did we have some sort of memory safety or buffer handling issue that was causing part of the query string to get injected into the returned row? That would mean the MySQL driver is fundamentally broken, but wouldn't this be a more widespread problem if that was the case? And if so, why is it just that part of the query?

Then I took a closer look at the query itself:

    let rows = sqlx::query_as::<_, Character>(
        r#"SELECT id, "api_token!: Uuid" as api_token, name, email
                FROM characters"#,
    )
    .fetch_all(&pool)
    .await?;

There's three main issues at play here:

1. This is the query_as() function, not the query_as!() macro, so the type override syntax is not being processed here.
   • The query_as() function does perform runtime type checking, but the MySQL driver considers strings and byte slices to be compatible because they use the same type designations in the wire protocol, so there's not really any good way to distinguish them anyway: https://github.com/launchbadge/sqlx/blob/v0.6.3/sqlx-core/src/mysql/types/bytes.rs#L22-L23
   • If you had been using the macro, this wouldn't compile as it would insist the type is String.
2. MySQL does not use double-quotes for identifiers, it uses backticks: `foo`
   • We use double-quotes throughout our documentation because it was primarily written with Postgres in mind,
     which does use double-quotes for identifiers. However, we always try to call this out to avoid confusion, e.g.: https://docs.rs/sqlx/latest/sqlx/macro.query.html#force-a-differentcustom-type
   • However, that would have produced a syntax error in the query and it just wouldn't execute.
3. The ultimate revelation: you have the syntax backwards.

You're not decoding the api_token column here. You are decoding the string literal "api_token!: Uuid", which, in an insane coincidence, just happens to be exactly 16 bytes, so Uuid::from_slice() is just happily reinterpreting the bytes of the string as a UUID:

# `-n` excludes the trailing newline.
$ echo -n "api_token!: Uuid" | xxd -ps
6170695f746f6b656e213a2055756964

This is why it's the exact same value for every row, because the string literal is a constant in the query.

The fix is easy; switch Uuid for uuid::fmt::Hyphenated and remove the type override syntax as it's not needed:

    let rows = sqlx::query_as::<_, Character>(
        r#"SELECT id, api_token, name, email
                FROM characters"#,
    )
    .fetch_all(&pool)
    .await?;

I'm going to leave this open, however, as it clearly demonstrates several shortcomings in our documentation that need to be addressed.

[aazev]

Thanks for looking into the issue @abonander . And my apologies for the typo there >.<

That was a really detailed explanation, I wasn't aware that without the macro, the type override syntax wasn't processed. I will take more care in the future. Thanks again =)

[Kinrany]

@abonander could I ask you to summarize the current state of uuid support for MySQL?

If I'm reading the thread so far correctly (and part of the reason I'm asking for clarification is that it's hard to extract the parts that weren't repro issues), using a uuid with a string (text or varchar) MySQL column is currently expected to error.

Doesn't that mean that it'd be better to remove all the "MySql for Uuid" implementations? Or are they intended for integer columns?