Moodle
  1. Moodle
  2. MDL-25522

get_potential_users fails with ORA-00918 with Oracle

    Details

    • Type: Bug Bug
    • Status: Closed
    • Priority: Major Major
    • Resolution: Fixed
    • Affects Version/s: 2.0
    • Fix Version/s: 2.0.1
    • Component/s: Enrolments
    • Labels:
      None
    • Environment:
      Oracle DB, Linux application server
    • Database:
      Oracle
    • Affected Branches:
      MOODLE_20_STABLE
    • Fixed Branches:
      MOODLE_20_STABLE
    • Rank:
      1111

      Description

      We are attempting a migration to Oracle (for "enterprise" reasons) and are hitting a hard stop when we try to add users to a course. Clicking the "Enrol users" button eventually invokes course_enrolment_manager->get_potential_users(). For Oracle, get_limit_sql in lib/dml/oci_native_moodle_database.php wraps the SQL call in order to limit the size of the result set. The resulting SQL looks like this:

      SELECT * FROM (
      SELECT u.id, u.firstname, u.lastname, u.username, u.email, u.lastaccess, u.picture, u.imagealt,
      u.id,u.picture,u.firstname,u.lastname,u.imagealt,u.email
      FROM m_user u
      WHERE id <> 1 AND u.deleted = 0 AND u.confirmed = 1 AND u.id NOT IN (
      SELECT ue.userid
      FROM m_user_enrolments ue
      JOIN m_enrol e ON (e.id = ue.enrolid AND e.id = 21))
      ORDER BY u.lastname ASC, u.firstname ASC)
      WHERE rownum <= 25

      The duplicate field names in the second SELECT results in failure (ORA-00918) in the first SELECT because it can't know which id (for example) is the right one to return. It fails for the same reason that this fails

      SELECT x FROM (SELECT u.id x, 1 as x FROM m_user u);

      and this succeeds

      SELECT x FROM (SELECT u.id x, 1 as x2 FROM m_user u);

      The duplicate field names are introduced by $ufields in this line in get_potential_users:

      $fields = 'SELECT u.id, u.firstname, u.lastname, u.username, u.email, u.lastaccess, u.picture, u.imagealt, '.$ufields;

      This is the full error message from the error log:

      [Thu Dec 02 19:33:00 2010] [error] [client xxx.xxx.xxx.xxx] Default exception handler: Error reading from database Debug: ORA-00918: column ambiguously defined\nSELECT u.id, u.firstname, u.lastname, u.username, u.email, u.lastaccess, u.picture, u.imagealt, u.id,u.picture,u.firstname,u.lastname,u.imagealt,u.email FROM m_user u\n WHERE id <> :guestid AND u.deleted = 0 AND u.confirmed = 1\n AND u.id NOT IN (SELECT ue.userid\n FROM m_user_enrolments ue\n JOIN m_enrol e ON (e.id = ue.enrolid AND e.id = :enrolid)) ORDER BY u.lastname ASC, u.firstname ASC\n[array (\n 'guestid' => '1',\n 'enrolid' => 21,\n 'oracle_num_rows' => 25,\n)]\n* line 391 of /lib/dml/moodle_database.php: dml_read_exception thrown\n* line 268 of /lib/dml/oci_native_moodle_database.php: call to moodle_database->query_end()\n* line 1043 of /lib/dml/oci_native_moodle_database.php: call to oci_native_moodle_database->query_end()\n* line 298 of /enrol/locallib.php: call to oci_native_moodle_database->get_records_sql()\n* line 129 of /enrol/ajax.php: call to course_enrolment_manager->get_potential_users()\n, referer: https://<my.moodle.domain>/enrol/users.php?id=41

        Activity

        Hide
        Aparup Banerjee added a comment -

        ok, i've got this duplicated, fixing....

        Show
        Aparup Banerjee added a comment - ok, i've got this duplicated, fixing....
        Hide
        Aparup Banerjee added a comment -

        Petr, please review this simple change to enrol locallib.
        https://github.com/nebgor/moodle/commit/d612a7f90e86a97585908c80cd05ce22151eb678

        (sorry about the commit msg not having the MDL number but this commit will go via cvs so i put it there)

        Show
        Aparup Banerjee added a comment - Petr, please review this simple change to enrol locallib. https://github.com/nebgor/moodle/commit/d612a7f90e86a97585908c80cd05ce22151eb678 (sorry about the commit msg not having the MDL number but this commit will go via cvs so i put it there)
        Show
        Aparup Banerjee added a comment - fix modified https://github.com/nebgor/moodle/commit/cf06dec3a218fa5372367542763087a7c2fce30e
        Hide
        Aparup Banerjee added a comment -

        thanks for the review Petr, fix committed into cvs head.

        Show
        Aparup Banerjee added a comment - thanks for the review Petr, fix committed into cvs head.
        Hide
        Aparup Banerjee added a comment -

        and thanks for the report Colin Campbell!

        Show
        Aparup Banerjee added a comment - and thanks for the report Colin Campbell!

          People

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

            Dates

            • Created:
              Updated:
              Resolved: