character_set_database errors with mysql8

[mrbart]

so mysql 8.0.26-17 now returns 'utf8' as 'utf8mb3' if the server is configured for backwards compatibility (started up with default-character-set=utf8)

the above makes it impossible to setup trac on a completely fresh installation with the latest versions of:

Ubuntu 20
Python 3.8.10
PyMySQL 0.9.3
Trac 1.5.3
MySQL 8.0.26-17

attached is modded version of mysql_backend.py which i modded/haxed just to force things along

[Jun Omae]

Please post the patch as an unified diff. See also TracDev/SubmittingPatches#Makethepatch.

[Jun Omae]

In addition, please share the error which you got.

[Jun Omae]

Tried to create a Trac environment with Trac 1.0.20 and MySQL 8.0.28:

Database connection string [sqlite:db/trac.db]> mysql://tracuser:password@127.0.0.1:13306/tractest

Creating and Initializing Project
Initenv for '/dev/shm/tracenv' failed.
Failed to create environment.
The charset and collation of database are 'utf8mb3' and 'utf8_bin'. The database must be created with one of (('utf8', 'utf8_bin'), ('utf8mb4', 'utf8mb4_bin')).
Traceback (most recent call last):
  File "/home/jun66j5/venv/trac/1.0.20/local/lib/python2.7/site-packages/trac/admin/console.py", line 463, in do_initenv
    options=options)
  File "/home/jun66j5/venv/trac/1.0.20/local/lib/python2.7/site-packages/trac/core.py", line 124, in __call__
    self.__init__(*args, **kwargs)
  File "/home/jun66j5/venv/trac/1.0.20/local/lib/python2.7/site-packages/trac/env.py", line 289, in __init__
    self.create(options)
  File "/home/jun66j5/venv/trac/1.0.20/local/lib/python2.7/site-packages/trac/env.py", line 612, in create
    DatabaseManager(self).init_db()
  File "/home/jun66j5/venv/trac/1.0.20/local/lib/python2.7/site-packages/trac/db/api.py", line 253, in init_db
    connector.init_db(**args)
  File "/home/jun66j5/venv/trac/1.0.20/local/lib/python2.7/site-packages/trac/db/mysql_backend.py", line 124, in init_db
    self._verify_variables(cnx)
  File "/home/jun66j5/venv/trac/1.0.20/local/lib/python2.7/site-packages/trac/db/mysql_backend.py", line 343, in _verify_variables
    supported=repr(self.SUPPORTED_COLLATIONS)))
TracError: The charset and collation of database are 'utf8mb3' and 'utf8_bin'. The database must be created with one of (('utf8', 'utf8_bin'), ('utf8mb4', 'utf8mb4_bin')).

I created a database with character set utf8 and collate utf8_bin and confirmed the variables for the database. character_set_database is utf8mb3, but collation_database is utf8_bin (not utf8mb3_bin).

Your MySQL connection id is 8
Server version: 8.0.28 MySQL Community Server - GPL

Copyright (c) 2000, 2022, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> create database tractest character set utf8 collate utf8_bin;
Query OK, 1 row affected, 2 warnings (0.00 sec)

mysql> show warnings;
+---------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level   | Code | Message                                                                                                                                                                     |
+---------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Warning | 3719 | 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous. |
| Warning | 3778 | 'utf8_bin' is a collation of the deprecated character set UTF8MB3. Please consider using UTF8MB4 with an appropriate collation instead.                                     |
+---------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

mysql> use tractest
Database changed

mysql> show variables like '%char%';
+--------------------------+--------------------------------+
| Variable_name            | Value                          |
+--------------------------+--------------------------------+
| character_set_client     | utf8mb4                        |
| character_set_connection | utf8mb4                        |
| character_set_database   | utf8mb3                        |
| character_set_filesystem | binary                         |
| character_set_results    | utf8mb4                        |
| character_set_server     | utf8mb4                        |
| character_set_system     | utf8mb3                        |
| character_sets_dir       | /usr/share/mysql-8.0/charsets/ |
+--------------------------+--------------------------------+
8 rows in set (0.01 sec)

mysql> show variables like '%coll%';
+-------------------------------+--------------------+
| Variable_name                 | Value              |
+-------------------------------+--------------------+
| collation_connection          | utf8mb4_0900_ai_ci |
| collation_database            | utf8_bin           |
| collation_server              | utf8mb4_0900_ai_ci |
| default_collation_for_utf8mb4 | utf8mb4_0900_ai_ci |
+-------------------------------+--------------------+
4 rows in set (0.00 sec)

[Jun Omae]

I'll fix it on 1.4-stable and trunk, and backport the fix to 1.0-stable and 1.2-stable.

[Jun Omae]

The utf8mb3 character set is introduced in MySQL 8.0.24 (confirmed with mysql:8.0.24 docker image).

​MySQL :: MySQL 8.0 Release Notes :: Changes in MySQL 8.0.24 (2021-04-20, General Availability)

[Jun Omae]

Proposed changes in:

• [c7996d76b/jomae.git] (jomae.git@t13463_trunk)
• [824b525d0/jomae.git] (jomae.git@t13463_1.4)
• [b0ee29457/jomae.git] (jomae.git@t13463_1.2)
• [4a8c86233/jomae.git] (jomae.git@t13463_1.0)

[Ryan J Ollos]

Changes look good to me. Could you push them in the next day or two?

[Jun Omae]

Thanks for the reviewing. I'm going to test again with latest version of PyMySQL and MySQL 8.0.28 and push the changes.

[Jun Omae]

Committed and merged in [17587:17590].