Lost connection to MySQL server during query - Can't connect to MySQL Server

Question

I am trying to access new, fresh MySQL Server over corporate network, machines are using Windows. MySQL running version 8.0, python is 3.9.13

My code (Variant 1 pymysql)

import pymysql

def test_mysql_connection(host, port, username, password):
    try:
        # Establish a connection to the MySQL server
        connection = pymysql.connect(
            host=host,
            port=port,
            user=username,
            password=password
        )

        # If the connection was successful, print a success message
        print("Connected to MySQL database successfully!")

        # Close the connection
        connection.close()
    except pymysql.Error as err:
        # If there was an error, print the error message
        print(f"Error: {err}")
    except Exception as e:
        print(f"An error occurred: {e}")

# Parameters
ip = "ip"
port = 1600
username = "admin"
password = "pass"

# Call the function to test the connection
test_mysql_connection(ip, port, username, password)

Result

Error: (2013, 'Lost connection to MySQL server during query ([WinError 10054] An existing connection was forcibly closed by the remote host)')

Also i tryed to access via sqlalchemy My code (Variant 1 sqlalchemy)

from sqlalchemy import create_engine
from sqlalchemy.exc import OperationalError

db_username = "admin"
db_password = "pass"
db_server = "ip,1600"  # Add port 1600 to the server address
db_name = "db"
driver = "ODBC Driver 18 for SQL Server"

# Replace 'your_connection_string' with your actual database connection string
connection_string = f"mssql+pyodbc://{db_username}:{db_password}@{db_server}/{db_name}?driver={driver}"

try:
    # Create the engine and attempt to connect
    engine = create_engine(connection_string)
    engine.connect()
    print("Connection successful!")
except OperationalError as e:
    print("Error connecting to the database:", e)

Result

