I've bumped into MySQL Connection not available. while working on a new Python script. Funny enough, this and any other exception wouldn't show up when a new MySQL connection was used per SQL query. I've read mysql-connector's documentation and my code looked solid. I've tried to search around on intranets, but nothing useful came out of that. I mean, “you have to tune connection timeout” - no. “This happens when connection is idle for long period of time” - not my case. “People are copy pasting the code without any thought” - yeah, that might be it, but still no.

I couldn't let it go. It has to work. It must work, but what am I missing? Therefore, after 20 or 40 tries I've created a small script with no op SQL queries and to my surprise it worked. No exception over same connection. Almost immediately I've noticed that I have a bug/typo in one of my SQL queries eg. SELECT * FROM mytable WHERE something = “something”; AND foo = “bar” AND lar = “mar” ….;(NOTE misplaced ;, probably leftover after some debugging). Therefore, some kind of warning, error or exception was warranted. However, something more useful than mysql.connector.errors.OperationalError: MySQL Connection not available. would be great.

Then I've tried to replicate this exception by breaking those no op queries, but mysql.connector.errors.DatabaseError: 2014 (HY000): Commands out of sync; you can't run this command now kept on popping up. I've realized that I have a loop in my script and not all queries get executed every time within one loop, because of if conditions. And that was it and here's a demo with mysql.connector.errors.OperationalError: MySQL Connection not available. exception:

#!/usr/bin/env python3
"""Demo how to get MySQL Connection not available exception."""
import mysql.connector


def main():
    """Main."""
    config = {
        "user": "root",
        "password": "password10",
        "host": "127.0.0.1",
        "database": "mydatabase",
        "connection_timeout": 15,
    }
    with mysql.connector.connect(**config) as cnx:
        for i in range(2):
            cursor = cnx.cursor()
            query = (
                "SELECT %(count)s FROM mytable WHERE id = %(abc)s;"
                "AND somecol = 1;"
            )
            params = {"count": 1, "abc": "abc"}
            cursor.execute(query, params)
            result = cursor.fetchall()
            print(result)
            if i == 0:
                continue

            params = {"count": 2}
            cursor.execute("SELECT %(count)s;", params)
            result = cursor.fetchall()
            print(result)


if __name__ == "__main__":
    main()

So here it is. Another reason, typo or bug in one of your SQL queries, why this exception might be popping up on you.

— Zdenek Styblik 2025/04/21 05:10