Collectives™ on Stack Overflow

Find centralized, trusted content and collaborate around the technologies you use most.

Teams

Q&A for work

Connect and share knowledge within a single location that is structured and easy to search.

I have set up an instance running a Cloud SQL database (MySQL), which I would like to access from an application I am deploying via GAE (python/flask) in the same project. I have tested this by (all on my local machine)

The above works. However, when I attempt to run the app by deploying to GAE I get an access error:

sqlalchemy.exc.OperationalError: (MySQLdb.OperationalError) (2003, "Can't connect to MySQL server on '[sql instance ip]' (110)")
I have set up my App Engine default service account with roles "Editor" (the default) and "Cloud SQL Client" (which I think is redundant based on permissions..). I have the Cloud SQL Admin API enabled.
My guess is that this is some trivial misconfiguration on my part, as I am new to GCP generally.
Error log after deploying/loading the app url:
2023-04-30 16:16:41 default[20230430t103558]  "GET / HTTP/1.1" 500
2023-04-30 16:16:42 default[20230430t103558]  [2023-04-30 16:16:42 +0000] [12] [INFO] Starting gunicorn 20.1.0
2023-04-30 16:16:42 default[20230430t103558]  [2023-04-30 16:16:42 +0000] [12] [INFO] Listening at: http://0.0.0.0:8081 (12)
2023-04-30 16:16:42 default[20230430t103558]  [2023-04-30 16:16:42 +0000] [12] [INFO] Using worker: sync
2023-04-30 16:16:42 default[20230430t103558]  [2023-04-30 16:16:42 +0000] [15] [INFO] Booting worker with pid: 15
2023-04-30 16:16:46 default[20230430t103558]  Server initialized for threading.
2023-04-30 16:18:53 default[20230430t103558]  [2023-04-30 16:18:53,864] ERROR in app: Exception on / [GET]
2023-04-30 16:18:53 default[20230430t103558]  Traceback (most recent call last):    File "/layers/google.python.pip/pip/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 145, in __init__      self._dbapi_connection = engine.raw_connection()    File "/layers/google.python.pip/pip/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 3288, in raw_connection      return self.pool.connect()    File "/layers/google.python.pip/pip/lib/python3.8/site-packages/sqlalchemy/pool/base.py", line 452, in connect      return _ConnectionFairy._checkout(self)    File "/layers/google.python.pip/pip/lib/python3.8/site-packages/sqlalchemy/pool/base.py", line 1268, in _checkout      fairy = _ConnectionRecord.checkout(pool)    File "/layers/google.python.pip/pip/lib/python3.8/site-packages/sqlalchemy/pool/base.py", line 716, in checkout      rec = pool._do_get()    File "/layers/google.python.pip/pip/lib/python3.8/site-packages/sqlalchemy/pool/impl.py", line 169, in _do_get      self._dec_overflow()    File "/layers/google.python.pip/pip/lib/python3.8/site-packages/sqlalchemy/util/langhelpers.py", line 147, in __exit__      raise exc_value.with_traceback(exc_tb)    File "/layers/google.python.pip/pip/lib/python3.8/site-packages/sqlalchemy/pool/impl.py", line 166, in _do_get      return self._create_connection()    File "/layers/google.python.pip/pip/lib/python3.8/site-packages/sqlalchemy/pool/base.py", line 393, in _create_connection      return _ConnectionRecord(self)    File "/layers/google.python.pip/pip/lib/python3.8/site-packages/sqlalchemy/pool/base.py", line 678, in __init__      self.__connect()    File "/layers/google.python.pip/pip/lib/python3.8/site-packages/sqlalchemy/pool/base.py", line 903, in __connect      pool.logger.debug("Error on connect(): %s", e)    File "/layers/google.python.pip/pip/lib/python3.8/site-packages/sqlalchemy/util/langhelpers.py", line 147, in __exit__      raise exc_value.with_traceback(exc_tb)    File "/layers/google.python.pip/pip/lib/python3.8/site-packages/sqlalchemy/pool/base.py", line 898, in __connect      self.dbapi_connection = connection = pool._invoke_creator(self)    File "/layers/google.python.pip/pip/lib/python3.8/site-packages/sqlalchemy/engine/create.py", line 637, in connect      return dialect.connect(*cargs, **cparams)    File "/layers/google.python.pip/pip/lib/python3.8/site-packages/sqlalchemy/engine/default.py", line 616, in connect      return self.loaded_dbapi.connect(*cargs, **cparams)    File "/layers/google.python.pip/pip/lib/python3.8/site-packages/MySQLdb/__init__.py", line 123, in Connect      return Connection(*args, **kwargs)    File "/layers/google.python.pip/pip/lib/python3.8/site-packages/MySQLdb/connections.py", line 185, in __init__      super().__init__(*args, **kwargs2)  MySQLdb.OperationalError: (2003, "Can't connect to MySQL server on '[sql instance ip]' (110)")
2023-04-30 16:18:53 default[20230430t103558]  The above exception was the direct cause of the following exception:
2023-04-30 16:18:53 default[20230430t103558]  Traceback (most recent call last):    File "/layers/google.python.pip/pip/lib/python3.8/site-packages/flask/app.py", line 2462, in wsgi_app      ctx.push()    File "/layers/google.python.pip/pip/lib/python3.8/site-packages/flask/ctx.py", line 375, in push      self.session = session_interface.open_session(self.app, self.request)    File "/layers/google.python.pip/pip/lib/python3.8/site-packages/flask_session/sessions.py", line 533, in open_session      saved_session = self.sql_session_model.query.filter_by(    File "/layers/google.python.pip/pip/lib/python3.8/site-packages/sqlalchemy/orm/query.py", line 2743, in first      return self.limit(1)._iter().first()  # type: ignore    File "/layers/google.python.pip/pip/lib/python3.8/site-packages/sqlalchemy/orm/query.py", line 2842, in _iter      result: Union[ScalarResult[_T], Result[_T]] = self.session.execute(    File "/layers/google.python.pip/pip/lib/python3.8/site-packages/sqlalchemy/orm/session.py", line 2231, in execute      return self._execute_internal(    File "/layers/google.python.pip/pip/lib/python3.8/site-packages/sqlalchemy/orm/session.py", line 2116, in _execute_internal      conn = self._connection_for_bind(bind)    File "/layers/google.python.pip/pip/lib/python3.8/site-packages/sqlalchemy/orm/session.py", line 1983, in _connection_for_bind      return trans._connection_for_bind(engine, execution_options)    File "<string>", line 2, in _connection_for_bind    File "/layers/google.python.pip/pip/lib/python3.8/site-packages/sqlalchemy/orm/state_changes.py", line 137, in _go
2023-04-30 16:18:53 default[20230430t103558]      ret_value = fn(self, *arg, **kw)
2023-04-30 16:18:53 default[20230430t103558]    File "/layers/google.python.pip/pip/lib/python3.8/site-packages/sqlalchemy/orm/session.py", line 1110, in _connection_for_bind
[... many lines of traceback ...]
2023-04-30 16:18:53 default[20230430t103558]    File "/layers/google.python.pip/pip/lib/python3.8/site-packages/MySQLdb/connections.py", line 185, in __init__
2023-04-30 16:18:53 default[20230430t103558]      super().__init__(*args, **kwargs2)
2023-04-30 16:18:53 default[20230430t103558]  sqlalchemy.exc.OperationalError: (MySQLdb.OperationalError) (2003, "Can't connect to MySQL server on '[sql instance ip]' (110)")
2023-04-30 16:18:53 default[20230430t103558]  (Background on this error at: https://sqlalche.me/e/20/e3q8)
Here is the relevant piece of my main.py:
USERNAME = os.environ.get('CLOUD_SQL_USERNAME')
PASSWORD = os.environ.get('CLOUD_SQL_PASSWORD')
PUBLIC_IP_ADDRESS = os.environ.get('CLOUD_SQL_PUBLIC_IP_ADDRESS')
DBNAME = os.environ.get('CLOUD_SQL_DATABASE_NAME')
CONN_NAME = os.environ.get('CLOUD_SQL_CONN_NAME')
if USERNAME and PASSWORD and PUBLIC_IP_ADDRESS and DBNAME and CONN_NAME:
    database_uri = "mysql+mysqldb://" \
                   f"{USERNAME}:{PASSWORD}@{PUBLIC_IP_ADDRESS}/{DBNAME}" \
                   f"?unix_socket=/cloudsql/{CONN_NAME}"
else:
    database_uri = 'sqlite:///mydb.sqlite3'
app = Flask(__name__)
app.config['SECRET_KEY'] = 'very_secret'
app.config['SQLALCHEMY_DATABASE_URI'] = database_uri
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False
db = SQLAlchemy(app)
Update:
I wanted to share the changes which seem to resolve my initial connection issues while still using Flask-SQLAlchemy as above, since I haven't found that approach in examples so far. Maybe it can be useful for novices like me. Most of the relevant info is in the tutorial a couple helpful folks have pointed me to.
from flask import Flask
from flask_sqlalchemy import SQLAlchemy
from google.cloud.sql.connector import Connector, IPTypes
import pymysql
ip_type = IPTypes.PUBLIC
connector = Connector(ip_type)
USERNAME = os.environ.get('CLOUD_SQL_USERNAME')
PASSWORD = os.environ.get('CLOUD_SQL_PASSWORD')
DBNAME = os.environ.get('CLOUD_SQL_DATABASE_NAME')
CONN_NAME = os.environ.get('CLOUD_SQL_CONN_NAME')
def get_engine_options():
    if USERNAME and PASSWORD and DBNAME and CONN_NAME:
        def getconn() -> pymysql.connections.Connection:
            conn: pymysql.connections.Connection = connector.connect(
                CONN_NAME,
                "pymysql",
                user=USERNAME,
                password=PASSWORD,
                db=DBNAME,
            return conn
        return {'creator' : getconn}
    return None
database_uri = "mysql+pymysql://" if get_engine_options() is not None else "sqlite:///mydb.sqlite3"
app = Flask(__name__)
app.config['SECRET_KEY'] = 'very_secret'
app.config['SQLALCHEMY_DATABASE_URI'] = database_uri
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False
db = SQLAlchemy(app, engine_options=get_engine_options())
                Did you follow this tutorial? cloud.google.com/sql/docs/mysql/connect-app-engine-standard
– guillaume blaquiere
                Apr 30 at 18:12
                Yes, I attempted to follow this tutorial using the Unix sockets approach. Let me add a bit more context from my application code in case that's helpful.
– Sean Pohorence
                Apr 30 at 18:29
                if you follow correctly the documentation, you should not add the public IP address. it's useless and the source of your error. But you have to configure the connection inside the app.yaml. Can you share it also?
– guillaume blaquiere
                Apr 30 at 18:40
                Ah, I have certainly missed this step completely then. My app.yaml is very short, just specifies entrypoint: gunicorn -t 0 -b :$PORT main:app, runtime: python38, and my environment vars. Is there an example of how that needs to be configured with the ip address/connection name?
– Sean Pohorence
                Apr 30 at 18:49
                You can refer to the repo sample. I think you have only the public IP to remove. github.com/GoogleCloudPlatform/python-docs-samples/tree/…
– guillaume blaquiere
                Apr 30 at 19:17
This depends on what type of GAE you are using - GAE Standard or GAE Flexible. Try to identify that and then the following links should help you complete your task.
GAE Standard: https://cloud.google.com/sql/docs/mysql/connect-app-engine-standard
GAE Flexible: https://cloud.google.com/sql/docs/mysql/connect-app-engine-flexible
Common Issues
GAE service is not having enough permissions to access sql
Networking - depending on the type of GAE you use.
The Cloud SQL Python Connector is definitely the recommended approach for connecting in any application/environment as it works for both Public and Private IP.
There is a section of the README that outlines usage with Flask-SQLAlchemy. Looks very similar to your update approach.
from flask import Flask
from flask_sqlalchemy import SQLAlchemy
from google.cloud.sql.connector import Connector, IPTypes
# Python Connector database connection function
def getconn():
    with Connector() as connector:
        conn = connector.connect(
            "project:region:instance-name", # Cloud SQL Instance Connection Name
            "pymysql",
            user="my-user",
            password="my-password",
            db="my-database",
            ip_type= IPTypes.PUBLIC  # IPTypes.PRIVATE for private IP
        return conn
app = Flask(__name__)
# configure Flask-SQLAlchemy to use Python Connector
app.config['SQLALCHEMY_DATABASE_URI'] = "mysql+pymysql://"
app.config['SQLALCHEMY_ENGINE_OPTIONS'] = {
    "creator": getconn
db = SQLAlchemy(app)
        
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.