Forums

Mysql Error: 2003: Can't connect to MySQL server

I have a hacker account and currently trying to connect from my local machine to mysql table in my python anywhere account. I have a python script that needs to access the remote database and I use the following code:

conn = mysql.connector.connect(host='userName.mysql.pythonanywhere-services.com',database='userName$database1',user='userName',password='xxxxx',port='3306')

Keeps giving me the same error:

errno=2003, values=(self.get_address(), _strioerror(err)))
mysql.connector.errors.InterfaceError: 2003: Can't connect to MySQL server on 'userName.mysql.pythonanywhere-services.com:3306' (60 Operation timed out)

I have been reading the forum for hours and although I saw answers to similar errors I just can't fix it mine. Any ideas out there? Thanks for your time.

[edited by admin for formatting]

Did you trying searching this Forum for '3306', there's quite a few posts?

The database isn't directly accessible from outside PythonAnywhere, but you can use an SSH tunnel

Thanks for pointing me out in the right direction, new to SSH tunnels. Trying to access the PA database from a python script and getting another time out error:

from sshtunnel import SSHTunnelForwarder
        with SSHTunnelForwarder(
                 ('userName.mysql.pythonanywhere-services.com', 3306),
                 ssh_password="xxxxxxxx",
                 ssh_username="userName",
                 remote_bind_address=('127.0.0.1', 3306)) as server:

            conn = mysql.connector.connect(host='127.0.0.1',
                                   port=server.local_bind_port,
                                   user='userName',
                                   passwd='xxxxxxx',
                                   db='userName$myDb')

Keep getting the same error:

sshtunnel.BaseSSHTunnelForwarderError: Could not connect to gateway: userName@ssh.pythonanywhere.com ((60, 'Operation timed out'))

Any help is appreciated. Thanks.

quick check- were you able to connect using the bash commands? ie.

ssh -L 3306:username.mysql.pythonanywhere-services.com:3306 username@ssh.pythonanywhere.com

Yes, it connects via terminal.

try this:

from sshtunnel import SSHTunnelForwarder
with SSHTunnelForwarder(
    ('ssh.pythonanywhere.com', 3306),
    ssh_password="xxxxxxxx",
    ssh_username="userName",
    remote_bind_address=('username.mysql.pythonanywhere-services.com', 3306)
) as server:
    conn = mysql.connector.connect(
        host='127.0.0.1',
        port=server.local_bind_port,
        user='userName',
        passwd='xxxxxxx',
        db='userName$myDb'
    )

(and substitute in your username)

Tried it several times and I get the same error (Made sure that user/pwd/db were correct).

sshtunnel.BaseSSHTunnelForwarderError: Could not connect to gateway: ssh.pythonanywhere.com ((60, 'Operation timed out'))

As a side note, I am able to connect via mysqlWorkBench. Thanks for your time Conrad.

Could you give the full stack trace for that error?

