Issues ever since I updated to MariaDB 10.6

[T1531]

Ever since I updated MariaDB, I have been having issues every now and then with queries getting stuck and breaking the website for everyone. What happens is that there is some intensive or problematic query that's started, the /tmp drive gets full (immediately), the query gets stuck, and then every user experiences issues because other queries are waiting for that stuck query to finish and it never does. I have to reboot the entire server to fix it because I'm not even able to kill the process. If I'm not online to fix it for 10 hours, the site experiences errors for 10 hours. Not good!

My first question is, shouldn't there be a way for that one query to fail without bringing everyone else down with it?

According to this page in MariaDB's documentation, there are three new variables introduced with MariaDB 10.3. idle_transaction_timeout, idle_write_transaction_timeout, and idle_readonly_transaction_timeout. It says the default for them is 0, which means transactions are never killed. Could these be what I need to configure or is it some other setting?

 

[andrew.n]

from what version did you upgrade? Do you have strict mode disabled?

 

[T1531]

from what version did you upgrade? Do you have strict mode disabled?

Honestly, I updated through WHM months ago and can't remember what it was before. This is from @@SQL_MODE:
STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

 

[ffeingol]

If /tmp is getting full, it's typically an issue with a query not MysQL/MariaDB per se. MySQL uses /tmp to store temporary tables. Those are needed for aggregate functions (things like sum) as well as potentially sorting/order by. MySQL/MariaDB (as far as I know) can only have one location for those and if that file system fills up, it can't do anything until the space is cleared.

When it happens you "may" be able to see the user/process/query that is causing the issue from the mysql/mariadb command "show full processlist" or via phpmyadmin. You should see something like "creating temp table".

 

[T1531]

If /tmp is getting full, it's typically an issue with a query not MysQL/MariaDB per se. MySQL uses /tmp to store temporary tables. Those are needed for aggregate functions (things like sum) as well as potentially sorting/order by. MySQL/MariaDB (as far as I know) can only have one location for those and if that file system fills up, it can't do anything until the space is cleared.

When it happens you "may" be able to see the user/process/query that is causing the issue from the mysql/mariadb command "show full processlist" or via phpmyadmin. You should see something like "creating temp table".

The first time I reported this and you helped me solve it, there was in fact a bad query from outdated software. I updated the software which fixed the bad query. The issue has happened much less since then, but it still happens every now and then- maybe about 4 times in the last 2 months. When I looked at the query in the process list, it was just four tables left joined together with a LIKE in the WHERE portion. One of the tables has about 200k rows and the LIKE was simply '%t%' - it came from a search page trying to filter on one of the columns. I see why querying with LIKE with just one letter on so many rows could be very slow, but should it really break the site for all other users? Also, this never happened before updating MariaDB and I used the previous version for about 3 years.