joomla 4.2.9 com_finder Storage Engine 'memory' unknown on modifying articles on innodb tables #40368

claudiosoprano · 2023-04-11T13:31:47Z

Steps to reproduce the issue

Modify an article

Expected result

no errors if it successed

Actual result

modifies are recorded but an error is throw

System information (as much as possible)

Percona mysql xtradb cluster with galera

php 8.1

Additional comments

When i try to modify an article, it get modified but an error is displayed on the screen with the message:

Save failed with the following error: Unknown storage engine 'MEMORY'

I checked all the joomla DB tables and they are all InnoDB

Fedik · 2023-04-11T14:37:50Z

Hello,
It seems that your server does not support MEMORY engine, which is neded for Finder (Smart search component) to work.

If you do not use search on your site you can disable Content => Finder plugin.

claudiosoprano · 2023-04-11T15:53:11Z

Yes my server doesn't support MEMORY engine, but i read on the joomla forum that innodb table would work, slower but works.

Is there a way to choose the type of the engine for the search function ?

Claudio

_{This comment was created with the J!Tracker Application at issues.joomla.org/tracker/joomla-cms/40368.}

Fedik · 2023-04-11T17:22:44Z

TBH I do not know about such posibility

alikon · 2023-04-12T06:15:08Z

ALTER TABLE my_table ENGINE = InnoDB;

should work

claudiosoprano · 2023-04-12T07:41:30Z

The problem is all the tables show ENGINE = Innodb, bone is 'Memory' and all the search features works with no error, the erro appears only when an article is modified, but repeat the modifies will be saved but this error appears

_{This comment was created with the J!Tracker Application at issues.joomla.org/tracker/joomla-cms/40368.}

claudiosoprano · 2023-04-12T07:45:05Z

This is the DB tables and the engine

None is 'Memory' all are InnoDB

Thanks

_{This comment was created with the J!Tracker Application at issues.joomla.org/tracker/joomla-cms/40368.}

claudiosoprano · 2023-04-12T07:50:14Z

I tried also to create a new article and it is not saved same error displayed

_{This comment was created with the J!Tracker Application at issues.joomla.org/tracker/joomla-cms/40368.}

Fedik · 2023-04-12T08:01:43Z

hmhm, that probably due to this code

joomla-cms/administrator/components/com_finder/src/Indexer/Indexer.php

Lines 624 to 625 in 7a8b628

    
           // Toggle the token tables back to memory tables. 
        
           $this->toggleTables(true);

joomla-cms/administrator/components/com_finder/src/Indexer/Indexer.php

Lines 967 to 1005 in 7a8b628

    
           protected function toggleTables($memory) 
        
           { 
        
               if (strtolower($this->db->getServerType()) != 'mysql') { 
        
                   return true; 
        
               } 
        
               static $state; 
        
               // Get the database adapter. 
        
               $db = $this->db; 
        
               // Check if we are setting the tables to the Memory engine. 
        
               if ($memory === true && $state !== true) { 
        
                   // Set the tokens table to Memory. 
        
                   $db->setQuery('ALTER TABLE ' . $db->quoteName('#__finder_tokens') . ' ENGINE = MEMORY'); 
        
                   $db->execute(); 
        
                   // Set the tokens aggregate table to Memory. 
        
                   $db->setQuery('ALTER TABLE ' . $db->quoteName('#__finder_tokens_aggregate') . ' ENGINE = MEMORY'); 
        
                   $db->execute(); 
        
                   // Set the internal state. 
        
                   $state = $memory; 
        
               } elseif ($memory === false && $state !== false) { 
        
                   // We must be setting the tables to the InnoDB engine. 
        
                   // Set the tokens table to InnoDB. 
        
                   $db->setQuery('ALTER TABLE ' . $db->quoteName('#__finder_tokens') . ' ENGINE = INNODB'); 
        
                   $db->execute(); 
        
                   // Set the tokens aggregate table to InnoDB. 
        
                   $db->setQuery('ALTER TABLE ' . $db->quoteName('#__finder_tokens_aggregate') . ' ENGINE = INNODB'); 
        
                   $db->execute(); 
        
                   // Set the internal state. 
        
                   $state = $memory; 
        
               } 
        
               return true; 
        
           }

