SCORM

 
 
Picture of Mia M
SCORM report error
 

My SCORM is running fine and reports completions as it is supposed to for students, as far as the student checkbox that should be checked when the activity is complete. However, when the authorized manager user goes to look at the report for the SCORM activity, we get a database read error as follows. Anyone know why? This was working fine and then stopped working, and no moodle updates have been done to our server since this started. Running Moodle 2.4.1+ (Build: 20130125).

====

Debug info: ORA-00918: column ambiguously defined SELECT * FROM (SELECT DISTINCT u.id || '#' || COALESCE(st.attempt, 0) AS uniqueid, st.scormid AS scormid, st.attempt AS attempt, u.id AS userid, u.idnumber, u.firstname, u.lastname, u.picture, u.imagealt, u.email, u.email, u.institution FROM m_user u LEFT JOIN m_scorm_scoes_track st ON st.userid = u.id AND st.scormid = 142 WHERE u.id IN (:o_param16,:o_param17,:o_param18,:o_param19,:o_param20,:o_param21) AND (st.userid IS NOT NULL OR st.userid IS NULL) ORDER BY uniqueid) WHERE rownum <= :o_oracle_num_rows [array ( 'o_param16' => 84, 'o_param17' => 165, 'o_param18' => 169, 'o_param19' => 103, 'o_param20' => 101, 'o_param21' => 6, 'o_oracle_num_rows' => 20, )] 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 345 of /mod/scorm/report/basic/report.php: call to oci_native_moodle_database->get_records_sql()
  • line 85 of /mod/scorm/report.php: call to scorm_basic_report->display()

=======

 
Average of ratings: -
Picture of Dan Marsden
Re: SCORM report error
Group DevelopersGroup Moodle Course Creator Certificate holdersGroup Particularly helpful MoodlersGroup Translators

looks like MDL-37449 - I don't have Oracle running but if you have an internal developer that could look and suggest a patch to fix it on the bug that would help.

 
Average of ratings: -
Walking on the snow towards Lago Nero...
Re: SCORM report error
Group DevelopersGroup Particularly helpful Moodlers

Hi Mia,
the issue seems related to a doubled u.email in the SQL query: could you jump into the Moodle Tracker and try my patch proposal?
There you'll find more support to solve the issue.

SELECT *
FROM (
  SELECT
    DISTINCT u.id || '#' || COALESCE(st.attempt, 0) AS uniqueid,
    st.scormid AS scormid,
    st.attempt AS attempt,
    u.id AS userid,
    u.idnumber,
    u.firstname,
    u.lastname,
    u.picture,
    u.imagealt,
    u.email,
    u.email
  FROM
    m_user u
  LEFT JOIN
    m_scorm_scoes_track st
      ON
        st.userid = u.id
        AND st.scormid = 1
  WHERE
    u.id IN (:o_param14,:o_param15,:o_param16,:o_param17)
    AND (st.userid IS NOT NULL OR st.userid IS NULL)
  ORDER BY uniqueid
)
WHERE rownum <= :o_oracle_num_rows
[array (
'o_param14' => 23,
'o_param15' => 22,
'o_param16' => 25,
'o_param17' => 24,
'o_oracle_num_rows' => 20,
)]

HTH,
Matteo

 
Average of ratings: -
Picture of Mia M
Re: SCORM report error
 

Thanks, Matteo - Is it just a matter of removing the double u.email reference in the code? If so, I can try that, but if it's more than that, I'm not sure I can try out this patch myself, as I'm not a developer. We do have a developer who can look at it but not this week. Can you give me more detailed instructions?

 
Average of ratings: -
Walking on the snow towards Lago Nero...
Re: SCORM report error
Group DevelopersGroup Particularly helpful Moodlers

Hi Mia,
you should jump to MDL-37449: there I'll try to propose a patch, spare time permitted.

BTW, find below my first rough proposal (it requires tests at my side) to remove the doubled u.email:

