Connection to remote MySQL db from Python 3.4

[stuart258]

Hi All,
I posted a question about using SSH Tunnel on Stack Exchange recently, but I'm not sure if that was the right place for it (and I didn't get any answer). Would it have been better to post it on here? This is the "Issues" section which doesn't really seem right... I'm a bit lost :-(

Thanks in advance.
Stuart.

[fernandezcuesta]

@stuart258 yes, SO should be the place where to start.
Anyway it looks to me that:

• you're trying to parse a key in the wrong format (ppk)
  • please check again converting your key to openSSH (with puttygen > Conversions > Export OpenSSH key)
• there's a bug handling some errors (should clearly suggest where the problem comes from):

  • please run again in debug mode:

      from sshtunnel import SSHTunnelForwarder, DEFAULT_LOGLEVEL
      ...
      DEFAULT_LOGLEVEL = 'DEBUG'
      with SSHTunnelForwarder(
      ...
    

Thanks for reporting!

[stuart258]

@fernandezcuesta thanks for getting back to me. Unfortunately I don't seem to have moved forwards:

• I've converted my key but nothing's changed - I assume that's the expected result at this point.
• I've added the code to use debug mode but the error message is the same - there's no extra information! What have I done wrong?

Thanks.

[fernandezcuesta]

Silly me, you've to do:

import logging
import sshtunnel
from sshtunnel import SSHTunnelForwarder
...
sshtunnel.DEFAULT_LOGLEVEL = logging.DEBUG
with SSHTunnelForwarder(
...

or cleaner:

from sshtunnel import open_tunnel
...
with open_tunnel(
    debug_level='DEBUG',
    ...
) as server:
...

[stuart258]

@fernandezcuesta thanks, that seems to be giving more useful results. I can see that the new key seems to be accepted but I'm struggling to interpret the rest of the feedback:

2016-05-18 20:42:28,825 | WARNING | Could not read SSH configuration file: ~/.ssh/config
2016-05-18 20:42:28,849 | INFO    | 0 keys loaded from agent
2016-05-18 20:42:28,880 | DEBUG   | Private key file (/etc/ssh/my_private_key.key, <class 'paramiko.rsakey.RSAKey'>) successfully loaded
2016-05-18 20:42:28,906 | INFO    | Connecting to gateway: my_remote_site:22 as user 'my_ssh_username'
2016-05-18 20:42:28,929 | DEBUG   | Concurrent connections allowed: True
2016-05-18 20:42:28,951 | DEBUG   | Trying to log in with key: b'xxx'
2016-05-18 20:43:41,804 | INFO    | Opening tunnel: 0.0.0.0:34517 <> 127.0.0.1:3308

2016-05-18 20:43:41,913 | ERROR   | Secsh channel 0 open FAILED: Connection refused: Connect failed
2016-05-18 20:43:41,942 | ERROR   | In #1 <-- ('127.0.0.1', 46228) to ('127.0.0.1', 3308) failed: ChannelException(2, 'Connect failed')
----------------------------------------
Exception happened during processing of request from ('127.0.0.1', 46228)
Traceback (most recent call last):
  File "/usr/local/lib/python3.4/dist-packages/sshtunnel.py", line 286, in handle
    src_address)
  File "/usr/local/lib/python3.4/dist-packages/paramiko/transport.py", line 834, in open_channel
    raise e
paramiko.ssh_exception.ChannelException: (2, 'Connect failed')

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "/usr/lib/python3.4/socketserver.py", line 613, in process_request_thread
    self.finish_request(request, client_address)
  File "/usr/lib/python3.4/socketserver.py", line 344, in finish_request
    self.RequestHandlerClass(request, client_address, self)
  File "/usr/lib/python3.4/socketserver.py", line 669, in __init__
    self.handle()
  File "/usr/local/lib/python3.4/dist-packages/sshtunnel.py", line 296, in handle
    raise HandlerSSHTunnelForwarderError(msg)
sshtunnel.HandlerSSHTunnelForwarderError: In #1 <-- ('127.0.0.1', 46228) to ('127.0.0.1', 3308) failed: ChannelException(2, 'Connect failed')
----------------------------------------
2016-05-18 20:43:42,747 | INFO    | Closing all open connections...
2016-05-18 20:43:42,773 | DEBUG   | Open local addresses: 0.0.0.0:34517
2016-05-18 20:43:42,822 | INFO    | Shutting down tunnel 0.0.0.0:34517
2016-05-18 20:43:42,888 | INFO    | Tunnel: 0.0.0.0:34517 <> 127.0.0.1:3308 is closed
2016-05-18 20:43:42,948 | DEBUG   | Transport is closed
Traceback (most recent call last):
  File "/home/pi/Documents/iot_pm2/iot_main.py", line 137, in <module>
    OpenRemoteDB()
  File "/home/pi/Documents/iot_pm2/iot_main.py", line 106, in OpenRemoteDB
    remotedb = pymysql.connect(host='127.0.0.1', user='remote_db_user', passwd='remote_db_password', db='remote_db_name', port=server.local_bind_port)
  File "/usr/local/lib/python3.4/dist-packages/pymysql/__init__.py", line 88, in Connect
    return Connection(*args, **kwargs)
  File "/usr/local/lib/python3.4/dist-packages/pymysql/connections.py", line 678, in __init__
    self.connect()
  File "/usr/local/lib/python3.4/dist-packages/pymysql/connections.py", line 889, in connect
    self._get_server_information()
  File "/usr/local/lib/python3.4/dist-packages/pymysql/connections.py", line 1190, in _get_server_information
    packet = self._read_packet()
  File "/usr/local/lib/python3.4/dist-packages/pymysql/connections.py", line 945, in _read_packet
    packet_header = self._read_bytes(4)
  File "/usr/local/lib/python3.4/dist-packages/pymysql/connections.py", line 981, in _read_bytes
    2013, "Lost connection to MySQL server during query")
pymysql.err.OperationalError: (2013, 'Lost connection to MySQL server during query')

[fernandezcuesta]

OK looks like a permissions issue with your local ports (most probably 127.0.0.1:3308).

[stuart258]

@fernandezcuesta thanks again for your support. Unfortunately I'm still stuck due to my newbie status - another pointer would be greatly appreciated!

To confirm that it's a permissions issue as you said, I wanted to start by running the program as an administrator (plus I don't know how to set permissions on ports anyway)! So I tried sudo python iot_main.py but got the error ImportError: No module named pymysql. So after some Googling, I added