.

    remote_bind_address=(db1.mysql.pythonanywhere-services.com', 3306)
File "/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/sshtunnel.py", line 1422, in __enter__
    self.start()
File "/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/sshtunnel.py", line 1147, in start
    self._raise(reason=e)
File "/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/sshtunnel.py", line 995, in _raise
    raise exception(reason)
sshtunnel.BaseSSHTunnelForwarderError: Could not connect to gateway: ssh.pythonanywhere.com ((60, 'Operation timed out')) raise exception(reason)
    sshtunnel.BaseSSHTunnelForwarderError: Could not connect to gateway: ssh.pythonanywhere.com ((60, 'Operation timed out'))

[edited by admin: formatting]

Oops, I think I see the problem. ('ssh.pythonanywhere.com', 3306) means that it's trying to connect to our SSH server on port 3306. If you just replace the tuple with a string ssh.pythonanywhere.com then I think it will work.

A new error shows up:

with SSHTunnelForwarder(
         ('ssh.pythonanywhere.com'),
         ssh_password="xxxxx",
         ssh_username="userName",
         remote_bind_address=('db1.mysql.pythonanywhere-services.com', 3306)

Stack Trade error:

  File "/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/mysql/connector/connection.py", line 1383, in cursor
    raise errors.OperationalError("MySQL Connection not available.")
mysql.connector.errors.OperationalError: MySQL Connection not available.

OK, sounds like we're getting somewhere... Do you have the mysql command line tool installed? If so, we can try setting up the ssh tunnel connection using Python and then using that to connect. Run a simple Python script with this in it:

import time

with SSHTunnelForwarder(
         ('ssh.pythonanywhere.com'),
         ssh_password="xxxxx",
         ssh_username="userName",
         remote_bind_address=('db1.mysql.pythonanywhere-services.com', 3306)
) as server:
    print("Local port: {}".format(server.local_bind_port)
    time.sleep(1000)

Note down the port number it prints out, then in a different console run the command

mysql -u userName -h localhost --port PORT_NUMBER -p 'userName$myDb'

...substituting in the port number that the Python script printed out, of course.

Thanks for your time giles.

The script output is: Local port: 51114

When I try to run the command (substituting in my user/pwd/db), it gives me an error:

mysql -u userName -h localhost --port 51114 -p 'userName$db1'

ERROR 1045 (28000): Access denied for user 'userName'@'localhost' (using password: YES)

Hmm. Are you sure you're using the right password when you run the mysql command? It should be the one you set on the "Databases" tab, not the one you use to log in to the PythonAnywhere website. (The website one should be used in the ssh_username parameter, though.)

Weird, I have the same pwd for both

just to double check- if you run

mysql -u pulpiwapi -h pulpiwapi.mysql.pythonanywhere-services.com -p

from pythonanywhere consoles you are able to login with the correct password right?

I am able to login from pythonanywhere consoles.

12:43 ~ $ mysql -u pulpiwapi -h pulpiwapi.mysql.pythonanywhere-services.com -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.

When I run the same command on my local machine I get:

ERROR 2003 (HY000): Can't connect to MySQL server on 'pulpiwapi.mysql.pythonanywhere-services.com' (60)

OK, I think to help drill down on this we'll need more information. Could you send us (via the "Send feedback" link) the exact code you're using, with just the passwords (both your website one and the MySQL one) replaced with Xs?

Was there a solution to using sshtunnel with mysql.connector.connect?

I'm stuck on the same problem.

What have you tried and what happened? Can you connect using the MySQL client from a terminal on your local machine? Can you connect using the MySQL client from a PythonAnywhere bash console?

Yes, I can connect on my local computer using SSH via MySQL workbench and I can connect using the PythonAnywhere bash console but I cannot get the code to work.

What code? Running where? What's the error?

I try this:

with SSHTunnelForwarder(
        ('ssh.pythonanywhere.com'),
        ssh_username="XXXX",
        ssh_password="XXXX",
        remote_bind_address=('canonsburg.mysql.pythonanywhere-services.com', 3306)
        ) as server:

        cnx = mysql.connector.connect(user='XXXX', password='XXXX',
                            port=server.local_bind_port,
                            host='127.0.0.1',database='canonsburg$default')


2016-07-21 15:22:55,592 :Connected (version 2.0, client OpenSSH_6.6.1p1)
2016-07-21 15:22:56,068 :Auth banner: b'<<<<<<:>~ PythonAnywhere SSH. Help @https://www.pythonanywhere.com/wiki/SSHAccess\n'
2016-07-21 15:22:56,068 :Authentication (password) successful!
2016-07-21 15:23:00,897 :Exception on /non_profit_connect/search [POST]
Traceback (most recent call last):
File "/home/canonsburg/.virtualenvs/my-virtualenv/lib/python3.4/site-packages/flask/app.py", line 1817, in wsgi_app
response = self.full_dispatch_request()
File "/home/canonsburg/.virtualenvs/my-virtualenv/lib/python3.4/site-packages/flask/app.py", line 1477, in full_dispatch_request
rv = self.handle_user_exception(e)
File "/home/canonsburg/.virtualenvs/my-virtualenv/lib/python3.4/site-packages/flask/app.py", line 1381, in handle_user_exception
reraise(exc_type, exc_value, tb)
File "/home/canonsburg/.virtualenvs/my-virtualenv/lib/python3.4/site-packages/flask/_compat.py", line 33, in reraise
raise value
File "/home/canonsburg/.virtualenvs/my-virtualenv/lib/python3.4/site-packages/flask/app.py", line 1475, in      full_dispatch_request
rv = self.dispatch_request()
File "/home/canonsburg/.virtualenvs/my-virtualenv/lib/python3.4/site-packages/flask/app.py", line 1461, in dispatch_request
return self.view_functions[rule.endpoint](**req.view_args)
File "/home/canonsburg/non-profit-connect/app.py", line 382, in non_profit_connect_search
search = User_info()
File "/home/canonsburg/non-profit-connect/info_db.py", line 22, in __init__
Cursor = cnx.cursor()
File "/home/canonsburg/.virtualenvs/my-virtualenv/lib/python3.4/site-packages/mysql/connect /connection.py", line 807, in cursor
raise errors.OperationalError("MySQL Connection not available.")
mysql.connector.errors.OperationalError: MySQL Connection not available.

I don't think that the remote_bind_address argument for SSHTunnelForwarder can be a DNS name. I think it needs to be an IP. You'll need to lookup the IP from a console on PythonAnywhere and try using that instead. I would suggest having a look through the docs for SSHTunnelForwarder for any other gotchas.

Just an update, as this thread seems to get a certain amount of traffic from Google -- Glenn was mistaken about the IP address. The problem in this thread was probably caused by a short timeout on the SSHTunnelForwarder.

We've put some sample code (including setting the timeout to something that seems to work well) on the help page.

can any one solve this problem

import pymysql

databse=pymysql.connect(host='testdatabase.mysql.pythonanywhere-services.com',user='testdatbase',passwd='admin.sree',db='testdatabase$tutorial')
c=database.cursor()
c.execute("SELECT * FROM data")
rows=c.fetchall()
for row in rows:
    print(row)

following one is the error

Traceback (most recent call last):
  File "C:/Users/Sree Lakshmi/AppData/Local/Programs/Python/Python36-32/newconnection.py", line 3, in <module>
    databse=pymysql.connect(host="testdatabase.mysql.pythonanywhere-services.com",user="testdatbase",passwd="admin.sree",db="testdatabase$tutorial")
  File "C:\Users\Sree Lakshmi\AppData\Local\Programs\Python\Python36-32\lib\site-packages\pymysql\__init__.py", line 90, in Connect
    return Connection(*args, **kwargs)
  File "C:\Users\Sree Lakshmi\AppData\Local\Programs\Python\Python36-32\lib\site-packages\pymysql\connections.py", line 706, in __init__
    self.connect()
  File "C:\Users\Sree Lakshmi\AppData\Local\Programs\Python\Python36-32\lib\site-packages\pymysql\connections.py", line 963, in connect
    raise exc
pymysql.err.OperationalError: (2003, "Can't connect to MySQL server on 'testdatabase.mysql.pythonanywhere-services.com' (timed out)")

[edited by admin for formatting]

hi there, i see a typo in the username you're using to connect. you've also told the whole world your database password, so you probably want to change that now ;-)

Hi all, I am running into a similar issue. I had tried to connect to my database on python anywhere directly from my code on my local computer. After reading previous comments, I decided to use the SSHTunnel method. Here is my code for setting everything up.

def data_cleaner():
host = 'xxx.mysql.pythonanywhere-services.com'
user = 'xxx'
password = 'xxx'
db = 'xxx$zipcode_census'
#normally local code cannot interact with pythonanywhere database servers
#must use sshtunnel to set up an external connection
with sshtunnel.SSHTunnelForwarder(
    ('ssh.pythonanywhere.com'),
    ssh_username='xxx',
    ssh_password='xxx',
    remote_bind_address=(host, 3306)
) as server:
    connection = mysql.connector.connect(
        host='127.0.0.1',
        port=server.local_bind_port,
        user=user,
        passwd=password,
        db=db,
    )
#MySQL interface
print("connection established")
cursor = connection.cursor()

However, when I run the code I receive the following error:

2018-06-01 17:26:00,573| ERROR   | Could not connect to gateway ssh.pythonanywhere.com:22 : 61
Traceback (most recent call last):
File "US_zipcode_cleaner.py", line 43, in <module>
data_cleaner()
File "US_zipcode_cleaner.py", line 22, in data_cleaner
remote_bind_address=(host, 3306)
File "/usr/local/lib/python3.6/site-packages/sshtunnel.py", line 1486, in __enter__
self.start()
File "/usr/local/lib/python3.6/site-packages/sshtunnel.py", line 1228, in start
reason='Could not establish session to SSH gateway')
File "/usr/local/lib/python3.6/site-packages/sshtunnel.py", line 1038, in _raise
raise exception(reason)
sshtunnel.BaseSSHTunnelForwarderError: Could not establish session to SSH gateway

Any help would be greatly appreciated!

why is the port 3306?

Hi Conrad, I have no idea why the port 3306, it was the port used in one of the examples you guys posted on the forum regarding setting up the sshtunnel. If 3306 is the wrong port, which one should I be using? How can I find out?

ah nvm. that was a red herring. that's the default mysql port.

what happens if you just try to ssh into ssh.pythonanywhere.com using any ssh client?

I have not tried to use a different ssh client yet, since I've never actually connected anything via ssh before. Hence, why I simply modified the original tutorial code you guys had to using my own credentials, hoping that things would work haha. I will try and look into other clients.

ok that would be the first step. also double check if say it's a firewall thing (eg: u can't access ssh at work but can at home)

