Good Day,
We have a feedback activity with 62 questions and when we are trying to show responses and download the excel but all we get is Error reading from database. Turned on debugging and the detailed error is
Debug info: Too many tables; MariaDB can only use 61 tables in a join
SELECT.
With the following error output.
Debug info: Too many tables; MariaDB can only use 61 tables in a join
SELECT
c.id, c.random_response, c.courseid, v12556.value AS
val12556, v12557.value AS val12557, v12558.value AS val12558,
v12559.value AS val12559, v12560.value AS val12560, v12561.value AS
val12561, v12563.value AS val12563, v12564.value AS val12564,
v12565.value AS val12565, v12566.value AS val12566, v12567.value AS
val12567, v12568.value AS val12568, v12570.value AS val12570,
v12571.value AS val12571, v12572.value AS val12572, v12573.value AS
val12573, v12574.value AS val12574, v12575.value AS val12575,
v12577.value AS val12577, v12578.value AS val12578, v12579.value AS
val12579, v12580.value AS val12580, v12581.value AS val12581,
v12582.value AS val12582, v12584.value AS val12584, v12585.value AS
val12585, v12586.value AS val12586, v12587.value AS val12587,
v12588.value AS val12588, v12589.value AS val12589, v12591.value AS
val12591, v12592.value AS val12592, v12593.value AS val12593,
v12594.value AS val12594, v12595.value AS val12595, v12596.value AS
val12596, v12598.value AS val12598, v12599.value AS val12599,
v12600.value AS val12600, v12601.value AS val12601, v12602.value AS
val12602, v12603.value AS val12603, v12605.value AS val12605,
v12606.value AS val12606, v12607.value AS val12607, v12608.value AS
val12608, v12609.value AS val12609, v12610.value AS val12610,
v12612.value AS val12612, v12613.value AS val12613, v12614.value AS
val12614, v12615.value AS val12615, v12616.value AS val12616,
v12617.value AS val12617, v12619.value AS val12619, v12620.value AS
val12620, v12621.value AS val12621, v12622.value AS val12622,
v12623.value AS val12623, v12624.value AS val12624, v12625.value AS
val12625, v12626.value AS val12626
FROM
mdl_feedback_completed c LEFT OUTER JOIN mdl_feedback_value v12556 ON
v12556.completed = c.id AND v12556.item = ? LEFT OUTER JOIN
mdl_feedback_value v12557 ON v12557.completed = c.id AND v12557.item = ?
LEFT OUTER JOIN mdl_feedback_value v12558 ON v12558.completed = c.id
AND v12558.item = ? LEFT OUTER JOIN mdl_feedback_value v12559 ON
v12559.completed = c.id AND v12559.item = ? LEFT OUTER JOIN
mdl_feedback_value v12560 ON v12560.completed = c.id AND v12560.item = ?
LEFT OUTER JOIN mdl_feedback_value v12561 ON v12561.completed = c.id
AND v12561.item = ? LEFT OUTER JOIN mdl_feedback_value v12563 ON
v12563.completed = c.id AND v12563.item = ? LEFT OUTER JOIN
mdl_feedback_value v12564 ON v12564.completed = c.id AND v12564.item = ?
LEFT OUTER JOIN mdl_feedback_value v12565 ON v12565.completed = c.id
AND v12565.item = ? LEFT OUTER JOIN mdl_feedback_value v12566 ON
v12566.completed = c.id AND v12566.item = ? LEFT OUTER JOIN
mdl_feedback_value v12567 ON v12567.completed = c.id AND v12567.item = ?
LEFT OUTER JOIN mdl_feedback_value v12568 ON v12568.completed = c.id
AND v12568.item = ? LEFT OUTER JOIN mdl_feedback_value v12570 ON
v12570.completed = c.id AND v12570.item = ? LEFT OUTER JOIN
mdl_feedback_value v12571 ON v12571.completed = c.id AND v12571.item = ?
LEFT OUTER JOIN mdl_feedback_value v12572 ON v12572.completed = c.id
AND v12572.item = ? LEFT OUTER JOIN mdl_feedback_value v12573 ON
v12573.completed = c.id AND v12573.item = ? LEFT OUTER JOIN
mdl_feedback_value v12574 ON v12574.completed = c.id AND v12574.item = ?
LEFT OUTER JOIN mdl_feedback_value v12575 ON v12575.completed = c.id
AND v12575.item = ? LEFT OUTER JOIN mdl_feedback_value v12577 ON
v12577.completed = c.id AND v12577.item = ? LEFT OUTER JOIN
mdl_feedback_value v12578 ON v12578.completed = c.id AND v12578.item = ?
LEFT OUTER JOIN mdl_feedback_value v12579 ON v12579.completed = c.id
AND v12579.item = ? LEFT OUTER JOIN mdl_feedback_value v12580 ON
v12580.completed = c.id AND v12580.item = ? LEFT OUTER JOIN
mdl_feedback_value v12581 ON v12581.completed = c.id AND v12581.item = ?
LEFT OUTER JOIN mdl_feedback_value v12582 ON v12582.completed = c.id
AND v12582.item = ? LEFT OUTER JOIN mdl_feedback_value v12584 ON
v12584.completed = c.id AND v12584.item = ? LEFT OUTER JOIN
mdl_feedback_value v12585 ON v12585.completed = c.id AND v12585.item = ?
LEFT OUTER JOIN mdl_feedback_value v12586 ON v12586.completed = c.id
AND v12586.item = ? LEFT OUTER JOIN mdl_feedback_value v12587 ON
v12587.completed = c.id AND v12587.item = ? LEFT OUTER JOIN
mdl_feedback_value v12588 ON v12588.completed = c.id AND v12588.item = ?
LEFT OUTER JOIN mdl_feedback_value v12589 ON v12589.completed = c.id
AND v12589.item = ? LEFT OUTER JOIN mdl_feedback_value v12591 ON
v12591.completed = c.id AND v12591.item = ? LEFT OUTER JOIN
mdl_feedback_value v12592 ON v12592.completed = c.id AND v12592.item = ?
LEFT OUTER JOIN mdl_feedback_value v12593 ON v12593.completed = c.id
AND v12593.item = ? LEFT OUTER JOIN mdl_feedback_value v12594 ON
v12594.completed = c.id AND v12594.item = ? LEFT OUTER JOIN
mdl_feedback_value v12595 ON v12595.completed = c.id AND v12595.item = ?
LEFT OUTER JOIN mdl_feedback_value v12596 ON v12596.completed = c.id
AND v12596.item = ? LEFT OUTER JOIN mdl_feedback_value v12598 ON
v12598.completed = c.id AND v12598.item = ? LEFT OUTER JOIN
mdl_feedback_value v12599 ON v12599.completed = c.id AND v12599.item = ?
LEFT OUTER JOIN mdl_feedback_value v12600 ON v12600.completed = c.id
AND v12600.item = ? LEFT OUTER JOIN mdl_feedback_value v12601 ON
v12601.completed = c.id AND v12601.item = ? LEFT OUTER JOIN
mdl_feedback_value v12602 ON v12602.completed = c.id AND v12602.item = ?
LEFT OUTER JOIN mdl_feedback_value v12603 ON v12603.completed = c.id
AND v12603.item = ? LEFT OUTER JOIN mdl_feedback_value v12605 ON
v12605.completed = c.id AND v12605.item = ? LEFT OUTER JOIN
mdl_feedback_value v12606 ON v12606.completed = c.id AND v12606.item = ?
LEFT OUTER JOIN mdl_feedback_value v12607 ON v12607.completed = c.id
AND v12607.item = ? LEFT OUTER JOIN mdl_feedback_value v12608 ON
v12608.completed = c.id AND v12608.item = ? LEFT OUTER JOIN
mdl_feedback_value v12609 ON v12609.completed = c.id AND v12609.item = ?
LEFT OUTER JOIN mdl_feedback_value v12610 ON v12610.completed = c.id
AND v12610.item = ? LEFT OUTER JOIN mdl_feedback_value v12612 ON
v12612.completed = c.id AND v12612.item = ? LEFT OUTER JOIN
mdl_feedback_value v12613 ON v12613.completed = c.id AND v12613.item = ?
LEFT OUTER JOIN mdl_feedback_value v12614 ON v12614.completed = c.id
AND v12614.item = ? LEFT OUTER JOIN mdl_feedback_value v12615 ON
v12615.completed = c.id AND v12615.item = ? LEFT OUTER JOIN
mdl_feedback_value v12616 ON v12616.completed = c.id AND v12616.item = ?
LEFT OUTER JOIN mdl_feedback_value v12617 ON v12617.completed = c.id
AND v12617.item = ? LEFT OUTER JOIN mdl_feedback_value v12619 ON
v12619.completed = c.id AND v12619.item = ? LEFT OUTER JOIN
mdl_feedback_value v12620 ON v12620.completed = c.id AND v12620.item = ?
LEFT OUTER JOIN mdl_feedback_value v12621 ON v12621.completed = c.id
AND v12621.item = ? LEFT OUTER JOIN mdl_feedback_value v12622 ON
v12622.completed = c.id AND v12622.item = ? LEFT OUTER JOIN
mdl_feedback_value v12623 ON v12623.completed = c.id AND v12623.item = ?
LEFT OUTER JOIN mdl_feedback_value v12624 ON v12624.completed = c.id
AND v12624.item = ? LEFT OUTER JOIN mdl_feedback_value v12625 ON
v12625.completed = c.id AND v12625.item = ? LEFT OUTER JOIN
mdl_feedback_value v12626 ON v12626.completed = c.id AND v12626.item = ?
WHERE c.anonymous_response = ? AND c.feedback = ?
ORDER BY random_response ASC LIMIT 0, 20
[array (
0 => '12556',
1 => '12557',
2 => '12558',
3 => '12559',
4 => '12560',
5 => '12561',
6 => '12563',
7 => '12564',
8 => '12565',
9 => '12566',
10 => '12567',
11 => '12568',
12 => '12570',
13 => '12571',
14 => '12572',
15 => '12573',
16 => '12574',
17 => '12575',
18 => '12577',
19 => '12578',
20 => '12579',
21 => '12580',
22 => '12581',
23 => '12582',
24 => '12584',
25 => '12585',
26 => '12586',
27 => '12587',
28 => '12588',
29 => '12589',
30 => '12591',
31 => '12592',
32 => '12593',
33 => '12594',
34 => '12595',
35 => '12596',
36 => '12598',
37 => '12599',
38 => '12600',
39 => '12601',
40 => '12602',
41 => '12603',
42 => '12605',
43 => '12606',
44 => '12607',
45 => '12608',
46 => '12609',
47 => '12610',
48 => '12612',
49 => '12613',
50 => '12614',
51 => '12615',
52 => '12616',
53 => '12617',
54 => '12619',
55 => '12620',
56 => '12621',
57 => '12622',
58 => '12623',
59 => '12624',
60 => '12625',
61 => '12626',
62 => 1,
63 => '64',
)]
Error code: dmlreadexception
Stack trace:
- line 474 of /lib/dml/moodle_database.php: dml_read_exception thrown
- line 1027 of /lib/dml/mysqli_native_moodle_database.php: call to moodle_database->query_end()
- line 301 of /mod/feedback/classes/responses_table.php: call to mysqli_native_moodle_database->get_recordset_sql()
- line 1608 of /lib/tablelib.php: call to mod_feedback_responses_table->query_db()
- line 341 of /mod/feedback/classes/responses_table.php: call to table_sql->out()
- line 356 of /mod/feedback/classes/responses_table.php: call to mod_feedback_responses_table->out()
- line 142 of /mod/feedback/show_entries.php: call to mod_feedback_responses_table->display()
now I checked other feedback activities paying attention to the total questions and found no issue displaying the responses if the total questions are under 60. I tested five (5) and only the ones with 74 questions and 68 questions had an issue.
Currently running Moodle 3.1.1+ (Build: 20160714).
Any help would be appreciated