Edgewall Software
Modify

Opened 2 years ago

Closed 2 years ago

#13463 closed defect (fixed)

character_set_database errors with mysql8

Reported by: mrbart Owned by: Jun Omae
Priority: normal Milestone: 1.4.4
Component: database backend Version: 1.5.3
Severity: normal Keywords: mysql
Cc: Branch:
Release Notes:

Make compatible with utf8mb3 charset since MySQL 8.

API Changes:
Internal Changes:

Description

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

Attachments (1)

mysql_backend.py (20.8 KB ) - added by mrbart 2 years ago.

Download all attachments as: .zip

Change History (10)

by mrbart, 2 years ago

Attachment: mysql_backend.py added

comment:1 by Jun Omae, 2 years ago

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

comment:2 by Jun Omae, 2 years ago

Keywords: pymysql mysql8 utf8mb3 character_set_database removed

In addition, please share the error which you got.

comment:3 by Jun Omae, 2 years ago

Milestone: 1.4.4

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)

comment:4 by Jun Omae, 2 years ago

Owner: set to Jun Omae
Status: newassigned

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

comment:5 by Jun Omae, 2 years ago

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)

comment:7 by Ryan J Ollos, 2 years ago

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

comment:8 by Jun Omae, 2 years ago

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

comment:9 by Jun Omae, 2 years ago

Resolution: fixed
Status: assignedclosed

Committed and merged in [17587:17590].

Modify Ticket

Change Properties
Set your email in Preferences
Action
as closed The owner will remain Jun Omae.
The resolution will be deleted. Next status will be 'reopened'.
to The owner will be changed from Jun Omae to the specified user.

Add Comment


E-mail address and name can be saved in the Preferences .
 
Note: See TracTickets for help on using tickets.