1

I'm trying to connect to a MySQL 8 database on PythonAnywhere with a python file. I have set up everything exactly as listed on their help page. I have a Premium account that allows external connections.

Using the same credentials I have connected MySQL Workbench to the database, and everything works correctly. However using the same credentials from my script, with logging, just hangs on a single line. If I change anything like the username, password, port number etc to be incorrect then it realizes and raises an exception.

The full code is:

import mysql.connector
import sshtunnel
import logging
import sys

log_format = "%(levelname)s %(asctime)s - %(message)s"
logging.basicConfig(
    stream=sys.stdout, filemode="w", format=log_format, level=logging.DEBUG
)

logger = logging.getLogger()
logger.propagate = True

sshtunnel.SSH_TIMEOUT = 5.0
sshtunnel.TUNNEL_TIMEOUT = 5.0

# SSH tunnel configuration
ssh_host = "ssh.pythonanywhere.com"
ssh_username = "my_pythonanywhere_username"
ssh_password = "my_pythonanywhere_password"

# Database configuration
db_host = "my_pythonanywhere_username.mysql.pythonanywhere-services.com"
db_port = 3306
db_username = "my_pythonanywhere_username"
db_password = "my_password"

logger.info("Hello before tunnel")

# Establish SSH tunnel
try:
    with sshtunnel.SSHTunnelForwarder(
        (ssh_host),  # , ssh_port),
        ssh_username=ssh_username,
        ssh_password=ssh_password,
        remote_bind_address=(db_host, db_port),
    ) as tunnel:
        logger.info("Hello mid tunnel")
        db_config = {
            "user": db_username,
            "password": db_password,
            "host": "127.0.0.1",
            "port": tunnel.local_bind_port,
        }
        logger.info(tunnel.local_bind_port)
        db = mysql.connector.connect(**db_config)
        logger.info("Hello after tunnel")

        # Execute SQL command
        query = "SHOW DATABASES;"
        cursor = db.cursor()
        cursor.execute(query)

        # Fetch and print the result
        databases = cursor.fetchall()
        for database in databases:
            print(database[0])

        # Close cursor and connection

except Exception as e:
    logger.error("An error occurred: %s", str(e))
finally:
    if "cursor" in locals() and cursor:
        cursor.close()
    if "db" in locals() and db:
        db.close()
    if "tunnel" in locals() and tunnel:
        tunnel.close()

logger.info("Hello end of file")

When I run this, I see "Hello before tunnel" and "Hello mid tunnel", but nothing further. Any errors in credentials and the try/except block catches it, and it prints the "Hello end of file" line.

The full list with log statements that gets printed when it hangs is:

