the actual queries are two in the indexer

$db->setQuery('ALTER TABLE ' . $db->quoteName('#__finder_tokens') . ' ENGINE = MEMORY');

$db->setQuery('ALTER TABLE ' . $db->quoteName('#__finder_tokens_aggregate') . ' ENGINE = MEMORY');

so maybe you need to change both

the actual queries are two in the indexer

The fix wraps both query in to try/catch.
What you see in first comment here, is a simple way to test the changes without messing around with DB setup.

@claudiosoprano If you could create a copy of your site, using the same kind of database cluster but a separate database so you don't mess up your real site, you can test this PR here by checking if it fixes your issue on that copy site.

You can find here links to a custom update URL and an update package with which you can update the copy site to 4.3-dev including the changes of this PR: https://ci.joomla.org/artifacts/joomla/joomla-cms/4.3-dev/40373/downloads/64607/

When you have tested, go to the issue tracker here https://issues.joomla.org/tracker/joomla-cms/40373 and use the blue "Test this" button at the top corner, then select the right test result and finally submit.

Thanks in advance.

I will test it as soon as i can (but not until tomorrow, i'm out of my office)

I have tested this item ✅ successfully on 98b29d1

Ok now it works with Percona Xtradb Cluster that use only InnoDB tables

I connect from home, applied the dev update, tested and it work, i can create/modify/delete articles without problems

Well the thing is that it is not even possible to disable the MEMORY engine on a MySQL or MariaDB because it is also used internally by the database server. The limitation comes from the replication of the Galera Cluster. See the first limitation listed on this page https://mariadb.com/kb/en/mariadb-galera-cluster-known-limitations/

Currently replication works only with the InnoDB storage engine.

So if you don't have such a cluster, you can only test it like described in the testing instructions, changing the SQL statement so it uses an invalid storage engine name and so causes the exception.

@HLeithner As far as I remember from a past issue, you have some experience with Galera Clusters. Do you know if there is a way to circumvent this by configuration of the replication? Would it be possible to exclude the 2 tables #__finder_tokens and #__finder_tokens_aggregate from replication? And @Hackwar would that work because the tables are only needed during indexing, or do these tables need to be replicated in a cluster because they are also used for searching?

In a Master-Master replication is not reccomended to replicate only some tables (specially if those tables are needed to make the DB work in the right way), but in a Master-Slave setup it can be set to have replicated only some tables

https://dba.stackexchange.com/questions/122189/replicate-only-some-tables-of-mariadb-database

there you will find command to do that and will find your link to limitation of Galera Cluster

I don't use the Galera Cluster, I only use Master-Slave replication

I have tested this item ✅ successfully on 98b29d1

I've successfully tested this PR with the method described in the instructions, i.e. with modifying the storage name to an invalid one to cause the exception.

RTC

@Fedik I've successfully tested this PR, and as it has 2 tests I've set it RTC.

However, there could be made a few small optimizations to the code. I have made a PR in your fork, see Fedik#10 . If you agree with my suggestions and merge my PR, I will test again this PR here, and I'm sure @claudiosoprano would also do that.

Back to pending due to code changes.

@claudiosoprano Could you test again? The pull request has received some code changes. You can download the changed file "Indexer.php" here and save it at the right place in folder "administrator/components/com_finder/src/Indexer": https://raw.githubusercontent.com/joomla/joomla-cms/d92d5ff43823285802daf9bf78d313f0c52aac9d/administrator/components/com_finder/src/Indexer/Indexer.php . I will also test again in a bit. Thanks in advance.

@claudiosoprano Update: Please wait a bit with testing again. It needs another change.

@claudiosoprano Now it's ready. Could you test again? You can download the changed file "Indexer.php" here and save it at the right place in folder "administrator/components/com_finder/src/Indexer": https://raw.githubusercontent.com/joomla/joomla-cms/d92d5ff43823285802daf9bf78d313f0c52aac9d/administrator/components/com_finder/src/Indexer/Indexer.php .

I have tested this item ✅ successfully on d92d5ff

I've successfully tested this PR with the method described in the instructions, i.e. with modifying the storage name to an invalid one to cause the exception.

I have tested this item ✅ successfully on d92d5ff

with test instruction only
no replication in place

RTC

I will test it as soon as possible

I will test it as soon as possible

All good @claudiosoprano ?

Ok it works, i can't find i tested this item

Ok it works, i can't find i tested this item

@claudiosoprano You can find it here in the issue tracker: https://issues.joomla.org/tracker/joomla-cms/40373 . Just use the blue "Test this" button at the top left corner. If you can't see that button you might have to login using your github account.

Thank you Fedir @Fedik for the PR and Richard for your involvement.

I have tested this item ✅ successfully on 70621b1

Ok works

@claudiosoprano Thanks for your test, but this pull request has been already merged and will be included in the upcoming 4.3.2 release, so it doesn’t need anymore to be tested.

The proposed fix is not enough.

The ALTER TABLE instruction on Galera clusters does not fail if the table is empty (as it is on a new installation), it only fails if the table contains at least a record. If the ALTER TABLE succeeds, MySQL returns an error at the first insert into the table. The detection logic should be improved.