Tunneling hangs with mysql connector whereas works with pymysql

[revolutionisme]

Hi,

This same piece of code of works with pymysql connector

    with SSHTunnelForwarder(
            (ssh_host),
            ssh_username=ssh_username,
            ssh_pkey=ssh_private_key,
            remote_bind_address=(db_server_host, 3306),
            local_bind_address=('', 3307)
    ) as tunnel:
        print("****SSH Tunnel Established****")
        sleep(1)

        db = pymysql.connect(
            host=localhost,
            user=user,
            password=password,
            database=database,
            port=3307,
        )
        print("Got db connection")

but this doesn't work and hangs forever:

    with SSHTunnelForwarder(
            (ssh_host),
            ssh_username=ssh_username,
            ssh_pkey=ssh_private_key,
            remote_bind_address=(db_server_host, 3306),
            local_bind_address=('', 3307)
    ) as tunnel:
        print("****SSH Tunnel Established****")
        sleep(1)

        db = mysql.connector.connect(
            host=localhost,
            user=user,
            password=password,
            database=database,
            port=3307,
        )

        print("Got db connection")

Does SSHTunnel behave differently based on the connector used?

[arodox]

I deleted my comment yesterday because I needed to test this solution some more, but I've found that enabling the use_pure option on the mysql connector will get it to work. But there is some limitation, the mysql connector doesn't work as expected in this configuration (I'm using python 3.5.2, mysql-connector-python 8.0.21 and sshtunnel 0.1.5). Mainly I've found if you're reading from a file you need to load the file outside of the ssh tunnel, Also when using the cursor, using multi=true will not import your data and will result in an Interface error. So here is my hack solution which works?

sqlfile = open('someimport.sql', mode="rb")
content = sqlfile.read()
with sshtunnel.open_tunnel(
    ('x.x.x.x', 22),
    ssh_pkey="/some/path/to/keyfile",
    remote_bind_address=('localhost', 3306),
    local_bind_address=('localhost',3307),
    set_keepalive=0.5,
) as tunnel:
    try:  
      cnx = mysql.connector.connect(
                port=.tunnel.local_bind_port, 
                use_pure=True
      )
      cursor = cnx.cursor()
      cursor.execute(content)
    except mysql.connector.errors.InterfaceError:
      pass     # This is very dodgy but the multi=True doesn't work
    finally:
      cursor.close()
      cnx.close()

    tunnel.close()

Hopefully this helps someone, but it should be noted that sshtunnel seems to work a lot better in later versions of python, >3.8

[Daviid-P]

I think I'm having the same problem, but even use_pure=True doesn't help. If you remove the MySQLConnectionPool it will close.

import pprint
from mysql.connector.pooling import MySQLConnectionPool

from sshtunnel import SSHTunnelForwarder

def mySelect(pool, query):
  cnx = pool.get_connection()
  c = cnx.cursor(dictionary=True)
  c.execute(query)
  ret = c.fetchall()
  if(cnx.is_connected()):
    c.close()
    cnx.close()
  return ret

pp = pprint.PrettyPrinter(indent=2)

sql_hostname = '127.0.0.1'
sql_username = ''
sql_password = ''
sql_main_database = ''
sql_port = 3306
ssh_host = ''
ssh_user = ''
ssh_port = 22

tunnel = SSHTunnelForwarder(
    (ssh_host, ssh_port),
    ssh_username=ssh_user,
    ssh_pkey='',
    remote_bind_address=(sql_hostname, sql_port)
)

tunnel.start()

pool = MySQLConnectionPool(pool_name = "pool",
                           pool_size = 4,
                           host=sql_hostname,
                           user=sql_username,
                           passwd=sql_password,
                           port=tunnel.local_bind_port,
                           database=sql_main_database)

pool.autocommit = True

print("Querying DB")
rows = mySelect(pool, "SELECT ALL * FROM (VALUES ROW ('1.1', '1.2', '1.3'),ROW ('2.1', '2.2', '2.3'),ROW ('3.1', '3.2', '3.3')) AS dummy (c1, c2, c3)")
print("Queried DB")
try:
  for idx, row in enumerate(rows):
    idx += 1
    print("%d/%d (%d%%)" % (idx,len(rows),float(idx)/len(rows)*100))
    pprint(row)
    if idx == 2:
      raise Exception('Exception')
except Exception as e:
  tunnel.stop() # Hangs here!!!!!
  raise e

[arodox]

I think I'm having the same problem, but even use_pure=True doesn't help. If you remove the MySQLConnectionPool it will close.

import pprint
from mysql.connector.pooling import MySQLConnectionPool

from sshtunnel import SSHTunnelForwarder

def mySelect(pool, query):
  cnx = pool.get_connection()
  c = cnx.cursor(dictionary=True)
  c.execute(query)
  ret = c.fetchall()
  if(cnx.is_connected()):
    c.close()
    cnx.close()
  return ret

pp = pprint.PrettyPrinter(indent=2)

sql_hostname = '127.0.0.1'
sql_username = ''
sql_password = ''
sql_main_database = ''
sql_port = 3306
ssh_host = ''
ssh_user = ''
ssh_port = 22

tunnel = SSHTunnelForwarder(
    (ssh_host, ssh_port),
    ssh_username=ssh_user,
    ssh_pkey='',
    remote_bind_address=(sql_hostname, sql_port)
)

tunnel.start()

pool = MySQLConnectionPool(pool_name = "pool",
                           pool_size = 4,
                           host=sql_hostname,
                           user=sql_username,
                           passwd=sql_password,
                           port=tunnel.local_bind_port,
                           database=sql_main_database)

pool.autocommit = True

print("Querying DB")
rows = mySelect(pool, "SELECT ALL * FROM (VALUES ROW ('1.1', '1.2', '1.3'),ROW ('2.1', '2.2', '2.3'),ROW ('3.1', '3.2', '3.3')) AS dummy (c1, c2, c3)")
print("Queried DB")
try:
  for idx, row in enumerate(rows):
    idx += 1
    print("%d/%d (%d%%)" % (idx,len(rows),float(idx)/len(rows)*100))
    pprint(row)
    if idx == 2:
      raise Exception('Exception')
except Exception as e:
  tunnel.stop() # Hangs here!!!!!
  raise e

I didn't mention in my original post, but try starting the tunnel using with e.g

with SSHTunnelForwarder(
    (ssh_host, ssh_port),
    ssh_username=ssh_user,
    ssh_pkey='',
    remote_bind_address=(sql_hostname, sql_port)
) as tunnel:
    #do the thing
    tunnel.stop()

I haven't been able to get it to work by assigning the object and using the start(), stop() functions. I think this might have something to do with threading but ¯_(ツ)_/¯

[Daviid-P]

I didn't mention in my original post, but try starting the tunnel using with e.g

with SSHTunnelForwarder(
   (ssh_host, ssh_port),
   ssh_username=ssh_user,
   ssh_pkey='',
   remote_bind_address=(sql_hostname, sql_port)
) as tunnel:
   #do the thing
   tunnel.stop()

I haven't been able to get it to work by assigning the object and using the start(), stop() functions. I think this might have something to do with threading but ¯\(ツ)/¯

Unfortunately this didn't work for me.

import pprint
from mysql.connector.pooling import MySQLConnectionPool

from sshtunnel import SSHTunnelForwarder

def mySelect(pool, query):
  cnx = pool.get_connection()
  c = cnx.cursor(dictionary=True)
  c.execute(query)
  ret = c.fetchall()
  if(cnx.is_connected()):
    c.close()
    cnx.close()
  return ret

pp = pprint.PrettyPrinter(indent=2)

sql_hostname = '127.0.0.1'
sql_username = ''
sql_password = ''
sql_main_database = ''
sql_port = 3306
ssh_host = ''
ssh_user = ''
ssh_port = 22

with SSHTunnelForwarder( (ssh_host, ssh_port), ssh_username=ssh_user, ssh_pkey='', remote_bind_address=(sql_hostname, sql_port)) as tunnel:
  tunnel.start()
  pool = MySQLConnectionPool(pool_name = "pool",
                                                        pool_size = 4,
                                                        host=sql_hostname,
                                                        user=sql_username,
                                                        passwd=sql_password,
                                                        port=tunnel.local_bind_port,
                                                        database=sql_main_database)
  pool.autocommit = True
  print("Querying DB")
  rows = mySelect(pool, "SELECT ALL * FROM (VALUES ROW ('1.1', '1.2', '1.3'),ROW ('2.1', '2.2', '2.3'),ROW ('3.1', '3.2', '3.3')) AS dummy (c1, c2, c3)")
  print("Queried DB")
  tunnel.stop()

I put the start and stop functions there just to try but it doesn't work either way. Last thing I see is "Querying DB" then it hangs.

I've tried pymysql and it works fine with start and stop.

[arodox]

I didn't mention in my original post, but try starting the tunnel using with e.g

with SSHTunnelForwarder(
   (ssh_host, ssh_port),
   ssh_username=ssh_user,
   ssh_pkey='',
   remote_bind_address=(sql_hostname, sql_port)
) as tunnel:
   #do the thing
   tunnel.stop()

I haven't been able to get it to work by assigning the object and using the start(), stop() functions. I think this might have something to do with threading but ¯_(ツ)_/¯

Unfortunately this didn't work for me.

import pprint
from mysql.connector.pooling import MySQLConnectionPool

from sshtunnel import SSHTunnelForwarder

def mySelect(pool, query):
  cnx = pool.get_connection()
  c = cnx.cursor(dictionary=True)
  c.execute(query)
  ret = c.fetchall()
  if(cnx.is_connected()):
    c.close()
    cnx.close()
  return ret

pp = pprint.PrettyPrinter(indent=2)

sql_hostname = '127.0.0.1'
sql_username = ''
sql_password = ''
sql_main_database = ''
sql_port = 3306
ssh_host = ''
ssh_user = ''
ssh_port = 22

with SSHTunnelForwarder( (ssh_host, ssh_port), ssh_username=ssh_user, ssh_pkey='', remote_bind_address=(sql_hostname, sql_port)) as tunnel:
  tunnel.start() #<----- Drop this line
  pool = MySQLConnectionPool(pool_name = "pool",
                                                        pool_size = 4,
                                                        host=sql_hostname,
                                                        user=sql_username,
                                                        passwd=sql_password,
                                                        port=tunnel.local_bind_port,
                                                        database=sql_main_database)
  pool.autocommit = True
  print("Querying DB")
  rows = mySelect(pool, "SELECT ALL * FROM (VALUES ROW ('1.1', '1.2', '1.3'),ROW ('2.1', '2.2', '2.3'),ROW ('3.1', '3.2', '3.3')) AS dummy (c1, c2, c3)")
  print("Queried DB")
  tunnel.stop()

I put the start and stop functions there just to try but it doesn't work either way. Last thing I see is "Querying DB" then it hangs.

I've tried pymysql and it works fine with start and stop.

Try removing the start() function when using with.

[pahaz]

We have an update. You can use .stop(force=True) (new in 0.2.2) for force closing active connections.

[Daviid-P]

We have an update. You can use .stop(force=True) (new in 0.2.2) for force closing active connections.

Seems to work fine. Thanks.

>>> tunnel.stop(force=True)
>>> tunnel
<class 'sshtunnel.SSHTunnelForwarder'> object
ssh gateway: XXX.XXX.XXX.XXX:22
proxy: no
username: 
authentication: {'pkeys': [('ssh-rsa', '')]}
hostkey: not checked
status: not started
keepalive messages: disabled
tunnel connection check: disabled
concurrent connections: allowed
compression: not requested
logging level: ERROR
local binds: [('0.0.0.0', 0)]
remote binds: [('127.0.0.1', 3306)]

[pahaz]

Sinc 0.2.2. If you don't want to wait for all active connections to close use .stop(force=True). If you need to wait for all active connections to close use .stop().

If anyone has any problem, feel free to open an issue.

Also, I have a plane to use .stop(force=True) in context exit, after some discussion it may be the default behavior. Details here: https://github.com/pahaz/sshtunnel/pull/209

[pahaz]

Duplicated: https://github.com/pahaz/sshtunnel/issues/173

[hdsheena]

I deleted my comment yesterday because I needed to test this solution some more, but I've found that enabling the use_pure option on the mysql connector will get it to work. But there is some limitation, the mysql connector doesn't work as expected in this configuration (I'm using python 3.5.2, mysql-connector-python 8.0.21 and sshtunnel 0.1.5). Mainly I've found if you're reading from a file you need to load the file outside of the ssh tunnel, Also when using the cursor, using multi=true will not import your data and will result in an Interface error. So here is my hack solution which works?

sqlfile = open('someimport.sql', mode="rb")
content = sqlfile.read()
with sshtunnel.open_tunnel(
    ('x.x.x.x', 22),
    ssh_pkey="/some/path/to/keyfile",
    remote_bind_address=('localhost', 3306),
    local_bind_address=('localhost',3307),
    set_keepalive=0.5,
) as tunnel:
    try:  
      cnx = mysql.connector.connect(
                port=.tunnel.local_bind_port, 
                use_pure=True
      )
      cursor = cnx.cursor()
      cursor.execute(content)
    except mysql.connector.errors.InterfaceError:
      pass     # This is very dodgy but the multi=True doesn't work
    finally:
      cursor.close()
      cnx.close()

    tunnel.close()

Hopefully this helps someone, but it should be noted that sshtunnel seems to work a lot better in later versions of python, >3.8

This solved my problem after a day of troubleshooting a script. THANK YOU.