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

[eirnym]

@abonander first of all, it's quite unclear what you're trying to show me on the page.

And I'd agree with you if this would be the case for all TEXT columns and TypeInfo would be Binary. However, the check in question is done solely on SQLx side and TypeInfo clearly shows Text as I've shown before.

If SQLx can't decide on how to decode bytes, I prefer to have a global option to automate the process to have less models and not to write conversion for every string manually or by using attributes on each string.

[yuyang-ok]

should we remove the flag check on str??? I don't find more document on internet , But i asked the poe . https://poe.com/s/11PGzdD22a5sWG25eiuy

BINARY_FLAG can have string value

[yuyang-ok]

By the way why the Type 's type is BLOB??? wired to me

[eirnym]

@yuyang-ok would similar fix or an option would be for SQLite?

[yuyang-ok]

Is this sloved?? I am still have this issue. I am using this version

[[package]]
name = "sqlx"
version = "0.9.0-alpha.1"
source = "git+https://github.com/launchbadge/sqlx?branch=main#24317d5eab40fbc33caf1142946e2f39caad73ea"
dependencies = [
 "sqlx-core",
 "sqlx-macros",
 "sqlx-mysql",
 "sqlx-postgres",
 "sqlx-sqlite",
]

I am using sql show tables

!!!!!!!!!!!!!!!!:MySqlTypeInfo { type: VarString, flags: ColumnFlags(NOT_NULL | BINARY | NO_DEFAULT_VALUE), collation: Collation(255), max_size: Some(256) }  VARBINARY

mysql return result still have a BINARY flag and interpreted as VARBINARY

can someone help???