Forums

Hello,

I set up a MySQL database on the server and I would like to establish a connection from my laptop. I copied the code from the instructions page and adapted it but it isn't able to query data or do anything. My code looks like this:

import sshtunnel
import mysql.connector

sshtunnel.SSH_TIMEOUT = 5.0
sshtunnel.TUNNEL_TIMEOUT = 5.0

with sshtunnel.SSHTunnelForwarder(
    ('ssh.pythonanywhere.com'),
    ssh_username='balcse', ssh_password='***',
    remote_bind_address=('balcse.mysql.pythonanywhere-services.com', 3306)

) as tunnel:
        con = mysql.connector.connect(
            user='balcse', password='***',
            host='127.0.0.1', port=tunnel.local_bind_port,
            database='balcse$dbname',
        )
        c=con.cursor()
        print(c.execute("SELECT VERSION(), CURRENT_DATE;"))
        con.close()

The ssh-tunnel is established but after that nothing happens. I get no error code or anything.

I had some troubles installing mysql (I have a Windows 10 machine) so I think the problem lies in that part but I also tried to connect to the database via pymysql, there I get the error 2003 "Can't connect to MySQL server on 'localhost'".

Many thanks for your help in advance :)

Update: Also tried to connect via MySQL Workbench, there I get the error "Failed to Connect to MYSQL at balcse.mysql.pythonanywhere-services.com:3306 through SSH tunnel a balcse@ssh.pythonanywhere.com with user balcse Access denied for user 'balcse'@'10.0.0.78' (using password: YES)"

If it's not printing anything, then I would suggest adding some debug prints so you can see what it's doing.

I could get some error to print, it says "Timeout warning for response on 115" for both the host and the database.

If I set the timeout variables to 1.0, I get this message: " mysql.connector.errors.OperationalError: 2013 (HY000): Lost connection to MySQL server at 'reading initial communication packet', system error: 0". If I set it higher, nothing happens.

That sounds like it's not actually creating the tunnel correctly. Can you SSH in to PythonAnywhere? What happens if you do the debugging from this page http://help.pythonanywhere.com/pages/SSHAccess/?

Via console I can connect to the server without problems but also in Python, I can connect to the server via sshtunnel.

Pymysql works now too, so I think I will use that solution because with mysql I'm still not able to connect

Cool. Glad you found something that works.

I have just one more question left, I'm not sure if I understood how this SSH tunneling works. Does the connection have to be opened (for example via command line) or is it anyhow opened by sshtunnel? Is it also possible to use a library like paramiko for it?

I hope this question makes sense, I'm just new to these things and I'm not sure if I've understood everything correctly

I don't know what connection you're talking abount. Your database connection is separate from the tunnel connection and both need to be working.

I understand, thanks for the answer!

Hey, sorry to come responding on a really old thread but I am having exactly the same issue, and getting exactly the same errors messages following the same steps here as the poster. I am able to connect with a SSH tunnel using putty (on windows 10) and send and commands like that, however even with this tunnel open I can't use it with python or CMD and if I try I get the following error message:

ERROR 2003 (HY000): Can't connect to MySQL server on '127.0.0.1' (10061)

Or in python:

_mysql_connector.MySQLInterfaceError: Can't connect to MySQL server on '127.0.0.1' (10061)

Thanks for any help

EDIT: Just to add, this connection also works with MySQL workbench

That sounds like something (a firewall or an antivirus or something like that) is blocking cmd and Python from connecting to the local end of the SSH tunnel.

Hey glenn and anyone who stumbles across this in future. I was indeed able to get the putty solution to work and it had nothing to do with the firewall or antivirus (although I did try turning them off to be thorough).

Would suggest adding this to the page in the putty section as well for anyone as silly as me. I do have a local server running (on port 3306) so I had both times set up the port source from 3333. To do this however an additional step is required of forwarding the port 3333 to the tunnel. That is you need to actually click the "Add" button after you have added the port to forward, I was silly enough not to (clicking Open straight away).

page I am referencing: https://help.pythonanywhere.com/pages/AccessingMySQLFromOutsidePythonAnywhere/

