I have a MySQL database which is accessible only via a jumphost. Topology in which we are trying to access MySQL is as follows
Client Host -> jumphost -> MySQL
MySQL is accessed by SSL port forwarding using the following command ssh -At -L 3306:MySQL:3306 user@jumphost
Once the tunnel is created, connection to MySQL from Workbench running on the client host is successful with host as localhost and port 3306.
But if I try to access MySQL using the same connection properties (host: localhost, port: 3306) via pymysql module of mysql the following error occurs OperationalError: (2003, "Can't connect to MySQL server on 'localhost' ([Errno 99] Cannot assign requested address)")
Code details:
import pymysql
import pandas
def getQueryResult(query):
host = 'localhost';
password = 'something';
database = 'test';
conn = pymysql.connect(host=host, port=int(3306), user="someuser", passwd=password, db=database);
df = pandas.read_sql_query(query, conn);
conn.close();
return df;
We expect the connection to be established successfully but instead we get an error with the trace
OSErrorTraceback (most recent call last)
/opt/conda/lib/python3.6/site-packages/pymysql/connections.py in connect(self, sock)
957 (self.host, self.port), self.connect_timeout,
--> 958 **kwargs)
959 break
/opt/conda/lib/python3.6/socket.py in create_connection(address, timeout, source_address)
723 if err is not None:
--> 724 raise err
725 else:
/opt/conda/lib/python3.6/socket.py in create_connection(address, timeout, source_address)
712 sock.bind(source_address)
--> 713 sock.connect(sa)
714 # Break explicitly a reference cycle
OSError: [Errno 99] Cannot assign requested address
During handling of the above exception, another exception occurred:
My Code callstack: Omitting these frames
/opt/conda/lib/python3.6/site-packages/pymysql/__init__.py in Connect(*args, **kwargs)
88 """
89 from .connections import Connection
---> 90 return Connection(*args, **kwargs)
91
92 from . import connections as _orig_conn
/opt/conda/lib/python3.6/site-packages/pymysql/connections.py in __init__(self, host, user, password, database, port, unix_socket, charset, sql_mode, read_default_file, conv, use_unicode, client_flag, cursorclass, init_command, connect_timeout, ssl, read_default_group, compress, named_pipe, no_delay, autocommit, db, passwd, local_infile, max_allowed_packet, defer_connect, auth_plugin_map, read_timeout, write_timeout, bind_address, binary_prefix)
702 self._sock = None
703 else:
--> 704 self.connect()
705
706 def _create_ssl_ctx(self, sslp):
/opt/conda/lib/python3.6/site-packages/pymysql/connections.py in connect(self, sock)
1003 exc.traceback = traceback.format_exc()
1004 if DEBUG: print(exc.traceback)
-> 1005 raise exc
1006
1007 # If e is neither DatabaseError or IOError, It's a bug.
OperationalError: (2003, "Can't connect to MySQL server on 'localhost' ([Errno 99] Cannot assign requested address)")
