Cloud Functions “Connection Refused” when connecting to Cloud SQL












1















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)")











share|improve this question

























  • 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
















1















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)")











share|improve this question

























  • 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














1












1








1


2






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)")











share|improve this question
















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






share|improve this question















share|improve this question













share|improve this question




share|improve this question








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











  • 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



















  • 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

















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












2 Answers
2






active

oldest

votes


















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.






share|improve this answer

































    0














    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'])





    share|improve this answer























      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
      });


      }
      });














      draft saved

      draft discarded


















      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









      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.






      share|improve this answer






























        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.






        share|improve this answer




























          1












          1








          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.






          share|improve this answer















          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.







          share|improve this answer














          share|improve this answer



          share|improve this answer








          edited Feb 11 at 16:35









          ayaio

          58.5k20132189




          58.5k20132189










          answered Feb 11 at 14:07









          Zou DinoZou Dino

          416




          416

























              0














              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'])





              share|improve this answer




























                0














                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'])





                share|improve this answer


























                  0












                  0








                  0







                  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'])





                  share|improve this answer













                  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'])






                  share|improve this answer












                  share|improve this answer



                  share|improve this answer










                  answered Nov 30 '18 at 20:20









                  Lisa Hewus FreshLisa Hewus Fresh

                  11




                  11






























                      draft saved

                      draft discarded




















































                      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.




                      draft saved


                      draft discarded














                      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





















































                      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







                      Popular posts from this blog

                      Bressuire

                      Vorschmack

                      Quarantine