TYPO3 Core

I recently came across this error in a single TYPO3 installation (TYPO3 8.7.10, PHP 7.0.30, MySQL 5.5.54) in two different situations:

• Pasting copied news in the backend
• Querying table tx_news_domain_model_news using QueryBuilder in a custom extension

The error only occurs in one of a dozen almost identical TYPO3 installations. And the error appeared all of a sudden a few weeks ago. Before that the installation was running fine without any problems. Until now I didn't manage to find out what causes this error.

Could it be that this is some kind of database error that has nothing to do with indexed_search or news?

MySQL documentation about Commands out of sync error_

In my case table tx_news_domain_model_news was broken. Reading data worked but writing data sometimes didn't work for unknown reasons. Running mysqlcheck --auto-repair fixed this problem for me.

Tobias Schmidt wrote:

I recently came across this error in a single TYPO3 installation (TYPO3 8.7.10, PHP 7.0.30, MySQL 5.5.54) in two different situations:

• Pasting copied news in the backend
• Querying table tx_news_domain_model_news using QueryBuilder in a custom extension

Thank you Tobias Schmidt! This was the right hint. In my case, the index of index_rel was broken. Deleting the table and rebuilding it fixed the problem for me!

Could we add this as a general error or troubleshoot for TYPO3 as a whole?

I came across the same error message in TYPO3 10. In my case it popped up in BE module of a custom extension and the actual problem was combination of insufficient sort_buffer_size on MySQL server in production and inefficient SELECT query. After I added one column used in ORDER BY clause to main index of the table, the problem was gone.

Would still like to know why I got this error in TYPO3 - I only found out about the insufficient sort_buffer_size when I rerun the problematic query in Adminer.

Found this ticket while googling the MariaDB error I got. In my case this is with EXT:news and a too greedy "starting point expansion". Solution/hack described here: https://github.com/georgringer/news/issues/1734

Maybe this mariadb bug is the cause of it: https://jira.mariadb.org/browse/MDEV-27937

Same bug appears in direct_mail when showing the "receipient list" for some bigger list (>1000 entries).

Bug of the "in_predicate_conversion" in later MariaDB versions (see https://mariadb.com/kb/en/conversion-of-big-in-predicates-into-subqueries/). Workaround for now is to turn that optimization off for the while in your `my.cnf`:

```
in_predicate_conversion_threshold=0
```

It "only happens in production" is because you are probably running newer MariaDB in production while you are running MySQL (or older MariaDB) in your local dev / docker environment. If you switch to the same MariaDB version as in production you can reproduce the error also locally.

This should be fixed in MariaDB 10.3.35, 10.4.25, 10.4.26, 10.5.16 and 10.7.4 to be released soon(tm).

I get this error when running the LinkValidator task on our TYPO3 installation (10,000+ pages) when setting the page tree traversal starting point to the root page and selecting a depth of infinite (i.e., link validator has to check more than thousands of pages).

Using

• TYPO3 10.4.27
• Debian 11 bullseye
• mariadb-server 1:10.5.15-0+deb11u1 ( https://packages.debian.org/de/bullseye/mariadb-server-10.5 )

DB/Connections/Default/initCommands = 'SET in_predicate_conversion_threshold=0;'

A hotfix summary for the not-so-tech-savy:

The most common reason for the error commands out of sync are special MariaDB versions which try to optimize queries with big IN() clauses. To stop this behaviour a config var needs to be set either in

(1) the MariaDB server config, e.g. my.cnf inside the category [mysqld]

[mysqld]
in_predicate_conversion_threshold=0

DB:
    Connections:
        Default:
            initCommands: 'SET in_predicate_conversion_threshold=0;

Just for the records: I had the same problem on a Debian machine after OS upgrade. Setting the suggested in_predicate_conversion_threshold did not help and I had to clear the contents of affected tables (for my luck only search index data from ext:ke_search). After that, the error did not appear any more.