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

Role override SQL retrieving extremely large combinatorial result set

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Closed
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: 1.7
    • Fix Version/s: 1.9
    • Labels:
      None
    • Environment:
      Database server: RHEL4 u3 x86_64, 1.4 TB RAID, MySQL 5.0.27
      Web server PHP4.4.4, Apache 1.3.37
    • Database:
      MySQL
    • Affected Branches:
      MOODLE_17_STABLE
    • Fixed Branches:
      MOODLE_19_STABLE

      Description

      An SQL query relating to role override function is producing large combinatorial result sets in our installation of Moodle 1.7+ (2006101009). We've seen the query below return upwards of 400k rows (around 10mb of data). Due to the large result set, this query alone takes upwards of 40 seconds to complete, depending on how many contexts are associated with the user.

      1. Query_time: 31 Lock_time: 0 Rows_sent: 334368 Rows_examined: 1340316
        SELECT rc.capability, c1.id as id1, c2.id as id2, (c1.contextlevel * 100 + c2.contextlevel) AS aggrlevel,
        rc.permission AS sum
        FROM
        mdl_role_assignments ra,
        mdl_role_capabilities rc,
        mdl_context c1,
        mdl_context c2
        WHERE
        ra.contextid=c1.id AND
        ra.roleid=rc.roleid AND
        ra.userid=22 AND
        rc.contextid=c2.id AND
        c1.id IN (54,35,83,102,107,75,172,203,69,389,223,219,257,383,291,298,512,302,366,363,378,217,316,457,456,431,362,319,361,345,58,354,385) AND
        rc.contextid != 1
        AND ((ra.timestart = 0 OR ra.timestart < 1168276177) AND (ra.timeend = 0 OR ra.timeend > 1168276177))
        GROUP BY
        rc.capability, (c1.contextlevel * 100 + c2.contextlevel), c1.id, c2.id, rc.permission
        ORDER BY
        aggrlevel ASC;

      It appears that most of these rows are discarded in a subsequent function call that determines if c1 is a parent of c2. I understand that there have been other bug reports regarding missing INDEX after upgrading vs. installing fresh. We recently upgraded from 1.5.3+ to 1.7 (on 1/8).

      Some sizes for the tables used in the above query:
      mdl_role_assignments: 3907 rows
      mdl_role_capabilities: 31318 rows
      mdl_context: 9267 rows (and growing constantly)

      If I can provide additional information that might be of use, please do not hesitate to let me know.

        Attachments

          Issue Links

            Activity

              People

              • Votes:
                6 Vote for this issue
                Watchers:
                12 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved:
                  Fix Release Date:
                  3/Mar/08