Indexer - Saving articles fails on a MySQL Galera replication cluster

[lscorcia]

Steps to reproduce the issue

Install MySQL8 as a Galera replication cluster;
Install Joomla v4-5. Make sure to create the tables #_finder_tokens_aggregate and #_finder_tokens as ENGINE=INNODB;
Create a new article and try to save it;

Expected result

Save operation should complete successfully.

Actual result

Joomla returns the following error:

Save failed with the following error: The table does not comply with the requirements by an external plugin.

System information (as much as possible)

MySQL 8 with Galera replication cluster;
Issue present both in Joomla v4 and v5.

Additional comments

The problem has already partially been analyzed in the context of issue #40368 and PR #40373 , but the merged fix is not enough to solve the issue. The ALTER TABLE instruction on Galera clusters does not fail if the table is empty (as it is on a new Joomla installation), it only fails if the table contains at least a record. As the ALTER TABLE succeeds, MySQL returns an error at the first insert into the table, i.e. when a new article is saved. The detection logic should be improved. I will provide a suitable PR.

[alikon]

closing as we have a pr #42414