Uploaded image for project: 'Moodle'
  1. Moodle
  2. MDL-21554

ORA-0918 when signing in from Mahara (MNET)

    Details

    • Type: Bug
    • Status: Closed
    • Priority: Blocker
    • Resolution: Fixed
    • Affects Version/s: 1.9.7, 1.9.10, 2.0
    • Fix Version/s: 1.9.11, 2.0
    • Component/s: Database SQL/XMLDB, MNet
    • Labels:
    • Environment:
      Redhat EL5, Oracle 11g, Moodle in combination with Mahara
    • Database:
      Oracle
    • Difficulty:
      Easy
    • Affected Branches:
      MOODLE_19_STABLE, MOODLE_20_STABLE
    • Fixed Branches:
      MOODLE_19_STABLE, MOODLE_20_STABLE

      Description

      In ./mnet/xmlrpc/client.php an invalid SQL is used which results in an ORA-0918 error (column ambiguesly defined).

      this is caused by the statement:

      **********************************************************
      // Find methods that we subscribe to on this host
      $sql = "
      SELECT *
      FROM
      {$CFG->prefix}mnet_rpc r,
      {$CFG->prefix}mnet_service2rpc s2r,
      {$CFG->prefix}mnet_host2service h2s
      WHERE
      r.xmlrpc_path = '{$this->method}' AND
      s2r.rpcid = r.id AND
      s2r.serviceid = h2s.serviceid AND
      h2s.subscribe = '1' AND
      h2s.hostid in ({$id_list})";
      **********************************************************

      Since columns like 'ID' exist in mnet_rpc AND mnet_service2rpc AND in mnet_host2service, this results in ORA-0918. The correct statedment should be:

      **********************************************************
      // Find methods that we subscribe to on this host
      $sql = "
      SELECT r.id
      ,r.function_name
      ,r.xmlrpc_path
      ,r.parent_type
      ,r.parent
      ,r.enabled
      ,r.profile
      ,s2r.id s2r_id
      ,s2r.serviceid
      ,s2r.rpcid
      ,h2s.hostid
      ,h2s.id h2s_id
      ,h2s.serviceid h2s_serviceid
      ,h2s.subscribe
      FROM
      {$CFG->prefix}mnet_rpc r,
      {$CFG->prefix}mnet_service2rpc s2r,
      {$CFG->prefix}mnet_host2service h2s
      WHERE
      r.xmlrpc_path = '{$this->method}' AND
      s2r.rpcid = r.id AND
      s2r.serviceid = h2s.serviceid AND
      h2s.subscribe = '1' AND
      h2s.hostid in ({$id_list})";
      **********************************************************

        Gliffy Diagrams

        1. MDL-21554-patch19.txt
          1 kB
          David Mudrak
        2. ora.patch.txt
          0.9 kB
          Penny Leach

          Issue Links

            Activity

            Hide
            mjollnir Penny Leach added a comment -

            If you change it to select r.id,
            and change the get_records_sql line to record_exists_sql,

            does that fix it?

            Show
            mjollnir Penny Leach added a comment - If you change it to select r.id, and change the get_records_sql line to record_exists_sql, does that fix it?
            Hide
            mjollnir Penny Leach added a comment -

            here's the patch.

            Show
            mjollnir Penny Leach added a comment - here's the patch.
            Hide
            stronk7 Eloy Lafuente (stronk7) added a comment -

            Adding here to David, as far as he has been working with MNET lately. This should be fixed both for 1.9.x and 2.0.x (if problem persists). Penny patch was for 1.9.x (old one).

            Ciao

            Show
            stronk7 Eloy Lafuente (stronk7) added a comment - Adding here to David, as far as he has been working with MNET lately. This should be fixed both for 1.9.x and 2.0.x (if problem persists). Penny patch was for 1.9.x (old one). Ciao
            Hide
            mudrd8mz David Mudrak added a comment -

            For 2.0, this has been already fixed independently on this issue - see
            http://github.com/moodle/moodle/commit/d82d2720378b1383d93cdec3562b993763b04f61

            Going to review Penny's patch for 1.9 but from the comment I read that Penny fixed it the same way as I did for 2.0 (which is a good sign).

            Show
            mudrd8mz David Mudrak added a comment - For 2.0, this has been already fixed independently on this issue - see http://github.com/moodle/moodle/commit/d82d2720378b1383d93cdec3562b993763b04f61 Going to review Penny's patch for 1.9 but from the comment I read that Penny fixed it the same way as I did for 2.0 (which is a good sign).
            Hide
            mudrd8mz David Mudrak added a comment -

            This is a patch for 1.9 I am about to commit. Please review.

            Show
            mudrd8mz David Mudrak added a comment - This is a patch for 1.9 I am about to commit. Please review.
            Hide
            mudrd8mz David Mudrak added a comment -

            Fixed in MOODLE_19_STABLE. As mentioned above, 2.0 was released having this fixed (though I was not aware of this issue)

            Show
            mudrd8mz David Mudrak added a comment - Fixed in MOODLE_19_STABLE. As mentioned above, 2.0 was released having this fixed (though I was not aware of this issue)
            Hide
            stronk7 Eloy Lafuente (stronk7) added a comment -

            Thanks David, Penny!

            Show
            stronk7 Eloy Lafuente (stronk7) added a comment - Thanks David, Penny!

              People

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

                Dates

                • Created:
                  Updated:
                  Resolved:
                  Fix Release Date:
                  24/Nov/10