It in use while indexing

claudiosoprano · 2023-04-12T08:36:06Z

Do you think i can comment out the row 625 ???

Or it needs a rework ?

_{This comment was created with the J!Tracker Application at issues.joomla.org/tracker/joomla-cms/40368.}

claudiosoprano · 2023-04-12T08:44:35Z

Note: this installation is working on another host with same Percona Cluster but on joomla 3.9.25 with php php 7.3, while the new joomla is 4.2.9 with php 8.1.2

same configuration on both hosts

_{This comment was created with the J!Tracker Application at issues.joomla.org/tracker/joomla-cms/40368.}

Fedik · 2023-04-12T08:50:43Z

Well, you can try, no one can forbid you to do so :)
but it will be reset on next update.

Or it needs a rework ?

Currently I don't know how it can be done differently, to cover your case.
Maybe someone else will look in to it.

this installation is working on another host with same Percona Cluster but on joomla 3.9.25

In Joomla 3 this is dummy method

joomla-cms/administrator/components/com_finder/helpers/indexer/indexer.php

Lines 570 to 573 in a2a0fac

    
           protected function toggleTables($memory) 
        
           { 
        
           	return true; 
        
           }

richard67 · 2023-04-12T10:14:42Z

We could add a check to the toggleTables method if "MEMORY" is in the list of available engines returned by a "SHOW ENGINES" SQL statement. The check should be just after the check if we are on MySQL and should save the result in a static variable or a class variable so it is not checked every time. @Fedik what do you think?

Fedik · 2023-04-12T10:34:51Z

That could work

richard67 · 2023-04-12T10:42:12Z

Unfortunately the "SHOW ENGINES" SQL statement doesn't support a WHERE clause like the "SHOW COLUMNS" does, so we cannot check for a specific engine, we can only get them all and then check the result. We could also do a "SELECT * FROM INFORMATION_SCHEMA.ENGINES WHERE ENGINE = 'MEMORY'", but as far as I know we avoid queries to the information_schema since there might not be enough privileges in some database server environments.

claudiosoprano · 2023-04-12T11:05:58Z

For me would be better to add an option or a variable to TRUE or FALSE in the configuration of joomla or for the component itself

_{This comment was created with the J!Tracker Application at issues.joomla.org/tracker/joomla-cms/40368.}

Fedik · 2023-04-12T11:09:18Z

... so we cannot check for a specific engine

hmhm, or maybe can just wrap current query code in to try/cactah, and set "is supported" flag on first run/crash.

richard67 · 2023-04-12T11:16:12Z

... so we cannot check for a specific engine

hmhm, or maybe can just wrap current query code in to try/cactah, and set "is supported" flag on first run/crash.

Could also be a way but then we should try to catch the right exception.

@claudiosoprano Question: How have you managed to install Joomla with a database server which doesn't support the "MEMORY" engine? I would expect the installation to fail here https://github.com/joomla/joomla-cms/blob/4.3-dev/installation/sql/mysql/extensions.sql#L607 . Or have you updated from 3.10? This would work because there is no change to the "MEMORY" engine on update, so there is no error on update.

Fedik · 2023-04-12T11:33:06Z

Or have you updated from 3.10? This would work because there is no change to the "MEMORY" engine on update, so there is no error on update.

@richard67 I think it also possible to edit that file before installation. But not like this is recommended anyway :)

richard67 · 2023-04-12T11:34:25Z

@Fedik This will fix the issue when running on a database which doesn't support the MEMORY engine. But that can only be the case on a site updated from 3.10. Installation of a new site will fail. @Hackwar What do you think? We could change the extensions.sql for mysql so it installs the table with the InnoDB engine and switching to MEMORY engine would be done in PHP only, and only when it's supported.

