Stops returning data after a dozen or so queries

[matmoscicki]

Hi,

When trying to get data, the program locks up on conn.select(select).await after 10-15 repetitions. What could be the cause?

async fn get_data() -> i64 {
    let mysql_addr = "mysql://user:pass@localhost/db_name";
    let conn = Quaint::new(&mysql_addr).await.unwrap();
    let select = Select::from_table("adresy").column("col");
    let result = conn.select(select).await.unwrap().first().unwrap()["col"].as_i64().unwrap();
    println!("result: {:?}", result);
    result
}

#[tokio::main]
async fn main() {
    for i in 0..100 {
        let result = futures::executor::block_on(get_data());
        println!("i: {}", i);
    }
}

[garrensmith]

@matmoscicki thanks for reporting. We know there is a connection pool issue that we are fixing. PR is here https://github.com/prisma/quaint/pull/349

If you have time, could you try that quaint version and see if you still get the same issue?

[matmoscicki]

@matmoscicki thanks for reporting. We know there is a connection pool issue that we are fixing. PR is here #349

If you have time, could you try that quaint version and see if you still get the same issue?

@garrensmith No problem.

this is my Cargo.toml:

[package]
name = "dbtest"
version = "0.1.0"
edition = "2021"

# See more keys and their definitions at https://doc.rust-lang.org/cargo/reference/manifest.html

[dependencies]
# quaint = { version = "0.1.13", features = ["mysql", "pooled", "tracing-log"] }
quaint = { git = "http://github.com/prisma/quaint", branch = "fixed-mobc", features = ["mysql", "pooled"] }
tokio = { version = "1", features = ["full"] }
futures = "0.3"

and the result is unfortunately even worse:

➜ dbtest (master) ✗ cargo run
    Finished dev [unoptimized + debuginfo] target(s) in 1.30s
     Running `target/debug/dbtest`
result: 1
i: 0
result: 1
i: 1
result: 1
i: 2
^C

[garrensmith]

What are you trying to do. The big thing is that you not actually using the connection pool. And second you are blocking on each query and not dropping the connection. If I modify it, to this it works fine:

async fn get_data() -> i64 {
    let mysql_addr = "mysql://user:pass@localhost/db_name";
    let conn = Quaint::new(&mysql_addr).await.unwrap();
    let select = Select::from_table("adresy").column("col");
    let result = conn.select(select).await.unwrap().first().unwrap()["col"].as_i64().unwrap();
    println!("result: {:?}", result);
    result
}

#[tokio::main]
async fn main() {
    for i in 0..100 {
        get_data().await;
        println!("i: {}", i);
    }
}

Or with a connection pool (notice I've modified it slightly to use postgres just to run quickly for me)

use std::time::Duration;

use quaint::{
    pooled::Quaint,
    prelude::{Queryable, Select},
};

async fn get_data() -> String {
    // let mysql_addr = "mysql://user:pass@localhost/db_name";
    let mysql_addr = "postgresql://postgres:prisma@localhost:5434";
    let mut builder = Quaint::builder(mysql_addr).unwrap();
    builder.pool_timeout(Duration::from_secs(5));

    let pool = builder.build();
    let select = Select::from_table("post").column("id");
    let conn = pool.check_out().await.unwrap();
    let result = conn.select(select).await.unwrap().first().unwrap()["id"]
        .as_str()
        .unwrap()
        .to_string();
    println!("result: {:?}", result);
    result
}

#[tokio::main]
async fn main() {
    for i in 0..100 {
        let _result = futures::executor::block_on(async { get_data() });
        println!("i: {}", i);
    }
}

[matmoscicki]

Thank you,

To be honest, I don't quite know how to use async yet :) This is a snippet from a larger project where there was just such a problem that I needed to retrieve data from a database. I did not need a field so I used single::Quaint. When I add async { get_data() } it works. I still do not understand why :) I do not know at what point the connection is dropped :) and why not inside the block_on() without async {}.

[matmoscicki]

No, it's not working for me if I use block_on(). It is fine for:

#[tokio::main]
async fn main() {
    for i in 0..100 {
        let result = get_data().await;
        println!("i: {} - {:?}", i, result);
    }
}

but for:

#[tokio::main]
async fn main() {
    for i in 0..100 {
        let result = futures::executor::block_on(  async { get_data().await } );
        println!("i: {} - {:?}", i, result);
    }
}

and

#[tokio::main]
async fn main() {
    for i in 0..100 {
        let result = futures::executor::block_on( get_data() );
        println!("i: {} - {:?}", i, result);
    }
}

I got 4 results only :/ How should I have to drop the connection?

[matmoscicki]

@garrensmith This only works for sqlite. For mysql and postgresql it does not work. Your example was running but not returning results (but only Future<>). On the other hand, when I want to retrieve a value from the database, I get the effect I had all the time, even if I drop the connection. I think that not using the pool should not be a problem.

use quaint::{prelude::*, single::Quaint};

async fn get_data() -> i64 {
    let mysql_addr = "postgresql://mmoscicki:mmoscicki@localhost/mydb";
    let conn = Quaint::new(&mysql_addr).await.unwrap();
    let select = Select::from_table("some_table").column("id");
    let result = { conn.select(select).await.unwrap().first().unwrap()["id"].as_i64().unwrap() };
    drop(conn);
    result
}

#[tokio::main]
async fn main() {
    for i in 0..100 {
        let result = futures::executor::block_on( get_data() );
        println!("i: {} - {:?}", i, result);
    }
}

it is working after this modification only:

fn main() {
    let rt = runtime::Builder::new_current_thread().enable_all().build().unwrap();
    for i in 0..100 {
        let result = rt.block_on( get_data() );
        // let result = futures::executor::block_on( get_data() );
        println!("i: {} - {:?}", i, result);
    }
}

[matmoscicki]

I am closing the issue because it is related to how tokio works and not quaint