That being said Glenn, I still can't get the WITH AS part of the code to work and it just gets stuck forever. I have indeed tried turning off any protection that might be blocking it and double/triple checked all details. Is there some kind of verbose like setting that could perhaps allow us to see what is going on? At least it is now not as desperate since I got it working with putty although it is a lot more manual. Here is my code: the print statement does get executed

import mysql.connector
import sshtunnel

sshtunnel.SSH_TIMEOUT = 5.0
sshtunnel.TUNNEL_TIMEOUT = 5.0

with sshtunnel.SSHTunnelForwarder(
    ('ssh.pythonanywhere.com'),
    ssh_username='NightShade', ssh_password='xxxxxxx',
    remote_bind_address=('NightShade.mysql.pythonanywhere-services.com', 3306)
) as tunnel:
    print("Starting to do stuff....", flush=True)
    connection = mysql.connector.connect(
        user='NightShade', password='xxxxxxx',
        host='127.0.0.1', port=tunnel.local_bind_port,
        database='myDBName',
    )
    # Do stuff
    connection.close()

You can add this to the top of the file:

import logging
logging.basicConfig()
logger = logging.getLogger(__name__)
logger.setLevel(logging.DEBUG)

and then add logger=logger as an argument to the SSHTunnelForwarder constructor to get additional logging about the SSH connection. I couldn't find a way to get extra logging for the mysqlconnector, but you can add a connect_timeout argument (in seconds, I think - the docs are not really clear) to see whether it's the mysql connection that is not being made at all.

Well here is my new code:

import sshtunnel
import mysql.connector
import logging

logging.basicConfig()
logger = logging.getLogger(__name__)
logger.setLevel(logging.DEBUG)

with sshtunnel.SSHTunnelForwarder(
    ('ssh.pythonanywhere.com', 22),
    ssh_username='NightShade', ssh_password='xxxx',
    remote_bind_address=('NightShade.mysql.pythonanywhere-services.com', 3306),
    logger=logger) as tunnel:
    print("Starting to do stuff....", flush=True)
    connection = mysql.connector.connect(
        user='NightShade', password='xxxx',
        host="127.0.0.1", port=tunnel.local_bind_port,
        connect_timeout=10
    )
    # Do stuff
    cur = connection.cursor()
    print("Finishing stuff....", flush=True)
    connection.close()

And it now does exit the connect with the connection time out so it seems the SQL connection is indeed not being made. This is the output I got, I tried doing a lot of research to save all of you guys any of the headache but I just can't work anything out. Closest I got was the WARNING the logs gave. The output:

WARNING:__main__:Could not read SSH configuration file: ~/ssh\config
INFO:__main__:0 keys loaded from agent
INFO:__main__:0 keys loaded from host directory
INFO:__main__:Connecting to gateway: ssh.pythonanywhere.com:22 as user 'NightShade'
DEBUG:__main__:Concurrent connections allowed: True
DEBUG:__main__:Trying to log in with password: *********
Starting to do stuff....
Traceback (most recent call last):
  File "C:\Users\xxxx\Desktop\Home\xxxxxxx", line 59, in <module>
    cur = connection.cursor()
  File "C:\Users\xxxx\Anaconda3\lib\site-packages\mysql\connector\connection_cext.py", line 524, in cursor
    raise errors.OperationalError("MySQL Connection not available.")
mysql.connector.errors.OperationalError: MySQL Connection not available.
[Finished in 15.1s]

Thanks for any help you have given or can still give. Also seems you got some interesting situation you are tackling with the bots on the forum page

That debug log, looks exactly like the one that I got for a successful SSH connection. So that part seems to be working perfectly.

This really looks like something on your machine is preventing the script from connecting to 127.0.0.1:local_port.

Perhaps try using localhost instead of 127.0.0.1. It's a long shot, but perhaps it will yield some information.

Well that didn't seem to change anything unfortunately, even with all the firewalls and antivirus switched off again. I tried running the code of pythonanywhere using a SSHtunnel (even though I know you can do without) just to see if it was a problem with my own local machine or not but it seems that this package is not contained in pythonanywhere.

