shared_zones table is missing mysql_charset and mysql_engine

[Guillaume Boutry]

The table shared_zones is missing the options `mysql_engine` and `mysql_charset`, this leads to an error when the database default's charset defined differs from the charset defined in other tables.

The error:
sqlalchemy.exc.OperationalError: (pymysql.err.OperationalError) (3780, "Referencing column 'zone_id' and referenced column 'id' in foreign key constraint 'shared_zones_ibfk_1' are incompatible.")
[SQL:
CREATE TABLE shared_zones (
 id CHAR(32) NOT NULL,
 created_at DATETIME,
 updated_at DATETIME,
 zone_id CHAR(32) NOT NULL,
 project_id VARCHAR(36) NOT NULL,
 target_project_id VARCHAR(36) NOT NULL,
 PRIMARY KEY (id),
 CONSTRAINT unique_shared_zone UNIQUE (zone_id, project_id, target_project_id),
 FOREIGN KEY(zone_id) REFERENCES zones (id) ON DELETE CASCADE
)

]

[Michael Johnson (johnsom)]

That should be the system wide default[1], but I am guessing you are in some kind of upgrade situation?
[1] https://github.com/openstack/designate/blob/master/designate/storage/sqlalchemy/alembic/versions/15b34ff3ecb8_fix_service_charset.py#L48

Or are you using a non-mysql database?

[Guillaume Boutry (gboutry)]

I'm using a MySQL database.

The issue comes from the database creation step. The database is not being created with encoding set as `UTF8` (alias to `utf8mb3`). Which makes the initial tables created using the database default encoding `utf8mb4` instead of `utf8mb3`.

The migration you pointed then changes the default encoding, and when the table `shared_zones` is created. It tries to use `shared_zones.zone_id` (utf8mb3) to reference `zones.id` (utf8mb4) which is not compatible.

[OpenStack Infra (hudson-openstack)]

Fix proposed to branch: master
Review: https://review.opendev.org/c/openstack/designate/+/910322