Can't disable prepared statements when connecting to a Starrock DB

[presedo93]

Bug Description

I'm trying to use sqlx to connect to a StarrocksDB. Checking the docs/forums, it doesn't support prepared-cache but it mentions that most MySQL clients should be able to disable that feature. I set statement_cache_capacity to 0 as the docs mention but I still get this error when trying to connect:

Error: Database(MySqlDatabaseError { code: Some("HY000"), number: 1064, message: "Getting analyzing error. Detail message: Set statement only support constant expr.." })

Can it be that setting statement_cache_capacity to 0 does not disable the prepared-cache?

Thanks!

Minimal Reproduction

use sqlx::mysql::MySqlConnectOptions;
use sqlx::ConnectOptions;

#[tokio::main]
async fn main() -> Result<(), Box<dyn std::error::Error>> {
    let _pool = MySqlConnectOptions::new()
        .host("127.0.0.1")
        .username("root")
        .database("my_db")
        .port(9030)
        .statement_cache_capacity(0)
        .connect()
        .await?;

    Ok(())
}

Info

• SQLx version: 0.7.4
• SQLx features enabled: ["runtime-tokio", "mysql"]
• Database server and version: StarrocksDB (MySQL)
• Operating system: macOS
• rustc --version: 1.77.2

[kiwilab-cn]

I meet the same issues, anyone could fix it?

[presedo93]

I gave it several tries but couldn't make it work... only way I've found to connect to StarrocksDB on Rust is using the mysql crate...

[groobyming]

+1

[groobyming]

I gave it several tries but couldn't make it work... only way I've found to connect to StarrocksDB on Rust is using the mysql crate...

@presedo93 I modified the source code to allow a simple query and solve the problem, but the code is ugly, It would be best if the official could support this switch feature.

[presedo93]

so cool!! I couldn't resist and give a try to your fork with my StarRocks instance... and I can connect! When running queries, I'm getting errors on the Protocol. It seems to be expecting 0xfe (EOF_Packet) but finds 0x3. But, at least, now I'm able to connect hehe.

Do you think you could do a PR on the repo? I could help you to give it visibility and have that feature :)

Thanks for the effort!

[groobyming]

so cool!! I couldn't resist and give a try to your fork with my StarRocks instance... and I can connect! When running queries, I'm getting errors on the Protocol. It seems to be expecting 0xfe (EOF_Packet) but finds 0x3. But, at least, now I'm able to connect hehe.

Do you think you could do a PR on the repo? I could help you to give it visibility and have that feature :)

Thanks for the effort!

I will try to optimize the code first and then submit a PR

[groobyming]

so cool!! I couldn't resist and give a try to your fork with my StarRocks instance... and I can connect! When running queries, I'm getting errors on the Protocol. It seems to be expecting 0xfe (EOF_Packet) but finds 0x3. But, at least, now I'm able to connect hehe.
Do you think you could do a PR on the repo? I could help you to give it visibility and have that feature :)
Thanks for the effort!

I will try to optimize the code first and then submit a PR

@presedo93 Hi prededo93, the error: 'Error: Database(MySqlDatabaseError { code: Some("HY000"), number: 1064, message: "Getting analyzing error. Detail message: Set statement only support constant expr.." })"

is caused by StarRocks not supporting the following: "Set statement with Select statement". For example: "Set sql_mode=(SELECT CONCAT(@@sql_mode, ',PIPES_AS_CONCAT,NO_ENGINE_SUBSTITUTION'))"

The problem code is as follows
https://github.com/launchbadge/sqlx/blob/main/sqlx-mysql/src/connection/executor.rs

You can modify the code as follows: dive into two statements.

But I'm not sure if Starrocks supports "Prepared Statement" because SQLx only supports prepared statements for execution.

[presedo93]

Hi @groobyming, thanks for the help! Reading their docs on Prepared Statements, it seems they support it. I will investigate a little bit further on the topic and let you know! 😃

[presedo93]

Hey @groobyming ! I've been giving a try to the support_disable_preparedstatement branch that is part of the PR #3280 but I get the same error as before. On the other hand when using the compatible_with_doris branch of your fork everything works perfectly!

The chunk of code I'm using is:

use sqlx::{mysql::MySqlPool, Executor};

#[tokio::main]
async fn main() -> Result<(), Box<dyn std::error::Error>> {
    let pool =
        MySqlPool::connect("mysql://root:@127.0.0.1:9030/database?useServerPrepStmts=false").await?;

    let rows = pool.fetch_one("SELECT * FROM table").await.unwrap();
    println!("{:?}", rows);

    Ok(())
}

And in my Cargo.toml, the sqlx import looks like this:

sqlx = { git = "https://github.com/groobyming/sqlx.git", branch = "support_disable_preparedstatement", features = [
  "runtime-tokio",
  "mysql",
] }

Am I missing something?
Thanks a lot for the support!!

[abonander]

This has nothing to do with prepared statements. It doesn't like the CONCAT() call in that SET statement. That's not executed as a prepared statement.

I don't like the solution of doing SELECT @@sql_mode; and then SET in two separate calls as that's an extra round-trip during the connection phase.

The solution is much simpler and doesn't require modifying SQLx, just toggle the two options that send the SET @@sql_mode in the first place (you probably don't care about them anyway):

let options = MySqlConnectOptions::parse("<database url>")?
    // Disable these options to not send `SET @@sql_mode=CONCAT(@@sql_mode, {})` which is not supported on StarRocks.
    // Defaults to `true` on the server side for MySQL 8 anyway
    .no_engine_substitution(false)
    // MySQL uses `||` as logical OR instead of string concatenation (standard SQL):
    // https://dev.mysql.com/doc/refman/8.4/en/logical-operators.html#operator_or
    .pipes_as_concat(false);

let pool = MySqlPool::connect_with(options).await?;

[presedo93]

I just gave it a try and yep, it works!! Thanks a lot @abonander !! I really wanted to use sqlx but the connection issue was pausing me on doing it!

[groobyming]

Hey @groobyming ! I've been giving a try to the support_disable_preparedstatement branch that is part of the PR #3280 but I get the same error as before. On the other hand when using the compatible_with_doris branch of your fork everything works perfectly!

The chunk of code I'm using is:

use sqlx::{mysql::MySqlPool, Executor};

#[tokio::main]
async fn main() -> Result<(), Box<dyn std::error::Error>> {
    let pool =
        MySqlPool::connect("mysql://root:@127.0.0.1:9030/database?useServerPrepStmts=false").await?;

    let rows = pool.fetch_one("SELECT * FROM table").await.unwrap();
    println!("{:?}", rows);

    Ok(())
}

And in my Cargo.toml, the sqlx import looks like this:

sqlx = { git = "https://github.com/groobyming/sqlx.git", branch = "support_disable_preparedstatement", features = [
  "runtime-tokio",
  "mysql",
] }

Am I missing something? Thanks a lot for the support!!

@presedo93 Sorry for the late reply, but the branch has just fixed the issue with the disabled prepared statement.