Bug 2263624

Description of problem:

During the upgrade from 16.2.6 to 17.1.2, the customer ran into the following error on their Controller nodes:

~~~
2024-02-09 20:32:16.345146 | 566f56ac-006f-7cda-9332-00000000b7ab |      FATAL | Create containers managed by Podman for /var/lib/tripleo-config/container-startup-config/step_3 | flpp-ospcontrol50 | error={"changed": false, "msg": "Failed containers: cinder_api_db_sync"}
~~~

When we look at the `/var/log/containers/stdouts/cinder_api_db_sync.log.1` file on their affected Controller node, the below logs show the reason the migration failed:
~~~
2024-02-09T20:32:03.307217551-03:00 stderr F + exec /usr/bin/bootstrap_host_exec cinder_api su cinder -s /bin/bash -c ''\''cinder-manage' db sync '--bump-versions'\'''
2024-02-09T20:32:04.624961278-03:00 stdout F Error during database migration: (pymysql.err.OperationalError) (1709, 'Index column size too large. The maximum column size is 767 bytes')
2024-02-09T20:32:04.624961278-03:00 stdout F [SQL: ALTER TABLE quota_usages ADD CONSTRAINT quota_usages_project_id_key UNIQUE (project_id, resource, race_preventer)]
2024-02-09T20:32:04.624961278-03:00 stdout F (Background on this error at: http://sqlalche.me/e/13/e3q8)
~~~

Specifically, the query that looks sto be failing when we receive the sqlalchemy error is `ALTER TABLE quota_usages ADD CONSTRAINT quota_usages_project_id_key UNIQUE (project_id, resource, race_preventer)`.

I got a copy of the customers Cinder database and loaded it into a 10.5.22-MariaDB container, and ran the command successfully without error.

~~~
MariaDB [(none)]> use cinder;

MariaDB [cinder]> ALTER TABLE quota_usages ADD CONSTRAINT quota_usages_project_id_key UNIQUE (project_id, resource, race_preventer);
Query OK, 0 rows affected (0.024 sec)
Records: 0  Duplicates: 0  Warnings: 0
~~~

Although the customer has `innodb_default_row_format=dynamic` specified in their `/var/lib/config-data/puppet-generated/my.cnf.d/galera.cnf` file, it appears they have gone from OSP 10 -> 13 -> 16.1 -> 16.2 -> 17.1 throughout the years.

As such, any database tables that previously existed prior to MariaDB 10.2.11 [1] appear to be using the `ROW_FORMAT=Compact`, which we validated with the customer to be the case. Some tables are indeed Dynamic, but they are very few amongst the masses.

[1] https://mariadb.com/kb/en/innodb-row-formats-overview/#compact-row-format

If I redo my previous experiment with the `ALTER TABLE` statement, but I force the table to be `ROW_FORMAT=COMPACT`, the execution also fails. That's because my default installation uses `innodb_default_row_format=dynamic`, and when it is compact and innodb_page_size is 4KB, index prefix key lengths are limited to 768 bytes (or less) [2]. The customer may also be using `utf-mb4` encoding, still waiting to check on that. If so, every character is taking up four bytes of the index key prefix length.

[2] https://dev.mysql.com/doc/refman/8.0/en/innodb-limits.html

From my research so far, it appears that this error is only hit when the table is a legacy table, and it appears there's no task to migrate customer DB tables to the modern equivalent.

Version-Release number of selected component (if applicable):
Red Hat OpenStack Platform 17.1 (RHOSP 17.1)
galera-26.4.14-1.el9_2.x86_64
mariadb-connector-c-config-3.2.6-1.el9_0.noarch
mariadb-common-10.5.22-1.el9_2.x86_64
mariadb-connector-c-3.2.6-1.el9_0.x86_64
mariadb-errmsg-10.5.22-1.el9_2.x86_64
mariadb-10.5.22-1.el9_2.x86_64
mariadb-backup-10.5.22-1.el9_2.x86_64
mariadb-server-10.5.22-1.el9_2.x86_64
mariadb-server-utils-10.5.22-1.el9_2.x86_64
mariadb-server-galera-10.5.22-1.el9_2.x86_64

How reproducible:
Always when the `ROW_FORMAT=Compact`, rather than Dynamic and adding indexes or constraints that exceed the byte size limit.

In environments that have come from OSP10/13, where MariaDB <10.2.1 was in-use, tables that were created then will still be using those formats, as it appears there is no migration task to update tables to non-legacy formats.

Steps to Reproduce:
1. Create the container to test on, and add the two specified lines into your db dump, (required with this version of MariaDB in order to successfully import). If you use vim, execute `:e ++ff=dos %` in order to set dos-style encoding.

~~~
mkdir -p ~/dev/test/mariadb{,/data}
cp ~/Downloads/mysqldump.sql ~/dev/test/mariadb && cd ~/dev/test/mariadb
# Testing with the below container for convenience, to avoid needing to setup kolla_config if we use `registry.redhat.io/rhosp-rhel9/openstack-mariadb:17.1.2-5` instead.
podman run --rm -dit --name  -p 3306:3306 -v ./data:/var/lib/mysql:z -e MYSQL_ROOT_PASSWORD=test docker.io/mariadb:10.5.2

# vim ~/dev/test/mariadb/mysqldump.sql
# :e ++ff=dos %
# Add the following lines towards the top:
DROP TABLE IF EXISTS `mysql`.`global_priv`;
DROP VIEW IF EXISTS `mysql`.`user`;
~~~

2. Starting with any OSP 16.2 database backup, import it into a MariaDB container.
~~~
mysql -uroot -pttest -P 3306 < mysqldump.sql
~~~

3. Drop existing indexes because they will prevent changing the InnoDB engine/row_format type. We don't care about them for reproduction/testing purposes. Copy/paste the output of the 3rd command to drop the indexes.
~~~
SET FOREIGN_KEY_CHECKS=0;
use cinder;
SELECT CONCAT('DROP INDEX `', index_name, '` ON `', table_name,'`;') AS _drop FROM mysql.innodb_index_stats WHERE index_name != 'PRIMARY' AND database_name = 'cinder';
~~~

4. Set the table `ROW_FORMAT` to `COMPACT`.
~~~
ALTER TABLE quota_usages ENGINE=InnoDB, ROW_FORMAT=Compact;
~~~

5. Run the command `ALTER TABLE quota_usages ADD CONSTRAINT quota_usages_project_id_key UNIQUE (project_id, resource, race_preventer);`
6. You will hit an error, and this stops the deployment/upgrade from progressing.
~~~
MariaDB [cinder]> ALTER TABLE quota_usages ADD CONSTRAINT quota_usages_project_id_key UNIQUE (project_id, resource, race_preventer);
ERROR 1709 (HY000): Index column size too large. The maximum column size is 767 bytes
~~~
7. Update again the `ROW_FORMAT=Dynamic`, and add the CONSTRAINT again, it will succeed.
~~~
MariaDB [cinder]> ALTER TABLE cinder.quota_usages ENGINE=InnoDB, ROW_FORMAT=Dynamic;
Query OK, 0 rows affected (0.023 sec)
Records: 0  Duplicates: 0  Warnings: 0
MariaDB [cinder]> ALTER TABLE quota_usages ADD CONSTRAINT quota_usages_project_id_key UNIQUE (project_id, resource, race_preventer);
Query OK, 0 rows affected (0.018 sec)
Records: 0  Duplicates: 0  Warnings: 0
~~~

Actual results:
- The upgrade is blocked and fails on any table that tries to add an index or constraint that exceeds the 767 byte-size limit.

Expected results:
- Any indexes or constraints added to any table should successfully be able to be added, without a limitation imposed by legacy table row formats.

Additional info:
- Please see next private comment.