-
Bug
-
Resolution: Unresolved
-
Minor
-
None
-
4.3.9, 4.4.5, 4.5
-
MOODLE_403_STABLE, MOODLE_404_STABLE, MOODLE_405_STABLE
The Security report in Moodle (Site administration > Reports > Security report) does not load for us, it appears to take around 10 minutes to load with most of the time being spent on the query that counts the number of users who have an XSS risk in the XSS risk check.
For us at least the page fails to finish loading to the browser with a console message of: Failed to load resource: net::ERR_INCOMPLETE_CHUNKED_ENCODING
At least in MySQL the query does full table scans:
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | PRIMARY | <derived2> | NULL | ALL | NULL | NULL | NULL | NULL | 1087 | 100 | NULL |
| 1 | PRIMARY | c | NULL | eq_ref | PRIMARY,mdl_cont_pat_ix | PRIMARY | 8 | rc.contextid | 1 | 100 | NULL |
| 1 | PRIMARY | ra | NULL | ref | mdl_roleassi_rol_ix,mdl_roleassi_con_ix,mdl_roleassi_use_ix,mdl_roleassi_rolcon_ix,mdl_roleassi_useconrol_ix | mdl_roleassi_rol_ix | 8 | rc.roleid | 1817 | 100 | NULL |
| 1 | PRIMARY | u | NULL | eq_ref | PRIMARY,mdl_user_del_ix | PRIMARY | 8 | rcmoodle.ra.userid | 1 | 50 | Using where |
| 1 | PRIMARY | sc | NULL | eq_ref | PRIMARY,mdl_cont_pat_ix | PRIMARY | 8 | rcmoodle.ra.contextid | 1 | 100 | Using where |
| 2 | DERIVED | rcx | NULL | ALL | mdl_rolecapa_rolconcap_uix,mdl_rolecapa_cap_ix | NULL | NULL | NULL | 10874 | 10 | Using where; Using temporary |
| 2 | DERIVED | cap | NULL | eq_ref | mdl_capa_nam_uix | mdl_capa_nam_uix | 1022 | rcmoodle.rcx.capability | 1 | 100 | Using where; Distinct |
it takes around 10 minutes to run on our database which has:
| Table | Rows |
|---|---|
| role_capabilities | 10790 |
| capabilities | 895 |
| context | 8782166 |
| role_assignments | 10100958 |
| user | 378579 |
I get a similar full table scan on the rcx table when running the query on my developer instance using moodle-docker, although the overall explain plan is slightly different.
