My Moodle not working

My Moodle not working

由Ross Stanbridge發表於
Number of replies: 6

"My Moodle" has stopped working on our system.  10% of the time it works.  The other 90% of the time we now get the following output (with debugging switched on):

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=2) 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' )) ) 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()
No course information to show.
 

Our Moodle setup is Moodle 1.9.3 (20081015), Apache 2.2, Windows 2003, PHP 5.2 and MS SQL 2005 (with FreeTDS DB connection).

評比平均分數： -
In reply to Ross Stanbridge

Re: My Moodle not working

由ETH Zürich發表於
This usually means your SQL statement is joining on more than one table, and doesn't know which column you're trying to retrieve.

The fact is, Moodle is not well-tested under other database types, but mysql because it is the official database in use.

I can help you fix this error, but I can assure that there will be no more errors.. so you can trace them one by one and fix them 微笑

solution for the above problem: at the end of the above query, i.e: inline_view.id = c.id ORDER BY visible DESC,sortorder ASC

change it to: inline_view.id = c.id ORDER BY visible DESC,c.sortorder ASC

how to? in course/lib.php there is a line says:
$courses = get_my_courses($USER->id, 'visible DESC,sortorder ASC', array('summary'));

change it to:
$courses = get_my_courses($USER->id, 'visible DESC,c.sortorder ASC', array('summary'));


Good Luck
Amr Hourani 微笑

評比平均分數： -
In reply to ETH Zürich

Re: My Moodle not working

由Ross Stanbridge發表於

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

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()
No course information to show.
評比平均分數： -
In reply to Ross Stanbridge

Re: My Moodle not working

由ETH Zürich發表於
Hi Ross,

The query above shows that you didnt make any change in it.. it is the save as the above one!
to apply the chnage on all site queries with "my courses"
go to lib/datalib.php on line 812, you will find a function called:

function get_my_courses($userid, $sort='visible DESC,sortorder ASC', $fields=NULL, $doanything=false,$limit=0) {

change it to:

function get_my_courses($userid, $sort='visible DESC,c.sortorder ASC', $fields=NULL, $doanything=false,$limit=0) {


Frankly, i dont trust microsoft products anymore after a very messy day happened to me with mirosoft 3 years ago.... aliases is really big topic in SQL, and I wonder why you didnt use mysql; at least you can get rid of such errors.

if things are still the same, would you please change debugging to EXTRA DEVELOPER ERRORS? and refresh pages/remove cache and run admin/cron.php more than once and see what will happen, then print out the error again.

Please let me know what the result you got...

good luck
Amr Hourani

評比平均分數： -
In reply to ETH Zürich

Re: My Moodle not working

由Ross Stanbridge發表於

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

評比平均分數： -
In reply to Ross Stanbridge

Re: My Moodle not working

由ETH Zürich發表於
Noway man!

there should be something wrong!.. are you sure you made what mentioned above?!

please, change: $courses = get_my_courses($USER->id, 'visible DESC,sortorder ASC', '*', false, 21);
to
$courses = get_my_courses($USER->id, 'visible DESC, c.sortorder ASC', '*', false, 21);
in my/index.php on line : 68

please make sure the database and server are not cached.. run admin/cron.php more than ones, refresh with CTRL.

評比平均分數： -
In reply to ETH Zürich

Re: My Moodle not working

由Ross Stanbridge發表於

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

評比平均分數： -