Forums

pymysql.err.OperationalError: "MySQL server has gone away" when connecting to Google Cloud SQL

I have been trying to connect to a Google Cloud SQL instance but with no luck.

The code works from my Windows development machine. I can access the Cloud SQL instance via a mysql client from the pythonanywhere bash console. I basically keep getting this kind of error on every call:

 (pymysql.err.OperationalError) (2006, "MySQL server has gone away (SSLEOFError(8, 'EOF occurred in violation of protocol (_ssl.c:2396)'))")

More complete error.log at the end.

My code:

def getconn():
    conn = connector.connect(
        "{}:{}:{}".format(gcs_projectid, gcs_location, gcs_instancename),
        "pymysql",
        user=user,
        password=password,
        db=db,
    )
    return conn

And the SQL Alchemy part:

# create connection pool to re-use connections
mypool = sqlalchemy.create_engine(
    "mysql+pymysql://{}".format(host),
    creator=getconn
)

I have tried:

  • creating a new mysql user on google side
  • if i give the wrong password, it fails with a different error message (Access denied), so at least "something" is going through
  • adding the IP reported in the logs to the authorized networks list on Google side
  • tried to disable SSL in connector.connect with ssl_disable=True (somehow!? gives the same error message)
  • tried to require SSL on Google connection side or set both encrypted/unencrypted as OK
  • updated pyOpenSSL to the same version on both machines
  • remove all gcloud files on pythonanywhere to start clean with a Google service account key instead of the user credentials generated by the gcloud tool

Really at a loss what to try here and would appreciate any help or pointers. For now I am circumventing the issue by not using the pymysql driver nor the bespoke cloud-sql-python connector but instead sticking with the mysql connect i am using to connect to the MySQL of Pythonanywhere.

Log from Google side:

{
  "textPayload": "2024-03-28T09:40:31.587678Z 159193 [Note] [MY-010914] [Server] Aborted connection 159193 to db: 'db' user: 'pythonanywhere' host: 'cloudsqlproxy~3.87.174.16' (Got an error reading communication packets).",
  "insertId": "s=f2a1d67cbe0c4c9f972b50a195563914;i=e6ddb;b=bcf66d354fd84228aaac50be97ec9af9;m=9cdab7678a;t=614b550729c4d;x=f9cfd79602ddf9c5-0@a1",
  "resource": {
    "type": "cloudsql_database",
    "labels": {
      "region": "us-central",
      "database_id": "project:db",
      "project_id": "projectid"
    }
  },
  "timestamp": "2024-03-28T09:40:31.588429Z",
  "severity": "INFO",
  "labels": {
    "INSTANCE_UID": "1-a69f903a-4ae4-416b-914f-e3dbed6ffe7c",
    "LOG_BUCKET_NUM": "44",
    "SOURCE_ID": "37626639366238303036383462363537e3b0c442"
  },
  "logName": "projects/steaminsights/logs/cloudsql.googleapis.com%2Fmysql.err",
  "receiveTimestamp": "2024-03-28T09:40:47.776790922Z"
}

More complete error.log:

