| Thread Previous • Date Previous • Date Next • Thread Next |
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
| Thread Previous • Date Previous • Date Next • Thread Next |
