Cloud Functions “Connection Refused” when connecti

问题:

I am trying to follow along with the example provided by Google to connect a Cloud Functions script to Cloud SQL with Python and a MySQL instance.

I have created a Cloud SQL instance in the project and then created a Cloud Function in which I pasted the entire script from the link into the inline editor. I set the environment variables equal to what they need to be in order to connect to Cloud SQL but I am getting an error.

Is there something obvious I am missing in order to make this work?

Error: function crashed. Details: (2003, "Can't connect to MySQL server on 'localhost' ([Errno 111] Connection refused)")

Traceback (most recent call last): File "/env/local/lib/python3.7/site-packages/pymysql/connections.py", line 582, in connect **kwargs) File "/opt/python3.7/lib/python3.7/socket.py", line 727, in create_connection raise err File "/opt/python3.7/lib/python3.7/socket.py", line 716, in create_connection sock.connect(sa) ConnectionRefusedError: [Errno 111] Connection refused During handling of the above exception, another exception occurred: Traceback (most recent call last): File "/user_code/main.py", line 49, in mysql_demo mysql_conn = pymysql.connect(**mysql_config) File "/env/local/lib/python3.7/site-packages/pymysql/init.py", line 94, in Connect return Connection(*args, **kwargs) File "/env/local/lib/python3.7/site-packages/pymysql/connections.py", line 327, in init self.connect() File "/env/local/lib/python3.7/site-packages/pymysql/connections.py", line 629, in connect raise exc pymysql.err.OperationalError: (2003, "Can't connect to MySQL server on 'localhost' ([Errno 111] Connection refused)") During handling of the above exception, another exception occurred: Traceback (most recent call last): File "/env/local/lib/python3.7/site-packages/pymysql/connections.py", line 570, in connect sock.connect(self.unix_socket) ConnectionRefusedError: [Errno 111] Connection refused During handling of the above exception, another exception occurred: Traceback (most recent call last): File "/env/local/lib/python3.7/site-packages/google/cloud/functions_v1beta2/worker.py", line 297, in run_http_function result = _function_handler.invoke_user_function(flask.request) File "/env/local/lib/python3.7/site-packages/google/cloud/functions_v1beta2/worker.py", line 199, in invoke_user_function return call_user_function(request_or_event) File "/env/local/lib/python3.7/site-packages/google/cloud/functions_v1beta2/worker.py", line 192, in call_user_function return self._user_function(request_or_event) File "/user_code/main.py", line 53, in mysql_demo mysql_conn = pymysql.connect(**mysql_config) File "/env/local/lib/python3.7/site-packages/pymysql/init.py", line 94, in Connect return Connection(*args, **kwargs) File "/env/local/lib/python3.7/site-packages/pymysql/connections.py", line 327, in init self.connect() File "/env/local/lib/python3.7/site-packages/pymysql/connections.py", line 629, in connect raise exc pymysql.err.OperationalError: (2003, "Can't connect to MySQL server on 'localhost' ([Errno 111] Connection refused)")

回答1:

Very important information:

Before you can get started, you need to go the GCP IAM to add the cloud SQL admin role to the cloud function service account. The service account information can be found in the general tab of the cloud function. After you complete this action, you should be good to go. If not, try to add more roles to your cloud function service account like project editor.

For code reference,( only part to change)

# TODO(developer): specify SQL connection details  
CONNECTION_NAME = getenv(
'INSTANCE_CONNECTION_NAME',
'proj-chatbot-og:us-central1:your connection name')
# Please don't change the name on the left like MYSQL_USER
DB_USER = getenv('MYSQL_USER', 'your user name')
DB_PASSWORD = getenv('MYSQL_PASSWORD', 'your password')
DB_NAME = getenv('MYSQL_DATABASE', 'your sql database')


mysql_config = {
'user': DB_USER,
'password': DB_PASSWORD,
'db': DB_NAME,
'charset': 'utf8mb4',
'cursorclass': pymysql.cursors.DictCursor,
'autocommit': True
}

The full documentation can be found in here https://cloud.google.com/functions/docs/sql

Please vote the solution if you found the solution solved your issue.

回答2:

I was having the same issue and after much back and forth with Google, this finally solved it for me.

The sample shows the following try clause:

if not mysql_conn:
    try:
        mysql_conn = pymysql.connect(**mysql_config)
    except OperationalError:
        # If production settings fail, use local development ones
        mysql_config['unix_socket'] = f'/cloudsql/{CONNECTION_NAME}'
        mysql_conn = pymysql.connect(**mysql_config)

I changed it to:

if not mysql_conn:
    mysql_config['unix_socket'] = f'/cloudsql/{CONNECTION_NAME}'
    mysql_conn = pymysql.connect(**mysql_config)

And used (data, context) as function arguments rather than (request)

This is what I ultimately ended up with as a test function:

from os import getenv

import pymysql
from pymysql.err import OperationalError

CONNECTION_NAME = getenv(
  'INSTANCE_CONNECTION_NAME',
  'connection_name')
DB_USER = getenv('MYSQL_USER', 'username')
DB_PASSWORD = getenv('MYSQL_PASSWORD', 'password')
DB_NAME = getenv('MYSQL_DATABASE', 'database')

mysql_config = {
  'user': DB_USER,
  'password': DB_PASSWORD,
  'db': DB_NAME,
  'charset': 'utf8mb4',
  'cursorclass': pymysql.cursors.DictCursor,
  'autocommit': True
}

if getenv('NODE_ENV', '') == 'production':
    mysql_config['unix_socket'] = f'/cloudsql/{CONNECTION_NAME}'

# Create SQL connection globally to enable reuse
# PyMySQL does not include support for connection pooling
mysql_conn = None


def __get_cursor():
    """
    Helper function to get a cursor
    PyMySQL does NOT automatically reconnect,
    so we must reconnect explicitly using ping()
    """
    try:
        return mysql_conn.cursor()
    except OperationalError:
        mysql_conn.ping(reconnect=True)
        return mysql_conn.cursor()


def mysql_demo(data, context):
    global mysql_conn

    # Initialize connections lazily, in case SQL access isn't needed for this
    # GCF instance. Doing so minimizes the number of active SQL connections,
    # which helps keep your GCF instances under SQL connection limits.
    if not mysql_conn:
        mysql_conn = pymysql.connect(**mysql_config)

    # Remember to close SQL resources declared while running this function.
    # Keep any declared in global scope (e.g. mysql_conn) for later reuse.
    with __get_cursor() as cursor:
        cursor.execute('SELECT NOW() as now')
        results = cursor.fetchone()
        return str(results['now'])