INFO 2023-08-23 19:58:31,099 - Hello before tunnel
DEBUG 2023-08-23 19:58:31,272 - starting thread (client mode): 0x9d33a440
DEBUG 2023-08-23 19:58:31,273 - Local version/idstring: SSH-2.0-paramiko_3.3.1
DEBUG 2023-08-23 19:58:31,376 - Remote version/idstring: SSH-2.0-OpenSSH_8.2p1 Ubuntu-4ubuntu0.5
INFO 2023-08-23 19:58:31,376 - Connected (version 2.0, client OpenSSH_8.2p1)
DEBUG 2023-08-23 19:58:31,474 - === Key exchange possibilities ===
DEBUG 2023-08-23 19:58:31,474 - kex algos: curve25519-sha256, curve25519-sha256@libssh.org, ecdh-sha2-nistp256, ecdh-sha2-nistp384, ecdh-sha2-nistp521, diffie-hellman-group-exchange-sha256, diffie-hellman-group16-sha512, diffie-hellman-group18-sha512, diffie-hellman-group14-sha256
DEBUG 2023-08-23 19:58:31,474 - server key: rsa-sha2-512, rsa-sha2-256, ssh-rsa
DEBUG 2023-08-23 19:58:31,474 - client encrypt: chacha20-poly1305@openssh.com, aes128-ctr, aes192-ctr, aes256-ctr, aes128-gcm@openssh.com, aes256-gcm@openssh.com
DEBUG 2023-08-23 19:58:31,474 - server encrypt: chacha20-poly1305@openssh.com, aes128-ctr, aes192-ctr, aes256-ctr, aes128-gcm@openssh.com, aes256-gcm@openssh.com
DEBUG 2023-08-23 19:58:31,481 - client mac: umac-64-etm@openssh.com, umac-128-etm@openssh.com, hmac-sha2-256-etm@openssh.com, hmac-sha2-512-etm@openssh.com, hmac-sha1-etm@openssh.com, umac-64@openssh.com, umac-128@openssh.com, hmac-sha2-256, hmac-sha2-512, hmac-sha1
DEBUG 2023-08-23 19:58:31,481 - server mac: umac-64-etm@openssh.com, umac-128-etm@openssh.com, hmac-sha2-256-etm@openssh.com, hmac-sha2-512-etm@openssh.com, hmac-sha1-etm@openssh.com, umac-64@openssh.com, umac-128@openssh.com, hmac-sha2-256, hmac-sha2-512, hmac-sha1
DEBUG 2023-08-23 19:58:31,483 - client compress: none, zlib@openssh.com
DEBUG 2023-08-23 19:58:31,484 - server compress: none, zlib@openssh.com
DEBUG 2023-08-23 19:58:31,485 - client lang: <none>
DEBUG 2023-08-23 19:58:31,485 - server lang: <none>
DEBUG 2023-08-23 19:58:31,486 - kex follows: False
DEBUG 2023-08-23 19:58:31,487 - === Key exchange agreements ===
DEBUG 2023-08-23 19:58:31,488 - Kex: curve25519-sha256@libssh.org
DEBUG 2023-08-23 19:58:31,489 - HostKey: rsa-sha2-512
DEBUG 2023-08-23 19:58:31,489 - Cipher: aes128-ctr
DEBUG 2023-08-23 19:58:31,490 - MAC: hmac-sha2-256
DEBUG 2023-08-23 19:58:31,491 - Compression: none
DEBUG 2023-08-23 19:58:31,491 - === End of kex handshake ===
DEBUG 2023-08-23 19:58:31,601 - kex engine KexCurve25519 specified hash_algo <built-in function openssl_sha256>
DEBUG 2023-08-23 19:58:31,602 - Switch to new keys ...
DEBUG 2023-08-23 19:58:31,603 - Got EXT_INFO: {'server-sig-algs': b'ssh-ed25519,sk-ssh-ed25519@openssh.com,ssh-rsa,rsa-sha2-256,rsa-sha2-512,ssh-dss,ecdsa-sha2-nistp256,ecdsa-sha2-nistp384,ecdsa-sha2-nistp521,sk-ecdsa-sha2-nistp256@openssh.com'}
DEBUG 2023-08-23 19:58:31,603 - Attempting password auth...
DEBUG 2023-08-23 19:58:31,834 - userauth is OK
INFO 2023-08-23 19:58:31,957 - Auth banner: b'<<<<<<:>~ PythonAnywhere SSH. Help @ https://help.pythonanywhere.com/pages/SSHAccess\n'
INFO 2023-08-23 19:58:31,972 - Authentication (password) successful!
INFO 2023-08-23 19:58:31,977 - Hello mid tunnel
INFO 2023-08-23 19:58:31,978 - 49493
DEBUG 2023-08-23 19:58:32,020 - [chan 0] Max packet in: 32768 bytes
DEBUG 2023-08-23 19:58:33,380 - Received global request "hostkeys-00@openssh.com"
DEBUG 2023-08-23 19:58:33,380 - Rejecting "hostkeys-00@openssh.com" global request from server.
DEBUG 2023-08-23 19:58:33,522 - [chan 0] Max packet out: 32768 bytes
DEBUG 2023-08-23 19:58:33,522 - Secsh channel 0 opened.

After printing that final line the program just hangs. Ctrl+C doesn't cause it to stop, only the bin seems to.

I'm using VSCode. I've tried running as administrator, no change. I've tried everything I can think of but I can't get any thing different to happen, other than errors. I've done what I can with the documentation but I'm pretty new to this, and I'm probably not making the most of the information in there. I even tried ChatGPT, which suggested I "Consult with an Expert"... The fact that it detects an incorrect database password tells e that the SSH and probably database connections are working correctly.

Any suggestions would be very much appreciated.

David
  • 63
  • 6
  • 1
    The difference I see between your code and what is provided on PythonAnywhere help page is that you use `mysql.connector` instead of `MySQLdb`, and your db connection (inside the tunnel) does not have the database name, something like `yourusername$database`. – caseneuve Aug 24 '23 at 08:14
  • According to PythonAnywhere it should be fine to use any MySQL library which is why I went with mysql connector. Hoewever it just doesn't work. I found if I changed to pymysql everything works fine. The only line to change in the code is: `db = mysql.connector.connect(**db_config)` `db = pymysql.connect(**db_config)` I also tried the same code using `MySQLdb` and again it all works perfectly. So it relates to `mysql.connector`. I can't find what about it is the problem, just a few forum posts elsewhere that describe the same problem, and the same outcome. Thank you for your time :). – David Aug 24 '23 at 17:42
  • Cool, glad you got that working! – caseneuve Aug 25 '23 at 08:10

1 Answers1

1

This was solved with the same solution as this question , use_pure is set to False by default, changing it to True gets the code working. It seems like the thing which is supposed to auto revert from False to True if the code has a problem isn't functioning.

The MySQL.connector documentation has more information here.

David
  • 63
  • 6