sqlx think medium text as binary?

[yuyang-ok]

Bug Description

thread 'mysql::tests::test_rename' panicked at dbnexus-db/src/mysql.rs:1396:14: called Result::unwrap() on an Err value: error occurred while decoding column 1: mismatched types; Rust type alloc::string::String (as SQL type VARCHAR) is not compatible with SQL type BLOB

Minimal Reproduction

Info

• SQLx version: [REQUIRED]
• SQLx features enabled: [REQUIRED]

  sqlx = { version = "0.8.0", features = [
  "runtime-tokio",
  "postgres",
  "sqlite",
  "mysql",
  "bigdecimal",
  "mac_address",
  "chrono",
  "ipnetwork",
  "bit-vec",
  "uuid",
  "json",
  ] }

• Database server and version: [REQUIRED] (MySQL / Postgres / SQLite <x.y.z>) mysql 9.0.1
• Operating system: [REQUIRED]

  yuyang@yuyangdembp db % uname -a 
  Darwin yuyangdembp 23.5.0 Darwin Kernel Version 23.5.0: Wed May  1 20:09:52 PDT 2024; root:xnu-10063.121.3~5/RELEASE_X86_64 x86_64

• rustc --version: [REQUIRED]

  yuyang@yuyangdembp db % rustc --version 
  rustc 1.77.2 (25ef9e3d8 2024-04-09)

[yuyang-ok]

any sql describe table_name will have this issue.

mysql version 8 have this issue too.

[abonander]

@alu this appears related to the changes in #2652, can you advise.

[alu]

@yuyang-ok Can you tell me the schema of the table and the query you are running? I could not reproduce the problem with the following method.

$ docker run --rm -p 10000:3306 -e MYSQL_ROOT_PASSWORD=root_pass mysql:9.0.1

let pool = sqlx::MySqlPool::connect("mysql://root:root_pass@localhost:10000")
    .await
    .unwrap();

sqlx::query("CREATE DATABASE IF NOT EXISTS issue_3390_db")
    .execute(&pool)
    .await
    .unwrap();

let pool = sqlx::MySqlPool::connect("mysql://root:root_pass@localhost:10000/issue_3390_db")
    .await
    .unwrap();

sqlx::query("DROP TABLE IF EXISTS issue_3390_table")
    .execute(&pool)
    .await
    .unwrap();

sqlx::query(
    "CREATE TABLE issue_3390_table (id INT AUTO_INCREMENT PRIMARY KEY, name MEDIUMTEXT)",
)
.execute(&pool)
.await
.unwrap();

sqlx::query("INSERT INTO issue_3390_table (name) VALUES(?)")
    .bind("name")
    .execute(&pool)
    .await
    .unwrap();

#[derive(Debug, PartialEq, Eq, sqlx::FromRow)]
#[allow(unused)]
struct Row {
    id: i32,
    name: String,
}

let row: Row = sqlx::query_as("SELECT id, name FROM issue_3390_table")
    .fetch_one(&pool)
    .await
    .unwrap();

assert_eq!(
    row,
    Row {
        id: 1,
        name: "name".into()
    }
);

[abonander]

@alu I think they're talking about the output of a DESCRIBE command for the table in question: https://dev.mysql.com/doc/refman/8.4/en/explain.html#explain-table-structure

This might be the same issue as #3387

[alu]

I used --column-type-info to check the returned types, and it appears that the Type and Key columns contain binary flags.

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

mysql> DESCRIBE issue_3390_table;
Field   1:  `Field`
Catalog:    `def`
Database:   ``
Table:      `COLUMNS`
Org_table:  ``
Type:       VAR_STRING
Collation:  latin1_swedish_ci (8)
Length:     64
Max_length: 4
Decimals:   0
Flags:      

Field   2:  `Type`
Catalog:    `def`
Database:   ``
Table:      `COLUMNS`
Org_table:  `columns`
Type:       BLOB
Collation:  latin1_swedish_ci (8)
Length:     16777215
Max_length: 10
Decimals:   0
Flags:      NOT_NULL BLOB BINARY NO_DEFAULT_VALUE 

Field   3:  `Null`
Catalog:    `def`
Database:   ``
Table:      `COLUMNS`
Org_table:  ``
Type:       VAR_STRING
Collation:  latin1_swedish_ci (8)
Length:     3
Max_length: 3
Decimals:   0
Flags:      NOT_NULL 

Field   4:  `Key`
Catalog:    `def`
Database:   ``
Table:      `COLUMNS`
Org_table:  `columns`
Type:       STRING
Collation:  latin1_swedish_ci (8)
Length:     3
Max_length: 3
Decimals:   0
Flags:      NOT_NULL BINARY ENUM NO_DEFAULT_VALUE 

Field   5:  `Default`
Catalog:    `def`
Database:   ``
Table:      `COLUMNS`
Org_table:  `columns`
Type:       BLOB
Collation:  latin1_swedish_ci (8)
Length:     65535
Max_length: 0
Decimals:   0
Flags:      BLOB BINARY 

Field   6:  `Extra`
Catalog:    `def`
Database:   ``
Table:      `COLUMNS`
Org_table:  ``
Type:       VAR_STRING
Collation:  latin1_swedish_ci (8)
Length:     256
Max_length: 14
Decimals:   0
Flags:      

+-------+------------+------+-----+---------+----------------+
| Field | Type       | Null | Key | Default | Extra          |
+-------+------------+------+-----+---------+----------------+
| id    | int        | NO   | PRI | NULL    | auto_increment |
| name  | mediumtext | YES  |     | NULL    |                |
+-------+------------+------+-----+---------+----------------+
2 rows in set (0.02 sec)

[DrewMcArthur]

If #3387 is the root cause here, I've opened up #3400 as a draft for a fix.

[yuyang-ok]

@DrewMcArthur thanks.

[eirnym]

The same thing happeing with SQLite and SQLX 0.8.2

create table test_table(
    id integer primary key autoicrement,
    body TEXT not null
)

When i use query_as function as presented above, SQLX tells me that body field is BLOB, while when I use query function, and select first row, column type is clearly Text:

let channels_raw: Vec<SqliteRow> =
  sqlx::query("select body from test_table")
  .fetch_all(&pool)
  .await
  .unwrap();

println!("type from column: {:?}", channels_raw[0].columns()[0]);
println!("type from value: {:?}", channels_raw[0].try_get_raw(0).unwrap().type_info());

this produces output

type from column: SqliteColumn { name: body, ordinal: 0, type_info: SqliteTypeInfo(Text) }
type from value: SqliteTypeInfo(Blob)

@yuyang-ok I can't explain this discrepancy.

[yuyang-ok]

@eirnym I am not familiar with this library too. I don't know how to fix this

[eirnym]

I wanted to emphasise that this issue is not only for MySQL, but it is probably for all SQL engines supported.

[abonander]

@eirnym that's a different issue entirely and is likely correct behavior. Please read about how data types and storage work in SQLite: https://www.sqlite.org/datatype3.html