I have checked my firewall settings, and the firewall is currently off, so that shouldn't be the issue. I have not tried a different ssh client yet, since most of them other than sshtunnel seems a bit hard to use. However, I have changed my MySQL client from MySQLdb to mysql-connector. I'm still getting an error, but the error is at least different from what I originally had.

Please reference my post above. The original error was "Could not establish session to SSH gateway". Now, it's this:

raise errors.OperationalError("MySQL Connection not available.")
mysql.connector.errors.OperationalError: MySQL Connection not available.

Although, I did add one more snippet to my code above right after the SSH setup, which is:

if connection.is_connected():
    print('Connection Successful!!!')
else:
    print('connection not successful')

and the output is always "connection not successful", so I'm not sure if I've made any progress yet on this issue.

outside of every python thing connect using ssh -l xxx -P ssh.pythonanywhere.com it should a line and then ask for your password. if successful you may try in an extra bash/terminal/whatever ssh -l foresight -P -L 3306:xxx.mysql.pythonanywhere-services.com -N this last command will open the tunnel for you (it outputs no text)

edit: -N and -L switches were swaped

Hi irtusb, I tried what you had said. After entering the first command, I was prompted to enter my password. After entering my password, I received the following output:

Connection closed by 23.21.200.247 port 22

Is that the normal response?

