Moodle

Is this still a problem in latest HEAD?

Yeah i've just updated and its still the case, Raising the priority now that MDL-11347 is fixed.

Thought I was completely crazy until I saw this one - comments in MDL-11656 may help you figure it out.

Error from viewing a category:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ') ) AND ra.roleid IN (21) AND c.cate' at line 15

SELECT ctx.path, ctx.instanceid, ctx.contextlevel, ra.hidden, r.id AS roleid, r.name as rolename, u.id AS userid, u.firstname, u.lastname FROM role_assignments ra JOIN context ctx ON ra.contextid = ctx.id JOIN user u ON ra.userid = u.id JOIN role r ON ra.roleid = r.id LEFT OUTER JOIN course c ON (ctx.instanceid=c.id AND ctx.contextlevel=50) WHERE ( c.id IS NOT NULL OR ra.contextid IN () ) AND ra.roleid IN (21) AND c.category = 841 ORDER BY r.sortorder ASC, ctx.contextlevel ASC, ra.sortorder ASC

• line 684 of lib/dmllib.php: call to debugging()
• line 700 of lib/datalib.php: call to get_recordset_sql()
• line 1800 of course/lib.php: call to get_courses_wmanagers()
• line 296 of course/category.php: call to print_courses()

Assigning to Petr as I don't think ML will have time this week.

Could you please try to upgrade again with latest 1.9dev? I can not replicate this problem.
Maybe this was already fixed, I did several changes in context creation last week.

Just updated from CVS and its still the same i'm afraid.

I will see if I can try track down something more reproduceable from it.

Some debugging for you:

The broken sql above tracked as:

• line 684 of lib/dmllib.php: call to debugging()
• line 703 of lib/datalib.php: call to get_recordset_sql()
• line 1802 of course/lib.php: call to get_courses_wmanagers()
• line 296 of course/category.php: call to print_courses()

1) In print_courses() the contents of $category are:
stdClass Object
(
[id] => 391
[name] => Forum and Chat
[description] =>
[parent] => 381
[sortorder] => 0
[coursecount] => 1
[visible] => 1
[timemodified] => 0
[depth] => 3
[path] => /371/381/391
[theme] =>
)

2) In get_courses_wmanagers(), in the main course loop around ~630 of datalib.php only one course is found (in keeping with course count above), and of the course object is :

stdClass Object
(
[category] => 391
[sortorder] => 29289
[shortname] => F+C0
[fullname] => Forum and Chat
[idnumber] =>
[teacher] => Teacher
[teachers] => Teachers
[student] => Student
[students] => Students
[guest] => 0
[startdate] => 0
[visible] => 1
[newsitems] => 1
[cost] =>
[enrol] =>
[groupmode] => 0
[groupmodeforce] => 0
[password] =>
[summary] => Forum and chat course for twilight training sessions
[currency] => USD
[id] => 2341
[context] => stdClass Object
(
[id] => 5111
[path] => /5111
[depth] => 5
[contextlevel] => 50
[instanceid] => 2341
)

[managers] => Array
(
)
)

3) That context path looks suspicious - and it is stripped to nothing on 634 causing the sql error.

I'm off to sleep now, but will look more at this tommorow.

did you set $CFG->coursemanager?

It was set during the upgrade settings stage of the upgrade (I guess - its set now and has the same error)

Could you please look into database directly - table context, id 5111 and anything with instanceid 2341, thanks

mysql> select * from context where id = 5111;
--------------------------------------

--------------------------------------

--------------------------------------
1 row in set (0.00 sec)

mysql> select * from context where instanceid = 2341;
------------------------------------------

------------------------------------------

------------------------------------------
2 rows in set (0.00 sec)

Hi Petr,

I've managed to reproduce the scenario on a simple moodle test site. I'm attaching a mysql dump of it here. Its at 1.8.3+ on upgrade this breaks when you try and go to the Category Test / 1 / 2

(admin user: admin pass: test )

Hopefully this will help get to the bottom of this one..

Thanks for the dump, I will try to reproduce/fix it today

I have also just hit this issue on my site. It occurred when I updated from an older 1.9beta t this one.

Only sub-sub categories are effected. Exactly the same symptoms as Dan has reported.

Any ideas on a cause yet?

I have attached a screenshot illustrating issue.

In my efforts to solve I recreated the sub categories. When new sub categories were added, I now had proper editing rights. This suggests to me that something has changed the data in the tables of the existing categories while upgrading. otherwise new sub categories would have still been effected by the issue.

However, when I move the courses from the old damadged sub-categories into the newly created ones, I still cannot enter or edit them.

Something for the hackfest tomorrow, I think!

any news from the hackfest?

We didn't get a chance to look at this in the hackfest unfortunately.

However, I was looking at this on the train back and I think the problem is that:

Course Categories with a depth above 1 don't build their context path properly on upgrade.

When I look in the contexts db table now, only the top level has a path/depth.

It looks like these queries are failing to do as they should:

INSERT INTO mdl_context_temp (id, path, depth) SELECT ctx.id, CONCAT(pctx.path,'/',ctx.id), 2+1 FROM mdl_context ctx JOIN mdl_course_categories c ON ctx.instanceid=c.id JOIN mdl_context pctx ON c.parent=pctx.instanceid WHERE ctx.contextlevel=40 AND pctx.contextlevel=40 AND c.depth=2 AND NOT EXISTS (SELECT 'x' FROM mdl_context_temp temp WHERE temp.id = ctx.id)

Needs more investigation..

I don't know much about how the context table works. I have a live site that has been down for a week.

So If I manually alter the mdl_context table, what is it I should be doing to get these courses back online?

JR

Hi guys,

I think the problem is

execute_sql($updatesql, $feedback);
execute_sql($udelsql, $feedback);

need to be inside the for loop otherwise there is nothing to pull for subsequent loops from the context table as it has not been updated with path properly. Please test/resolve,

Cheers,

Yu

That looks good to me, testing some more..

Julian -

I just did another upgrade test and this command in the database is letting me in. I do not think it is the proper fix, but it's working:

update `mdl_course_categories` set `sortorder` = 999 WHERE `depth` >=2

Possibly you could make a database backup, try this and report back?

confirming the patch, works fine for me - thanks!!

please reopen in case of any problems

I just grabbed the latest from CVS and also ran the sql command Matt suggested and still have the issue.

Hi Julian, I am afraid it's a bit more complicated than that to fix your problem. You can try the following, but please make a backup of your database first.

1) Under command line in the main directory run php -r 'include("./config.php");build_context_path(true);'
2) Pray

Cheers and good luck

Yu

Thanks Yu, I also spoke to Petr who pointed me in the same direction before I read your comment. Prayers answered, site restored. Great work guys.

JR

Fixed now, thanks!