ColumnNotFound error when querying with MySQL

[ElectrifyPro]

Bug Description

I have this function in my code:

pub async fn fetch_post(&self, id: Option<usize>) -> Option<Post> {
    let query = match id {
        Some(id) => sqlx::query("SELECT id, title, date, text FROM posts WHERE id = ?")
            .bind(id as u64),
        None => sqlx::query("SELECT id, title, date, text FROM posts ORDER BY id DESC"),
    };

    query.fetch_one(&self.pool) // self.pool: sqlx::Pool<sqlx::MySql>
        .await
        .map(|row| match Post::from_row(&row) {
            Ok(post) => post,
            Err(err) => panic!("Failed to parse post: {:?} ({:?})", err, row),
        })
        .ok()
}

Where Post is:

#[derive(sqlx::FromRow)]
pub struct Post {
    id: u32,
    title: String,
    date: PrimitiveDateTime,
    text: String,
}

And the schema of the posts table in my database is (there are several records in this table):

+-------+--------------+------+-----+-------------------+-------------------+
| Field | Type         | Null | Key | Default           | Extra             |
+-------+--------------+------+-----+-------------------+-------------------+
| id    | int unsigned | NO   | PRI | NULL              |                   |
| title | varchar(128) | YES  |     | NULL              |                   |
| date  | datetime     | YES  |     | CURRENT_TIMESTAMP | DEFAULT_GENERATED |
| text  | text         | YES  |     | NULL              |                   |
+-------+--------------+------+-----+-------------------+-------------------+

This is the bug: In my scenario, only the second query (None => sqlx::query("...) usually gets called. At some point, calling that query will start returning an sqlx::Error::ColumnNotFound error is returned instead (notice column_names is empty):

thread 'actix-server worker 0' panicked at 'Failed to parse post: ColumnNotFound("id") (MySqlRow { ..., columns: [MySqlColumn { ordinal: 0, name: id, type_info: ..., flags: ... }, MySqlColumn { ordinal: 1, name: title, type_info: ..., flags: ... }, MySqlColumn { ordinal: 2, name: date, type_info: ..., flags: ... }, MySqlColumn { ordinal: 3, name: text, type_info: ..., flags: ... }],, column_names: {} })'

Because column_names is empty, and indexing into a row relies on the existence of columns in column_names...

// sqlx-core/src/mysql/row.rs
impl ColumnIndex<MySqlRow> for &'_ str {
    fn index(&self, row: &MySqlRow) -> Result<usize, Error> {
        row.column_names // <-- always empty
            .get(*self)
            .ok_or_else(|| Error::ColumnNotFound((*self).into()))
            .map(|v| *v)
    }
}

...Error::ColumnNotFound will keep on being returned. I'd imagine SQLx determines the columns involved in a SELECT query from the output of the database, so it seems like column_names being empty is a bug.

Minimal Reproduction

I know it's incredibly frustrating that I don't have any clear way to reproduce this, since it's such a niche issue...

Info

• SQLx version: 0.6.2
• SQLx features enabled: ["runtime-tokio-rustls", "mysql", "uuid", "time"]
• Database server and version: MySQL 8.0.26
• Operating system: Linux
• rustc --version: rustc 1.65.0 (897e37553 2022-11-02)

[michaelmenon]

Do we have any resolution on this issue. This happens specifically when we are iterating over a result set from a Stored procedure.

But when I do row.try_get(0) it returns the column value but if I provide the column name it does not give value and throws column not found error.

while let Some(r) = rows.try_next().await.map_err(|e|RepoError(e.to_string()))?{
    
   let UserId  = r.try_get("UserId").unwrap_or(-1), /// throws error column not found
  
}

But if I replace the statement like this
let UserId = r.try_get(0).unwrap_or(-1) //// it works

One more solution I have found is that if I return two result sets in the Stored procedure the second result set always work, even with the column name. So right now I am returning a dummy first result set from the stored procedure. But want to check if there is a fix available for this issue.

[SteveGZr]

I have the same issue. It occurs sporadic.

[Zayd-McArdle]

This issue is still happening, and I cannot call my stored procedures for retrieving data whilst I am integration testing.

[duelafn]

I am encountering this too, reproducible via.

src/main.rs

use sqlx::mysql::MySqlPoolOptions;
use sqlx::FromRow as _;


#[derive(Debug, Clone, PartialEq, Eq)]
#[derive(sqlx::FromRow)]
pub struct Test {
    id: i32,
    name: String,
}

#[tokio::main]
async fn main() {
    let pool = MySqlPoolOptions::new()
        .max_connections(5)
        .connect("mysql://test:12345@localhost/test").await.unwrap();

    // CREATE TABLE test (
    //   id           int(11)      NOT NULL AUTO_INCREMENT
    // , name         text         NOT NULL
    // , PRIMARY KEY (id)
    // ) DEFAULT CHARSET=utf8;
    let row = sqlx::query("INSERT INTO test (name) VALUES (?) RETURNING *")
        .bind("Alice")
        .fetch_one(&pool).await.unwrap();

    println!("{row:?}");
    let test = Test::from_row(&row).unwrap();
    println!("{test:?}");
}

Cargo.toml

[package]
name = "test"
version = "1.0.0"
edition = "2024"
rust-version = "1.88"

[dependencies]
sqlx  = { version = "0.8.0", default-features = false, features = ["macros", "runtime-tokio", "mysql"] }
tokio = { version = "1.32", default-features = false, features = [ "full" ] }

Error message

Row has empty column_names, but MySqlColumn struct does have correct name field.

MySqlRow { row: Row { storage: b"\0\x02\0\0\0\x05Alice", values: [Some(1..5), Some(6..11)] }, format: Binary, columns: [MySqlColumn { ordinal: 0, name: id, type_info: MySqlTypeInfo { type: Long, flags: ColumnFlags(NOT_NULL | PRIMARY_KEY | AUTO_INCREMENT), max_size: Some(11) }, flags: Some(ColumnFlags(NOT_NULL | PRIMARY_KEY | AUTO_INCREMENT)) }, MySqlColumn { ordinal: 1, name: name, type_info: MySqlTypeInfo { type: Blob, flags: ColumnFlags(NOT_NULL | BLOB | NO_DEFAULT_VALUE), max_size: Some(262140) }, flags: Some(ColumnFlags(NOT_NULL | BLOB | NO_DEFAULT_VALUE)) }], column_names: {} }

thread 'main' panicked at src/main.rs:29:37:
called `Result::unwrap()` on an `Err` value: ColumnNotFound("id")

Versions tested

• sqlx 0.8.6
• (Debian 13) mariadb from 11.8.3-MariaDB, client 15.2 for debian-linux-gnu (x86_64) using EditLine wrapper
• (Debian 12) mariadb Ver 15.1 Distrib 10.11.14-MariaDB, for debian-linux-gnu (x86_64) using EditLine wrapper