Certificate database error in 2.4 moodle

Certificate database error in 2.4 moodle

by Mia Musolino -
Number of replies: 7

I just upgraded to moodle 2.4. Using Oracle database. Certificates worked fine in previous version but now when student user completes a course and gets certificate, the error message below displays. Can someone help me solve this? Thanks!

====

Error reading from database

More information about this error

Debug info: ORA-00904: "C"."DELETED": invalid identifier SELECT MAX(c.timecompleted) as timecompleted FROM m_course_completions c WHERE c.userid = :o_userid AND c.course = :o_courseid AND c.deleted IS NULL [array ( 'o_userid' => '6', 'o_courseid' => '82', )] Error code: dmlreadexception
Stack trace:
  • line 426 of /lib/dml/moodle_database.php: dml_read_exception thrown
  • line 274 of /lib/dml/oci_native_moodle_database.php: call to moodle_database->query_end()
  • line 1101 of /lib/dml/oci_native_moodle_database.php: call to oci_native_moodle_database->query_end()
  • line 1382 of /lib/dml/moodle_database.php: call to oci_native_moodle_database->get_records_sql()
  • line 1035 of /lib/dml/oci_native_moodle_database.php: call to moodle_database->get_record_sql()
  • line 1131 of /mod/certificate/lib.php: call to oci_native_moodle_database->get_record_sql()
  • line 97 of /mod/certificate/type/letter_non_embedded/certificate.php: call to certificate_get_date()
  • line 90 of /mod/certificate/view.php: call to require()

===

 

Average of ratings: -
In reply to Mia Musolino

Re: Certificate database error in 2.4 moodle

by Matteo Scaramuccia -
Picture of Core developers Picture of Peer reviewers Picture of Plugin developers

Hi Mia,
it's a known issue: read more here. In the mean time, you can temporarly re-add the deleted deleted field into the tables or change the code by your own e.g.:

