Moodle
  1. Moodle
  2. MDL-25594

completion_criteria_activity cron fails with Oracle ORA-00923

    Details

    • Database:
      Oracle
    • Testing Instructions:
      Hide

      Previous behaviour:

      • Running cron on Oracle with completion enabled site-wide would crash while running the course completion criteria database queries

      New behaviour:

      • Running cron on Oracle with completion enabled site-wide succeeds
      • Criteria work as expected: e.g.
        • enabe completion for a course
        • add a course grade criteria
        • when a user in a tracked role achieves this grade they should be marked complete in the course after the next cron run (although on small sites this may take multiple cron runs to complete due to a timestamp issue).
      Show
      Previous behaviour: Running cron on Oracle with completion enabled site-wide would crash while running the course completion criteria database queries New behaviour: Running cron on Oracle with completion enabled site-wide succeeds Criteria work as expected: e.g. enabe completion for a course add a course grade criteria when a user in a tracked role achieves this grade they should be marked complete in the course after the next cron run (although on small sites this may take multiple cron runs to complete due to a timestamp issue).
    • Affected Branches:
      MOODLE_20_STABLE
    • Fixed Branches:
      MOODLE_20_STABLE
    • Pull Master Branch:
      MDL-25594-HEAD
    • Rank:
      15094

      Description

      The problem appears to be due to the use of an Oracle keyword as the column alias. The below query fails in sqlplus, also, unless I change the "cr.timeend AS date," to "cr.timeend AS mydate,".

      Here is the cron output:

      Running completion_criteria_activity->cron()
      !!! Error reading from database !!!
      !! ORA-00923: FROM keyword not found where expected

      SELECT DISTINCT
      c.id AS course,
      cr.timeend AS date,
      cr.id AS criteriaid,
      ra.userid AS userid,
      mc.timemodified AS timecompleted
      FROM
      m_course_completion_criteria cr
      INNER JOIN
      m_course c
      ON cr.course = c.id
      INNER JOIN
      m_context con
      ON con.instanceid = c.id
      INNER JOIN
      m_role_assignments ra
      ON ra.contextid = con.id
      INNER JOIN
      m_course_modules_completion mc
      ON mc.coursemoduleid = cr.moduleinstance
      AND mc.userid = ra.userid
      LEFT JOIN
      m_course_completion_crit_compl cc
      ON cc.criteriaid = cr.id
      AND cc.userid = ra.userid
      WHERE
      cr.criteriatype = 4
      AND con.contextlevel = 50
      AND c.enablecompletion = 1
      AND cc.id IS NULL
      AND (
      mc.completionstate = 1
      OR mc.completionstate = 2
      )

      [array (
      )] !!
      !! Stack trace: * line 391 of /lib/dml/moodle_database.php: dml_read_exception thrown

      • line 268 of /lib/dml/oci_native_moodle_database.php: call to moodle_database->query_end()
      • line 1010 of /lib/dml/oci_native_moodle_database.php: call to oci_native_moodle_database->query_end()
      • line 231 of /lib/completion/completion_criteria_activity.php: call to oci_native_moodle_database->get_recordset_sql()
      • line 220 of /lib/completion/cron.php: call to completion_criteria_activity->cron()
      • line 43 of /lib/completion/cron.php: call to completion_cron_criteria()
      • line 198 of /lib/cronlib.php: call to completion_cron()
      • line 79 of /admin/cron.php: call to cron_run()
        !!

        Activity

        Hide
        Eloy Lafuente (stronk7) added a comment -

        Sorry, rejecting this integration request because:

        1) It's missing testing instructions.
        2) It's incomplete because there are at least 3 "AS date" occurrences @ /lib/completion
        3) Both 20_STABLE and master fixes must be provided.

        Note the change applied seems correct but incomplete because of 1, 2, 3 above. Should be easy to complete, it's completionlib, lol!

        Thanks and ciao

        Show
        Eloy Lafuente (stronk7) added a comment - Sorry, rejecting this integration request because: 1) It's missing testing instructions. 2) It's incomplete because there are at least 3 "AS date" occurrences @ /lib/completion 3) Both 20_STABLE and master fixes must be provided. Note the change applied seems correct but incomplete because of 1, 2, 3 above. Should be easy to complete, it's completionlib, lol! Thanks and ciao
        Hide
        Eloy Lafuente (stronk7) added a comment -

        This has been integrated, thanks!

        Show
        Eloy Lafuente (stronk7) added a comment - This has been integrated, thanks!
        Hide
        Eloy Lafuente (stronk7) added a comment -

        Passing tests on Oracle, by setting some activity criteria as specified in the testing instructions.

        Ciao

        Show
        Eloy Lafuente (stronk7) added a comment - Passing tests on Oracle, by setting some activity criteria as specified in the testing instructions. Ciao
        Hide
        Eloy Lafuente (stronk7) added a comment -

        Also tested with grade criteria, works ok.

        NOTE: while looking to the completion report I noticed that the "grades" icon is not shown properly. It seems to link to:

        /theme/image.php?theme=standard&image=i%2Fgrade&rev=162

        Surely that needs a look.

        Ciao

        Show
        Eloy Lafuente (stronk7) added a comment - Also tested with grade criteria, works ok. NOTE: while looking to the completion report I noticed that the "grades" icon is not shown properly. It seems to link to: /theme/image.php?theme=standard&image=i%2Fgrade&rev=162 Surely that needs a look. Ciao
        Hide
        Eloy Lafuente (stronk7) added a comment -

        Closing as fixed. Many thanks!

        Show
        Eloy Lafuente (stronk7) added a comment - Closing as fixed. Many thanks!

          People

          • Votes:
            0 Vote for this issue
            Watchers:
            2 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved: