ChatGpt Api Connection With Google Cloud Database Issues

[Ozren]

We are encountering multiple issues while trying to establish a connection between our Cloud Run service and Cloud SQL database using API Gateway. Our goal is to set up a secure, functional API that will allow external access to the database. Here’s a detailed summary of what we have done and the problems we’re facing.

Project Overview

• Objective: Establish a secure API connection to our Cloud SQL database so our external application (using API Gateway) can interact with it.
• Setup: We’re using Cloud SQL (MySQL) for data storage, Cloud Run for service hosting, and API Gateway for secure access. The final goal is to enable API requests to interact with the Cloud SQL database through Cloud Run, with api_key security in place.

Steps Completed So Far

1. API Gateway Configuration:

   • Configured an API Gateway (LEX API Gateway) with a swagger.yaml file.
   • Set up the x-google-backend extension to route requests from the Gateway to our Cloud Run service, with the following details:
     • Protocol: http/1.1
     • Address: Cloud Run service URL.
   • Configured security with an api_key requirement.

2. Cloud SQL Database Setup:

   • Instance Name: sqltiger
   • Databases: Two databases named LexComms and Tiger.
   • Public IP: Configured public IP access for the Cloud SQL instance.
   • Permissions: Granted relevant roles (Cloud SQL Admin, Cloud SQL Client, Cloud SQL Viewer) to the service account used for API Gateway and Cloud Run.

3. Cloud Run Configuration:

   • Environment Variables: Configured DB connection details as environment variables (DB_HOST, DB_USER, DB_PASSWORD, DB_NAME).
   • Cloud SQL Connections: Set up the Cloud SQL instance connection directly within Cloud Run.
   • Service Account: Changed the service account for Cloud Run to the one with API Gateway permissions (lex-sql-access-api).
   • Port Configuration: Verified that Cloud Run is set to use port 8080.

Current Issues

1. Database Connection Refusal:

   • Error Message: When sending a request via curl to the API Gateway, we receive:
     json

     Copy code

     {"error":"2003: Can't connect to MySQL server on 'None:3306' (Errno 111: Connection refused)"}
   • We have verified that the database is accessible via its public IP and credentials.

2. API Key and Access Issues:

   • Error Message (Previously): Initially, we encountered a PERMISSION_DENIED error, which was resolved by enabling the API service in the project.
   • Current Status: We still face issues where API requests do not seem to access the database, even after setting the API key and verifying that it is active and unrestricted.

3. Connection Testing from Local Machine:

   • We attempted to connect to the database directly using the mysql command from a local environment but encountered issues due to command recognition (Windows environment). However, the database should theoretically be reachable via its public IP.

4. Cloud SQL Connection Configuration Uncertainty:

   • We are uncertain whether the Cloud Run environment correctly recognizes the Cloud SQL connection, despite setting up the connection details both as environment variables and in the Cloud SQL connections section within Cloud Run.

Request for Assistance

Could you please guide us on the following points?

1. Configuration Check: Could you verify if our configuration of API Gateway, Cloud Run, and Cloud SQL aligns with best practices, particularly in relation to securing database connections through API Gateway?
2. Cloud SQL Direct Connection (Cloud Run): Are there any additional configurations required to ensure that Cloud Run can securely connect to Cloud SQL without issues?
3. Debugging Tips: If the configuration appears correct, could you suggest any debugging steps we could take to identify where the connection might be failing?

Thank you for your assistance with this complex setup.

[mcbsalceda]

Hi @Ozren,

Welcome to Google Cloud Community!

It looks like your MySQL connection is being refused due to network settings. Here's a quick breakdown of what could be going wrong:

• skip-networking: If MySQL is set to skip-networking, it won’t accept any TCP/IP connections. Make sure this line is commented out in your MySQL config file (my.cnf or my.ini) and restart MySQL.
• bind_address: If bind_address is set to 127.0.0.1, it only accepts local connections. Change it to 0.0.0.0 (or leave it default) to allow remote connections.
• Check that your firewall allows traffic on port 3306 to your MySQL server. If you're using Cloud SQL, make sure there’s no firewall blocking Cloud Run’s access to the database.
• If you're using a public IP, ensure there's no other network misconfiguration blocking the connection.
• If you're connecting remotely and direct access is blocked, you may need to use HTTP tunneling or SSH tunneling for secure connections.
• Follow the Cloud SQL to Cloud Run guide to ensure you're using the right connection setup (Unix socket is preferred over public IP).

To troubleshoot, check MySQL logs for connection issues and test connectivity from a different environment or consider reaching out to Google Cloud Support. Their team is great at diagnosing underlying issues. When you contact them, be sure to provide as much detail as possible and include screenshots. This will help them understand your problem better and get it sorted out more quickly.

Hope this helps!