I've deployed my Python app on Heroku and have connected it to a clearDB database. This works perfectly when run locally, however when running on Heroku my INSERT statements don't seem to be committing properly (sometimes they do, sometimes they don't). It's running through gunicorn.
This is my MySQL setup:
APP_ROOT = os.path.join(os.path.dirname(__file__), '..')
dotenv_path = os.path.join(APP_ROOT, '.env')
db_user = os.getenv('DB_USER2')
db_pass = os.getenv('DB_PASSWORD2')
db_database = os.getenv('DB_DATABASE2')
db_host = os.getenv('DB_HOST2')
app.config['MYSQL_DATABASE_USER'] = db_user
app.config['MYSQL_DATABASE_PASSWORD'] = db_pass
app.config['MYSQL_DATABASE_DB'] = db_database
app.config['MYSQL_DATABASE_HOST'] = db_host
These variables are coming from a .env file. But they are also added as environmental variables on Heroku. So connection to the server is same both locally and on Heroku.
It's completely random whether it works or not. Fetch statements work perfectly fine, but inserting or updating seems to cause me a lot of issues. Again, this works perfectly with no issues locally, but doesn't on Heroku. (When run locally, it seems to take a bit of time to save the data, which is fine. But it doesn't take much time at all on Heroku so I'm assuming it's somehow skipping the commit).
There are no errors in the logs either. It just seems to bypass the statement sometimes and not commit. Doing a hard refresh sometimes brings up the data, but then it disappears again. Most of the times the data isn't saved at all.
I have one cursor open throughout the app and I'm using
conn.ping(True) before every SQL statement, and
conn.commit() after fetching the data.
Any help would be appreciated, thank you!