"Lost connection to MySQL server at 'handshake: reading initial communication packet', system error: 11" using docker sql proxy

Question 1

"Lost connection to MySQL server at 'handshake: reading initial communication packet', system error: 11" using docker sql proxy

mysql google-cloud-platform docker-compose mlflow mysql-proxy

Henry · Feb 24, 2020 · Viewed 8.3k times · Source

Answer

Answer

According to your error output, the root cause should be connect: connection refused

sql_proxy_1  | 2020/02/24 22:38:24 couldn't connect to "test-project:us-central1:test-instance": dial tcp 10.200.0.10:3307: connect: connection refused

Refer to Google Cloud SQL command

There's no way to edit MySQL service port yet, which means Cloud SQL for MySQL only listens to the default 3306 port.

And you're using Cloud SQL Proxy, which will use 3307 port for proxy connection.

About the "connection refused" definition, according to the serverfault post

Nothing is listening on the IP:Port you are trying to connect to

Most likely the connection string of YAML file is wrong.

Which might be the wrong ProjectID, region, or instance name.

You can check the YAML setting again and try to connect the Cloud SQL instance.

Question 2

Essentially, I have a SQL database on Google Cloud Platform and I am using the proxy docker image to establish a local connection for development for when I modify the mlflow docker image. I've spent a disgusting amount of time trying to debug this and figure out what is going on, but this is the first time I've ever dealt with this type of connection.

When I run docker-compose -f local.yaml up I get the following output:

