Can't access my MySQL database from outside PythonAnywhere : Forums : PythonAnywhere

Can't access my MySQL database from outside PythonAnywhere

Code:

import mysql.connector
import sshtunnel

sshtunnel.SSH_TIMEOUT = 5.0
sshtunnel.TUNNEL_TIMEOUT = 5.0

try:
    with sshtunnel.SSHTunnelForwarder(
        ('ssh.pythonanywhere.com'),
        ssh_username='businessaccount', ssh_password='xxxxxxxx',
        remote_bind_address=('BusinessAccount.mysql.pythonanywhere-services.com', 3306)
    ) as tunnel:
        print("tunnel #1: "+str(tunnel.local_bind_port))

        mydb = mysql.connector.connect(
            user='BusinessAc',
            password='xxxxxxxx',
            host='127.0.0.1',
            port=tunnel.local_bind_port,
            database='BusinessAc$default',
        )

        mydb.close()
except Exception as e:
    print(str(e))

Error:

tunnel #1: 50493
2020-09-12 13:56:31,295| ERROR   | Could not establish connection from ('127.0.0.1', 50493) to remote side of the tunnel
2020-09-12 13:56:31,325| ERROR   | Secsh channel 0 open FAILED: open failed: Administratively prohibited
2013 (HY000): Lost connection to MySQL server at 'reading initial communication packet', system error: 0

I just follow what's in the documentation but I got this kind of error.

I'm using python 3.5.1

HumbleBusinessAccount | 6 posts | Sept. 12, 2020, 6:14 a.m. | permalink

Are you able to just ssh to PythonAnywhere using the same credentials?

fjl | 4600 posts | PythonAnywhere staff | Sept. 12, 2020, 7:01 p.m. | permalink

No, pythonanywhere credentials and database credentials are different from each other.

I can create tunnel just like in the above

tunnel #1: 50493

But connecting to pythonanywhere database gives me an error like that.

HumbleBusinessAccount | 6 posts | Sept. 13, 2020, 3:50 a.m. | permalink

Have you noticed that your username is HumbleBusinessAccount not businessaccount and your mysql user is HumbleBusinessAc not BusinessAc

fjl | 4600 posts | PythonAnywhere staff | Sept. 13, 2020, 7:04 a.m. | permalink

Yes I edited it before posting here :D

About the credentials it's all good :D

open failed: Administratively prohibited

It looks like server problem maybe I'm not sure.

HumbleBusinessAccount | 6 posts | Sept. 13, 2020, 7:36 a.m. | permalink

Could you copy here the real line with your ssh_username? Does it exactly match your username?

fjl | 4600 posts | PythonAnywhere staff | Sept. 13, 2020, 11:12 a.m. | permalink

This is my real code.

pythonanywhere username and database username are different.

import mysql.connector
import sshtunnel

sshtunnel.SSH_TIMEOUT = 5.0
sshtunnel.TUNNEL_TIMEOUT = 5.0

try:
    with sshtunnel.SSHTunnelForwarder(
        ('ssh.pythonanywhere.com'),
        ssh_username='Humblebusinessaccount', ssh_password='xxxxxxxx',
        remote_bind_address=('HumbleBusinessAccount.mysql.pythonanywhere-services.com', 3306)
    ) as tunnel:
        print("tunnel #1: "+str(tunnel.local_bind_port))

        mydb = mysql.connector.connect(
            user='HumbleBusinessAc',
            password='xxxxxxxx',
            host='127.0.0.1',
            port=tunnel.local_bind_port,
            database='HumbleBusinessAc$default',
        )

        mydb.close()
except Exception as e:
    print(str(e))

HumbleBusinessAccount | 6 posts | Sept. 13, 2020, 1:54 p.m. | permalink

Your ssh username should be HumbleBusinessAccount not Humblebusinessaccount.

fjl | 4600 posts | PythonAnywhere staff | Sept. 13, 2020, 6:21 p.m. | permalink

When I change the ssh username from Humblebusinessaccount to HumbleBusinessAccount it paused after the printing of tunnel.local_bind_port

and when I do the print(tunnel) under the print("tunnel #1: "+str(tunnel.local_bind_port))

I have this print result

tunnel #1: 49694
<class 'sshtunnel.SSHTunnelForwarder'> object

ssh gateway: ssh.pythonanywhere.com:22

proxy: no

username: HumbleBusinessAccount

authentication: {'password': 'xxxxxxxxxxxxx'}

hostkey: not checked

status: 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: [('HumbleBusinessAccount.mysql.pythonanywhere-services.com', 3306)]

HumbleBusinessAccount | 6 posts | Sept. 14, 2020, 2:43 a.m. | permalink

That looks like it's working. Your code does nothing with the database, it just closes the connection after opening it. So, if you're not getting an exception, then it's working and you should be able to query the database.

glenn | 9718 posts | PythonAnywhere staff | Sept. 14, 2020, 9:43 a.m. | permalink

I can't fetch data from my database it just paused and now when I run this I got this error:

ERROR 1226 (42000): User 'HumbleBusinessAc' has exceeded the 'max_user_connections' resource (current value: 6)

HumbleBusinessAccount | 6 posts | Sept. 14, 2020, 2:17 p.m. | permalink

Ok. That means that you are actually managing to connect to the database, but you have other processes that are holding connections open so your database has run out of connections. Make sure that any time you open a connection, that you close it, even if there is an exception. Connections time out after 5 min of inactivity so, if they are connections that were created as part of trying and failing to get an SSH tunnel, they will be closed in 5 min and you should be able to connect again.

glenn | 9718 posts | PythonAnywhere staff | Sept. 14, 2020, 2:48 p.m. | permalink