richard67 · 2023-04-12T11:41:02Z

@Fedik I still would prefer to check with "SHOW ENGINES" because I'd not like to catch all kinds of exceptions. I could make a PR with that and additionally other changes for making new installations work on MySQL (or MariaDB) without MEMORY engine support.

Fedik · 2023-04-12T11:56:42Z

I do not mind, if you think that will be better, we can close my PR anytime ;)

Hackwar · 2023-04-12T11:56:53Z

Yes, the correct solution would be to install as innodb, then after the SQL files are executed to change the engine to memory if supported and to extend the check in the toggle function to check that properly with the SHOW ENGINES command.

Hackwar · 2023-04-12T11:58:19Z

In J3 the toggle function isn't empty either. The class is extended by another, DB specific class. Since the changes in J4 have been reduced to nearly nothing, that distinction has been removed.

richard67 · 2023-04-12T12:26:14Z

@Hackwar And what do you think about the fact that in extensions.sql we use the MEMORY storage engine for these 2 tables, so making a new installation on a MySQL (or MariaDB) which does not support that engine will fail, wile in the update SQL script where the finder stuff is touched, the table is not touched either, so if someone has set it to InnoDB it will remain like this until the indexer runs and then fail with the exception?

Hackwar · 2023-04-12T12:27:35Z

Yes, the correct solution would be to install as innodb, then after the SQL files are executed to change the engine to memory if supported and to extend the check in the toggle function to check that properly with the SHOW ENGINES command.

install as innodb, then ... change the engine to memory if supported

claudiosoprano · 2023-04-12T12:43:16Z

We updated from v3.x on a cloned server that used a single MYSQL server, then backupped with akeeba and restored with kickstart.php using the Percona XtraDB Cluster with no problems or errors

The problem is for example with mysql that

show engines;

will always answer that memory engine is supported because mysql itself use the MEMORY storage engine and it can't be disabled

https://forums.mysql.com/read.php?92,635909,635909#msg-635909

but Percona Xtradb Cluster (either using MariaDV or Mysql) will not support this engine, but mysql inside will always show

mysql> show engines;
+--------------------+---------+----------------------------------------------------------------------------+--------------+------+------------+
| Engine | Support | Comment | Transactions | XA | Savepoints |
+--------------------+---------+----------------------------------------------------------------------------+--------------+------+------------+
| MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |
| PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO |
| BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO |
| InnoDB | DEFAULT | Percona-XtraDB, Supports transactions, row-level locking, and foreign keys | YES | YES | YES |
| MyISAM | YES | MyISAM storage engine | NO | NO | NO |
| ARCHIVE | YES | Archive storage engine | NO | NO | NO |
| CSV | YES | CSV storage engine | NO | NO | NO |
| FEDERATED | NO | Federated MySQL storage engine | NULL | NULL | NULL |
| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |
+--------------------+---------+----------------------------------------------------------------------------+--------------+------+------------+
9 rows in set (0.00 sec)

and maybe also some other DBMS could answer in this way.

For this i asked for a variable configuration to set FALSE or TRUE to disable or enable the memory table engine

Claudio

_{This comment was created with the J!Tracker Application at issues.joomla.org/tracker/joomla-cms/40368.}

richard67 · 2023-04-12T13:00:31Z

So it seems we can't rely on the "SHOW ENGINES". That means we have to catch the exception like @Fedik 's PR #40373 does.

Hackwar · 2023-04-12T13:13:56Z

I strongly suggest to not add yet another manual option here. This has to be figured out automatically by Joomla as @Fedik did.

claudiosoprano · 2023-04-12T13:20:16Z

the query are two, i posted both in the Your text to link here...

_{This comment was created with the J!Tracker Application at issues.joomla.org/tracker/joomla-cms/40368.}

Fedik · 2023-04-12T13:34:43Z

Okay, I will reopen the PR, that fix for toggleTables,
however for installation it still need to implement something.

richard67 · 2023-04-12T15:32:16Z

