Unable to connect to GCP cloud SQL from FAST API container using unix_socket

Question

Running the FAST API image is throwing the following error.

pymysql.err.OperationalError: (2003, "Can't connect to MySQL server on None")

I am using databases for async operations with 'mysqldb' driver. Even thought he DB credentials are correct the connection is not being established. Please suggest any solutions.

from databases import Database
from utils.const import DB_USER, DB_NAME, DB_PASSWORD, PROJECT_ID, REGION, INSTANCE

# Define the Cloud SQL connection details
SOCKET_PATH = f'/cloudsql/{PROJECT_ID}:{REGION}:{INSTANCE}'

# Create the connection string
DATABASE_URL = f"mysql+mysqldb://{DB_USER}:{DB_PASSWORD}@/{DB_NAME}?unix_socket={SOCKET_PATH}"

# Create the Database object
db = Database(DATABASE_URL)

Answer 1

There are a few things you can try to troubleshoot your connection to Cloud SQL from a FastAPI container using Unix sockets:

1. Make sure that the Cloud SQL instance is up and running. You can check this by running the following command:

gcloud sql instances list

This will list all of the Cloud SQL instances in your project. Make sure that the instance you are trying to connect to is listed and has a status of RUNNING.

2. Make sure that the Cloud SQL instance is accessible from the FastAPI container. You can do this by running the following command from within the container:

nc -vz 3306

This will try to connect to the Cloud SQL instance on port 3306. If the connection is successful, you will see the following output:

Connection to 3306 succeeded!

If the connection is unsuccessful, you will see the following output:

nc: connect to 3306 (localhost): Connection refused

If the connection is unsuccessful, you must ensure the Cloud SQL instance is accessible from the FastAPI container. You can do this by checking the firewall rules for the Cloud SQL instance and making sure that the FastAPI container can access the instance.

3. Make sure that the database credentials are correct. You can check this by running the following command from within the FastAPI container:

mysql -u <db_user> -p <db_password>

This will try to connect to the Cloud SQL instance using the specified database credentials. If the connection is successful, you will be prompted to enter a password. If the connection is unsuccessful, you will see the following output:

ERROR 1045 (28000): Access denied for user '<db_user>'@'localhost' (using password: YES)

If the connection is unsuccessful, you need to make sure that the database credentials are correct. You can check this by logging into the Cloud SQL instance and running the following command:

SELECT user, host, password FROM mysql.user;

This will list all of the users and passwords for the Cloud SQL instance. Make sure that the database credentials you are using are listed in the output of this command.

4. Make sure that the Unix socket path is correct. The Unix socket path is located in the SOCKET_PATH variable in your code. Make sure that this path is correct and that the FastAPI container has access to the Unix socket file.

If you have checked all of the above and you are still unable to connect to Cloud SQL, you can try the following:

• Restart the Cloud SQL instance.
• Restart the FastAPI container.
• Try using a different database driver, such as PyMySQL.

If you are still having trouble connecting to Cloud SQL, please contact Google Cloud support for assistance.

Answer 2

As per the databases package documentation it does not mention it supports mysqldb(mysqlclient)

I would recommend maybe trying to use aiomysql or asyncmy as an async MySQL option.

These are properly tested for unix sockets connections. (unix socket test)