Cloud Functions “Connection Refused” when connecting to Cloud SQL
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)")
python google-cloud-platform google-cloud-functions google-cloud-sql
|
show 3 more comments
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)")
python google-cloud-platform google-cloud-functions google-cloud-sql
It's failing to connect and defaulting to the localhost for development testing, and obvious the CF isn't running a sql instance so that fails. Are you attempting to connect through the private ip of the instance? I'd double check your connection string.
– FridayPush
Nov 15 '18 at 20:24
I'm using the exact code in the example Google provided. My understanding is that it isnt a private ip since CF can connect to Cloud SQL directly so i can use the "connection name" as the server in the format of f'/cloudsql/project.region.instance'
– LoxBagel
Nov 16 '18 at 15:28
TheINSTANCE_CONNECTION_NAME
should be of the form<project_id>:<region>:<instance_id>
. You can copy it from the Instance Overview page under "Instance connection name". Can you try that and let us know if it works?
– Dustin Ingram
Nov 16 '18 at 22:02
@LoxBagel did you have time to try the code with the INSTANCE_CONNECTION_NAME in form <project_id>:<region>:<instance_id>? If so, please, answer the question to the benefit of the community.
– Alex Riquelme
Nov 22 '18 at 14:46
I did use that format, and I still get the same error. It appears that just copying the example code and running does not work.
– LoxBagel
Nov 23 '18 at 15:31
|
show 3 more comments
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)")
python google-cloud-platform google-cloud-functions google-cloud-sql
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)")
python google-cloud-platform google-cloud-functions google-cloud-sql
python google-cloud-platform google-cloud-functions google-cloud-sql
edited Nov 16 '18 at 8:38
Mangu
2,41621628
2,41621628
asked Nov 15 '18 at 18:37
LoxBagelLoxBagel
4326
4326
It's failing to connect and defaulting to the localhost for development testing, and obvious the CF isn't running a sql instance so that fails. Are you attempting to connect through the private ip of the instance? I'd double check your connection string.
– FridayPush
Nov 15 '18 at 20:24
I'm using the exact code in the example Google provided. My understanding is that it isnt a private ip since CF can connect to Cloud SQL directly so i can use the "connection name" as the server in the format of f'/cloudsql/project.region.instance'
– LoxBagel
Nov 16 '18 at 15:28
TheINSTANCE_CONNECTION_NAME
should be of the form<project_id>:<region>:<instance_id>
. You can copy it from the Instance Overview page under "Instance connection name". Can you try that and let us know if it works?
– Dustin Ingram
Nov 16 '18 at 22:02
@LoxBagel did you have time to try the code with the INSTANCE_CONNECTION_NAME in form <project_id>:<region>:<instance_id>? If so, please, answer the question to the benefit of the community.
– Alex Riquelme
Nov 22 '18 at 14:46
I did use that format, and I still get the same error. It appears that just copying the example code and running does not work.
– LoxBagel
Nov 23 '18 at 15:31
|
show 3 more comments
It's failing to connect and defaulting to the localhost for development testing, and obvious the CF isn't running a sql instance so that fails. Are you attempting to connect through the private ip of the instance? I'd double check your connection string.
– FridayPush
Nov 15 '18 at 20:24
I'm using the exact code in the example Google provided. My understanding is that it isnt a private ip since CF can connect to Cloud SQL directly so i can use the "connection name" as the server in the format of f'/cloudsql/project.region.instance'
– LoxBagel
Nov 16 '18 at 15:28
TheINSTANCE_CONNECTION_NAME
should be of the form<project_id>:<region>:<instance_id>
. You can copy it from the Instance Overview page under "Instance connection name". Can you try that and let us know if it works?
– Dustin Ingram
Nov 16 '18 at 22:02
@LoxBagel did you have time to try the code with the INSTANCE_CONNECTION_NAME in form <project_id>:<region>:<instance_id>? If so, please, answer the question to the benefit of the community.
– Alex Riquelme
Nov 22 '18 at 14:46
I did use that format, and I still get the same error. It appears that just copying the example code and running does not work.
– LoxBagel
Nov 23 '18 at 15:31
It's failing to connect and defaulting to the localhost for development testing, and obvious the CF isn't running a sql instance so that fails. Are you attempting to connect through the private ip of the instance? I'd double check your connection string.
– FridayPush
Nov 15 '18 at 20:24
It's failing to connect and defaulting to the localhost for development testing, and obvious the CF isn't running a sql instance so that fails. Are you attempting to connect through the private ip of the instance? I'd double check your connection string.
– FridayPush
Nov 15 '18 at 20:24
I'm using the exact code in the example Google provided. My understanding is that it isnt a private ip since CF can connect to Cloud SQL directly so i can use the "connection name" as the server in the format of f'/cloudsql/project.region.instance'
– LoxBagel
Nov 16 '18 at 15:28
I'm using the exact code in the example Google provided. My understanding is that it isnt a private ip since CF can connect to Cloud SQL directly so i can use the "connection name" as the server in the format of f'/cloudsql/project.region.instance'
– LoxBagel
Nov 16 '18 at 15:28
The
INSTANCE_CONNECTION_NAME
should be of the form <project_id>:<region>:<instance_id>
. You can copy it from the Instance Overview page under "Instance connection name". Can you try that and let us know if it works?– Dustin Ingram
Nov 16 '18 at 22:02
The
INSTANCE_CONNECTION_NAME
should be of the form <project_id>:<region>:<instance_id>
. You can copy it from the Instance Overview page under "Instance connection name". Can you try that and let us know if it works?– Dustin Ingram
Nov 16 '18 at 22:02
@LoxBagel did you have time to try the code with the INSTANCE_CONNECTION_NAME in form <project_id>:<region>:<instance_id>? If so, please, answer the question to the benefit of the community.
– Alex Riquelme
Nov 22 '18 at 14:46
@LoxBagel did you have time to try the code with the INSTANCE_CONNECTION_NAME in form <project_id>:<region>:<instance_id>? If so, please, answer the question to the benefit of the community.
– Alex Riquelme
Nov 22 '18 at 14:46
I did use that format, and I still get the same error. It appears that just copying the example code and running does not work.
– LoxBagel
Nov 23 '18 at 15:31
I did use that format, and I still get the same error. It appears that just copying the example code and running does not work.
– LoxBagel
Nov 23 '18 at 15:31
|
show 3 more comments
2 Answers
2
active
oldest
votes
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.
add a comment |
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'])
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53325928%2fcloud-functions-connection-refused-when-connecting-to-cloud-sql%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
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.
add a comment |
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.
add a comment |
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.
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.
edited Feb 11 at 16:35
ayaio
58.5k20132189
58.5k20132189
answered Feb 11 at 14:07
Zou DinoZou Dino
416
416
add a comment |
add a comment |
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'])
add a comment |
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'])
add a comment |
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'])
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'])
answered Nov 30 '18 at 20:20
Lisa Hewus FreshLisa Hewus Fresh
11
11
add a comment |
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53325928%2fcloud-functions-connection-refused-when-connecting-to-cloud-sql%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
It's failing to connect and defaulting to the localhost for development testing, and obvious the CF isn't running a sql instance so that fails. Are you attempting to connect through the private ip of the instance? I'd double check your connection string.
– FridayPush
Nov 15 '18 at 20:24
I'm using the exact code in the example Google provided. My understanding is that it isnt a private ip since CF can connect to Cloud SQL directly so i can use the "connection name" as the server in the format of f'/cloudsql/project.region.instance'
– LoxBagel
Nov 16 '18 at 15:28
The
INSTANCE_CONNECTION_NAME
should be of the form<project_id>:<region>:<instance_id>
. You can copy it from the Instance Overview page under "Instance connection name". Can you try that and let us know if it works?– Dustin Ingram
Nov 16 '18 at 22:02
@LoxBagel did you have time to try the code with the INSTANCE_CONNECTION_NAME in form <project_id>:<region>:<instance_id>? If so, please, answer the question to the benefit of the community.
– Alex Riquelme
Nov 22 '18 at 14:46
I did use that format, and I still get the same error. It appears that just copying the example code and running does not work.
– LoxBagel
Nov 23 '18 at 15:31