Problem connecting to Clickhouse

[chungst]

I am trying to fetch data from Clickhouse and pipe them into Polars and I got stuck in the dispatcher.run part.
I have tried both TextProtocol and BinaryProtocol on MySQLSource but that didn't make a difference. The table is show small which only has 98 rows and it shouldn't stuck at Start writing forever. Did I miss anything?

fn run() -> Result<(), Box<dyn Error>> {
    let database_url = "";
    let pool_size = 8;

    let source = MySQLSource::<TextProtocol>::new(&database_url, pool_size)?;
    let mut destination = Arrow2Destination::new();
    let queries = &["select * from tables"];
    let dispatcher = Dispatcher::<MySQLSource<TextProtocol>, Arrow2Destination, MySQLArrow2Transport<TextProtocol>>::new(source, &mut destination, queries, None);
    let r = dispatcher.run().expect("run failed");

    Ok(())
}

2024-03-26T04:35:04.285101Z DEBUG connectorx::dispatcher: Run dispatcher    
2024-03-26T04:35:04.285135Z DEBUG connectorx::dispatcher: Prepare    
2024-03-26T04:35:04.285154Z DEBUG connectorx::dispatcher: Fetching metadata    
2024-03-26T04:35:04.295518Z DEBUG connectorx::dispatcher: Do not need counts in advance    
2024-03-26T04:35:04.299392Z DEBUG connectorx::dispatcher: Allocate destination memory: 0x0    
2024-03-26T04:35:04.299445Z DEBUG connectorx::dispatcher: Create destination partition    
2024-03-26T04:35:04.299459Z DEBUG connectorx::dispatcher: Start writing    

[wangxiaoying]

Hi @chungst , according to the log (Allocate destination memory: 0x0), there is 0 columns in the result, which is pretty weird. Basically 0x0 means # row x # col. # row is 0 for arrow destination since we do not need to preallocate the buffer, but # col should not be 0. Not sure whether it is the reason that caused the issue. Can you make sure that the mysql driver can access your clickhouse?

[wangxiaoying]

Is there a way for me just using the source(without any destinations) to issue a SQL query?

Internally connectorx uses mysql crate to connect to clickhouse (text protocol only). Here is its repo and this query_iter is the specific API we call at here. Can you try to use this mysql crate directly and see whether if you can get any data?

[chungst]

It is strange, it connected without problem and the query result was expected and correct.

pub fn run() -> Result<(), Box<dyn Error>> {
    use mysql::*;
    use mysql::prelude::*;

    let pool = Pool::new(database_url)?;

    let mut conn = pool.get_conn()?;

    let r = conn.query_iter("SELECT * FROM branches")?.count();

    // printed 98 which is correct
    println!("{r}");

    Ok(())
}

[chungst]

After further digging, I found that the flow is already failing at fetch_metadata
stmt is not in the right shape

[connectorx/src/sources/mysql/mod.rs:103] &stmt = Statement {
    inner: InnerStmt {
        columns: None,
        params: None,
        stmt_packet: StmtPacket {
            status: ConstU8(
                PhantomData<mysql_common::packets::InvalidStmtPacketStatus>,
            ),
            statement_id: 0,
            num_columns: 0,
            num_params: 0,
            __skip: Skip,
            warning_count: 0,
        },
        connection_id: 502,
    },
    named_params: None,
}

Because of this, src_schema will be empty. Combining it with 1 and 2

debug!(
    "Allocate destination memory: {}x{}",
    total_rows,
    src_schema.len()
);

It explained why Allocate destination memory: 0x0 happened(0 rows are expected as you explained).

However, I am not sure if it was due to the source being Clickhouse(as far as I know, clickhouse doesn't support the full MySql protocol, I could be wrong).

[wangxiaoying]

Thanks for the investigation!

This is weird, since if the stmt returns with error, our currently logic will seek to generate a query like "SELECT * from branches limit 1" to directly query the database for schema like here. And if it still fails, it should throw an error here with debug and warning messages. However, it seems like there is no such messages printed in your log.

Can you please confirm the code didn't reach to this path?

[chungst]

The code did not reach https://github.com/sfu-db/connector-x/blob/main/connectorx/src/sources/mysql/mod.rs#L126
Given the output from dbg! on these two lines.

[connectorx/src/sources/mysql/mod.rs:102] &first_query = Naked(
    "select * from branches",
)
[connectorx/src/sources/mysql/mod.rs:103] conn.prep(first_query) = Ok(
    Statement {
        inner: InnerStmt {
            columns: None,
            params: None,
            stmt_packet: StmtPacket {
                status: ConstU8(
                    PhantomData<mysql_common::packets::InvalidStmtPacketStatus>,
                ),
                statement_id: 0,
                num_columns: 0,
                num_params: 0,
                __skip: Skip,
                warning_count: 0,
            },
            connection_id: 563,
        },
        named_params: None,
    },
)

The return of conn.prep is Ok and there is no way it will reach the Err part. Although It looks like the problem started all the way back in the MySQL crate(I could be wrong), I am not sure if there should be a guard internally in connector-x on the Statement