Moodle
  1. Moodle
  2. MDL-21554

ORA-0918 when signing in from Mahara (MNET)

    Details

    • Type: Bug Bug
    • Status: Closed
    • Priority: Blocker 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
    • Rank:
      200

      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})";
      **********************************************************

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

        Issue Links

          Activity

          Hide
          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
          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
          Penny Leach added a comment -

          here's the patch.

          Show
          Penny Leach added a comment - here's the patch.
          Hide
          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
          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
          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
          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
          David Mudrak added a comment -

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

          Show
          David Mudrak added a comment - This is a patch for 1.9 I am about to commit. Please review.
          Hide
          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
          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
          Eloy Lafuente (stronk7) added a comment -

          Thanks David, Penny!

          Show
          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: