From 1435a15ce3013da0a3138e1c5ab6ac523c382bb8 Mon Sep 17 00:00:00 2001 From: Julia Kreger Date: Mon, 3 Oct 2022 16:06:37 -0700 Subject: Fix allocations default table type In trying to figure out why I was unable to run all of the test_migrations tests, I realized we need to fix and clean up our unicode declarations. Specifically, the way I found this was my local mysql install was defaulted to using 4 Byte Unicode characters, however some of our fields are 255 characters, which do not fit inside of InnoDB tables. They do, however fit with the "utf8" storage alias, which is presently short for UTF8MB3, as opposed to UTF8MB4 which is what my local database server was configured for. Because this was in opportunistic tests, I wasn't able to really sort out what was going on and thought we needed to shorten the fields. In reality, it turns out we never defined the allocations table to use UTF8 and Innodb for storage. Storage engine wise, this is not a big deal, but may mean a DBA will one day need to dump and reload the allocation table of a deployment. Character set wise... It is not great, but there is not a good way for us to do this programatically. In my opinion, the chance of an issue being encountered by an operator is unlikely, which out weighs the risk and impact of dumping the entire table, deleting the table, recreating the table with the updated schema and then repopulating the entries. Of course, if operators are not using allocations, then it really doesn't matter for them. Along the way, I discovered we had used the "UTF8" type alias, which may change one day, which would break Ironic. As such, I've also updated the definitions used to create databases and updated our documentation. Recommended reading: https://docs.sqlalchemy.org/en/14/dialects/mysql.html#unicode https://dev.mysql.com/doc/refman/8.0/en/charset-unicode-utf8mb4.html Story: 2010348 Task: 46492 Change-Id: I4103152489bf61e2d614eaa297da858f7b2112a3 --- doc/source/contributor/dev-quickstart.rst | 7 +++++++ .../install/include/common-prerequisites.inc | 10 +++++++++- .../versions/2581ebaf0cb2_initial_migration.py | 16 +++++++-------- .../versions/2aac7e0872f6_add_deploy_templates.py | 8 ++++---- .../alembic/versions/48d6c242bb9b_add_node_tags.py | 4 ++-- ...10e_added_port_group_table_and_altered_ports.py | 4 ++-- .../versions/82c315d60161_add_bios_settings.py | 4 ++-- .../9ef41f07cb58_add_node_history_table.py | 4 ++-- .../b4130a7fc904_create_nodetraits_table.py | 4 ++-- .../versions/dd67b91a1981_add_allocations_table.py | 5 ++++- .../allocations-charset-5384d1ea00964bdd.yaml | 23 ++++++++++++++++++++++ 11 files changed, 65 insertions(+), 24 deletions(-) create mode 100644 releasenotes/notes/allocations-charset-5384d1ea00964bdd.yaml diff --git a/doc/source/contributor/dev-quickstart.rst b/doc/source/contributor/dev-quickstart.rst index 3fe03f02b..6f63104f9 100644 --- a/doc/source/contributor/dev-quickstart.rst +++ b/doc/source/contributor/dev-quickstart.rst @@ -131,6 +131,13 @@ The unit tests need a local database setup, you can use ``tools/test-setup.sh`` to set up the database the same way as setup in the OpenStack test systems. +.. note:: + If you encounter issues executing unit tests, specifically where errors + may indicate that a field is too long, check your database's default + character encoding. Debian specifically sets MariaDB to ``utf8mb4`` + which utilizes 4 byte encoded unicode characters by default, which is + incompatible by default. + Additional Tox Targets ---------------------- diff --git a/doc/source/install/include/common-prerequisites.inc b/doc/source/install/include/common-prerequisites.inc index edaca46d0..718e80c9d 100644 --- a/doc/source/install/include/common-prerequisites.inc +++ b/doc/source/install/include/common-prerequisites.inc @@ -22,8 +22,16 @@ MySQL database that is used by other OpenStack services. .. code-block:: console # mysql -u root -p - mysql> CREATE DATABASE ironic CHARACTER SET utf8; + mysql> CREATE DATABASE ironic CHARACTER SET utf8mb3; mysql> GRANT ALL PRIVILEGES ON ironic.* TO 'ironic'@'localhost' \ IDENTIFIED BY 'IRONIC_DBPASSWORD'; mysql> GRANT ALL PRIVILEGES ON ironic.* TO 'ironic'@'%' \ IDENTIFIED BY 'IRONIC_DBPASSWORD'; + +.. note:: + When creating the database to house Ironic, specifically on MySQL/MariaDB, + the character set *cannot* be 4 byte Unicode characters. This is due to + an internal structural constraint. UTF8, in these database platforms, + has traditionally meant ``utf8mb3``, short for "UTF-8, 3 byte encoding", + however the platforms are expected to move to ``utf8mb4`` which is + incompatible with Ironic. diff --git a/ironic/db/sqlalchemy/alembic/versions/2581ebaf0cb2_initial_migration.py b/ironic/db/sqlalchemy/alembic/versions/2581ebaf0cb2_initial_migration.py index d47a3d131..1587dc94a 100644 --- a/ironic/db/sqlalchemy/alembic/versions/2581ebaf0cb2_initial_migration.py +++ b/ironic/db/sqlalchemy/alembic/versions/2581ebaf0cb2_initial_migration.py @@ -38,8 +38,8 @@ def upgrade(): sa.Column('drivers', sa.Text(), nullable=True), sa.PrimaryKeyConstraint('id'), sa.UniqueConstraint('hostname', name='uniq_conductors0hostname'), - mysql_ENGINE='InnoDB', - mysql_DEFAULT_CHARSET='UTF8' + mysql_charset='UTF8MB3', + mysql_engine='InnoDB', ) op.create_table( 'chassis', @@ -51,8 +51,8 @@ def upgrade(): sa.Column('description', sa.String(length=255), nullable=True), sa.PrimaryKeyConstraint('id'), sa.UniqueConstraint('uuid', name='uniq_chassis0uuid'), - mysql_ENGINE='InnoDB', - mysql_DEFAULT_CHARSET='UTF8' + mysql_engine='InnoDB', + mysql_charset='UTF8MB3' ) op.create_table( 'nodes', @@ -77,8 +77,8 @@ def upgrade(): sa.ForeignKeyConstraint(['chassis_id'], ['chassis.id'], ), sa.PrimaryKeyConstraint('id'), sa.UniqueConstraint('uuid', name='uniq_nodes0uuid'), - mysql_ENGINE='InnoDB', - mysql_DEFAULT_CHARSET='UTF8' + mysql_engine='InnoDB', + mysql_charset='UTF8MB3' ) op.create_index('node_instance_uuid', 'nodes', ['instance_uuid'], unique=False) @@ -95,7 +95,7 @@ def upgrade(): sa.PrimaryKeyConstraint('id'), sa.UniqueConstraint('address', name='uniq_ports0address'), sa.UniqueConstraint('uuid', name='uniq_ports0uuid'), - mysql_ENGINE='InnoDB', - mysql_DEFAULT_CHARSET='UTF8' + mysql_engine='InnoDB', + mysql_charset='UTF8MB3' ) # end Alembic commands diff --git a/ironic/db/sqlalchemy/alembic/versions/2aac7e0872f6_add_deploy_templates.py b/ironic/db/sqlalchemy/alembic/versions/2aac7e0872f6_add_deploy_templates.py index 0b5e8ff10..0cdc38fb2 100644 --- a/ironic/db/sqlalchemy/alembic/versions/2aac7e0872f6_add_deploy_templates.py +++ b/ironic/db/sqlalchemy/alembic/versions/2aac7e0872f6_add_deploy_templates.py @@ -39,8 +39,8 @@ def upgrade(): sa.PrimaryKeyConstraint('id'), sa.UniqueConstraint('uuid', name='uniq_deploytemplates0uuid'), sa.UniqueConstraint('name', name='uniq_deploytemplates0name'), - mysql_ENGINE='InnoDB', - mysql_DEFAULT_CHARSET='UTF8' + mysql_engine='InnoDB', + mysql_charset='UTF8MB3' ) op.create_table( @@ -62,6 +62,6 @@ def upgrade(): sa.Index('deploy_template_id', 'deploy_template_id'), sa.Index('deploy_template_steps_interface_idx', 'interface'), sa.Index('deploy_template_steps_step_idx', 'step'), - mysql_ENGINE='InnoDB', - mysql_DEFAULT_CHARSET='UTF8' + mysql_engine='InnoDB', + mysql_charset='UTF8MB3' ) diff --git a/ironic/db/sqlalchemy/alembic/versions/48d6c242bb9b_add_node_tags.py b/ironic/db/sqlalchemy/alembic/versions/48d6c242bb9b_add_node_tags.py index 641419f09..b0e12e56b 100644 --- a/ironic/db/sqlalchemy/alembic/versions/48d6c242bb9b_add_node_tags.py +++ b/ironic/db/sqlalchemy/alembic/versions/48d6c242bb9b_add_node_tags.py @@ -36,7 +36,7 @@ def upgrade(): sa.Column('tag', sa.String(length=255), nullable=False), sa.ForeignKeyConstraint(['node_id'], ['nodes.id'], ), sa.PrimaryKeyConstraint('node_id', 'tag'), - mysql_ENGINE='InnoDB', - mysql_DEFAULT_CHARSET='UTF8' + mysql_engine='InnoDB', + mysql_charset='UTF8MB3' ) op.create_index('node_tags_idx', 'node_tags', ['tag'], unique=False) diff --git a/ironic/db/sqlalchemy/alembic/versions/5ea1b0d310e_added_port_group_table_and_altered_ports.py b/ironic/db/sqlalchemy/alembic/versions/5ea1b0d310e_added_port_group_table_and_altered_ports.py index a799c1b1d..7b1eacbe0 100644 --- a/ironic/db/sqlalchemy/alembic/versions/5ea1b0d310e_added_port_group_table_and_altered_ports.py +++ b/ironic/db/sqlalchemy/alembic/versions/5ea1b0d310e_added_port_group_table_and_altered_ports.py @@ -42,8 +42,8 @@ def upgrade(): sa.UniqueConstraint('address', name='uniq_portgroups0address'), sa.UniqueConstraint('name', name='uniq_portgroups0name'), - mysql_ENGINE='InnoDB', - mysql_DEFAULT_CHARSET='UTF8') + mysql_engine='InnoDB', + mysql_charset='UTF8MB3') op.add_column(u'ports', sa.Column('local_link_connection', sa.Text(), nullable=True)) op.add_column(u'ports', sa.Column('portgroup_id', sa.Integer(), diff --git a/ironic/db/sqlalchemy/alembic/versions/82c315d60161_add_bios_settings.py b/ironic/db/sqlalchemy/alembic/versions/82c315d60161_add_bios_settings.py index 0d93bed30..33c141caa 100644 --- a/ironic/db/sqlalchemy/alembic/versions/82c315d60161_add_bios_settings.py +++ b/ironic/db/sqlalchemy/alembic/versions/82c315d60161_add_bios_settings.py @@ -37,6 +37,6 @@ def upgrade(): sa.Column('version', sa.String(length=15), nullable=True), sa.ForeignKeyConstraint(['node_id'], ['nodes.id'], ), sa.PrimaryKeyConstraint('node_id', 'name'), - mysql_ENGINE='InnoDB', - mysql_DEFAULT_CHARSET='UTF8' + mysql_engine='InnoDB', + mysql_charset='UTF8MB3' ) diff --git a/ironic/db/sqlalchemy/alembic/versions/9ef41f07cb58_add_node_history_table.py b/ironic/db/sqlalchemy/alembic/versions/9ef41f07cb58_add_node_history_table.py index 9f5b855ed..748d281e2 100644 --- a/ironic/db/sqlalchemy/alembic/versions/9ef41f07cb58_add_node_history_table.py +++ b/ironic/db/sqlalchemy/alembic/versions/9ef41f07cb58_add_node_history_table.py @@ -48,5 +48,5 @@ def upgrade(): sa.Index('history_node_id_idx', 'node_id'), sa.Index('history_uuid_idx', 'uuid'), sa.Index('history_conductor_idx', 'conductor'), - mysql_ENGINE='InnoDB', - mysql_DEFAULT_CHARSET='UTF8') + mysql_engine='InnoDB', + mysql_charset='UTF8MB3') diff --git a/ironic/db/sqlalchemy/alembic/versions/b4130a7fc904_create_nodetraits_table.py b/ironic/db/sqlalchemy/alembic/versions/b4130a7fc904_create_nodetraits_table.py index 8cf30a2d9..66216b722 100644 --- a/ironic/db/sqlalchemy/alembic/versions/b4130a7fc904_create_nodetraits_table.py +++ b/ironic/db/sqlalchemy/alembic/versions/b4130a7fc904_create_nodetraits_table.py @@ -37,7 +37,7 @@ def upgrade(): sa.Column('trait', sa.String(length=255), nullable=False), sa.ForeignKeyConstraint(['node_id'], ['nodes.id'], ), sa.PrimaryKeyConstraint('node_id', 'trait'), - mysql_ENGINE='InnoDB', - mysql_DEFAULT_CHARSET='UTF8' + mysql_engine='InnoDB', + mysql_charset='UTF8MB3' ) op.create_index('node_traits_idx', 'node_traits', ['trait'], unique=False) diff --git a/ironic/db/sqlalchemy/alembic/versions/dd67b91a1981_add_allocations_table.py b/ironic/db/sqlalchemy/alembic/versions/dd67b91a1981_add_allocations_table.py index 55560dc68..74ab297a5 100644 --- a/ironic/db/sqlalchemy/alembic/versions/dd67b91a1981_add_allocations_table.py +++ b/ironic/db/sqlalchemy/alembic/versions/dd67b91a1981_add_allocations_table.py @@ -48,7 +48,10 @@ def upgrade(): sa.ForeignKeyConstraint(['node_id'], ['nodes.id'], ), sa.PrimaryKeyConstraint('id'), sa.UniqueConstraint('name', name='uniq_allocations0name'), - sa.UniqueConstraint('uuid', name='uniq_allocations0uuid') + sa.UniqueConstraint('uuid', name='uniq_allocations0uuid'), + mysql_engine='InnoDB', + mysql_charset='UTF8MB3' + ) op.add_column('nodes', sa.Column('allocation_id', sa.Integer(), nullable=True)) diff --git a/releasenotes/notes/allocations-charset-5384d1ea00964bdd.yaml b/releasenotes/notes/allocations-charset-5384d1ea00964bdd.yaml new file mode 100644 index 000000000..3db4da086 --- /dev/null +++ b/releasenotes/notes/allocations-charset-5384d1ea00964bdd.yaml @@ -0,0 +1,23 @@ +--- +fixes: + - | + Fixes an missing MySQL/MariaDB character set configuration and default + table type encoding for the ``allocations`` database table. Previously, + If Ironic's database was attempted to be populated on a machine which + was using 4 byte character encoding, such as MySQL/MariaDB on Debian + based systems, then the database schema creation would fail. +upgrade: + - This upgrade updates the default character set to utilized in the + database tables when using MySQL/MariaDB. Previously, the default + for Ironic was ``UTF8``, however we now explicitly set ``UTF8MB3`` + which is short for "3 byte UTF8" encoding. The exception to this + is the ``allocations`` table, which would just rely upon the database + default. This was done as Ironic's database schema is incompatible + with MySQL/MariaDB's ``UTF8MB4``, or "4 byte UTF8" character encoding + and storage constraints. + - Upgrading will change the default chracter encoding of all tables. + For most tables, this should be an effective noop, but may result in + transitory table locks. For the ``allocations`` table, it will need to + be re-written, during which the database engine will have locked the + table from being used. Operators are advised to perform test upgrades + and set expectation and upgrade plans accordingly. -- cgit v1.2.1