Using the example code for connecting a cloud function to Cloud SQL, I successfully created a new function with slightly different output. It ran fine for a while. Now, with no changes whatsoever to the code, I am getting a OperationalError: (2003, "Can't connect to MySQL server on 'localhost' ([Errno 111] Connection refused)"). Here's the full error:
sql-test-function-1
szzv41dltb8k
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/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/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/worker.py", line 192, in call_user_function return self._user_function(request_or_event) File "/user_code/main.py", line 50, 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)")
I've seen numerous examples of people having a similar problem both here and on Google GitHub support and have tried all their solutions (enabling Cloud SQL Admin API, implementing the answer in this Stackoverflow question, etc.) but I keep getting the same error. I've tried restarting the SQL instance as well as creating a new function with the same code in a Chrome Incognito window to avoid caching issues: same error.
Here is my code for the cloud function:
from os import getenv
import pymysql
from pymysql.err import OperationalError
# TODO(developer): specify SQL connection details
CONNECTION_NAME = getenv(
'INSTANCE_CONNECTION_NAME',
'la-cloud-functions:us-central1:cf-sql-1')
DB_USER = getenv('MYSQL_USER', 'root')
DB_PASSWORD = getenv('MYSQL_PASSWORD', 'root')
DB_NAME = getenv('MYSQL_DATABASE', 'gallery')
mysql_config = {
'user': DB_USER,
'password': DB_PASSWORD,
'db': DB_NAME,
'charset': 'utf8mb4',
'cursorclass': pymysql.cursors.DictCursor,
'autocommit': True
}
# 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(request):
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:
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)
# 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 * from albums')
row = cursor.fetchone()
while row is not None:
print(row)
row = cursor.fetchone()
If anyone has any other suggestions, I'd be thrilled.
TIA - Joe
