Please help us help you and fill where relevant:
Your LimeSurvey version: Versión 5.6.31+230718
Own server or LimeSurvey hosting: CentOS Linux release 7.9.2009 (Core)
More than 4000 active surveys
More than 700 users
==================
Hello.
We have a  problem with MySQL database.
Queries of this type:SELECT a.qid, a.code, l.answer, a.scale_id, a.assessment_value FROM lime_answers AS a JOIN lime_questions q on a.qid=q.qid JOIN lime_answer_l10ns l on l.aid=a.aid WHERE  1=1 and a.qid = q.qid and q.sid = 351814 and l.language='es' ORDER BY a.qid, a.scale_id, a.sortorder;

   SELECT a.qid, a.code, l.answer, a.scale_id, a.assessment_value FROM lime_answers AS a JOIN lime_questions q on a.qid=q.qid JOIN lime_answer_l10ns l on l.aid=a.aid WHERE  1=1 and a.qid = q.qid and q.sid = 268699 and l.language='es' ORDER BY a.qid, a.scale_id, a.sortorder;

They crash the MySql database server which is on a different server than where limesurvey is installed.
Could it be a database problem? Or may be the 5.6.31 version that I updated in July 2023? The first time we had a problem like this was last November.
Any ideas?

Thank you very much.

What is the definition of «crash the MySql database server» ?

When you have this request ?

Hi DenisChenu. Thank you very much.

The server becomes saturated with many of these open queries and cannot serve any of the applications that use MySQL. This occurs when surveys are answered.

I have now seen that this is a specific survey. The administrator of that survey has made several copies to send to different groups of respondents.

When someone answers one of them the problems begin.

A couple of months ago the same thing happened with another survey, which generated the same query that never closed on the database server, but it did not saturate the server as much.

Sorry for my english.

Thank you so much.

OK,

Then :
- have a greater memory server (maybe temporary)
- try to add some index on lime_answers ?
- try to add some index on lime_questions too ?

Report the issue : maybe we can improve the request.

Thanks for your quick response.

We have increased memory.
About the index, I don't understand what you want to tell me.
The queries are made from the survey.
How could I send the .lss file?

Thank yo so much.

When i talk about report issue
community.limesurvey.org/bug-tracker/

About the index dev.mysql.com/doc/refman/8.0/en/create-index.html
But i'm not sure of the index to create.

Hi DenisChenu!

Thank you very much for your interest and your help.
I will study your recommendations.

Kind regards.

Denis did mean that you should report the bug in the bug tracker of Limesurvey, not report his message to the forum moderators...

Hi holch!

thanks for the clarification.


Kind regards.

Thank you for reporting issue

bugs.limesurvey.org/view.php?id=19407

Hope we find a solution …
You can test to create a new index on answers table maybe ?
See github.com/LimeSurvey/LimeSurvey/commit/...506a9ab5950c7ad99R16

Hi DenisChenu.

Thank you for your answer.

I will try to create a new index on table "lime_answer" (although I don't quite understand the content of the link you sent me).

Just in case, I have installed a version 6.4.5 with an empty database, to migrate some users and their surveys, in order to lighten the database if I can't fix the issue.

Thank you so much.
I'll tell you how it went.

Kind regards.

YolandaJolly wrote:

I will try to create a new index on table "lime_answer" (although I don't quite understand the content of the link you sent me).
 

PHP code is not important,
We just need to find the good index to add (and here there are 2 joins inside the request)