Public bug reported: Description =========== Since MySQL 8.0 and MariaDB 10.6, the utf8 charset has been removed and replaced by utf8mb3. A 'utf8' alias is still there to prepare the transition, but next move will be to remove it completely in a future major release.
I have also noticed that in MariaDB 11.5, the default collations for a few character sets have changed, including the one for utf8mb3. You can see the default collation set in 11.5 on this page: https://mariadb.com/kb/en/server-system-variables/#character_set_collations The same for MySQL>5.7, the default collation of utf8mb4 has changed (https://dev.mysql.com/doc/refman/9.1/en/server-system-variables.html#sysvar_default_collation_for_utf8mb4). For now, the migrations scripts to upgrade/create OpenStack tables use the utf8 charset without specifying any collation, so it uses the default collation one: https://github.com/openstack/nova/blob/master/nova/db/main/migrations/versions/8f2f1571d55b_initial_version.py#L274-L275. Problems occur when we try to create new tables that have foreign keys referencing previously existing tables. For example, the nova caracal update fails because there is a table create request (share_mapping) that has a foreign key on the existing `instances` table. Since the collations of the 2 columns linked by the foreign key constraints are different, the request fails. Steps to reproduce ================== We need to have a working OpenStack Nova (2023.2 for example) with a MySQL (<8) or MariaDB (<10.6). Then upgrade your database cluster to a higher version, 8.4 for MySQL or 11.5 for MariaDB for example. Finally try to upgrade Nova from 2023.2 to 2024.1 and run the 'nova-manage db sync' command. Expected result =============== No error with the 'nova-manage db sync' command. Actual result ============= $ nova-manage db sync Error: (pymysql.err.OperationalError) (1005, 'Can\'t create table `nova_cell0`.`share_mapping` (errno: 150 "Foreign key constraint is incorrectly formed")') [SQL: CREATE TABLE share_mapping ( created_at DATETIME, updated_at DATETIME, id BIGINT NOT NULL AUTO_INCREMENT, uuid VARCHAR(36), instance_uuid VARCHAR(36), share_id VARCHAR(36), status VARCHAR(32), tag VARCHAR(48), export_location TEXT, share_proto VARCHAR(32), PRIMARY KEY (id), CONSTRAINT share_mapping_instance_uuid_fkey FOREIGN KEY(instance_uuid) REFERENCES instances (uuid) )CHARSET=utf8 ENGINE=InnoDB We can see in MariaBD logs: LATEST FOREIGN KEY ERROR ------------------------ Error in foreign key constraint of table `nova_cell0`.`share_mapping`: Create table `nova_cell0`.`share_mapping` with foreign key `share_mapping_instance_uuid_fkey` constraint failed. Field type or character set for column 'instance_uuid' does not match referenced column 'uuid'. Environment =========== Debian 12 OpenStack Nova 2023.1 (before upgrade) MariaDB 10.5 (at start, then 11.5) ** Affects: nova Importance: Undecided Status: New -- You received this bug notification because you are a member of Yahoo! Engineering Team, which is subscribed to OpenStack Compute (nova). https://bugs.launchpad.net/bugs/2084562 Title: Default charsets/collates have changed in recent versions of MySQL and MariaDB Status in OpenStack Compute (nova): New Bug description: Description =========== Since MySQL 8.0 and MariaDB 10.6, the utf8 charset has been removed and replaced by utf8mb3. A 'utf8' alias is still there to prepare the transition, but next move will be to remove it completely in a future major release. I have also noticed that in MariaDB 11.5, the default collations for a few character sets have changed, including the one for utf8mb3. You can see the default collation set in 11.5 on this page: https://mariadb.com/kb/en/server-system-variables/#character_set_collations The same for MySQL>5.7, the default collation of utf8mb4 has changed (https://dev.mysql.com/doc/refman/9.1/en/server-system-variables.html#sysvar_default_collation_for_utf8mb4). For now, the migrations scripts to upgrade/create OpenStack tables use the utf8 charset without specifying any collation, so it uses the default collation one: https://github.com/openstack/nova/blob/master/nova/db/main/migrations/versions/8f2f1571d55b_initial_version.py#L274-L275. Problems occur when we try to create new tables that have foreign keys referencing previously existing tables. For example, the nova caracal update fails because there is a table create request (share_mapping) that has a foreign key on the existing `instances` table. Since the collations of the 2 columns linked by the foreign key constraints are different, the request fails. Steps to reproduce ================== We need to have a working OpenStack Nova (2023.2 for example) with a MySQL (<8) or MariaDB (<10.6). Then upgrade your database cluster to a higher version, 8.4 for MySQL or 11.5 for MariaDB for example. Finally try to upgrade Nova from 2023.2 to 2024.1 and run the 'nova-manage db sync' command. Expected result =============== No error with the 'nova-manage db sync' command. Actual result ============= $ nova-manage db sync Error: (pymysql.err.OperationalError) (1005, 'Can\'t create table `nova_cell0`.`share_mapping` (errno: 150 "Foreign key constraint is incorrectly formed")') [SQL: CREATE TABLE share_mapping ( created_at DATETIME, updated_at DATETIME, id BIGINT NOT NULL AUTO_INCREMENT, uuid VARCHAR(36), instance_uuid VARCHAR(36), share_id VARCHAR(36), status VARCHAR(32), tag VARCHAR(48), export_location TEXT, share_proto VARCHAR(32), PRIMARY KEY (id), CONSTRAINT share_mapping_instance_uuid_fkey FOREIGN KEY(instance_uuid) REFERENCES instances (uuid) )CHARSET=utf8 ENGINE=InnoDB We can see in MariaBD logs: LATEST FOREIGN KEY ERROR ------------------------ Error in foreign key constraint of table `nova_cell0`.`share_mapping`: Create table `nova_cell0`.`share_mapping` with foreign key `share_mapping_instance_uuid_fkey` constraint failed. Field type or character set for column 'instance_uuid' does not match referenced column 'uuid'. Environment =========== Debian 12 OpenStack Nova 2023.1 (before upgrade) MariaDB 10.5 (at start, then 11.5) To manage notifications about this bug go to: https://bugs.launchpad.net/nova/+bug/2084562/+subscriptions -- Mailing list: https://launchpad.net/~yahoo-eng-team Post to : [email protected] Unsubscribe : https://launchpad.net/~yahoo-eng-team More help : https://help.launchpad.net/ListHelp
