Forums

Code hanging with MySQL.Connector. Works with other libraries.

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 the help page (except I am using mysql.connector, which the help page says should be fine). 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. I have also tried the same code using MySQLdb and pymysql, and in both cases it works perfectly. I want to use mysql.connector as I have already built a fair amount of my program up using it, particularly dictionary placeholders for some quite complex queries. However using the same credentials from my program with mysql.connector, 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_db_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])

except Exception as e:
    logger.error("An error occurred: %s", str(e))


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 everything works correctly using the other libraries points to a problem with mysql.connector, but I can't find what it is or how to test it.

When using pymysql only one line changes:

db = mysql.connector.connect(**db_config)  
# turns into:
db = pymysql.connect(**db_config)

Any suggestions would be very much appreciated.

deleted-user-14869766 | 7 posts | Aug. 24, 2023, 7:26 p.m. | permalink

Let us have a closer look at this on Monday.

Staff pafk | 2973 posts | PythonAnywhere staff | Aug. 26, 2023, 11:30 a.m. | permalink

OK, I've just run your code (with my credentials, of course), and it worked seamlessly with mysql.connector, here is the logging extract with timestamps:

INFO 2023-08-28 12:05:10,017 - Hello before tunnel...
INFO 2023-08-28 12:05:11,074 - Hello mid tunnel...
INFO 2023-08-28 12:05:12,966 - Hello after tunnel...
information_schema ...
INFO 2023-08-28 12:05:14,079 - Hello end of file

So it's not a direct issue with this package. It's probably worth mentioning that I have only a dummy database here, so not much data to fetch.

Staff pafk | 2973 posts | PythonAnywhere staff | Aug. 28, 2023, 10:13 a.m. | permalink

Thank you for trying it. I'm not sure what to make of it then. I tried the same code from another computer on my local network and got the same hanging, and also tried running it from command prompt as administrator and got the same bug. Using my phone hotspot also hangs the same.

Do you have the same versions as I do?

Name: mysql-connector-python Version: 8.1.0

Name: sshtunnel Version: 0.4.0

deleted-user-14869766 | 7 posts | Aug. 28, 2023, 11:01 a.m. | permalink

I've just tried the same code to connect to an AWS MySQL database and it connects perfectly, so I don't think it's an issue with my local machine / router.

deleted-user-14869766 | 7 posts | Aug. 28, 2023, 3:07 p.m. | permalink

This sounds suspiciously like the issue in this Stack Overflow question. Perhaps you could try the trick of setting use_pure to True suggested there?

Staff giles | 11788 posts | PythonAnywhere staff | Aug. 28, 2023, 5:27 p.m. | permalink

Fantastic, that's done it. Well done. Your stack overflow searching is better than mine, I didn't come across that post. Thank you.

According to the MySQL documentation use_pure = False by default:

If use_pure=False and the C Extension is not available, then Connector/Python will automatically fall back to the pure Python implementation.

mysql.connector.HAVE_CEXT is True for me, so it thinks that it does have the C extension, but then it isn't actually running properly, and isn't reverting to use_pure = True like it is supposed to.

I've got no idea why it's doing the same thing on both computers I tried, and why it is connecting to an AWS MySQL database without causing an error, but either way that's the solution. Thanks again.

deleted-user-14869766 | 7 posts | Aug. 28, 2023, 6:13 p.m. | permalink

Great to hear it worked! (Just a margin note -- yes, I used an older version; the last version working for me without use_pure set to True was 8.0.30.)

Staff pafk | 2973 posts | PythonAnywhere staff | Aug. 28, 2023, 10:14 p.m. | permalink