2024-03-28 09:40:32,589: Exception on /appdlcs/1296260/ [GET]
    latestRefresh = db.session.query(genrestats.date).order_by(text('date desc')).limit(1).one()
  File "/home/SteamInsights/.local/lib/python3.10/site-packages/sqlalchemy/orm/query.py", line 2793, in one
    return self._iter().one()  # type: ignore
  File "/home/SteamInsights/.local/lib/python3.10/site-packages/sqlalchemy/orm/query.py", line 2842, in _iter
    result: Union[ScalarResult[_T], Result[_T]] = self.session.execute(
  File "/home/SteamInsights/.local/lib/python3.10/site-packages/sqlalchemy/orm/session.py", line 2262, in execute
    return self._execute_internal(
  File "/home/SteamInsights/.local/lib/python3.10/site-packages/sqlalchemy/orm/session.py", line 2144, in _execute_internal
    result: Result[Any] = compile_state_cls.orm_execute_statement(
  File "/home/SteamInsights/.local/lib/python3.10/site-packages/sqlalchemy/orm/context.py", line 293, in orm_execute_statement
    result = conn.execute(
  File "/home/SteamInsights/.local/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1412, in execute
    return meth(
  File "/home/SteamInsights/.local/lib/python3.10/site-packages/sqlalchemy/sql/elements.py", line 516, in _execute_on_connection
    return connection._execute_clauseelement(
  File "/home/SteamInsights/.local/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1635, in _execute_clauseelement
    ret = self._execute_context(
  File "/home/SteamInsights/.local/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1844, in _execute_context
    return self._exec_single_context(
  File "/home/SteamInsights/.local/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1984, in _exec_single_context
    self._handle_dbapi_exception(
  File "/home/SteamInsights/.local/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 2339, in _handle_dbapi_exception
    raise sqlalchemy_exception.with_traceback(exc_info[2]) from e
  File "/home/SteamInsights/.local/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1965, in _exec_single_context
    self.dialect.do_execute(
  File "/home/SteamInsights/.local/lib/python3.10/site-packages/sqlalchemy/engine/default.py", line 921, in do_execute
    cursor.execute(statement, parameters)
  File "/home/SteamInsights/.local/lib/python3.10/site-packages/pymysql/cursors.py", line 153, in execute
    result = self._query(query)
  File "/home/SteamInsights/.local/lib/python3.10/site-packages/pymysql/cursors.py", line 322, in _query
    conn.query(q)
  File "/home/SteamInsights/.local/lib/python3.10/site-packages/pymysql/connections.py", line 557, in query
    self._execute_command(COMMAND.COM_QUERY, sql)
  File "/home/SteamInsights/.local/lib/python3.10/site-packages/pymysql/connections.py", line 861, in _execute_command
    self._write_bytes(packet)
  File "/home/SteamInsights/.local/lib/python3.10/site-packages/pymysql/connections.py", line 806, in _write_bytes
    raise err.OperationalError(
sqlalchemy.exc.OperationalError: (pymysql.err.OperationalError) (2006, "MySQL server has gone away (SSLEOFError(8, 'EOF occurred in violation of protocol (_ssl.c:2396)'))")
[SQL: SELECT genrestats.date AS genrestats_date 
FROM genrestats ORDER BY date desc 
 LIMIT %(param_1)s]
[parameters: {'param_1': 1}]
(Background on this error at: https://sqlalche.me/e/20/e3q8)
Traceback (most recent call last):
  File "/home/SteamInsights/.local/lib/python3.10/site-packages/pymysql/connections.py", line 779, in _read_bytes
    data = self._rfile.read(num_bytes)
  File "/usr/local/lib/python3.10/socket.py", line 705, in readinto
    return self._sock.recv_into(b)
  File "/usr/local/lib/python3.10/ssl.py", line 1274, in recv_into
    return self.read(nbytes, buffer)
  File "/usr/local/lib/python3.10/ssl.py", line 1130, in read
    return self._sslobj.read(len, buffer)
ssl.SSLError: [SSL: DECRYPTION_FAILED_OR_BAD_RECORD_MAC] decryption failed or bad record mac (_ssl.c:2548)
**NO MATCH**
During handling of the above exception, another exception occurred:
**NO MATCH**
Traceback (most recent call last):
  File "/home/SteamInsights/.local/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1965, in _exec_single_context
    self.dialect.do_execute(
  File "/home/SteamInsights/.local/lib/python3.10/site-packages/sqlalchemy/engine/default.py", line 921, in do_execute
    cursor.execute(statement, parameters)
  File "/home/SteamInsights/.local/lib/python3.10/site-packages/pymysql/cursors.py", line 153, in execute
    result = self._query(query)
  File "/home/SteamInsights/.local/lib/python3.10/site-packages/pymysql/cursors.py", line 322, in _query
    conn.query(q)
  File "/home/SteamInsights/.local/lib/python3.10/site-packages/pymysql/connections.py", line 558, in query
    self._affected_rows = self._read_query_result(unbuffered=unbuffered)
  File "/home/SteamInsights/.local/lib/python3.10/site-packages/pymysql/connections.py", line 822, in _read_query_result
    result.read()
  File "/home/SteamInsights/.local/lib/python3.10/site-packages/pymysql/connections.py", line 1200, in read
    first_packet = self.connection._read_packet()
  File "/home/SteamInsights/.local/lib/python3.10/site-packages/pymysql/connections.py", line 739, in _read_packet
    packet_header = self._read_bytes(4)
  File "/home/SteamInsights/.local/lib/python3.10/site-packages/pymysql/connections.py", line 785, in _read_bytes
    raise err.OperationalError(
pymysql.err.OperationalError: (2013, 'Lost connection to MySQL server during query ([SSL: DECRYPTION_FAILED_OR_BAD_RECORD_MAC] decryption failed or bad record mac (_ssl.c:2548))')
Beloved premium user SteamInsights | 2 posts | March 28, 2024, 11:27 a.m. | permalink

What if you use mysqlclient package?

Staff fjl | 4614 posts | PythonAnywhere staff | March 28, 2024, 3:57 p.m. | permalink

It works with the mysql.connector package, but not the more native google cloud connector using pymysql. The fact that it's throwing a SSL error is what seems the most odd here.

Beloved premium user SteamInsights | 2 posts | April 3, 2024, 12:46 p.m. | permalink

You could ask GCP what is happening on their side.

Staff fjl | 4614 posts | PythonAnywhere staff | April 3, 2024, 2:54 p.m. | permalink