Course completion report - ERROR: Can't find data record in database table course.

Course completion report - ERROR: Can't find data record in database table course.

by Peter G -
Number of replies: 24

Hello,

I've abruptly started getting the following error when viewing the completion report for my course. I've tried resetting the completion criteria, and also backing up and restoring. Any advice is greatly appreciated!


Can't find data record in database table course.

More information about this error

---

Debug info: SELECT id,category FROM {course} WHERE id IS NULL
[array (
)]
Error code: invalidrecord

---

Stack trace:

  • line 1546 of /lib/dml/moodle_database.php: dml_missing_record_exception thrown
  • line 1522 of /lib/dml/moodle_database.php: call to moodle_database->get_record_select()
  • line 6818 of /lib/accesslib.php: call to moodle_database->get_record()
  • line 138 of /completion/criteria/completion_criteria_course.php: call to context_course::instance()
  • line 409 of /report/completion/index.php: call to completion_criteria_course->get_title_detailed()

Average of ratings: Useful (1)
In reply to Peter G

Re: Course completion report - ERROR: Can't find data record in database table course.

by James Noronha -

I've just encountered this exact problem...did you ever find an answer for it?

Average of ratings: -
In reply to James Noronha

Re: Course completion report - ERROR: Can't find data record in database table course.

by Peter G -

Sadly, no.

Average of ratings: -
In reply to James Noronha

Re: Course completion report - ERROR: Can't find data record in database table course.

by Howard Miller -
Picture of Core developers Picture of Documentation writers Picture of Particularly helpful Moodlers Picture of Peer reviewers Picture of Plugin developers
What version of Moodle? There is exact and then there is exact... can we get your error trace too please (just to be sure). Once I know the version, I'll have a look and see what the code is trying to do.
Average of ratings: -
In reply to Howard Miller

Re: Course completion report - ERROR: Can't find data record in database table course.

by James Noronha -
Hi there thanks for the reply!

Here's what I did immediately before the problem and the error trace. I'm using the latest version of Moodle 3.7.1 (Build 20190708)

I only have this problem on the completion report for a single course. (This course was prepared from scratch as a new course over the past week). The completion report was working perfectly with data contained from about 8 people who had tested the course. We realized there was an issue with one of the completion requirements and had to change them. In doing so we had to unlock and clear the completion data in order to change the completion setting. We did that... but when I went to look at the new completion report (after running the cron to recreate the completion data) we hit the error that displays the following:

Notice: Trying to get property 'id' of non-object in /home/forge/solearn.ca/completion/criteria/completion_criteria_course.php on line 138
Can't find data record in database table course.

More information about this error

×Debug info: SELECT id,category FROM {course} WHERE id IS NULL
[array (
)]
Error code: invalidrecord
×Stack trace:
line 1546 of /lib/dml/moodle_database.php: dml_missing_record_exception thrown
line 1522 of /lib/dml/moodle_database.php: call to moodle_database->get_record_select()
line 6818 of /lib/accesslib.php: call to moodle_database->get_record()
line 138 of /completion/criteria/completion_criteria_course.php: call to context_course::instance()
line 409 of /report/completion/index.php: call to completion_criteria_course->get_title_detailed()

As you can see the stack trace is exactly what the OP experienced in his error as well. I've tried the following things to rectify the situation:

We've tested other courses and they are all showing the completion report as normal... the activity report renders perfectly as well so we can see that activities are completing. Near as I can tell is that unlocking the completion data removed a record that the report is looking for. But we can't figure out how to recreate that record.

1. Running the cron over and over, and then specifically running the tasks that deal with cleanup... all of the cron jobs are completing succesfully in the log...

2. Tried turning off completion (both activity completion and the course completion settings) and then reenabling them in the hopes that whatever record its looking for would recreate and it did not do so.

3. Tried duplicating the course by backing it up and restoring it as a new course. The new course encounters the same error.

That's all I have at the moment. Any help would be monumentally appreciated.

Cheers,

James
Average of ratings: -
In reply to James Noronha

Re: Course completion report - ERROR: Can't find data record in database table course.

by Henrik John -

Hi,

we are experiencing the exact same problem caused by unlocking the completion requirements for a course. Have you found a solution to this problem?

Cheers,

Henrik

Average of ratings: -
In reply to Henrik John

Re: Course completion report - ERROR: Can't find data record in database table course.

by Guillaume Kruger -

We have exactly the same issue after unlocking the completion requirements for a course. Any ideas out there?

Thanks

Guillaume

Average of ratings: -
In reply to James Noronha

Re: Course completion report - ERROR: Can't find data record in database table course.

