Forums

I've read through a number of queries similar to this but none of them seems to solve the problem The original poster usually seems to find a workaround.

I cannot connect MySQL Workbench to my database on pythonanywhere

The error message says:

Lost connection to MySQL server at 'reading initial communication packet', system error: 0

When I try to run this code in python, I also get an error

import mysql.connector
import sshtunnel

sshtunnel.SSH_TIMEOUT = 5.0
sshtunnel.TUNNEL_TIMEOUT = 5.0

with sshtunnel.SSHTunnelForwarder( ('ssh.eu.pythonanywhere.com'), ssh_username='xxxx', ssh_password='xxxx', remote_bind_address='xxxx.mysql.pythonanywhere-services.com', 3306)) as tunnel:
    connection = mysql.connector.connect(user='xxxx', password='xxxx',host='127.0.0.1', port=tunnel.local_bind_port,database='minimal_flask',)
# Do stuff
connection.close()

I get the error message:

2020-05-14 16:26:40,466| ERROR   | Secsh channel 0 open FAILED: open failed: Administratively prohibited
2020-05-14 16:26:40,468| ERROR   | Could not establish connection from ('127.0.0.1', 57196) to remote side of the tunnel

I do have MySQL running on my local machine listening on 3306 and it seems like that may be the problem but I don't know how to fix it

I can ssh successfully from my local machine to the python anywhere server using bash terminal but when I connect from that terminal to the MySQL instance, it says it's denied with this error:

ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)

I use MySQL Workbench quite a lot and am pretty comfortable using it. I have previously set it up and connected successfully to remote servers and it has been fine

For the workbench error, we can't really make any guessing without knowing your configuration. See http://help.pythonanywhere.com/pages/AccessingMySQLFromOutsidePythonAnywhere/ for the configuration you need and make sure that you're using the right password in each section.

With the SSH tunnel, we had one user that also got the "Administratively prohibited" error and it turned out that using pymysql instead of mysql connector fixed the issue (https://www.pythonanywhere.com/forums/topic/18924/). As an experiment, could you try that?

Your final error is because you are trying to connect to your database through a local socket instead of using the connection details that are provided on your Databases page.

Thanks Glen.

The MySQL Workbench is the only problem that I need to solve as that is the tool that I use to administer all MySQL databases. I posted the other bits of information to try and help with the diagnosis.

MySQL Workbench: I should have said that I used the advice from http://help.pythonanywhere.com/pages/AccessingMySQLFromOutsidePythonAnywhere/ to set up the MySQL Workbench and I followed it exactly. I think other people have found the same problem but I can't see a resolution for it anywhere.

Python Access: I've not used pymysql before but I will have a read and see if I can get it to work as part of the diagnostics.

SSH Terminal window: I'm struggling to understand what you mean by: "you are trying to connect to your database through a local socket instead of using the connection details that are provided on your Databases page".

As I understand it (I def. could be wrong!) when I use ssh to connect to the server in a terminal window, the resulting terminal window is as if I am typing into a terminal window directly on the remote machine.

So when I type the command mysql into that window, it is executing the mysql comand directly on my pythonanywhere machine. I don't understand how I could get that terminal to connect to a local copy of mysql on my local machine unless I expose my local machine on the internet and link back to it.

That assumption seems to be confirmed by the fact that if I open a terminal through the website and try to execute the mysql command, I get exactly the same error (diagnostic only, it wouldn't be a sensible thing to do as there are dedicated terminals available on the website that definitely work)

@Glen. I think I misread part of your message. When you talked about me connecting using localhost, I now believe you were referring to the python code. And yes, it was a mistake on my part. Correcting it to the details provided on the database tab still leads to an error

It's the mySQL Workbench that is the critical problem :)

Regarding this code:

import mysql.connector
import sshtunnel

sshtunnel.SSH_TIMEOUT = 5.0
sshtunnel.TUNNEL_TIMEOUT = 5.0

with sshtunnel.SSHTunnelForwarder( ('ssh.eu.pythonanywhere.com'), ssh_username='xxxx', ssh_password='xxxx', remote_bind_address='xxxx.mysql.pythonanywhere-services.com', 3306)) as tunnel:
    connection = mysql.connector.connect(user='xxxx', password='xxxx',host='127.0.0.1', port=tunnel.local_bind_port,database='minimal_flask',)
# Do stuff
connection.close()

...I think the problem might be indentation -- those last two lines should be inside the with block, because the SSH tunnel is closed when you exit that block.

But the MySQL Workbench issue is the important one, of course. Perhaps it would be easiest if we could see a screenshot of the configuration you've done. Probably best not to post one here in the forums, just in case there's private data in it, so could you send one to support@pythonanywhere.com?

@giles - thanks, that is a function of my copy and pasting skills!!

I will send support a screenshot of the MySQL Workshop configuration as you suggest. Thanks for the suggestion :)

Well!!!

My Bad!!!

I thought I would reset all the passwords to be really certain that I knew what they were

Guess what? It now works perfectly!

Sorry to waste anyone's time

No problem -- glad to hear you worked it out!