I then tried the second command as you suggested. I literally copied and pasted your command. The result was:

3306:xxx.mysql.pythonanywhere-services.com: option requires an argument -- L
usage: ssh [*********] [-b bind_address] [-c cipher_spec]
       [-D [bind_address:]port] [-E log_file] [-e escape_char]
       [-F configfile] [-I pkcs11] [-i identity_file]
       [-J [user@]host[:port]] [-L address] [-l login_name] [-m mac_spec]
       [-O ctl_cmd] [-o option] [-p port] [-Q query_option] [-R address]
       [-S ctl_path] [-W host:port] [-w local_tun[:remote_tun]]
       [user@]hostname [command]

Since you mentioned that there should be no output, I'm guessing something went wrong?

Hi everyone, I just wanted to let you know that I have resolved my problem. Turns out it was a basic indentation error on my part... (*I feel ashamed to come out of my apartment now haha). I was executing my code outside the with statement as recommended by the pythonanywhere guide. Hence, by the time my code was trying to send data to the mysql table, the connection had timed out already. I only realized this after reading giles' comment from another forum post.

https://www.pythonanywhere.com/forums/topic/12777/

For anyone having a similar issue, please refer to my previous posts.

mmm yes, I always forget the order of the -L and -N switches, edited accordingly. and I'm glad you solved your problem.

Glad you worked it out, @foresight :-)