sql_proxy_1  | 2020/02/24 22:38:00 current FDs rlimit set to 1048576, wanted limit is 8500. Nothing to do here.
sql_proxy_1  | 2020/02/24 22:38:00 using credential file for authentication; [email protected]
sql_proxy_1  | 2020/02/24 22:38:01 Listening on /cloudsql/test-project:us-central1:test-instance for test-project:us-central1:test-instance
sql_proxy_1  | 2020/02/24 22:38:01 Ready for new connections
sql_proxy_1  | 2020/02/24 22:38:02 New connection for "test-project:us-central1:test-instance"
sql_proxy_1  | 2020/02/24 22:38:24 couldn't connect to "test-project:us-central1:test-instance": dial tcp 10.200.0.10:3307: connect: connection refused
...
mlflow_1     | 2020/02/24 22:38:24 ERROR mlflow.cli: (MySQLdb._exceptions.OperationalError) (2013, "Lost connection to MySQL server at 'handshake: reading inital communication packet', system error:
11")
mlflow_1     | (Background on this error at: http://sqlalche.me/e/e3q8)
mlflow_1     | Traceback (most recent call last):
mlflow_1     |   File "/usr/local/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 2276, in _wrap_pool_connect
mlflow_1     |     return fn()
mlflow_1     |   File "/usr/local/lib/python3.7/site-packages/sqlalchemy/pool/base.py", line 303, in unique_connection
mlflow_1     |     return _ConnectionFairy._checkout(self)
mlflow_1     |   File "/usr/local/lib/python3.7/site-packages/sqlalchemy/pool/base.py", line 773, in _checkout
mlflow_1     |     fairy = _ConnectionRecord.checkout(pool)
mlflow_1     |   File "/usr/local/lib/python3.7/site-packages/sqlalchemy/pool/base.py", line 492, in checkout
mlflow_1     |     rec = pool._do_get()
mlflow_1     |   File "/usr/local/lib/python3.7/site-packages/sqlalchemy/pool/impl.py", line 139, in _do_get
mlflow_1     |     self._dec_overflow()
mlflow_1     |   File "/usr/local/lib/python3.7/site-packages/sqlalchemy/util/langhelpers.py", line 68, in __exit__
mlflow_1     |     compat.reraise(exc_type, exc_value, exc_tb)
mlflow_1     |   File "/usr/local/lib/python3.7/site-packages/sqlalchemy/util/compat.py", line 153, in reraise
mlflow_1     |     raise value
mlflow_1     |   File "/usr/local/lib/python3.7/site-packages/sqlalchemy/pool/impl.py", line 136, in _do_get
mlflow_1     |     return self._create_connection()
mlflow_1     |   File "/usr/local/lib/python3.7/site-packages/sqlalchemy/pool/base.py", line 308, in _create_connection
mlflow_1     |     return _ConnectionRecord(self)
mlflow_1     |   File "/usr/local/lib/python3.7/site-packages/sqlalchemy/pool/base.py", line 437, in __init__
mlflow_1     |     self.__connect(first_connect_check=True)
mlflow_1     |   File "/usr/local/lib/python3.7/site-packages/sqlalchemy/pool/base.py", line 652, in __connect
mlflow_1     |     connection = pool._invoke_creator(self)
mlflow_1     |   File "/usr/local/lib/python3.7/site-packages/sqlalchemy/engine/strategies.py", line 114, in connect
mlflow_1     |     return dialect.connect(*cargs, **cparams)
mlflow_1     |   File "/usr/local/lib/python3.7/site-packages/sqlalchemy/engine/default.py", line 489, in connect
mlflow_1     |     return self.dbapi.connect(*cargs, **cparams)
mlflow_1     |   File "/usr/local/lib/python3.7/site-packages/MySQLdb/__init__.py", line 84, in Connect
mlflow_1     |     return Connection(*args, **kwargs)
mlflow_1     |   File "/usr/local/lib/python3.7/site-packages/MySQLdb/connections.py", line 179, in __init__
mlflow_1     |     super(Connection, self).__init__(*args, **kwargs2)
mlflow_1     | MySQLdb._exceptions.OperationalError: (2013, "Lost connection to MySQL server at 'handshake: reading inital communication packet', system error: 11")
mlflow_1     |
mlflow_1     | The above exception was the direct cause of the following exception:
mlflow_1     |
mlflow_1     | Traceback (most recent call last):
mlflow_1     |   File "/usr/local/lib/python3.7/site-packages/mlflow/cli.py", line 270, in server
mlflow_1     |     initialize_backend_stores(backend_store_uri, default_artifact_root)
mlflow_1     |   File "/usr/local/lib/python3.7/site-packages/mlflow/server/handlers.py", line 97, in initialize_backend_stores
mlflow_1     |     _get_tracking_store(backend_store_uri, default_artifact_root)
mlflow_1     |   File "/usr/local/lib/python3.7/site-packages/mlflow/server/handlers.py", line 83, in _get_tracking_store
mlflow_1     |     _tracking_store = _tracking_store_registry.get_store(store_uri, artifact_root)
mlflow_1     |   File "/usr/local/lib/python3.7/site-packages/mlflow/tracking/_tracking_service/registry.py", line 37, in get_store
mlflow_1     |     return builder(store_uri=store_uri, artifact_uri=artifact_uri)
mlflow_1     |   File "/usr/local/lib/python3.7/site-packages/mlflow/server/handlers.py", line 54, in _get_sqlalchemy_store
mlflow_1     |     return SqlAlchemyStore(store_uri, artifact_uri)
mlflow_1     |   File "/usr/local/lib/python3.7/site-packages/mlflow/store/tracking/sqlalchemy_store.py", line 95, in __init__
mlflow_1     |     inspected_tables = set(sqlalchemy.inspect(self.engine).get_table_names())
mlflow_1     |   File "/usr/local/lib/python3.7/site-packages/sqlalchemy/inspection.py", line 63, in inspect
mlflow_1     |     ret = reg(subject)
mlflow_1     |   File "/usr/local/lib/python3.7/site-packages/sqlalchemy/engine/reflection.py", line 137, in _insp
mlflow_1     |     return Inspector.from_engine(bind)
mlflow_1     |   File "/usr/local/lib/python3.7/site-packages/sqlalchemy/engine/reflection.py", line 133, in from_engine
mlflow_1     |     return Inspector(bind)
mlflow_1     |   File "/usr/local/lib/python3.7/site-packages/sqlalchemy/engine/reflection.py", line 107, in __init__
mlflow_1     |     bind.connect().close()
mlflow_1     |   File "/usr/local/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 2209, in connect
mlflow_1     |     return self._connection_cls(self, **kwargs)
mlflow_1     |   File "/usr/local/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 103, in __init__
mlflow_1     |     else engine.raw_connection()
mlflow_1     |   File "/usr/local/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 2307, in raw_connection
mlflow_1     |     self.pool.unique_connection, _connection
mlflow_1     |   File "/usr/local/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 2280, in _wrap_pool_connect
mlflow_1     |     e, dialect, self
mlflow_1     |   File "/usr/local/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 1547, in _handle_dbapi_exception_noconnection
mlflow_1     |     util.raise_from_cause(sqlalchemy_exception, exc_info)
mlflow_1     |   File "/usr/local/lib/python3.7/site-packages/sqlalchemy/util/compat.py", line 398, in raise_from_cause
mlflow_1     |     reraise(type(exception), exception, tb=exc_tb, cause=cause)
mlflow_1     |   File "/usr/local/lib/python3.7/site-packages/sqlalchemy/util/compat.py", line 152, in reraise
mlflow_1     |     raise value.with_traceback(tb)
mlflow_1     |   File "/usr/local/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 2276, in _wrap_pool_connect
mlflow_1     |     return fn()
mlflow_1     |   File "/usr/local/lib/python3.7/site-packages/sqlalchemy/pool/base.py", line 303, in unique_connection
mlflow_1     |     return _ConnectionFairy._checkout(self)
mlflow_1     |   File "/usr/local/lib/python3.7/site-packages/sqlalchemy/pool/base.py", line 773, in _checkout
mlflow_1     |     fairy = _ConnectionRecord.checkout(pool)
mlflow_1     |   File "/usr/local/lib/python3.7/site-packages/sqlalchemy/pool/base.py", line 492, in checkout
mlflow_1     |     rec = pool._do_get()
mlflow_1     |   File "/usr/local/lib/python3.7/site-packages/sqlalchemy/pool/impl.py", line 139, in _do_get
mlflow_1     |     self._dec_overflow()
mlflow_1     |   File "/usr/local/lib/python3.7/site-packages/sqlalchemy/util/langhelpers.py", line 68, in __exit__
mlflow_1     |     compat.reraise(exc_type, exc_value, exc_tb)
mlflow_1     |   File "/usr/local/lib/python3.7/site-packages/sqlalchemy/util/compat.py", line 153, in reraise
mlflow_1     |     raise value
mlflow_1     |   File "/usr/local/lib/python3.7/site-packages/sqlalchemy/pool/impl.py", line 136, in _do_get
mlflow_1     |     return self._create_connection()
mlflow_1     |   File "/usr/local/lib/python3.7/site-packages/sqlalchemy/pool/base.py", line 308, in _create_connection
mlflow_1     |     return _ConnectionRecord(self)
mlflow_1     |   File "/usr/local/lib/python3.7/site-packages/sqlalchemy/pool/base.py", line 437, in __init__
mlflow_1     |     self.__connect(first_connect_check=True)
mlflow_1     |   File "/usr/local/lib/python3.7/site-packages/sqlalchemy/pool/base.py", line 652, in __connect
mlflow_1     |     connection = pool._invoke_creator(self)
mlflow_1     |   File "/usr/local/lib/python3.7/site-packages/sqlalchemy/engine/strategies.py", line 114, in connect
mlflow_1     |     return dialect.connect(*cargs, **cparams)
mlflow_1     |   File "/usr/local/lib/python3.7/site-packages/sqlalchemy/engine/default.py", line 489, in connect
mlflow_1     |     return self.dbapi.connect(*cargs, **cparams)
mlflow_1     |   File "/usr/local/lib/python3.7/site-packages/MySQLdb/__init__.py", line 84, in Connect
mlflow_1     |     return Connection(*args, **kwargs)
mlflow_1     |   File "/usr/local/lib/python3.7/site-packages/MySQLdb/connections.py", line 179, in __init__
mlflow_1     |     super(Connection, self).__init__(*args, **kwargs2)
mlflow_1     | sqlalchemy.exc.OperationalError: (MySQLdb._exceptions.OperationalError) (2013, "Lost connection to MySQL server at 'handshake: reading inital communication packet', system error: 11")

Lightweight Code

local.yaml

# Environment Variables
# PROJECT_NAME="test-project"
# SQL_DATABASE="test-database"
# SQL_INSTANCE="test-instance"
# SQL_REGION="us-central1"
# BUCKET_NAME="gs://test-bucket/"

version: "3"
services:
    mlflow:
        image: 
            mlflow:latest
        ports:
            - "6050:6050"
        volumes:
            - cloudsql:/cloudsql
            - ${PWD}/secrets/google/key.json:/var/secrets/google/key.json:ro
        depends_on: 
            - sql_proxy
        environment:
            - GOOGLE_APPLICATION_CREDENTIALS=/var/secrets/google/key.json
        entrypoint: 
            mlflow server --workers 4 --backend-store-uri mysql://username:password@/$SQL_DATABASE?unix_socket=/cloudsql/$PROJECT_NAME:$SQL_REGION:$SQL_INSTANCE --default-artifact-root $BUCKET_NAME --host 0.0.0.0 --port 6050

    sql_proxy:
        image: gcr.io/cloudsql-docker/gce-proxy:1.14
        volumes:
            - ${PWD}/secrets/google/key.json:/root/keys/keyfile.json:ro
            - cloudsql:/cloudsql
        command:
            - "/cloud_sql_proxy"
            - "-dir=/cloudsql"
            - "-instances=test-project:us-central1:test-instance"
            - "-credential_file=/root/keys/keyfile.json"
        ports:
            - "3306:3306"
volumes:
    cloudsql:

"Lost connection to MySQL server at 'handshake: reading initial communication packet', system error: 11" using docker sql proxy

Lightweight Code

Answer

Related questions