Hi,
Statistics are not working for my moodle site. When I run the cron, either scheduled or manually, I get the following output (with debug settings set to developer).
Running auth crons if required...
Running daily statistics gathering, starting at 1223942400:
1:0 2:0 3:1 <div class="notifytiny" style="text-align:center"><br /><br />UPDATE mdl_stats_daily
SET stat2 = (SELECT COUNT(DISTINCT ra.userid)
FROM mdl_role_assignments ra JOIN mdl_context ctx
ON ctx.id = ra.contextid
CROSS JOIN mdl_course c
JOIN mdl_role_capabilities rc
ON rc.roleid = ra.roleid LEFT OUTER JOIN mdl_course_categories cc1
ON cc1.id = c.category
WHERE ra.roleid = mdl_stats_daily.roleid AND
c.id = mdl_stats_daily.courseid AND
((rc.capability = 'moodle/course:view' )
AND rc.permission = 1 AND rc.contextid = 1
AND (ctx.contextlevel = 10
OR (c.id = ctx.instanceid AND ctx.contextlevel = 50) OR (cc1.id = ctx.instanceid AND ctx.contextlevel = 40))) AND
EXISTS (SELECT 'x'
FROM mdl_log l
WHERE l.course = mdl_stats_daily.courseid AND
l.userid = ra.userid AND l.time >= 1223942400 AND l.time < 1224028800))
WHERE mdl_stats_daily.stattype = 'enrolments' AND
mdl_stats_daily.timeend = 1224028800 AND
mdl_stats_daily.courseid IN
(SELECT DISTINCT l.course
FROM mdl_log l
WHERE l.time >= 1223942400 AND l.time < 1224028800)<ul style="text-align:left"><li>line 103 of lib\dmllib.php: call to debugging()</li><li>line 255 of lib\statslib.php: call to execute_sql()</li><li>line 468 of admin\cron.php: call to stats_cron_daily()</li></ul></div>
<div class="notifytiny" style="text-align:center"><br /><br />SELECT name FROM mdl_config WHERE name = 'statsrunning'<ul style="text-align:left"><li>line 686 of lib\dmllib.php: call to debugging()</li><li>line 1134 of lib\dmllib.php: call to get_recordset_sql()</li><li>line 1098 of lib\dmllib.php: call to get_field_sql()</li><li>line 648 of lib\moodlelib.php: call to get_field()</li><li>line 476 of lib\adminlib.php: call to set_config()</li><li>line 559 of lib\statslib.php: call to set_cron_lock()</li><li>line 468 of admin\cron.php: call to stats_cron_daily()</li></ul></div>
...error occured, completed 0 days of statistics.
Cron script completed correctly
My Moodle setup is: Moodle 1.9.3+ (20081015), Apache 2.2, Php 5.2.6, MS SQL 2005 and FreeTDS
Cheers,
Ross
Think I've worked out what is going on.
The issue seems to be linked to some code in lib\accesslib.php. The code in question can be found on lines 1061 - 1063.
/// To keep cross-db we need to strip any prefix in the ORDER BY clause for queries using UNION
$sql .= "
" . preg_replace('/[a-z]+\./i', '', $sort); /// Add ORDER BY clause
Once this code is commented out (or removed) then everything works with no other changes carried out! Do you know if removing the lines mentioned above is going to have any detremental effects to other parts of Moodle?
This code was part of the added code for bug MDL-16209, discussion on Performance Issue in get_user_courses_bycap.
http://moodle.org/mod/forum/discuss.php?d=104137
Cheers,
Ross
Still no luck!! Saved the changes, reset Apache and cleared the local cache and I still get the same message!
Increased debug mode to developer and now I see the following message:
Ambiguous column name 'sortorder'.
SELECT c.id,c.sortorder,c.shortname,c.idnumber,c.category,c.fullname,c.teacher,c.teachers,c.student,c.students,c.guest,c.startdate,c.visible,c.newsitems,c.cost,c.enrol,c.groupmode,c.groupmodeforce,c.*, ctxid, ctxpath, ctxdepth, ctxlevel, categorypath FROM ( SELECT c.id, ctx.id AS ctxid, ctx.path AS ctxpath, ctx.depth AS ctxdepth, ctx.contextlevel AS ctxlevel, cc.path AS categorypath FROM mdl_course c JOIN mdl_course_categories cc ON c.category=cc.id JOIN mdl_context ctx ON (c.id=ctx.instanceid AND ctx.contextlevel=50) JOIN mdl_role_assignments ra ON (ra.contextid=ctx.id AND ra.userid=4) UNION SELECT c.id, ctx.id AS ctxid, ctx.path AS ctxpath, ctx.depth AS ctxdepth, ctx.contextlevel AS ctxlevel, cc.path AS categorypath FROM mdl_course c JOIN mdl_course_categories cc ON c.category=cc.id JOIN mdl_context ctx ON (c.id=ctx.instanceid AND ctx.contextlevel=50) JOIN mdl_role_capabilities rc ON (rc.contextid=ctx.id AND (rc.capability='moodle/course:view' )) UNION SELECT c.id, ctx.id AS ctxid, ctx.path AS ctxpath, ctx.depth AS ctxdepth, ctx.contextlevel AS ctxlevel, cc.path AS categorypath FROM mdl_course c JOIN mdl_course_categories cc ON c.category=cc.id JOIN mdl_context ctx ON (c.id=ctx.instanceid AND ctx.contextlevel=50) WHERE (ctx.path LIKE '/1/3/1104/1104/%') ) inline_view INNER JOIN mdl_course c ON inline_view.id = c.id ORDER BY visible DESC,sortorder ASC
line 686 of lib\dmllib.php: call to debugging()
line 1065 of lib\accesslib.php: call to get_recordset_sql()
line 936 of lib\datalib.php: call to get_user_courses_bycap()
line 68 of my\index.php: call to get_my_courses()
Incorrect $rs used!
line 822 of lib\dmllib.php: call to debugging()
line 1071 of lib\accesslib.php: call to rs_fetch_next_record()
line 936 of lib\datalib.php: call to get_user_courses_bycap()
line 68 of my\index.php: call to get_my_courses()
Incorrect $rs used!
line 866 of lib\dmllib.php: call to debugging()
line 1082 of lib\accesslib.php: call to rs_close()
line 936 of lib\datalib.php: call to get_user_courses_bycap()
line 68 of my\index.php: call to get_my_courses()
I have to use MS SQL at the moment as it's the DB of choice for our place (fits in with out IT model). Stopped using IIS last year though due to the amount of issues we had with Moodle so maybe next year we will look at switching to My SQL for Moodle?!
Cheers,
Ross
Thanks for reply Amr,
Sadly I still get the same message (have restarted Apache and cleared local cache just to make sure).
One strange anomaly I've found is that "My Moodle" works temporarily again after I enrol on a course! Log off and on again and I'm back to the same error message.
The changes made to the course\lib.php file do not seem to be reflected in the error message I'm shown on screen. The current message is now shown below.
Cheers,
Ross