I have an old project that I am trying to upgrade to a newer version of SQL. It is currently running MySql 5.5 and is working just fine. I have migrated som test data to MariaDB 10.5.9, but when I try running a query (which works fine on MySql 5.5), MariaDB crashes.
The query is quite big, and uses WHERE IN extensively. Currently I am unable to refactor the query sadly, so I am instead trying to figure out what causes the crash.
It has 3 WHERE IN. First is 24 items, second is 696 and third is 2. If I remove just one item from either the first or second WHERE IN, it works are returns data instantly. The
answers table is
The error I am getting
SQL Error [08S01]: Communications link failure The last packet sent successfully to the server was 0 milliseconds ago. The driver has not received any packets from the server.
And the query
SELECT definition_id, answer AS value FROM answers WHERE definition_id IN (...) AND respondent_id in (...) AND context IN (1, 0) LIMIT 50
I have already tried changing
max_allowed_packet to something higher (it was 16MB in 5.5), but it sadly changes nothing.
Result of EXPLAIN SQL_NO_CACHE (if I remove alot of the data in the WHERE IN to avoid crash)
|1||PRIMARY||ALL||824||Using temporary; Using filesort|
|2||DERIVED||s1||range||definition_respondent_context,respondent_id||definition_respondent_context||12||824||Using index condition; Using temporary; Using filesort|
EDIT: I managed to get it working by using a join on the definitions table
SELECT a.definition_id, a.answer AS value FROM answers AS a JOIN definitions AS d ON a.definition_id = d.id WHERE d.id IN (...) AND a.respondent_id in (...) AND a.context IN (1, 0) LIMIT 50
One solution to your problem is to change your design/approach such that you don’t have
WHERE IN (...) clauses with 500-1000 items in them. For one thing, it’s doubtful that you would ever have some application passing so many parameters back to your database instance. So, assuming this data is not coming from the outside, then it should be possible to maintain it in a separate table. Assuming you had two tables for this, your query could then become:
SELECT a.definition_id, a.answer AS value FROM answers a INNER JOIN definitions d ON d.id = a.definition_id INNER JOIN respondents r ON r.id = a.respondent_id WHERE context IN (1, 0) -- ORDER BY <something> LIMIT 50;