Steps to reproduce the issue

1. On production, use a DB user limited to SELECT, INSERT, UPDATE, DELETE (no ALTER, no CREATE, no DROP).
2. Enable Smart Search and its content plugins.
3. Go to Components → Smart Search → Index and click Rebuild (or run a CLI reindex if applicable).
4. Observe SQL errors in logs or in the backend.

Expected result

1. Indexing works without requiring schema-modifying privileges at runtime.
2. com_finder uses disk-based tables and existing indexes without issuing ALTER TABLE.

Actual result

Indexing attempts to run:

ALTER TABLE `#__finder_tokens` ENGINE = MEMORY;
ALTER TABLE `#__finder_tokens_aggregate` ENGINE = MEMORY;

Since ALTER is denied, reindexing fails and Smart Search is not usable on production. The message is "An Errior Has Occured". ALTER command denied to user "XXX@localhost" for table 'XXX'.'XXX_finder_tokens'.

System information (as much as possible)

Joomla: 5.3.4

PHP: 8.2.29

MySQL: 10.11.14-MariaDB-0+deb12u2-log

Web server: Apache

Additional comments

File: administrator/components/com_finder/src/Indexer/Indexer.php
Method: toggleTables($memory)
switches #__finder_tokens and #__finder_tokens_aggregate between MEMORY and disk via ALTER TABLE
Trigger: called based on memory_table_limit and other thresholds during indexing

Suggested improvement(s)

Option A (configurable):

Add a component parameter (e.g., Disable engine switching / MEMORY tables).

When enabled, toggleTables() becomes a no-op and indexing remains on disk tables only (no ALTER).

Option B (automatic fallback):

In toggleTables(), detect that ALTER is not permitted (or that engine switching is unsupported) and silently skip engine changes, continuing on disk.

Either approach would let Smart Search run without schema privileges at runtime.

Workarounds tried

Grant ALTER temporarily → works but not acceptable for production.

Disable Smart Search → acceptable functionally, but not desired.

Index on staging and copy tables → breaks as soon as indexing runs on prod.

Additional context

On Joomla 4, we did not observe ALTER at runtime during indexing (please reconfirm).

This looks like a Joomla 5 behavior tied to the new indexer logic.

Tracks

You may look at https://joomla.stackexchange.com/questions/34285/why-does-com-finder-smart-search-require-sql-alter-privileges-in-production-c/34286#34286 where Irata suggested some modifications in details. Many thanks to him !