- Posts: 16
- Thank you received: 3
Welcome to the LimeSurvey Community Forum
Ask the community, share ideas, and connect with other LimeSurvey users!
Help! We have a problem with MySQL database
- YolandaJolly
- Topic Author
- Offline
- New Member
Less
More
3 months 1 week ago #255703
by YolandaJolly
Help! We have a problem with MySQL database was created by YolandaJolly
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.
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.
Please Log in to join the conversation.
- DenisChenu
- Offline
- LimeSurvey Community Team
Less
More
- Posts: 13716
- Thank you received: 2502
3 months 1 week ago #255705
by DenisChenu
Assistance on LimeSurvey forum and LimeSurvey core development are on my free time.
I'm not a LimeSurvey GmbH member, professional service on demand , plugin development .
I don't answer to private message.
Replied by DenisChenu on topic Help! We have a problem with MySQL database
What is the definition of «crash the MySql database server» ?
When you have this request ?
When you have this request ?
Assistance on LimeSurvey forum and LimeSurvey core development are on my free time.
I'm not a LimeSurvey GmbH member, professional service on demand , plugin development .
I don't answer to private message.
The following user(s) said Thank You: YolandaJolly
Please Log in to join the conversation.
- YolandaJolly
- Topic Author
- Offline
- New Member
Less
More
- Posts: 16
- Thank you received: 3
3 months 1 week ago #255708
by YolandaJolly
Replied by YolandaJolly on topic Help! We have a problem with MySQL database
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.
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.
Please Log in to join the conversation.
- DenisChenu
- Offline
- LimeSurvey Community Team
Less
More
- Posts: 13716
- Thank you received: 2502
3 months 1 week ago #255709
by DenisChenu
Assistance on LimeSurvey forum and LimeSurvey core development are on my free time.
I'm not a LimeSurvey GmbH member, professional service on demand , plugin development .
I don't answer to private message.
Replied by DenisChenu on topic Help! We have a problem with MySQL database
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.
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.
Assistance on LimeSurvey forum and LimeSurvey core development are on my free time.
I'm not a LimeSurvey GmbH member, professional service on demand , plugin development .
I don't answer to private message.
The following user(s) said Thank You: YolandaJolly
Please Log in to join the conversation.
- YolandaJolly
- Topic Author
- Offline
- New Member
Less
More
- Posts: 16
- Thank you received: 3
3 months 1 week ago #255711
by YolandaJolly
Replied by YolandaJolly on topic Help! We have a problem with MySQL database
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.
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.
Please Log in to join the conversation.
- DenisChenu
- Offline
- LimeSurvey Community Team
Less
More
- Posts: 13716
- Thank you received: 2502
3 months 1 week ago #255725
by DenisChenu
Assistance on LimeSurvey forum and LimeSurvey core development are on my free time.
I'm not a LimeSurvey GmbH member, professional service on demand , plugin development .
I don't answer to private message.
Replied by DenisChenu on topic Help! We have a problem with MySQL database
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.
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.
Assistance on LimeSurvey forum and LimeSurvey core development are on my free time.
I'm not a LimeSurvey GmbH member, professional service on demand , plugin development .
I don't answer to private message.
The following user(s) said Thank You: YolandaJolly
Please Log in to join the conversation.
- YolandaJolly
- Topic Author
- Offline
- New Member
Less
More
- Posts: 16
- Thank you received: 3
3 months 1 week ago #255728
by YolandaJolly
Replied by YolandaJolly on topic Help! We have a problem with MySQL database
Hi DenisChenu!
Thank you very much for your interest and your help.
I will study your recommendations.
Kind regards.
Thank you very much for your interest and your help.
I will study your recommendations.
Kind regards.
Please Log in to join the conversation.
- holch
- Offline
- LimeSurvey Community Team
Less
More
- Posts: 11712
- Thank you received: 2756
3 months 1 week ago #255736
by holch
I answer at the LimeSurvey forum in my spare time, I'm not a LimeSurvey GmbH employee.
No support via private message.
Replied by holch on topic Help! We have a problem with MySQL database
Denis did mean that you should report the bug in the bug tracker of Limesurvey, not report his message to the forum moderators...
I answer at the LimeSurvey forum in my spare time, I'm not a LimeSurvey GmbH employee.
No support via private message.
The following user(s) said Thank You: DenisChenu, YolandaJolly
Please Log in to join the conversation.
- YolandaJolly
- Topic Author
- Offline
- New Member
Less
More
- Posts: 16
- Thank you received: 3
3 months 1 week ago #255742
by YolandaJolly
Replied by YolandaJolly on topic Help! We have a problem with MySQL database
Hi holch!
thanks for the clarification.
Kind regards.
thanks for the clarification.
Kind regards.
Please Log in to join the conversation.
- DenisChenu
- Offline
- LimeSurvey Community Team
Less
More
- Posts: 13716
- Thank you received: 2502
3 months 1 week ago #255959
by DenisChenu
Assistance on LimeSurvey forum and LimeSurvey core development are on my free time.
I'm not a LimeSurvey GmbH member, professional service on demand , plugin development .
I don't answer to private message.
Replied by DenisChenu on topic Help! We have a problem with MySQL database
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
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
Assistance on LimeSurvey forum and LimeSurvey core development are on my free time.
I'm not a LimeSurvey GmbH member, professional service on demand , plugin development .
I don't answer to private message.
The following user(s) said Thank You: YolandaJolly
Please Log in to join the conversation.
- YolandaJolly
- Topic Author
- Offline
- New Member
Less
More
- Posts: 16
- Thank you received: 3
3 months 1 week ago #255967
by YolandaJolly
Replied by YolandaJolly on topic Help! We have a problem with MySQL database
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.
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.
Please Log in to join the conversation.
- DenisChenu
- Offline
- LimeSurvey Community Team
Less
More
- Posts: 13716
- Thank you received: 2502
3 months 1 week ago #255997
by DenisChenu
We just need to find the good index to add (and here there are 2 joins inside the request)
Assistance on LimeSurvey forum and LimeSurvey core development are on my free time.
I'm not a LimeSurvey GmbH member, professional service on demand , plugin development .
I don't answer to private message.
Replied by DenisChenu on topic Help! We have a problem with MySQL database
PHP code is not important,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).
We just need to find the good index to add (and here there are 2 joins inside the request)
Assistance on LimeSurvey forum and LimeSurvey core development are on my free time.
I'm not a LimeSurvey GmbH member, professional service on demand , plugin development .
I don't answer to private message.
The following user(s) said Thank You: YolandaJolly
Please Log in to join the conversation.