Moodle in English: Execution of Sql Query

Execution of Sql Query

by Hamza Khan - Thursday, July 9, 2020, 2:03 PM

Hi !
I found this query in log files of moodle 2.4

SELECT c.id,c.shortname , ctx.id AS ctxid, ctx.path AS ctxpath, ctx.depth AS ctxdepth, ctx.contextlevel AS ctxlevel, ctx.instanceid AS ctxinstance

FROM mdl_course c

LEFT JOIN mdl_context ctx ON (ctx.instanceid = c.id AND ctx.contextlevel = 50)

ORDER BY c.id ASC;

Where is the query executed as it returns all the courses in the lms which slows down our system......Thank you

Re: Execution of Sql Query

by Howard Miller - Thursday, July 9, 2020, 2:42 PM

Moodle 2.4 has been completely unsupported for just over six years!

If I were you, I would seriously consider upgrading.

Average of ratings: -

Re: Execution of Sql Query

by Hamza Khan - Thursday, July 9, 2020, 4:14 PM

We are working on the upgrade to 3.7, but for now I need solution to this problem .......any help would really be appreciated, Thank you

Average of ratings: -

Re: Execution of Sql Query

by Howard Miller - Friday, July 10, 2020, 2:45 PM

Ok - let me try to be clearer. You are saying that there is a query in an ancient version of Moodle that is causing a performance problem.

How do you know that in the intervening 6 years it has not been identified and fixed? That's the first step in this investigation. So you can do what I would do and just upgrade and hope for the best (because you need to anyway). Alternatively, you can dig around and see if that code was changed in newer versions or see if there are any tracker reports relating to it. And it you find that there are? Then you need to upgrade

What I doubt will happen is that anybody here will install 2.4 to do any of that for you. I'm not trying to be "that guy", I'm strongly recommending you get on with the upgrade.

The only other thing you can do is to run the check for indexes in Site administration > Development > XMLDB editor to check for any missing indexes.

Average of ratings: -

Re: Execution of Sql Query

by James Steerpike - Thursday, July 9, 2020, 3:41 PM

From the command line, assuming your database name is moodle
sudo mysql -uroot

At the mysqlprompt > use moodle;
Then your query
SELECT c.id,c.shortname , ctx.id AS ctxid, ctx.path AS ctxpath, ctx.depth AS ctxdepth, ctx.contextlevel AS ctxlevel, ctx.instanceid AS ctxinstance
FROM mdl_course c
LEFT JOIN mdl_context ctx ON (ctx.instanceid = c.id AND ctx.contextlevel = 50)
ORDER BY c.id ASC;

quit;

you out of mysql and back to the command line.

Average of ratings: -

Re: Execution of Sql Query

by Hamza Khan - Thursday, July 9, 2020, 4:17 PM

Brother I want to know where the query is written in moodle 2.4 code really sorry for not making it clear as I am a beginner, Thank you

Average of ratings: -

Re: Execution of Sql Query

by Rick Jerz - Thursday, July 9, 2020, 9:07 PM

This is pretty advanced stuff for a beginner. I am wondering what your real goal is? Also, as Howard mentioned, 2.4 is way back, so I would not be able to test anything that I might want to share with you.

Average of ratings: -

Re: Execution of Sql Query

by Hamza Khan - Thursday, July 9, 2020, 10:33 PM

it would really be appreciated if you can share whatever your knowledge is about this problem.As LMS was down for few our and according to my findings this query might be causing the problem that is why I need to know where the query is written in moodle code. kindly read the following post for further details thank you

NOTE: https://moodle.org/mod/forum/discuss.php?d=406759#p1642003

Average of ratings: -

Re: Execution of Sql Query

by James Steerpike - Thursday, July 9, 2020, 11:26 PM

I would suspect the query is adhoc rather than Moodle code. Perhaps you could use grep to search php code?

Average of ratings: -

General help

Execution of Sql Query