Hi,
I'm migrating a local Flask app that uses Flask-SQLAlchemy to Google Cloud Run, but getting StaleDataErrors on db.session.commit() related to the Cloud SQL Connector.
These errors don't happen locally or when I'm in Cloud Run if using the standard Flask-SQLAlchemy connector method which is just setting the SQLALCHEMY_DATABASE_URI variable.
Here's the error message in the log:
sqlalchemy.orm.exc.StaleDataError: UPDATE statement on table 'mytable' expected to update 1 row(s); 0 were matched.
Which is caught on the db.session.commit(), see:
File "/usr/local/lib/python3.10/site-packages/gunicorn/workers/gthread.py", line 271, in handle
keepalive = self.handle_request(req, conn)
File "/usr/local/lib/python3.10/site-packages/gunicorn/workers/gthread.py", line 323, in handle_request
respiter = self.wsgi(environ, resp.start_response)
File "/usr/local/lib/python3.10/site-packages/flask/app.py", line 2551, in __call__
return self.wsgi_app(environ, start_response)
File "/usr/local/lib/python3.10/site-packages/flask/app.py", line 2531, in wsgi_app
response = self.handle_exception(e)
File "/usr/local/lib/python3.10/site-packages/flask/app.py", line 2528, in wsgi_app
response = self.full_dispatch_request()
File "/usr/local/lib/python3.10/site-packages/flask/app.py", line 1825, in full_dispatch_request
rv = self.handle_user_exception(e)
File "/usr/local/lib/python3.10/site-packages/flask/app.py", line 1823, in full_dispatch_request
rv = self.dispatch_request()
File "/usr/local/lib/python3.10/site-packages/flask/app.py", line 1799, in dispatch_request
return self.ensure_sync(self.view_functions[rule.endpoint])(**view_args)
File "/usr/local/lib/python3.10/site-packages/flask_security/decorators.py", line 394, in decorated_view
return fn(*args, **kwargs)
File "/usr/local/lib/python3.10/site-packages/flask_security/decorators.py", line 488, in decorated_view
return fn(*args, **kwargs)
File "/app/app/admin/routes.py", line 248, in edit_mytable
db.session.commit()
File "/usr/local/lib/python3.10/site-packages/sqlalchemy/orm/scoping.py", line 553, in commit
return self._proxied.commit()
File "/usr/local/lib/python3.10/site-packages/sqlalchemy/orm/session.py", line 1903, in commit
trans.commit(_to_root=True)
File "<string>", line 2, in commit
File "/usr/local/lib/python3.10/site-packages/sqlalchemy/orm/state_changes.py", line 137, in _go
Here's how I'm using the Cloud SQL Connector:
from google.cloud.sql.connector import Connector, IPTypes
basedir = os.path.abspath(os.path.dirname(__file__))
load_dotenv(path.join(basedir, '.env'))
def getconn():
with Connector() as connector:
conn = connector.connect(
environ.get('INSTANCE_CONNECTION_NAME'), # Cloud SQL Instance Connection Name
"pymysql",
user=environ.get('DATABASE_USER'),
password=environ.get('DATABASE_PASSWORD'),
db=environ.get('DATABASE_NAME'),
ip_type= IPTypes.PRIVATE # IPTypes.PRIVATE for private IP
)
return conn
class Config(object):
SECRET_KEY = environ.get("SECRET_KEY")
FLASK_APP = "app.py"
# MySQL database
if os.getenv('K_SERVICE', ''):
# Using google cloud connector
SQLALCHEMY_DATABASE_URI = 'mysql+pymysql://'
SQLALCHEMY_ENGINE_OPTIONS = {"creator": getconn}
# else:
# Running locally
SQLALCHEMY_DATABASE_URI = 'mysql+pymysql://{}:{}@{}:{}/{}'.format(
environ.get('DATABASE_USER'),
environ.get('DATABASE_PASSWORD'),
environ.get('DATABASE_HOST'),
environ.get('DATABASE_PORT'),
environ.get('DATABASE_NAME')
) + '?charset=utf8mb4'
And here's an example route that is getting the error, but it happens for all routes that edit an object. Note that the error happens consistently when the user doesn't change any data and just clicks "Submit" on the form.
@blueprint.route('/mytable/edit/<int:id>', methods=['GET', 'POST'])
@auth_required()
@roles_required('admin')
def edit_mytable(id):
add_mytable = False
mytable = MyTables.query.get_or_404(id)
form = MyTableForm(obj=mytable)
if form.validate_on_submit():
mytable.id = form.id.data
mytable.term = form.term.data.lower()
db.session.commit()
flash('You have successfully edited mytable.', 'info')
# redirect to the next page
return redirect(url_for('admin_blueprint.list_mytable'))
form.id.data = mytable.id
form.term.data = mytable.term
return render_template('mytable.html', action="Edit",
add_mytable=add_mytable, form=form,
mytable=mytable, title="Edit My Table")
I suspect this has something to do with the connection pooling when using the Cloud SQL Connector, but I'm not able to troubleshoot it as it runs fine locally. I've tried various pool settings with no luck. Is there a setting for Cloud SQL Connector that would not cause StaleDataErrors?
If I add a db.session.refresh(object) to preceed all of my db.session.commit() statements, there is no error, but that isn't typical and I'd prefer to just keep the standard pattern of calling db.session.commit() in all routes and database updates.
Thank you!
LinkedIn
Twitter