Would it be beneificial if I sent you my code by the "Send feedback" link perhaps? (passwords and all) just so we can eliminate if this is a local problem or not?

I have also tried setting a manual "local bind address" to port 3333 but no luck with that either

Thanks again :)

edit: Potentially I may be able to see if I can get another computer to try this over the same network to see if it is the machine or network that could be at fault tomorrow

Trying with another computer on the same network sounds like a good test. You could also run it on PythonAnywhere like you tried to earlier; you'd just need to run

pip3.6 install --user sshtunnel

...to get the package installed, replacing the "3.6" with the Python version you're using if it's different.

It looks like another user found a solution in this thread. Check it out to see if it helps you.

Hey giles and glenn,

Never got around to trying on a different computer on the same network but I did try it on pythonanywhere and it did indeed work (with sshtunnel and mysql.connector).

Using the linked solution worked too (with pymysql), so something is going on there that mysql is not doing but I suppose this is the solution to the problem :)

Thankyou for both of your help!

Interesting. That would make me suspect that there was some sort of issue with the install of mysqlclient. It makes it really interesting that we had 2 users with the same issue.

Hello dear friends,

Just wondering if there is a final solution to this... since I am receiving the same error as of now. While attempting the link at Accessing your MySQL Database

Could you share the error with us?

Thats the thing, there is no error. Just stuck infinitely, if I use the SQL statements linked to the cursor. Without the cursor. There is an Attribute Error such as -

AttributeError: 'NoneType' object has no attribute 'error'

Is there something wrong with my steps ?

Here is the code:

import mysql.connector
from mysql.connector import errorcode
import sshtunnel

sshtunnel.SSH_TIMEOUT = 5.0
sshtunnel.TUNNEL_TIMEOUT = 5.0

with sshtunnel.SSHTunnelForwarder(
    ('ssh.pythonanywhere.com'),
    ssh_username='xxxxxxxxxxx', ssh_password='xxxxxxxxxxxx',
    remote_bind_address=('xxxxxxxxx.mysql.pythonanywhere-services.com', 3306)
) as tunnel:
    connection = mysql.connector.connect(
        user='xxxxxxxx', password='xxxxxxxxx',
        host='127.0.0.1', port=tunnel.local_bind_port,
        database='xxxxxxxxxxxx',
    )
    cursors = connection.cursor()
    query = ("SELECT xxxxx FROM xxxxx WHERE xxxx = 'xxxx'")
    cursors.execute(query)
    #for xxxxxx in cursors:
    #   print("{}".format(xxxxxx))
    cursors.close()
    connection.close()

For some reason it works now...I am not sure what was the error

Good that it works, but I understand that it would be better to know what was the problem.

Not really sure @fjl. The most probable reason could be the pythonanywhere.com scheduled downtime. There was no error you see. It just did nothing while trying to run the py script. I had to CTRL+C to kill the process. The connection works flawlessly now.

Right, thanks!

Good day. I am trying to connect to a MySQL database on my pythonanywhere server using a script made with C code.

I am creating an SSH tunnel using libssh and trying to connect to the database with libmysqlclient-dev from a Linux client.

I am following this tutorial: https://blog.monyog.com/how-to-integrating-ssh-tunneling-inside-your-application/

and I was wondering if someone could give me some advice on how to setup this example to work with my database.

I've not written C code to do the tunneling myself, but if that tutorial has the right code, then you'll just need to set the <SSH_HOST> to ssh.pythonanywhere.com, the <SSH_USER> to your username, samosapiens, the <SSH_PASSWORD> to the password you use to log in to our website, the <REMOTE_MYSQL_HOST> to the database address on the "Databases" page, the <REMOTE_MYSQL_PORT> to the MySQL default port of 3306, <SOME_MYSQL_USER> to the username on the "Databases" page, and <SOME_MYSQL_PASSWORD> to the password that you specified on that page (which is also stored in the file .my.cnf in your file storage).