by Gigi Sun -
I was able to solve the problem by Unlocking the Course Completion in the course completion settings in the course and changed the conditions. The report worked after that, although the course completion dates are all changed due to unlocking it.
Average of ratings: -
In reply to Gigi Sun

Re: Course completion report - ERROR: Can't find data record in database table course.

by Guillaume Kruger -
Thanks for the tip, unfortunately this did not work for us.  Anybody with any other ideas we can try?
Average of ratings: -
In reply to Guillaume Kruger

Re: Course completion report - ERROR: Can't find data record in database table course.

by Riaan Steenberg -

Hi there,

We are running the lastest version of moodle 3.7.1 or 2 

It has something to do with the unlocking of course completion criteria. I found that when I went into a course that has been dormant and when we unlocked the criteria for completion, all of a sudden it had an empty dropdown in the "complete related courses". I thought nothing of it as it remained empty.

We started getting seemingly unrelated issues on the completion reports and I started looking for a solution and found this thread. 

After cleaning up the database and attempting to find something that is not referenced properly I started to think that something must be wrong in the database. 

After looking around I found some records in the database that had null modules. 

You can identify them with

SELECT * FROM prefix_course_completion_criteria where module is null; 

By deleting these null records I was able to access the Course Completion reports that I was unable to access before. I also found that this was somehow linked to criteriatype 8 or criteria type 7 in the database. 

It may be unrelated but I also found a lot of duplicated values in prefix_course_completions. I literally had hundreds of duplicated values for the same courses that was affected by the predix_course_completion_criteria. 

I also found this tracker from Moodle 2.4.1 that raised the issue but that had an issue with being unable to reproduce the problem. 

https://tracker.moodle.org › browse › MDL-40081

I think we need to look for a way to reproduce the problem. 

Best wishes,

Riaan
Average of ratings: Useful (2)
In reply to Riaan Steenberg

Re: Course completion report - ERROR: Can't find data record in database table course.

by Peter G -
Riaan, thank you! I've been wrestling with this for months now, and your diagnosis is spot on. In any case, at least for me, deleting the records fixed everything.

I think you're right that the issue is triggered by unlocking course completion criteria. I don't have a very strong understanding of the moodle codebase, however, so it's difficult for me to speculate much beyond that. I will see what I can do to reproduce the error, though, now that I know how it's fixed!

-Peter
Average of ratings: -
In reply to Peter G

Re: Course completion report - ERROR: Can't find data record in database table course.

by Willow Schools -

i tried deleting the course overview block but didn't work for me. Any other ideas

Average of ratings: -
In reply to Willow Schools

Re: Course completion report - ERROR: Can't find data record in database table course.

