I’m making a Flask app with a MySQL database. I recently switched my database on heroku from an Ignite DB to a Punch MySQL DB. I have also just tried converting the connection to a pooled connection in order to avoid the maximum connections being used up.
Now, it gives me the error:
mysql.connector.errors.ProgrammingError: Collation ‘utf8mb4_0900_ai_ci’ unknown
but, when I check the character collation using MySQL Workbench, none of the tables nor the database itself shows that collation, just ‘utf8mb3_unicode_ci’
Here is some of the code:
from flask import Flask, redirect, render_template, request, session
from flask_session import Session
from tempfile import mkdtemp
from flask_mysqlpool import MySQLPool
from werkzeug.security import check_password_hash, generate_password_hash
from helpers import login_required, check_available_chars
# Configure application
app = Flask(__name__)
# Ensure templates are auto-reloaded
app.config["TEMPLATES_AUTO_RELOAD"] = True
# Configure session to use filesystem (instead of signed cookies)
app.config["SESSION_FILE_DIR"] = mkdtemp()
app.config["SESSION_PERMANENT"] = False
app.config["SESSION_TYPE"] = "filesystem"
app.secret_key = 'CENSORED'
app.config["SESSION_PERMANENT"] = True
app.config["SESSION_TYPE"] = "filesystem"
Session(app)
app.config['MYSQL_HOST'] = 'us-cluster-east-01.k8s.cleardb.net'
app.config['MYSQL_USER'] = 'CENSORED'
app.config['MYSQL_PASSWORD'] = 'CENSORED'
app.config['MYSQL_DB'] = 'CENSORED'
app.config['MYSQL_CURSORCLASS'] = 'DictCursor'
app.config['MYSQL_POOL_NAME'] = 'mysql_pool'
app.config['MYSQL_POOL_SIZE'] = 5
app.config['MYSQL_AUTOCOMMIT'] = True
mysql = MySQLPool(app)
@app.route("/login", methods=["GET", "POST"])
def login():
"""Log user in"""
# Forget any user_id
session.clear()
# User reached route via POST (as by submitting a form via POST)
if request.method == "POST":
# Ensure username was submitted
if not request.form.get("businessName"):
return render_template("login.html", errorMessage = "must provide business name")
# apostrophes and semicolons not allowed in username, to avoid SQL conflicts
if ';' in request.form.get("businessName") or '\'' in request.form.get("businessName"):
return render_template("login.html", businessName=request.form.get("businessName"), errorMessage = "Invalid business name")
# Ensure password was submitted
elif not request.form.get("password"):
return render_template("login.html", businessName=request.form.get("businessName"), errorMessage = "must provide password")
conn = mysql.connection.get_connection()
cur = conn.cursor(dictionary=True)
try:
cur.execute("SELECT * FROM businesses WHERE name = %s", (request.form.get("businessName"),))
rows = cur.fetchall()
# Ensure username exists and password is correct
if len(rows) != 1 or not check_password_hash(rows[0]['password'], request.form.get("password")):
cur.close()
return render_template("login.html", businessName=request.form.get("businessName"), errorMessage = "Invalid business name and/or password")
else:
# Remember which user has logged in (if in login page)
session["user_id"] = rows[0]['businessID']
# Redirect user to message entry page
cur.close()
finally:
cur.close()
return redirect("/")
# User reached route via GET (as by clicking a link or via redirect)
else:
return render_template("login.html")
@app.route("/logout")
def logout():
"""Log user out"""
# Forget any user_id
session.clear()
# Redirect user to login form
return redirect("/")
@app.route("/register", methods=["GET", "POST"])
def register():
conn = mysql.connection.get_connection()
cur = conn.cursor(dictionary=True)
try:
cur.execute("SELECT * FROM letterSets")
sets = cur.fetchall()
if request.method == "GET":
cur.close()
return render_template("register.html", sets=sets)
elif request.method == "POST":
if request.form.get("numberOfRows"):
numRows = int(request.form.get("numberOfRows"))
else:
numRows = 0
if request.form.get("signWidth"):
width = float(request.form.get("signWidth"))
else:
width = 0
if not (request.form.get("doubleSided")):
prevMessages = [request.form.get('prevMessageSingle')]
elif not (request.form.get("secondMessageSame")):
prevMessages = [request.form.get('prevMessageDouble1'),request.form.get('prevMessageDouble2')]
else:
prevMessages = [request.form.get('prevMessageDouble1'),request.form.get('prevMessageDouble1')]
# Ensure business name was submitted
if not request.form.get("businessName"):
cur.close()
return render_template("register.html", errorMessage = "Must provide username", numRows=numRows, signWidth=width, currentMessages=prevMessages, sets=sets)
# Ensure password was submitted
elif not request.form.get("password"):
cur.close()
return render_template("register.html", businessName=request.form.get("businessName"), numRows=numRows, signWidth=width, currentMessages=prevMessages, errorMessage = "Must provide password", sets=sets)
elif not request.form.get("numberOfRows") or numRows == 0:
cur.close()
# TODO trigger this error
return render_template("register.html", businessName=request.form.get("businessName"), signWidth=width, currentMessages=prevMessages, errorMessage = "Must provide number of rows on your sign", sets=sets)
elif not request.form.get("signWidth") or width == 0:
cur.close()
# TODO trigger this error
return render_template("register.html", businessName=request.form.get("businessName"), numRows=numRows, currentMessages=prevMessages, errorMessage = "Must provide width of sign in inches and decimal fractions. Go and measure it.", sets=sets)
# Can't use a business name already used
cur.execute("SELECT * FROM businesses WHERE name = %s", (request.form.get("businessName"),))
rows = cur.fetchall()
if len(rows) != 0:
cur.close()
# TODO trigger this error
return render_template("register.html", businessName=request.form.get("businessName"), numRows=numRows, signWidth=width, currentMessages=prevMessages, errorMessage = "Business name already exists", sets=sets)
# Can't use backslashes or semicolons in username
elif ';' in request.form.get("businessName") or '\'' in request.form.get("businessName"):
cur.close()
# TODO trigger this error
return render_template("register.html", businessName=request.form.get("businessName"), numRows=numRows, signWidth=width, currentMessages=prevMessages, errorMessage = "Invalid business name. Cannot include ' or ;", sets=sets)
else:
# Passwords don't match...
if (request.form.get("password") != request.form.get("confirmation")):
cur.close()
# TODO trigger this error
return render_template("register.html", businessName=request.form.get("businessName"), numRows=numRows, signWidth=width, currentMessages=prevMessages, errorMessage = "Passwords do not match", sets=sets)
businessName = request.form.get("businessName")
numberOfRows = request.form.get("numberOfRows")
signWidth = request.form.get("signWidth")
letterSet = request.form.get("letterSet")
cur.execute("SELECT letterChar from letterSpecs WHERE setID = %s", (letterSet,))
letterSetDB = cur.fetchall()
lettersAvailable = []
for row in letterSetDB:
lettersAvailable.append(row['letterChar'])
prevMessageLetters = {}
for letter in lettersAvailable:
prevMessageLetters[letter] = 0
for prev in prevMessages:
if prev.replace(' ','') == '':
prevMessage=""
else:
prevMessage = prev.upper()
# make sure all letters in the message are actually in the business inventory, if not, give error message with all the characters not allowed
# FUNCTION: CHECK AVAILABLE CHARS
errorMessage = check_available_chars(prevMessage, lettersAvailable)
if (errorMessage != ""):
cur.close()
return render_template("register.html", businessName=request.form.get("businessName"), numRows=numRows, signWidth=width, currentMessages=prevMessages, errorMessage=errorMessage, sets=sets)
for char in prevMessage:
if char == '9':
char="6"
if not char == ' ':
prevMessageLetters[char] += 1
password = generate_password_hash(request.form.get("password"))
if not (request.form.get('doubleSided')):
prevMessage1 = request.form.get("prevMessageSingle")
prevMessage2 = ""
doubleSided = 0
else:
prevMessage1 = request.form.get("prevMessageDouble1")
prevMessage2 = request.form.get("prevMessageDouble2")
doubleSided = 1
# insert new row with new business data, then get id from inserted row
cur.execute("INSERT INTO businesses (name, password, numberOfRows, signWidth, doubleSided, prevMessage1, prevMessage2, setID) VALUES (%s, %s, %s, %s, %s, %s, %s, %s)", (businessName, password, numberOfRows, signWidth, doubleSided, prevMessage1.upper(), prevMessage2.upper(), letterSet,))
mysql.connection.commit()
# Remember which user has logged in (if in register page)
cur.execute("SELECT businessID FROM businesses WHERE name = %s", (businessName,))
newID = cur.fetchall()[0]['businessID']
session["user_id"] = newID
cur.execute("SELECT letterChar from letterSpecs WHERE setID = (SELECT setID from businesses WHERE businessID = %s)", (session["user_id"],))
letterSetDB = cur.fetchall()
currentLetterID = 0
for letter in letterSetDB:
currentLetterID += 1
cur.execute("INSERT INTO letters (letterChar, numberAvailable, businessID, businessLetterID) VALUES (%s, %s, %s, %s)", [letter['letterChar'], prevMessageLetters[letter['letterChar']], session["user_id"], currentLetterID])
mysql.connection.commit()
# automatically go to enter page
cur.close()
return redirect("/")
finally:
cur.close()
And here is my requirements.txt file:
amqp==5.1.1
asgiref==3.5.2
async-timeout==4.0.2
attrs==23.1.0
Babel==2.13.0
billiard==3.6.4.0
blinker==1.6.2
cachelib==0.10.2
celery==5.2.7
certifi==2023.5.7
cffi==1.16.0
charset-normalizer==3.0.1
chromedriver-py==118.0.5993.70
click==8.1.3
click-didyoumean==0.3.0
click-plugins==1.1.1
click-repl==0.2.0
colorama==0.4.6
Deprecated==1.2.14
dj-database-url==2.0.0
Django==4.1.3
django-celery-results==2.4.0
django-heroku==0.3.1
django-jsonfield==1.4.1
django-mathfilters==1.0.0
djangorestframework==3.14.0
Flask==2.3.2
Flask-MySQL==1.5.2
Flask-MySQLdb==1.0.1
flask-mysqlpool==1.0.5
Flask-Session==0.5.0
gunicorn==20.0.4
h11==0.14.0
idna==3.4
img2pdf==0.5.1
itsdangerous==2.1.2
Jinja2==3.1.2
jsonfield==3.1.0
kombu==5.2.4
lxml==5.1.0
MarkupSafe==2.1.3
mysql==0.0.3
mysql-connector-python==8.0.33
mysqlclient==2.1.1
numpy==1.23.4
outcome==1.2.0
packaging==23.2
pathlib==1.0.1
pdf2image==1.16.2
pikepdf==8.11.2
pillow==10.2.0
prompt-toolkit==3.0.36
protobuf==3.20.3
psycopg2==2.9.6
psycopg2-binary==2.9.6
pycparser==2.21
PyMySQL==1.0.3
pypdf==3.3.0
PyPDF2==3.0.1
PySocks==1.7.1
python-dotenv==1.0.0
pytz==2022.7.1
redis==4.5.0
reportlab==3.6.12
requests==2.31.0
selenium==4.14.0
simplejson==3.18.1
six==1.16.0
sniffio==1.3.0
sortedcontainers==2.4.0
sqlparse==0.4.3
trio==0.22.2
trio-websocket==0.11.1
typing_extensions==4.5.0
tzdata==2022.6
urllib3==1.26.14
vine==5.0.0
wcwidth==0.2.6
Werkzeug==2.3.4
whitenoise==6.4.0
wrapt==1.16.0
wsproto==1.2.0
also, with this new “pooling” connection, what is the proper “commit” statement?
