MySql type `VARCHAR` with `*_bin` collation incorrectly interpreted as `VARBINARY` Column

[DrewMcArthur]

Is your feature request related to a problem? Please describe.
I have a column in my MySql DB: Column: FileName, Collation: utf8mb4_0900_bin, Definition: varchar(255), which when I try to convert to a struct:

#[derive(sqlx::FromRow)]
#[sqlx(rename_all = "PascalCase")]
struct Row {
  file_name: String
}

throws an error:

error occurred while decoding column "FileName": mismatched types; Rust type
  | `alloc::string::String` (as SQL type `VARCHAR`) is not compatible with SQL
  | type `VARBINARY`

Describe the solution you'd like
This conversion should work, since the actual type is a VARCHAR, not VARBINARY

Describe alternatives you've considered
I could store these variables as Vec<u8>, or manually impl FromRow, but that's obviously not ideal.

Additional context
I assume the collation is what's causing sqlx to interpret the type as VARBINARY?

[abonander]

The problem is that the only way we know the difference between Vec<u8> and String is by checking the BINARY flag: https://github.com/launchbadge/sqlx/blob/main/sqlx-mysql/src/protocol/text/column.rs#L203

This affects the macros because they use TypeInfo::compatible() to decide what Rust type to map to.

[abonander]

Alternatively, we could special-case for certain _bin collations. Ironically we'd be re-adding similar checks to those we just removed in #2652.

[DrewMcArthur]

hm. so i see on line 170 of that file

        let is_binary = flags.contains(ColumnFlags::BINARY);

but I don't think this is necessarily correct - based on 12.8.5 of the mysql docs

Binary strings (as stored using the BINARY, VARBINARY, and BLOB data types) have a character set and collation named binary. ...
Nonbinary strings (as stored using the CHAR, VARCHAR, and TEXT data types) have a character set and collation other than binary.

So it seems like VARCHAR columns with a ..._bin collation are being treated as VARBINARY since that ColumnFlags::BINARY is set. I didn't find where that comes from after a quick look, but I think all that's needed is a bit more specificity there, in order to more closely match up with what the MySQL docs are saying in what I quoted above.

This is low priority since I'm able to just manually convert String::from_utf8(Vec<u8>) for now, but this sounds like a fun problem to mess around with, if you wouldn't mind my contribution & giving me some guidance along the way!

[abonander]

So it seems like VARCHAR columns with a ..._bin collation are being treated as VARBINARY since that ColumnFlags::BINARY is set. I didn't find where that comes from after a quick look, but I think all that's needed is a bit more specificity there, in order to more closely match up with what the MySQL docs are saying in what I quoted above.

That's something I've really come to loathe about MySQL. The documentation generally runs the gamut from decent but incomplete, to misleading, to nonexistent, and nothing does what it says on the tin. We just recently addressed a similar issue that had to do with MYSQL_TYPE_ENUM not actually being the proper type designation for enums.

[abonander]

cc @alu (from #2652)

[DrewMcArthur]

@alu from your comment on #3390,

It seems to work as expected if BINARY is also accepted by compatible

I think this is the source of the issue for me. When I SHOW EXTENDED FULL FIELDS FROM File;, the column in question looks like:

Field	Type	Collation	...
FileName	varchar(255)	utf8mb4_0900_bin	...

So I think the issue is that this collation returns the BINARY flag, that doesn't mean it isn't compatible with the Rust String type. If the collation is literally binary (per my comment above), then yes it isn't compatible, but if the collation is *_bin (like this column), it should be compatible.

[alu]

@abonander @DrewMcArthur
Could these suffixes be considered interchangeable as well?

• *_ai/*_as (Accent Insensitive/ Accent Sensitive)
• *_ci/*_cs (Case Insensitive/Case Sensitive)

In other words, are they considered incompatible only if collation is binary?

[DrewMcArthur]

@alu I don't know about interchangeable per se, but they should all be compatible with Rust's String type, yes.

[DrewMcArthur]

here's another tidbit of helpful documentation: 13.3.4 The BLOB and TEXT Types

BLOB values are treated as binary strings (byte strings). They have the binary character set and collation, and comparison and sorting are based on the numeric values of the bytes in column values. TEXT values are treated as nonbinary strings (character strings). They have a character set other than binary, and values are sorted and compared based on the collation of the character set.

From this, I would think that the ColumnType::Blob wouldn't be compatible here, unless those are stand-ins for a separate (but, as the docs say, nearly identical) ColumnType::Text

If you use the BINARY attribute with a TEXT data type, the column is assigned the binary (_bin) collation of the column character set.

I assume "the BINARY attribute" refers to ColumnFlags::BINARY. So really, I think that compatible function should look more like this:

    fn compatible(ty: &MySqlTypeInfo) -> bool {
        // TODO: Support more collations being returned from SQL?
        matches!(
            ty.r#type,
            ColumnType::VarChar
                | ColumnType::Blob
                | ColumnType::TinyBlob
                | ColumnType::MediumBlob
                | ColumnType::LongBlob
                | ColumnType::String
                | ColumnType::VarString
                | ColumnType::Enum
        ) && !ty.collation == Collation::BINARY
    }

of course, this requires adding back a lot of the PR that abonander mentioned earlier, but I think removing collation from consideration conflated ColumnFlags::BINARY with Collation::BINARY. Since TEXT with a utf8_bin collation would have the ColumnFlags::BINARY flag, and would be treated as an incompatible BLOB, which isn't quite right