Okay, I will reopen the PR, that fix for toggleTables, however for installation it still need to implement something.

I think that can be done with a different PR.

richard67 · 2023-04-12T15:34:19Z

@Hackwar Should we lave this issue open for the installation thing (which is buried in a comment below the initial post? Or shall we close it and make a new issue especially for the installation thing?

richard67 · 2023-04-12T20:20:46Z

Closing as having a pull request. See #40373 .

richard67 · 2023-04-13T11:17:44Z

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.

This explains why @claudiosoprano could install Joomla on a single database without replication.

claudiosoprano · 2023-04-13T11:58:08Z

All right @richard67, it is a limitation of Galera Cluster replication, mysql and mariadb support it, this is because SHOW ENGINES; command shows storage engine memory is supported but then it will not work on the Cluster, while on a single server all works like a charms, but with the patch for the PR it is working now.

Thanks anyway for the patch, i suppose it will be useful for others DBMS that don't use MEMORY storage engine.

lscorcia · 2023-11-28T23:47:45Z

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.

joomla-cms-bot added the No Code Attached Yet label Apr 11, 2023

richard67 added the bug label Apr 11, 2023

Quy added the Information Required label Apr 11, 2023

richard67 removed the Information Required label Apr 12, 2023

Fedik mentioned this issue Apr 12, 2023

Finder Indexer detect whether Memory engine is supported #40373

Merged

4 tasks

This comment was marked as outdated.

Sign in to view

richard67 closed this as completed Apr 12, 2023

richard67 mentioned this issue Jun 1, 2023

Joomla 4.3.2 - Storage Engine memory disabled issue #40697

Closed

lscorcia mentioned this issue Nov 29, 2023

Indexer - Saving articles fails on a MySQL Galera replication cluster #42413

Closed

joomla 4.2.9 com_finder Storage Engine 'memory' unknown on modifying articles on innodb tables #40368

joomla 4.2.9 com_finder Storage Engine 'memory' unknown on modifying articles on innodb tables #40368

Comments

claudiosoprano commented Apr 11, 2023

Steps to reproduce the issue

Expected result

Actual result

System information (as much as possible)

Additional comments

Fedik commented Apr 11, 2023

claudiosoprano commented Apr 11, 2023

Fedik commented Apr 11, 2023

alikon commented Apr 12, 2023

claudiosoprano commented Apr 12, 2023

claudiosoprano commented Apr 12, 2023

claudiosoprano commented Apr 12, 2023

Fedik commented Apr 12, 2023

claudiosoprano commented Apr 12, 2023

claudiosoprano commented Apr 12, 2023

Fedik commented Apr 12, 2023

richard67 commented Apr 12, 2023

Fedik commented Apr 12, 2023

richard67 commented Apr 12, 2023 • edited

claudiosoprano commented Apr 12, 2023

Fedik commented Apr 12, 2023

richard67 commented Apr 12, 2023

This comment was marked as outdated.

Fedik commented Apr 12, 2023

richard67 commented Apr 12, 2023 • edited

richard67 commented Apr 12, 2023

Fedik commented Apr 12, 2023

Hackwar commented Apr 12, 2023

Hackwar commented Apr 12, 2023

richard67 commented Apr 12, 2023

Hackwar commented Apr 12, 2023

claudiosoprano commented Apr 12, 2023

richard67 commented Apr 12, 2023

Hackwar commented Apr 12, 2023

claudiosoprano commented Apr 12, 2023

Fedik commented Apr 12, 2023 • edited

richard67 commented Apr 12, 2023

richard67 commented Apr 12, 2023

richard67 commented Apr 12, 2023

richard67 commented Apr 13, 2023

claudiosoprano commented Apr 13, 2023 • edited

lscorcia commented Nov 28, 2023

richard67 commented Apr 12, 2023 •

edited

richard67 commented Apr 12, 2023 •

edited

Fedik commented Apr 12, 2023 •

edited

claudiosoprano commented Apr 13, 2023 •

edited