by Jason Hollowell -
Picture of Particularly helpful Moodlers
Sorry, it's been a while since I dealt with this issue. As I remember, deleting the course overview block fixed everything for me but, at the same time, that didn't really explain the root of the problem. Have you checked your server error logs for odd negative numbered courses (see my discussion with Howard linked here - https://moodle.org/mod/forum/discuss.php?d=401427#p1619415). That was clearly the problem for me but why that query was being generated with a negative course id was never determined.

Regards

Jason
Average of ratings: -
In reply to Peter G

Re: Course completion report - ERROR: Can't find data record in database table course.

by Jason Hollowell -
Picture of Particularly helpful Moodlers

Peter, all,

I am having this same problem (sort of) all of a sudden. I have two users who get the message:

"Can't find data record in database table course"

When they click on the "Dashboard" link. One gets it immediately when clicking and the other gets it when attempting to navigate to the second page of the list of courses in the Dashboard.

I've searched for about 3 hours now and have had no success in fixing this issue. sad

I did notice that one teacher had turned completion tracking on but had not created any activities thereafter. Also, that teacher is different from the two who are experiencing the Dashboard display problem.

I am using Moodle 3.7.2+ and, oddly, when I turn all debugging on, I get no error trace when the error message ("Can't find data record in database table course") is displayed...?

Any help or advice will be greatly appreciated.

Jason

Average of ratings: -
In reply to Jason Hollowell

Re: Course completion report - ERROR: Can't find data record in database table course.

by Peter G -
Hi Jason,

Did you see Riaan Steenberg's post? That's what ultimately fixed it for me.

Partially-empty database rows are getting created somehow, but beyond that I don't have much understanding of what's going on. All I know is that removing them manually ultimately fixed the problem for me.

It's worth checking into if you haven't already.
-Peter
Average of ratings: -
In reply to Peter G

Re: Course completion report - ERROR: Can't find data record in database table course.

by Jason Hollowell -
Picture of Particularly helpful Moodlers
Peter,

Yes, thanks, I tried that but there are no null rows. sad I'm completely lost. No debugging message displayed either so I'm left scratching my head.

Jason
Average of ratings: -
In reply to Jason Hollowell

Re: Course completion report - ERROR: Can't find data record in database table course.

by James Hamilton -

Hi Jason, I'm having the same issue with this error on the dashboard. Did you ever find a solution?

Average of ratings: -
In reply to James Hamilton

Re: Course completion report - ERROR: Can't find data record in database table course.

by James Hamilton -
An update here, the culprit to my woes on the dashboard was the Calendar. Anywhere the calendar block appeared throws this error, and the calendar itself does not function. I'll make a new topic for my issue, feel it's unrelated here.
Average of ratings: Useful (1)
In reply to James Hamilton

Re: Course completion report - ERROR: Can't find data record in database table course.

by Jason Hollowell -
Picture of Particularly helpful Moodlers
James,

In my case, the course_overview block seemed to have been the culprit. After removing that block from my site, everything was fine. The root cause, however, was never determined. My interaction with Howard Miller regarding the issue, can be found here: https://moodle.org/mod/forum/discuss.php?d=401427#p1619415
Regards
Jason
Average of ratings: Useful (1)
In reply to Peter G

Re: Course completion report - ERROR: Can't find data record in database table course.

by Jaswant Tak -
Dear All,

Actually there is a bug in Moodle. May be Moodle will fix it in new release. But if you want you can change the following code to fix this issue.

File: /completion/criteria/completion_criteria_course.php
Line: 137
Original code: $prereq = $DB->get_record('course', array('id' => $this->courseinstance));
Change to: $prereq = $DB->get_record('course', array('id' => $this->course));

File: /report/completion/index.php
Line: 467
Original code: $crs = $DB->get_record('course', array('id' => $criterion->courseinstance));
Change to: $crs = $DB->get_record('course', array('id' => $criterion->course));

Cheers
Jaswant
Average of ratings: -
In reply to Jaswant Tak

Re: Course completion report - ERROR: Can't find data record in database table course.

by Leon Stringer -
Picture of Core developers Picture of Particularly helpful Moodlers

I think this is wrong, there is no bug in this source code. courseinstance is correct as this will not be NULL if the Completion of other courses criteria is set, which is where criteriatype = 8 (COMPLETION_CRITERIA_TYPE_COURSE).

Changing the code to $this->course/$criterion->course will instead report on the wrong value: the current course not the course in the criterion.

If courseinstance were NULL when criteriatype = 8 then there is a problem with the data which could indicate a Moodle bug.

Average of ratings: -
In reply to Leon Stringer

Re: Course completion report - ERROR: Can't find data record in database table course.

by Lexy Walker -
Picture of Core developers Picture of Particularly helpful Moodlers
The was a bug in Moodle where if you made Course A's completion dependent on Course B, then subsequently deleted Course B, the completion criteria page for Course A would throw errors and refuse to load and insert NULL rows into the database. It was MDL-68005 which I reported and you commented on. I believe it has now been fixed in 3.7.6.
Average of ratings: -
In reply to Lexy Walker

Re: Course completion report - ERROR: Can't find data record in database table course.

by Leon Stringer -
Picture of Core developers Picture of Particularly helpful Moodlers

Hi Lexy,

I was trying to remember that issue so I could check before I posted my comment but couldn't find it!

But I should have been clearer, my comment was regarding Jaswant's reply only becaue it led to some confusion here. I was trying to clarify that the reply did not describe a problem in Moodle.

There may have been – and indeed was – a bug in Moodle but not in the code shown. The proposed $this->course/$criterion->course change may have stopped the error for MDL-68005, etc. but by using a wrong-but-valid course ID not by actually fixing the bug.

Average of ratings: -
In reply to Leon Stringer

Re: Course completion report - ERROR: Can't find data record in database table course.

by Lexy Walker -
Picture of Core developers Picture of Particularly helpful Moodlers
Yeah, you're right. This proposed edit definitely isn't the solution to the problem (the problem which has already been fixed).

I was just commenting on the original problem further up this thread because I read it and thought "I remember Howard making me reproduce that and submit a bug report"
Average of ratings: -
In reply to Lexy Walker

Re: Course completion report - ERROR: Can't find data record in database table course.

by Howard Miller -
Picture of Core developers Picture of Documentation writers Picture of Particularly helpful Moodlers Picture of Peer reviewers Picture of Plugin developers
I did smile

AND... if anybody above is still running 3.7.anything then they should not be. The 3.7 branch went out of support at the end of last year.
Average of ratings: -