Error connecting to the database: (pyodbc.OperationalError) ('08001', '[08001] [Microsoft][ODBC Driver 18 for SQL Server]TCP Provider: Timeout error [258].  (258) (SQLDriverConnect); [08001] [Microsoft][ODBC Driver 18 for SQL Server]Login timeout expired (0); [08001] [Microsoft][ODBC Driver 18 for SQL Server]Unable to complete login process due to delay in prelogin response (258)')
(Background on this error at: https://sqlalche.me/e/14/e3q8)

Since it is corporate network the only open port is 1600 that's the reason why I'm not using default one. I saw multiple posts over internet about this problem and i tried many things.

MySQL server is configured to listens to 1600 port
Ofc Port 1600 is open on both client and server firewall (also in hardware firewall device)
ODBC Driver 18.0 is installed as well
Timeout and keep alive times are extended
Query limit rows are extended none of this helps

When I try to access remotely via MySQL Workbench I can access from both client and server PC without any problems. I can create tables, write data, queries everything.

Any suggestions?

Update 1 Based on comment from @Gord I updates the sqlalchemy code with MySQL ODBC 8.1 Unicode Driver and got new error.

sqlalchemy.exc.DBAPIError: (pyodbc.Error) ('HY000', '[HY000] [MySQL][ODBC 8.1(w) Driver]Missing option MULTI_HOST=1 (0) (SQLDriverConnect); [HY000] [MySQL][ODBC 8.1(w) Driver]Missing option MULTI_HOST=1 (0)')
(Background on this error at: https://sqlalche.me/e/14/dbapi)

Update 2 I added MULTI_HOST=1 option to the code but have new error. connection_string = f"mssql+pyodbc://{db_username}:{db_password}@{db_server}/{db_name}?driver={driver}&MULTI_HOST=1"

pyodbc.Error: ('HY000', "[HY000] [MySQL][ODBC 8.1(w) Driver]Unknown MySQL server host '1600' (11001) (2005) (SQLDriverConnect); [HY000] [MySQL][ODBC 8.1(w) Driver]Unknown MySQL server host '1600' (11001) (2005)")

The above exception was the direct cause of the following exception:

sqlalchemy.exc.DBAPIError: (pyodbc.Error) ('HY000', "[HY000] [MySQL][ODBC 8.1(w) Driver]Unknown MySQL server host '1600' (11001) (2005) (SQLDriverConnect); [HY000] [MySQL][ODBC 8.1(w) Driver]Unknown MySQL server host '1600' (11001) (2005)")
(Background on this error at: https://sqlalche.me/e/14/dbapi)

Update 3 After update mssql+pyodbc:// to mysql+pymysql:// got new error again:

Traceback (most recent call last):
  File "c:\Users\gjykz5\Documents\main.py", line 16, in <module>
    engine.connect()
  File "C:\Users\gjykz5\AppData\Local\Programs\Python\Python39\lib\site-packages\sqlalchemy\engine\base.py", line 3315, in connect
    return self._connection_cls(self, close_with_result=close_with_result)
  File "C:\Users\gjykz5\AppData\Local\Programs\Python\Python39\lib\site-packages\sqlalchemy\engine\base.py", line 96, in __init__
    else engine.raw_connection()
  File "C:\Users\gjykz5\AppData\Local\Programs\Python\Python39\lib\site-packages\sqlalchemy\engine\base.py", line 3394, in raw_connection
    return self._wrap_pool_connect(self.pool.connect, _connection)
  File "C:\Users\gjykz5\AppData\Local\Programs\Python\Python39\lib\site-packages\sqlalchemy\engine\base.py", line 3361, in _wrap_pool_connect
    return fn()
  File "C:\Users\gjykz5\AppData\Local\Programs\Python\Python39\lib\site-packages\sqlalchemy\pool\base.py", line 327, in connect
    return _ConnectionFairy._checkout(self)
  File "C:\Users\gjykz5\AppData\Local\Programs\Python\Python39\lib\site-packages\sqlalchemy\pool\base.py", line 894, in _checkout
    fairy = _ConnectionRecord.checkout(pool)
  File "C:\Users\gjykz5\AppData\Local\Programs\Python\Python39\lib\site-packages\sqlalchemy\pool\base.py", line 493, in checkout
    rec = pool._do_get()
  File "C:\Users\gjykz5\AppData\Local\Programs\Python\Python39\lib\site-packages\sqlalchemy\pool\impl.py", line 146, in _do_get
    self._dec_overflow()
  File "C:\Users\gjykz5\AppData\Local\Programs\Python\Python39\lib\site-packages\sqlalchemy\util\langhelpers.py", line 70, in __exit__
    compat.raise_(
  File "C:\Users\gjykz5\AppData\Local\Programs\Python\Python39\lib\site-packages\sqlalchemy\util\compat.py", line 211, in raise_
    raise exception
  File "C:\Users\gjykz5\AppData\Local\Programs\Python\Python39\lib\site-packages\sqlalchemy\pool\impl.py", line 143, in _do_get
    return self._create_connection()
  File "C:\Users\gjykz5\AppData\Local\Programs\Python\Python39\lib\site-packages\sqlalchemy\pool\base.py", line 273, in _create_connection
    return _ConnectionRecord(self)
  File "C:\Users\gjykz5\AppData\Local\Programs\Python\Python39\lib\site-packages\sqlalchemy\pool\base.py", line 388, in __init__
    self.__connect()
  File "C:\Users\gjykz5\AppData\Local\Programs\Python\Python39\lib\site-packages\sqlalchemy\pool\base.py", line 691, in __connect
    pool.logger.debug("Error on connect(): %s", e)
  File "C:\Users\gjykz5\AppData\Local\Programs\Python\Python39\lib\site-packages\sqlalchemy\util\langhelpers.py", line 70, in __exit__
    compat.raise_(
  File "C:\Users\gjykz5\AppData\Local\Programs\Python\Python39\lib\site-packages\sqlalchemy\util\compat.py", line 211, in raise_
    raise exception
  File "C:\Users\gjykz5\AppData\Local\Programs\Python\Python39\lib\site-packages\sqlalchemy\pool\base.py", line 686, in __connect
    self.dbapi_connection = connection = pool._invoke_creator(self)
  File "C:\Users\gjykz5\AppData\Local\Programs\Python\Python39\lib\site-packages\sqlalchemy\engine\create.py", line 574, in connect
    return dialect.connect(*cargs, **cparams)
  File "C:\Users\gjykz5\AppData\Local\Programs\Python\Python39\lib\site-packages\sqlalchemy\engine\default.py", line 598, in connect
    return self.dbapi.connect(*cargs, **cparams)
TypeError: __init__() got an unexpected keyword argument 'driver'

Your SQLAlchemy test is failing because you are trying to use the Microsoft SQL Server dialect (and ODBC driver) to connect to MySQL. — Gord Thompson, Aug 17 '23 at 10:38
Thanks for reply, I missed that. I updated the code with `MySQL ODBC 8.1 Unicode Driver` I got new error. Will update the post. — directx995, Aug 17 '23 at 12:48
`mssql+pyodbc://` is still the Microsoft SQL Server (mssql) dialect. You probably want to use `mysql+pymysql://` — Gord Thompson, Aug 17 '23 at 13:10
I just adjusted it and new error pops up. I'm updating post. — directx995, Aug 17 '23 at 15:18
https://docs.sqlalchemy.org/en/20/dialects/mysql.html#module-sqlalchemy.dialects.mysql.pymysql — Gord Thompson, Aug 17 '23 at 15:56

Lost connection to MySQL server during query - Can't connect to MySQL Server

0 Answers0