Moodle

Query multiple, overflowing database platform

Details

  • Type: Bug Bug
  • Status: Open Open
  • Priority: Critical Critical
  • Resolution: Unresolved
  • Affects Version/s: 1.9.5
  • Fix Version/s: None
  • Component/s: Database activity module
  • Labels:
    None
  • Environment:
    PHP Version 5.1.6
    Mysql 5.0.45
    Linux Red Hat 4.1.2-44
  • Database:
    MySQL
  • Difficulty:
    Moderate
  • Affected Branches:
    MOODLE_19_STABLE

Description

We have a problem with the following Moodle module "Database":

"/mod/data/view.php?d=122&mode=asearch"

In certain cases, Moodle tries to execute a query on a course and the Mysql database gets collapsed.

This is the mumber of records from the tables of our database:

mdl_data_content ? 7300 records
mdl_data_records ? 1200 records
mdl_user ? 7200 records

This is one of the Mysql queries, which causes the problem:

This query generates the following "Cartesian Product" which causes the database to hang:

SELECT COUNT(DISTINCT c.recordid)
FROM mdl_data_content c,
mdl_data_records r,
mdl_data_content cs,
mdl_user u ,
mdl_data_content c375 ,
mdl_data_content c376 ,
mdl_data_content c377 ,
mdl_data_content c378

WHERE c.recordid = r.id
AND r.dataid = 105
AND r.userid = u.id
AND cs.recordid = r.id
AND c375.recordid = r.id
AND c376.recordid = r.id
AND c377.recordid = r.id
AND c378.recordid = r.id;

We expect your help for our problem, thank you very much in advance.

Greetings.

Issue Links

This issue duplicates:
MDL-17051 Moodle1.9.3 getting slow Critical Closed

Activity

Ascending order - Click to sort in descending order
Hide
Permalink
T. Nuin added a comment -

Hi,

I'm the programmer at the University of Bath working on Moodle. We have experienced the same problem. I wanted to know if anyone is currently looking at this? If not, or if this is in limbo, then I'll resolve this, hopefully I can allocate time next week. This is a serious problem as these queries have actually managed to bring our entire Moodle system down when a student runs a more complicated search in the Database Module.

So before I get stuck in and potentially duplicate effort, has anyone else made a start on this?

Thanks,

Taliesin Nuin.

Show
T. Nuin added a comment - Hi, I'm the programmer at the University of Bath working on Moodle. We have experienced the same problem. I wanted to know if anyone is currently looking at this? If not, or if this is in limbo, then I'll resolve this, hopefully I can allocate time next week. This is a serious problem as these queries have actually managed to bring our entire Moodle system down when a student runs a more complicated search in the Database Module. So before I get stuck in and potentially duplicate effort, has anyone else made a start on this? Thanks, Taliesin Nuin.
Hide
Permalink
Jean FRUITET added a comment -

In that script ./mod/data/view.php
Remove all
mdl_data_content cs,
and
AND cs.recordid = r.id
and replace all SQL clause
$searchselect = " AND (cs.content
by
$searchselect = " AND (c.content

and that's all

Show
Jean FRUITET added a comment - In that script ./mod/data/view.php Remove all mdl_data_content cs, and AND cs.recordid = r.id and replace all SQL clause $searchselect = " AND (cs.content by $searchselect = " AND (c.content and that's all
Hide
Permalink
Hubert Chathi added a comment -

This seems to be the same bug as MDL-17327. Can you try copying mod/data/view.php from the latest weekly stable release, and see if it fixes the issue?

Show
Hubert Chathi added a comment - This seems to be the same bug as MDL-17327. Can you try copying mod/data/view.php from the latest weekly stable release, and see if it fixes the issue?
Hide
Permalink
Generazion Consulting S.L. added a comment -

Thanks Hubert Chathi, we have solved the problem with your advice. Friends is working properly.

Show
Generazion Consulting S.L. added a comment - Thanks Hubert Chathi, we have solved the problem with your advice. Friends is working properly.
Hide
Permalink
Paul Dawson added a comment -

This bug is still present in the current 1.9.7+ release. The database query results in the system grinding to a halt in some cases.

I have altered the view.php file from Moodle 1.9.7+ (Build: 20100203) and attached it. Jean FRUITET's post above was the correct solution it seems. It might be a good idea for someone responsible for this activity to check this into the latest release.

Show
Paul Dawson added a comment - This bug is still present in the current 1.9.7+ release. The database query results in the system grinding to a halt in some cases. I have altered the view.php file from Moodle 1.9.7+ (Build: 20100203) and attached it. Jean FRUITET's post above was the correct solution it seems. It might be a good idea for someone responsible for this activity to check this into the latest release.
Hide
Permalink
Fred Woolard added a comment -

I've added a comment to MDL-17327 which may address your issue.

Show
Fred Woolard added a comment - I've added a comment to MDL-17327 which may address your issue.
Hide
Permalink
Franck Bellemain added a comment -

Problems with the database module are still opened in my 1.9.13 version of moodle. I made the Jean Fruitet corrections, but it doesn't seem to be enough, I still reach the max_connections value. And I haven't been able to find any recent solution, it's almost like this module isn't maintened in the 1.9 version of moodle since the problem is known and some fixes are posted, but the code continue the same.

Show
Franck Bellemain added a comment - Problems with the database module are still opened in my 1.9.13 version of moodle. I made the Jean Fruitet corrections, but it doesn't seem to be enough, I still reach the max_connections value. And I haven't been able to find any recent solution, it's almost like this module isn't maintened in the 1.9 version of moodle since the problem is known and some fixes are posted, but the code continue the same.

Dates

  • Created:
    Updated:
Atlassian JIRA (v4.4.3#663-r165197) | Report a problem
Powered by a free Atlassian JIRA open source license for Moodle. Try JIRA - bug tracking software for your team.