No table to Connect to MySQL via SSH tunnel

[prasadboyane]

trafficstars

What language are you using?

Python

What version are you using?

polars 0.18.3 connectorx 0.3.1

What database are you using?

MySQL

What dataframe are you using?

Polars

Can you describe your bug?

I need to connect to MySQL via SSH tunnel. In general use case:

1. we create sshtunnel.SSHTunnelForwarder and put in all the deails.
2. start the tunnel
3. Create connection object (conn) via mysql.connector or pymysql
4. Use that conn in pandas pd.read_sql_query(query, conn)

But in connector-x, we have to use URI like mysql://{mysql_user}:{mysql_password}@{tunnel_host}:{tunne_port}/{mysql_db} eg. cx.read_sql(con_uri, query) This same approach we have ti use it for Polars, Which is not working

What are the steps to reproduce the behavior?

1. we create sshtunnel.SSHTunnelForwarder and put in all the deails.
2. start the tunnel
3. Create connection object (conn) via mysql.connector or pymysql
4. Use that conn in pandas or polars pd.read_sql_query(query, conn)

It gives timeout issue.

Database setup if the error only happens on specific data or data type

NA

Example query / code

tunnel = sshtunnel.SSHTunnelForwarder(
         (ssh_host, ssh_port),
         ssh_username=ssh_user,
         ssh_pkey=ssh_pkey,
         remote_bind_address=(remote_host, remote_port)
)
tunnel.start()

con_uri=f'mysql://{mysql_user}:{mysql_password}@127.0.0.1:{tunnel.local_bind_port}/{mysql_db}'
query = 'SELECT VERSION();'
cx.read_sql(con_uri, query)  
print(cx)```

#### What is the error?

timed out waiting for connection

[ababino]

I run into the same problem with postgresql db. I haven't found a solution yet.

[avnav0]

same issue. when i test with mysql-connector-python:

   # cnx = mysql.connector.connect(
    #     user=MYSQL_USER,
    #     password=MYSQL_PASS,
    #     host=tunnel.local_bind_host,
    #     port=tunnel.local_bind_port,
    #     database=MYSQL_DB,
    #     # connection_timeout=5,
    #     use_pure=True
    # )

it works.

the use_pure flag is the difference between it working and not.

when i try with connectorx or polars - it hangs.

full code:

sshtunnel.TUNNEL_TIMEOUT = 10.0
with sshtunnel.SSHTunnelForwarder(
    (SERVER_HOST, SERVER_PORT), # ssh server
    ssh_username=SERVER_USER,
    ssh_pkey=pkey,
    remote_bind_address=(MYSQL_HOST, MYSQL_PORT), # db server
    local_bind_address=('localhost', 3307)
) as tunnel:
    print(tunnel.tunnel_bindings)
    uri = f"mysql://{MYSQL_USER}:{MYSQL_PASS}@{tunnel.local_bind_host}:{tunnel.local_bind_port}/{MYSQL_DB}"
    
    # cnx = mysql.connector.connect(
    #     user=MYSQL_USER,
    #     password=MYSQL_PASS,
    #     host=tunnel.local_bind_host,
    #     port=tunnel.local_bind_port,
    #     database=MYSQL_DB,
    #     # connection_timeout=5,
    #     use_pure=True
    # )
    # cursor = cnx.cursor()
    # cursor.execute('SELECT count(1) FROM mytable.order_journal')
    # [print(x) for x in cursor]
    # cursor.close()
    # cnx.close()

    # polars_df = pl.read_database('SELECT count(1) FROM mytable.order_journal', uri)
    query = 'SELECT count(1) FROM mytable.order_journal'
    # print(polars_df.describe)
    cx.read_sql(conn=uri, query=query, protocol='binary', return_type="polars")  
    cx.close()
    print('logged off')