Moodle
  1. Moodle
  2. MDL-30514

Register Moodle fails with "Error reading from database (backport of MDL-34072, MDL-34440)

    Details

    • Database:
      Oracle
    • Testing Instructions:
      Hide

      0. This fix is only for 22_STABLE. DEBUG_DEVELOPER and display errors recommended.

      1. For each DB flavor (mysql, postgresql, mssql and oracle)...
      1.1. Install one site.
      1.2. Proceed with registration.
      1.2. TEST: No error happen and the registration ends ok.
      2. Yay!

      Show
      0. This fix is only for 22_STABLE. DEBUG_DEVELOPER and display errors recommended. 1. For each DB flavor (mysql, postgresql, mssql and oracle)... 1.1. Install one site. 1.2. Proceed with registration. 1.2. TEST: No error happen and the registration ends ok. 2. Yay!
    • Affected Branches:
      MOODLE_20_STABLE, MOODLE_22_STABLE
    • Fixed Branches:
      MOODLE_22_STABLE
    • Pull from Repository:
    • Rank:
      33209

      Description

      Clicking on the Register Site with Mooches button under the registration tab generates a "Error reading from Database" message.

      Apache Error log shows this:

      Default exception handler: Error reading from database Debug: ORA-00933: SQL command not properly ended
      SELECT COUNT(*) FROM (
             SELECT DISTINCT ue.userid, e.courseid
             FROM m_user_enrolments ue, m_enrol e, m_course c
             WHERE ue.enrolid = e.id
                 AND e.courseid <> :o_siteid
                 AND c.id = e.courseid
                 AND c.visible = 1) as total
      [array (
        'o_siteid' => '1',
      )]
      * 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 1093 of /lib/dml/oci_native_moodle_database.php: call to oci_native_moodle_database->query_end()
      * line 1270 of /lib/dml/moodle_database.php: call to oci_native_moodle_database->get_records_sql()
      * line 1029 of /lib/dml/oci_native_moodle_database.php: call to moodle_database->get_record_sql()
      * line 1345 of /lib/dml/moodle_database.php: call to oci_native_moodle_database->get_record_sql()
      * line 1516 of /lib/dml/moodle_database.php: call to moodle_database->get_field_sql()
      * line 3838 of /course/lib.php: call to moodle_database->count_records_sql()
      * line 371 of /admin/registration/forms.php: call to average_number_of_participants()
      * line 153 of /lib/formslib.php: call to site_registration_form->definition()
      * line 59 of /admin/registration/register.php: call to moodleform->moodleform(), referer: https://moodle.umass.edu/admin/registration/index.php
      

        Issue Links

          Activity

          Hide
          Michael de Raadt added a comment -

          Thanks for reporting this. I was not able to replicate the problem, but I'm not using Oracle.

          I've reformatted the error report to make it more readable. It looks to be the same code that still exists in master.

          I can't see how the query is being interpreted as incomplete. Perhaps Eloy, our DB Guru, can spot something there.

          Show
          Michael de Raadt added a comment - Thanks for reporting this. I was not able to replicate the problem, but I'm not using Oracle. I've reformatted the error report to make it more readable. It looks to be the same code that still exists in master. I can't see how the query is being interpreted as incomplete. Perhaps Eloy, our DB Guru, can spot something there.
          Hide
          Sara Arjona added a comment - - edited

          We found the same problem (because we also use Oracle). To fix it, it's necessary to move de "AS TOTAL" in the function average_number_of_participants of the course/lib.php file after the count( * ). So the correct SQL must be:

          $sql = 'SELECT COUNT( * ) AS total FROM (
          SELECT DISTINCT ue.userid, e.courseid
          FROM

          {user_enrolments}

          ue,

          {enrol}

          e,

          {course}

          c
          WHERE ue.enrolid = e.id
          AND e.courseid <> :siteid
          AND c.id = e.courseid
          AND c.visible = 1)';

          The same SQL problem can be found also in the average_number_of_courses_modules of the same file (course.lib.php).

          Show
          Sara Arjona added a comment - - edited We found the same problem (because we also use Oracle). To fix it, it's necessary to move de "AS TOTAL" in the function average_number_of_participants of the course/lib.php file after the count( * ). So the correct SQL must be: $sql = 'SELECT COUNT( * ) AS total FROM ( SELECT DISTINCT ue.userid, e.courseid FROM {user_enrolments} ue, {enrol} e, {course} c WHERE ue.enrolid = e.id AND e.courseid <> :siteid AND c.id = e.courseid AND c.visible = 1)'; The same SQL problem can be found also in the average_number_of_courses_modules of the same file (course.lib.php).
          Hide
          Iñigo Zendegi added a comment -

          We also use Oracle and we've found this problem in 2.1.6 and 2.2.3 versions too.

          We've tried the patch proposed by Sara and worked fine.

          Show
          Iñigo Zendegi added a comment - We also use Oracle and we've found this problem in 2.1.6 and 2.2.3 versions too. We've tried the patch proposed by Sara and worked fine.
          Hide
          Sara Arjona added a comment -

          The problem is solved, at least, in Moodle 2.3.2 version.
          Thank you!

          Show
          Sara Arjona added a comment - The problem is solved, at least, in Moodle 2.3.2 version. Thank you!
          Hide
          Eloy Lafuente (stronk7) added a comment -

          Yeah, with MDL-34072 and MDL-34440 Moodle 2.3 and upwards have this fixed for all DBs.

          I'm going to backport the resulting solution to 2.2.x too (somehow it was not done in while fixing those issues).

          BTW the correct, cross-db solutions is to take rid of the "AS" keyword, so the query will end with:

              ....
              ....
              AND c.id = e.courseid
              AND c.visible = 1) total';
          

          (following point #12 of http://docs.moodle.org/dev/Database)

          Ciao

          Show
          Eloy Lafuente (stronk7) added a comment - Yeah, with MDL-34072 and MDL-34440 Moodle 2.3 and upwards have this fixed for all DBs. I'm going to backport the resulting solution to 2.2.x too (somehow it was not done in while fixing those issues). BTW the correct, cross-db solutions is to take rid of the "AS" keyword, so the query will end with: .... .... AND c.id = e.courseid AND c.visible = 1) total'; (following point #12 of http://docs.moodle.org/dev/Database ) Ciao
          Hide
          Dan Poltawski added a comment -

          Thanks Eloy, i've integrated this to 22_STABLE.

          (No idea how we all managed to miss this one)

          Show
          Dan Poltawski added a comment - Thanks Eloy, i've integrated this to 22_STABLE. (No idea how we all managed to miss this one)
          Hide
          Michael de Raadt added a comment -

          Test result: Success!

          Tested in Moodle 2.2 with Oracle, PostgreSQL, MySQL and MS SQL.

          Show
          Michael de Raadt added a comment - Test result: Success! Tested in Moodle 2.2 with Oracle, PostgreSQL, MySQL and MS SQL.
          Hide
          Eloy Lafuente (stronk7) added a comment -

          Closing as fixed, many thanks for your awesome collaboration.

          Show
          Eloy Lafuente (stronk7) added a comment - Closing as fixed, many thanks for your awesome collaboration.

            People

            • Votes:
              4 Vote for this issue
              Watchers:
              4 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: