I'm a Kolla contributor and was trying to bump MariaDB from 10.11 to 11.8 for Kolla and Kolla-Ansible projects.

During this, I found problems that can happen across the whole OpenStack services with MariaDB's default charset and collation set changes.

Here's the note I made for each problem I found.

Problems with default collation set change

• MariaDB changed default collation set of charset utf8mb3 from utf8mb3_general_ci to utf8mb3_uca1400_ai_ci from 11.5 https://jira.mariadb.org/browse/MDEV-25829
• K-A currently sets database level charset to utf8 (Which is aliased to utf8mb3) and collation set to utf8_general_ci (aliased to utf8mb3_general_ci)
• Problem detected when deploying fresh magnum on MariaDB 11.8: https://bugs.launchpad.net/magnum/+bug/2121797
• This is because how MariaDB works. If charset is not specified when creating a table, MariaDB uses database level charset and collation set (if set) or server level charset and collation set
• If only charset is specified when creating a table, MariaDB uses MariaDB default (defined as a list of mapping in variable ``collation_connection``)
• Magnum specified charset when creating *some* of tables not all -> Collation set inconsistency in magnum database.
• Server-wide collation set inconsistency (when DB and services are freshly deployed)
• Some services such as Nova, Keystone, Cinder, designate provides charset when creating tables (Also alters database level charset to utf8 in first alembic op) Let's call them group A
• Some services such as Glance, Neutron, Octavia, Placement don't. Let's call them group B
• Then we have Magnum which mixed up. Let's call it... Magnum
• These differences cause database level collation set inconsistency between group A and group B services because of MariaDB's behaviour on creating a table and database
• Potential problem for MariaDB 10.11 to 11.8 upgrade
• Existing databases in MariaDB server have collation set of utf8mb3_general_ci
• Because of MariaDB's new default collation set for utf8mb3 and how the table creation works, if any services decide to create a new table, they can be created with utf8mb3_uca1400_ai_ci and causes collation set inconsistency
• This issue can be invisible at first but if any project decides to create new tables then do string comparison between the old tables and new tables, it will be visible
• Potential solution A: Override MariaDB default for utf8mb3 to utf8mb3_general_ci for each service clients https://mariadb.com/docs/server/reference/data-types/string-data-types/character-sets/setting-character-sets-and-collations#changing-default-collation
• Pros: Easy
• Cons:
• In fact, it doesn't work. Because there are slight differences on how each projects create tables, only some of them (Keystone, designate) are affected
• Even if it worked, It's just delaying the problem. Looks like community pushed forward in the past: https://review.opendev.org/c/openstack/kolla-ansible/+/455154
• Potential solution B: Change K-A's server level collation set config to utf8mb3_uca1400_ai_ci then migrate (ALTER TABLE) all tables to use new collation on upgrade
• Pros: We'll probably don't need to worry about this matter for a long time
• Cons: 
• Service disruption, inter-table relationships such as foreign keys make altering table hard.
• Percona-toolkit claims it can be done with near-zero downtime with FK handling strategies. Can be useful. https://docs.percona.com/percona-toolkit/pt-online-schema-change.html (GPLv2 license)
• Potential solution C: Use MariaDB 11.4 instead
• Pros: 11.4 is also an LTS but without this drama
• Cons: Not the latest LTS

Problems with default character set change

• MariaDB's new default charset utf8mb4 https://jira.mariadb.org/browse/MDEV-19123
• This is not urgent but MariaDB is planning to remove alias between utf8 and utf8mb3 https://jira.mariadb.org/browse/MDEV-30041
• Currently most of openstack projects use 'utf8' when specifying a character set not 'utf8mb3'. This will all need to be updated to 'utf8mb3'.
• Or we can try moving on to utf8mb4 but ironic need to use utf8mb3 due to its internal design https://docs.openstack.org/ironic/latest/install/install.html#set-up-the-database-for-bare-metal
• Transition to utf8mb4 would also need a migration plan
• Using MariaDB 11.4 also helps here because this change is from 11.6

I suggest to raise this topic on next OpenStack TC meeting. I think it's worth having a inter-project conversation as these will affect any projects that uses DB.

What do people think about this? Any inputs are appreciated.

Kind regards,

Seunghun Lee

Cloud Engineer, StackHPC