Unknown collation: 'utf8mb4_0900_ai_ci'

[CraigFoote]

Hi guys, great book. I'm at the last chapter and I'm stuck. I've got the edited schema.sql and data.sql sourced into MariaDB and I can run queries at the MariaDB prompt. When I try to connect using the webapp though I get an error - mysql.connector.errors.DatabaseError: 1273 (HY000): Unknown collation: 'utf8mb4_0900_ai_ci'.

I've posed a question on StackOverflow with more information.

Have you seen this before or have any ideas how to fix? I've installed mariadb-server via apt, maybe there's more I need to install?

Trace:

Traceback (most recent call last):
  File "/home/craig/Documents/Link to Documents/Head-First Python 3rd ed/webapp/.venv/lib/python3.12/site-packages/mysql/connector/connection_cext.py", line 705, in cmd_query
    self._cmysql.query(
_mysql_connector.MySQLInterfaceError: Unknown collation: 'utf8mb4_0900_ai_ci'

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "/home/craig/Documents/Link to Documents/Head-First Python 3rd ed/webapp/.venv/lib/python3.12/site-packages/flask/app.py", line 1498, in __call__
    return self.wsgi_app(environ, start_response)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/home/craig/Documents/Link to Documents/Head-First Python 3rd ed/webapp/.venv/lib/python3.12/site-packages/flask/app.py", line 1476, in wsgi_app
    response = self.handle_exception(e)
               ^^^^^^^^^^^^^^^^^^^^^^^^
  File "/home/craig/Documents/Link to Documents/Head-First Python 3rd ed/webapp/.venv/lib/python3.12/site-packages/flask/app.py", line 1473, in wsgi_app
    response = self.full_dispatch_request()
               ^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/home/craig/Documents/Link to Documents/Head-First Python 3rd ed/webapp/.venv/lib/python3.12/site-packages/flask/app.py", line 882, in full_dispatch_request
    rv = self.handle_user_exception(e)
         ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/home/craig/Documents/Link to Documents/Head-First Python 3rd ed/webapp/.venv/lib/python3.12/site-packages/flask/app.py", line 880, in full_dispatch_request
    rv = self.dispatch_request()
         ^^^^^^^^^^^^^^^^^^^^^^^
  File "/home/craig/Documents/Link to Documents/Head-First Python 3rd ed/webapp/.venv/lib/python3.12/site-packages/flask/app.py", line 865, in dispatch_request
    return self.ensure_sync(self.view_functions[rule.endpoint])(**view_args)  # type: ignore[no-any-return]
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/home/craig/Documents/Link to Documents/Head-First Python 3rd ed/webapp/app.py", line 16, in display_swim_sessions
    data = data_utils.get_swim_sessions()
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/media/craig/240ssd/Documents/Head-First Python 3rd ed/webapp/data_utils.py", line 14, in get_swim_sessions
    with DBcm.UseDatabase(db_details) as db:
  File "/home/craig/Documents/Link to Documents/Head-First Python 3rd ed/webapp/.venv/lib/python3.12/site-packages/DBcm.py", line 96, in __enter__
    self.conn = mysql.connector.connect(**self.configuration)
                ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/home/craig/Documents/Link to Documents/Head-First Python 3rd ed/webapp/.venv/lib/python3.12/site-packages/mysql/connector/pooling.py", line 322, in connect
    return CMySQLConnection(*args, **kwargs)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/home/craig/Documents/Link to Documents/Head-First Python 3rd ed/webapp/.venv/lib/python3.12/site-packages/mysql/connector/connection_cext.py", line 151, in __init__
    self.connect(**kwargs)
  File "/home/craig/Documents/Link to Documents/Head-First Python 3rd ed/webapp/.venv/lib/python3.12/site-packages/mysql/connector/abstracts.py", line 1411, in connect
    self._post_connection()
  File "/home/craig/Documents/Link to Documents/Head-First Python 3rd ed/webapp/.venv/lib/python3.12/site-packages/mysql/connector/abstracts.py", line 1351, in _post_connection
    self.set_charset_collation(charset=self._charset_id)
  File "/home/craig/Documents/Link to Documents/Head-First Python 3rd ed/webapp/.venv/lib/python3.12/site-packages/mysql/connector/abstracts.py", line 1326, in set_charset_collation
    self._execute_query(f"SET NAMES '{charset_name}' COLLATE '{collation_name}'")
  File "/home/craig/Documents/Link to Documents/Head-First Python 3rd ed/webapp/.venv/lib/python3.12/site-packages/mysql/connector/opentelemetry/context_propagation.py", line 97, in wrapper
    return method(cnx, *args, **kwargs)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/home/craig/Documents/Link to Documents/Head-First Python 3rd ed/webapp/.venv/lib/python3.12/site-packages/mysql/connector/connection_cext.py", line 713, in cmd_query
    raise get_mysql_exception(
mysql.connector.errors.DatabaseError: 1273 (HY000): Unknown collation: 'utf8mb4_0900_ai_ci'

[CraigFoote]

UPDATE: I just tried a little test with the MariaDB Connector and it worked. Now to see if I can wrangle the book's code to use it.

[barryp-work]

I've taken a quick look at your stackoverflow page and what I saw there led me to take a look at the docs to the underlying MySQL connector/python library. This page https://dev.mysql.com/doc/connector-python/en/connector-python-api-mysqlconnection-set-charset-collation.html caught my eye, especially the syntax. It's appears the set_charset_collation function can take two parameters, which both default to "None". Maybe try calling the function like this: db.set_charset_collation(collation='utf8mb4_uca1400_ai_ci') to see if that makes a difference? Without the collation= bit, the calls shown in your stackoverflow post are assigning utf8mb4_uca1400_ai_ci to the charset parameter (which is not what you want here).

Keeping my fingers crossed for you.

--Paul.

[CraigFoote]

Hi Paul, thanks for replying. I tried adding the db.set_charset_collation(collation='utf8mb4_uca1400_ai_ci') and reconnecting but I get the same error :(

In the meantime I suspect the cadence of the MariaDB-Connector matches MariaDB better than the mysql-connector.

[barryp-work]

Yes, most likely. I've never seen this error before but - if truth be told - my database interactions tend to be pretty vanilla (with defaults charsets, etc., etc.). My "older" DBcm code (developed for - and documented in - the 2nd edition of my book) continued to work for me when I updated everything for the 3rd edition, even though I'm now using MariaDB by default. Clearly, things are a little more complex than I anticipated. Sorry about that.

Take a look at the API for the MariaDB connector module and compare it to the MySQL one. If they are compatible, a small edit to the imports statements at the top of the DBcm module may be all that's required here.

Let me know how you get on.

--Paul.

[CraigFoote]

I will, thanks Paul. Coffee...

[CraigFoote]

Following https://mariadb.com/docs/server/connect/programming-languages/python/, first I had to install MariaDB connector, pip3 install mariadb==1.1.10. It produced an error stating I needed to install MariaDB Connector/C using sudo apt install libmariadb3 libmariadb-dev. Once installed and imported into a copy of DBcm from the appendix code, I saw there was no "database" key in the dict it passes to the mariadb.connector but instead it takes a port, 3306. So I also modified the queries.py to fully qualify the table references with swimDB.tablename. I also had to tweak the error handling to catch MariaDB.error but then everything ran fine! Back to the book now. Thanks for the help.

[barryp-work]

I'm glad you've fixed your issue. Thanks for the kind comments on my book. 👍