MySQL 8.0.30+: `liquibase.quartz.init.xml` fails if `sql_generate_invisible_primary_key` is enabled

[Thunderforge]

Background

MySQL 8.0.30 and later supports a feature to generate invisible primary keys for any InnoDB table that is created without an explicit primary key. This can be enabled by setting sql_generate_invisible_primary_key like so:

SET sql_generate_invisible_primary_key=ON;

For MySQL Flexible Server databases hosted in Microsoft Azure, this setting is enabled by default and it cannot be disabled via SQL script, only through the Azure Portal.

Problem

When sql_generate_invisible_primary_key is enabled, running Quartz's liquibase.quartz.init.xml (as described in the Quartz wiki's setup guide) results in the following error:

ChangeSet liquibase/liquibase.quartz.init.xml::quartz-init::quartz encountered an exception.
liquibase.exception.DatabaseException: Multiple primary key defined [Failed SQL: (1068) ALTER TABLE fooSchema.QRTZ_LOCKS ADD PRIMARY KEY (SCHED_NAME, LOCK_NAME)]

I think the script should be modified so that it properly executes even if this setting is on, especially given that it's a default configuration for Azure MySQL databases.

Workaround

An end user can temporarily disable sql_generate_invisible_primary_key, run this Liquibase script, then reenable it. As previously stated, Azure does not allow disalbing it via SQL script, only through the Azure Portal.

Related issues

• MySQL 8.0.30 DROP PRIMARY KEY cannot work without ADD PRIMARY KEY in same transaction liquibase/liquibase#5262 (a general Liquibase issue with primary keys and sql_generate_invisible_primary_key)