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?