$ git diff
diff --git a/deprecatedlib.php b/deprecatedlib.php
index 758f751..5905774 100644
--- a/deprecatedlib.php
+++ b/deprecatedlib.php
@@ -46,8 +46,7 @@ function certificate_generate_date($certificate, $course) {
         $sql = "SELECT MAX(c.timecompleted) as timecompleted
                 FROM {course_completions} c
                 WHERE c.userid = :userid
-                AND c.course = :courseid
-                AND c.deleted IS NULL";
+                AND c.course = :courseid";
         if ($timecompleted = $DB->get_record_sql($sql, array('userid' => $userid, 'courseid' => $course->id))) {
             if (!empty($timecompleted->timecompleted)) {
                 $date = $timecompleted->timecompleted;
diff --git a/lib.php b/lib.php
index 55dc2a2..e26e946 100644
--- a/lib.php
+++ b/lib.php
@@ -727,8 +727,7 @@ function certificate_get_issues($certificateid, $sort="ci.timecreated ASC", $gro
                                    FROM {user} u
                                    INNER JOIN {certificate_issues} ci
                                    ON u.id = ci.userid
-                                   WHERE u.deleted = 0
-                                   AND ci.certificateid = :certificateid
+                                   WHERE ci.certificateid = :certificateid
                                    ORDER BY {$sort}", array('certificateid' => $certificateid),
                                    $page * $perpage,
                                    $perpage);
@@ -1139,8 +1138,7 @@ function certificate_get_date($certificate, $certrecord, $course, $userid = null
         $sql = "SELECT MAX(c.timecompleted) as timecompleted
                 FROM {course_completions} c
                 WHERE c.userid = :userid
-                AND c.course = :courseid
-                AND c.deleted IS NULL";
+                AND c.course = :courseid";
         if ($timecompleted = $DB->get_record_sql($sql, array('userid' => $userid, 'courseid' => $course->id))) {
             if (!empty($timecompleted->timecompleted)) {
                 $date = $timecompleted->timecompleted

HTH,
Matteo

Average of ratings: -
In reply to Matteo Scaramuccia

Re: Certificate database error in 2.4 moodle

by Mia Musolino -

I made these changes and can now open certificates fine. Thanks!

However I realize now that I was also getting a different error (maybe even in 2.3) when clicking on the View x Certificates link when the teacher views saved certificates. Here is the error. Have you seen this?

Debug info:

ORA-00918: column ambiguously defined
SELECT *
FROM (SELECT u.*, ci.code, ci.timecreated
FROM m_user u
INNER JOIN m_certificate_issues ci
ON u.id = ci.userid
WHERE ci.certificateid = :o_certificateid
ORDER BY firstname || ' ' || lastname )
WHERE rownum <= :o_oracle_num_rows
[array (
'o_certificateid' => '142',
'o_oracle_num_rows' => 30,
)]
Error code: dmlreadexception

Stack trace:

 

  • line 426 of /lib/dml/moodle_database.php: dml_read_exception thrown

 

  • line 274 of /lib/dml/oci_native_moodle_database.php: call to moodle_database->query_end()

 

  • line 1101 of /lib/dml/oci_native_moodle_database.php: call to oci_native_moodle_database->query_end()

 

  • line 729 of /mod/certificate/lib.php: call to oci_native_moodle_database->get_records_sql()

 

  • line 92 of /mod/certificate/report.php: call to certificate_get_issues()

 

 

Average of ratings: -
In reply to Mia Musolino

Re: Certificate database error in 2.4 moodle

by Matteo Scaramuccia -
Picture of Core developers Picture of Peer reviewers Picture of Plugin developers

Hi Mia,
I'm not able to find references for this Certificate issue into the Tracker, - FYI I do not use Oracle in my instances - but you can give this patch below a try:

diff --git a/report.php b/report.php
index 7d8b07d..5bec81c 100644
--- a/report.php
+++ b/report.php
@@ -89,7 +89,7 @@ if (!$download) {
 add_to_log($course->id, 'certificate', 'view', "report.php?id=$cm->id", '$certificate->id', $cm->id);

 // Ensure there are issues to display, if not display notice
-if (!$users = certificate_get_issues($certificate->id, $DB->sql_fullname(), $groupmode, $cm, $page, $perpage)) {
+if (!$users = certificate_get_issues($certificate->id, $DB->sql_fullname('u.firstname', 'u.lastname'), $groupmode, $cm, $page, $perpage)) {
     echo $OUTPUT->header();
     groups_print_activity_menu($cm, $CFG->wwwroot . '/mod/certificate/report.php?id='.$id);
     notify(get_string('nocertificatesissued', 'certificate'));

I've filed an issue into the Tracker: CONTRIB-4067.

HTH,
Matteo

Average of ratings: -
In reply to Matteo Scaramuccia

Re: Certificate database error in 2.4 moodle

by Matteo Scaramuccia -
Picture of Core developers Picture of Peer reviewers Picture of Plugin developers

Hi Mia,
have you already try my patch proposal?
In the mean time, Mark (Nelson) got in touch with the issue and he is asking for some checks. Could you follow his suggestions there in the Tracker and provide him the requested info?

TIA,
Matteo

Average of ratings: -
In reply to Matteo Scaramuccia

Re: Certificate database error in 2.4 moodle

by Mark Nelson -
Picture of Core developers Picture of Documentation writers Picture of Particularly helpful Moodlers Picture of Peer reviewers Picture of Plugin developers Picture of Testers

Hi Matteo,

Thanks for your help on these forums.

Mia, it appears you have a firstname and lastname column in the certificate_issues table, these should not be present in this table. Do you know if these columns were manually added to your instance?

Regards,

Mark

Average of ratings: -
In reply to Mark Nelson

Re: Certificate database error in 2.4 moodle

by Mia Musolino -

we have not added any columns to the tables, so I don't know about that. Can I just remove that line of the query in the code that has first name and last name, or replace it w/ something else?

 

Average of ratings: -
In reply to Matteo Scaramuccia

Re: Certificate database error in 2.4 moodle

by Mia Musolino -

I did try your patch proposal, Matteo, with no success -- I removed the change after I made it since it seemed to not make a difference. I will now check the tracker to see what other information is needed.

Average of ratings: -