$ git diff
diff --git a/mod/scorm/report/basic/report.php b/mod/scorm/report/basic/report.php
index 86dbbff..6a1efcb 100644
--- a/mod/scorm/report/basic/report.php
+++ b/mod/scorm/report/basic/report.php
@@ -270,8 +270,8 @@ class scorm_basic_report extends scorm_default_report {
                             // Construct the SQL
             $select = 'SELECT DISTINCT '.$DB->sql_concat('u.id', '\'#\'', 'COALESCE(st.attempt, 0)').' AS uniqueid, ';
             $select .= 'st.scormid AS scormid, st.attempt AS attempt, ' .
-                    'u.id AS userid, u.idnumber, u.firstname, u.lastname, u.picture, u.imagealt, u.email' .
-                    get_extra_user_fields_sql($coursecontext, 'u', '', array('idnumber')) . ' ';
+                    'u.id AS userid, u.idnumber, u.firstname, u.lastname, u.picture, u.imagealt' .
+                    get_extra_user_fields_sql($coursecontext, 'u', '', array('email', 'idnumber')) . ' ';

             // This part is the same for all cases - join users and scorm_scoes_track tables
             $from = 'FROM {user} u ';

You need to backup that file (to go back in case of errors) and modify it to remove the red parts, adding the green ones.

HTH,
Matteo

 
Average of ratings: -
Walking on the snow towards Lago Nero...
Re: SCORM report error
Group DevelopersGroup Particularly helpful Moodlers

Hi Mia,
I've just tested it on my dev LAMP and my first proposal was wrong (never propose a fix before successfully testing it wink): it deletes u.email which is not correct, I'm working on this patch proposal right now:

$ git diff
diff --git a/mod/scorm/report/basic/report.php b/mod/scorm/report/basic/report.php
index 86dbbff..aebc178 100644
--- a/mod/scorm/report/basic/report.php
+++ b/mod/scorm/report/basic/report.php
@@ -34,6 +34,8 @@ class scorm_basic_report extends scorm_default_report {
      */
     function display($scorm, $cm, $course, $download) {
         global $CFG, $DB, $OUTPUT, $PAGE;
+
+$DB->set_debug(true);
         $contextmodule= context_module::instance($cm->id);
         $action = optional_param('action', '', PARAM_ALPHA);
         $attemptids = optional_param_array('attemptid', array(), PARAM_RAW);
@@ -271,7 +273,7 @@ class scorm_basic_report extends scorm_default_report {
             $select = 'SELECT DISTINCT '.$DB->sql_concat('u.id', '\'#\'', 'COALESCE(st.attempt, 0)').' AS uniqueid, ';
             $select .= 'st.scormid AS scormid, st.attempt AS attempt, ' .
                     'u.id AS userid, u.idnumber, u.firstname, u.lastname, u.picture, u.imagealt, u.email' .
-                    get_extra_user_fields_sql($coursecontext, 'u', '', array('idnumber')) . ' ';
+                    get_extra_user_fields_sql($coursecontext, 'u', '', array('email', 'idnumber')) . ' ';

             // This part is the same for all cases - join users and scorm_scoes_track tables
             $from = 'FROM {user} u ';
diff --git a/mod/scorm/report/interactions/report.php b/mod/scorm/report/interactions/report.php
index 167be51..5ae9076 100644
--- a/mod/scorm/report/interactions/report.php
+++ b/mod/scorm/report/interactions/report.php
@@ -35,6 +35,8 @@ class scorm_interactions_report extends scorm_default_report {
      */
     function display($scorm, $cm, $course, $download) {
         global $CFG, $DB, $OUTPUT, $PAGE;
+
+$DB->set_debug(true);
         $contextmodule = context_module::instance($cm->id);
         $action = optional_param('action', '', PARAM_ALPHA);
         $attemptids = optional_param_array('attemptid', array(), PARAM_RAW);
@@ -159,7 +161,7 @@ class scorm_interactions_report extends scorm_default_report {
             $select = 'SELECT DISTINCT '.$DB->sql_concat('u.id', '\'#\'', 'COALESCE(st.attempt, 0)').' AS uniqueid, ';
             $select .= 'st.scormid AS scormid, st.attempt AS attempt, ' .
                     'u.id AS userid, u.idnumber, u.firstname, u.lastname, u.picture, u.imagealt, u.email'.
-                    get_extra_user_fields_sql($coursecontext, 'u', '', array('idnumber')) . ' ';
+                    get_extra_user_fields_sql($coursecontext, 'u', '', array('email', 'idnumber')) . ' ';

             // This part is the same for all cases - join users and scorm_scoes_track tables
             $from = 'FROM {user} u ';

The $DB->set_debug(true); is used for debugging purposes: it will write all the queries directly in the page to double check them and see if Oracle is happy with them.

HTH,
Matteo

 
Average of ratings: -
Picture of Mia M
Re: SCORM report error
 

OK, we made the change regarding the duplicate email reference and it works. Our developer says it had to do w/ email also being in “Coursecontext”, so the field was doubling up. Does that help? in any case i works for us now so thanks!

 
Average of ratings: -
Walking on the snow towards Lago Nero...
Re: SCORM report error
Group DevelopersGroup Particularly helpful Moodlers

Hi Mia,
could you ask your developer to drop some lines into MDL-37449 about my fix proposal? $coursecontext is already "involved" in my patch and I'm in trouble in understanding her/his suggestion.

Thanks in advance,
Matteo

 
Average of ratings: -
Picture of Andrea Bicciolo
Re: SCORM report error
Group DevelopersGroup Particularly helpful MoodlersGroup TestersGroup Translators

Hi,

we was working on the same issue just during those days and we landed on the same solution. We'll add comments on MDL-37449 to follow up.

 
Average of ratings: -