import sys
sys.path.append("/usr/local/lib/python3.4/dist-packages/")

to my code, but now I get a Traceback ending with

from Crypto.Util import Counter
ImportError: cannot import name _counter

and I'm lost again (plus I think I'm going off-topic for this issue, plus some of this is Linux-specific rather than generic :-( ... sorry. Please tell me if I should post this as a new question on Stack Exchange.)

[fernandezcuesta]

well I've no clue what may be happening but from logs perspective it looks like there's no way to open a connection to ('127.0.0.1', 46228), being the latter a random port.
You may try to try a short snippet like:

import socket
s = socket.socket(socket.AF_INET, socket.SOCK_STREAM)
s.bind(('127.0.0.1', 46228))
s.listen(1)

[stuart258]

@fernandezcuesta thanks, I tried that snippet and "nothing happened" i.e. there was no feedback. So that means that the permissions for accessing 46228 are fine, doesn't it?

[stuart258]

Hi @fernandezcuesta and everyone, sorry for the long delay in coming back but I'm still struggling with this. I've progressed to running the script as an administrator (the issue above was my own stupidity :-(). But I'm getting the same error so I'm even more confused... I don't understand how it can be a permissions issue if the administrator has the same problem?

Any further help would be greatly appreciated. I didn't think that connecting to a remote db was going to be so difficult - especially after having done it from the command line without too many problems :-(.

Thanks.
Stuart.

[pahaz]

Can you past the fill console log. And what you have done.

[stuart258]

@pahaz, when you say the full console log, do you simply mean the output from the console? If so, here it is with my script below. Thanks.

Console output:

pi@jsmd:~ $ sudo python3 /home/pi/Documents/iot_pm2/iot_ssh_example_for_help.py
2016-07-26 21:08:00,012 | WARNING | Could not read SSH configuration file: ~/.ssh/config
2016-07-26 21:08:00,013 | INFO    | 0 keys loaded from agent
2016-07-26 21:08:00,022 | DEBUG   | Private key file (/etc/ssh/my_private_key.key, <class 'paramiko.rsakey.RSAKey'>) successfully loaded
2016-07-26 21:08:00,024 | INFO    | Connecting to gateway: my_remote_site:22 as user 'my_ssh_username'
2016-07-26 21:08:00,025 | DEBUG   | Concurrent connections allowed: True
2016-07-26 21:08:00,027 | DEBUG   | Trying to log in with key: b'xxx'
2016-07-26 21:09:11,771 | INFO    | Opening tunnel: 0.0.0.0:41455 <> 127.0.0.1:3308
2016-07-26 21:09:11,815 | ERROR   | Secsh channel 0 open FAILED: Connection refused: Connect failed
2016-07-26 21:09:11,817 | ERROR   | In #1 <-- ('127.0.0.1', 43354) to ('127.0.0.1', 3308) failed: ChannelException(2, 'Connect failed')
----------------------------------------
Exception happened during processing of request from ('127.0.0.1', 43354)
Traceback (most recent call last):
  File "/usr/local/lib/python3.4/dist-packages/sshtunnel.py", line 286, in handle
    src_address)
  File "/usr/local/lib/python3.4/dist-packages/paramiko/transport.py", line 834, in open_channel
    raise e
paramiko.ssh_exception.ChannelException: (2, 'Connect failed')

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "/usr/lib/python3.4/socketserver.py", line 613, in process_request_thread
    self.finish_request(request, client_address)
  File "/usr/lib/python3.4/socketserver.py", line 344, in finish_request
    self.RequestHandlerClass(request, client_address, self)
  File "/usr/lib/python3.4/socketserver.py", line 669, in __init__
    self.handle()
  File "/usr/local/lib/python3.4/dist-packages/sshtunnel.py", line 296, in handle
    raise HandlerSSHTunnelForwarderError(msg)
sshtunnel.HandlerSSHTunnelForwarderError: In #1 <-- ('127.0.0.1', 43354) to ('127.0.0.1', 3308) failed: ChannelException(2, 'Connect failed')
----------------------------------------
2016-07-26 21:09:11,831 | INFO    | Closing all open connections...
2016-07-26 21:09:11,832 | DEBUG   | Open local addresses: 0.0.0.0:41455
2016-07-26 21:09:11,838 | INFO    | Shutting down tunnel 0.0.0.0:41455
2016-07-26 21:09:11,876 | INFO    | Tunnel: 0.0.0.0:41455 <> 127.0.0.1:3308 is closed
2016-07-26 21:09:11,918 | DEBUG   | Transport is closed
Traceback (most recent call last):
  File "/home/pi/Documents/iot_pm2/iot_ssh_example_for_help.py", line 41, in <module>
    OpenRemoteDB()
  File "/home/pi/Documents/iot_pm2/iot_ssh_example_for_help.py", line 35, in OpenRemoteDB
    remotedb = pymysql.connect(host='127.0.0.1', user='remote_db_user', passwd='remote_db_password', db='remote_db_name', port=server.local_bind_port)
  File "/usr/local/lib/python3.4/dist-packages/pymysql/__init__.py", line 88, in Connect
    return Connection(*args, **kwargs)
  File "/usr/local/lib/python3.4/dist-packages/pymysql/connections.py", line 678, in __init__
    self.connect()
  File "/usr/local/lib/python3.4/dist-packages/pymysql/connections.py", line 889, in connect
    self._get_server_information()
  File "/usr/local/lib/python3.4/dist-packages/pymysql/connections.py", line 1190, in _get_server_information
    packet = self._read_packet()
  File "/usr/local/lib/python3.4/dist-packages/pymysql/connections.py", line 945, in _read_packet
    packet_header = self._read_bytes(4)
  File "/usr/local/lib/python3.4/dist-packages/pymysql/connections.py", line 981, in _read_bytes
    2013, "Lost connection to MySQL server during query")
pymysql.err.OperationalError: (2013, 'Lost connection to MySQL server during query')
pi@jsmd:~ $ 

Script

import dropbox, pymysql, shlex, shutil, subprocess
import logging
import sshtunnel
from sshtunnel import SSHTunnelForwarder
import iot_config as cfg

def CloseLocalDB():
    localcur.close()
    localdb.close()

def CloseRemoteDB():
    # Disconnect from the database
#    remotecur.close()
#    remotedb.close()
    # Close the SSH tunnel
#    ssh.close()
    print("end of CloseRemoteDB function")

def OpenLocalDB():
    global localcur, localdb
    localdb = pymysql.connect(host=cfg.localdbconn['host'], user=cfg.localdbconn['user'], passwd=cfg.localdbconn['passwd'], db=cfg.localdbconn['db'])
    localcur = localdb.cursor()

def OpenRemoteDB():
    global remotecur, remotedb
    sshtunnel.DEFAULT_LOGLEVEL = logging.DEBUG
    with SSHTunnelForwarder(
            ('my_remote_site', 22),
            ssh_username = "my_ssh_username",
            ssh_private_key = "/etc/ssh/my_private_key.key",
            ssh_private_key_password = "my_private_key_password",
            remote_bind_address = ('127.0.0.1', 3308)) as server:
        remotedb = None
#Following line gives an error if uncommented
        remotedb = pymysql.connect(host='127.0.0.1', user='remote_db_user', passwd='remote_db_password', db='remote_db_name', port=server.local_bind_port)
        #remotecur = remotedb.cursor()

# Main program starts here
OpenLocalDB()
CloseLocalDB()
OpenRemoteDB()
CloseRemoteDB()

[fernandezcuesta]

Just a stupid question, are you sure mariadb is listening on port 3308 instead of default 3306?

[stuart258]

@fernandezcuesta, yes, that's it! Thank you very much for your help - I owe you a beer! And yes, I'm feeling a bit stupid... I copied an example but should have checked the details.

Thanks again.
Stuart.

[fernandezcuesta]

Glad to see it helped! Anyway I saw the error is not what is supposed to be
since and code needs to be fixed, so thanks!