select query uses more than MAX_JOIN_SIZE on mysql

[Hugh Davenport]

When I tried to use an Artefact (Files, Images and Video) within a special Group - in my case it case I tried to use Image - I get that error message in debugger:

+++

Failed to get a recordset: mysql error: [1104: The SELECT would examine more than MAX_JOIN_SIZE rows; check your WHERE and use SET SQL_BIG_SELECTS=1 or SET SQL_MAX_JOIN_SIZE=# if the SELECT is okay] in EXECUTE(" SELECT a.id, a.artefacttype, a.mtime, f.size, a.title, a.description, a.locked, a.allowcomments, u.profileicon AS defaultprofileicon, COUNT(DISTINCT c.id) AS childcount, COUNT (DISTINCT aa.artefact) AS attachcount, COUNT(DISTINCT va.view) AS viewcount, COUNT(DISTINCT api.id) AS profileiconcount, r.can_edit, r.can_view, r.can_republish, a.author FROM "ep_artefact" a LEFT OUTER JOIN "ep_artefact_file_files" f ON f.artefact = a.id LEFT OUTER JOIN "ep_artefact" c ON c.parent = a.id LEFT OUTER JOIN "ep_artefact" api ON api.parent = a.id AND api.artefacttype = 'profileicon' LEFT OUTER JOIN "ep_view_artefact" va ON va.artefact = a.id LEFT OUTER JOIN "ep_artefact_attachment" aa ON aa.attachment = a.id LEFT OUTER JOIN "ep_usr" u ON a.id = u.profileicon AND a.owner = u.id LEFT OUTER JOIN ( SELECT ar.artefact, ar.can_edit, ar.can_view, ar.can_republish FROM "ep_artefact_access_role" ar INNER JOIN "ep_group_member" gm ON ar.role = gm.role WHERE gm.group = '1' AND gm.member = '2' ) r ON r.artefact = a.id WHERE a.artefacttype IN ('image','profileicon','folder') AND a.group = '1' AND a.owner IS NULL AND (r.can_view = 1 OR a.author = '2') AND a.parent IS NULL GROUP BY a.id, a.artefacttype, a.mtime, f.size, a.title, a.description, a.locked, a.allowcomments, u.profileicon, r.can_edit, r.can_view, r.can_republish, a.author") Command was: SELECT a.id, a.artefacttype, a.mtime, f.size, a.title, a.description, a.locked, a.allowcomments, u.profileicon AS defaultprofileicon, COUNT(DISTINCT c.id) AS childcount, COUNT (DISTINCT aa.artefact) AS attachcount, COUNT(DISTINCT va.view) AS viewcount, COUNT(DISTINCT api.id) AS profileiconcount, r.can_edit, r.can_view, r.can_republish, a.author FROM "ep_artefact" a LEFT OUTER JOIN "ep_artefact_file_files" f ON f.artefact = a.id LEFT OUTER JOIN "ep_artefact" c ON c.parent = a.id LEFT OUTER JOIN "ep_artefact" api ON api.parent = a.id AND api.artefacttype = 'profileicon' LEFT OUTER JOIN "ep_view_artefact" va ON va.artefact = a.id LEFT OUTER JOIN "ep_artefact_attachment" aa ON aa.attachment = a.id LEFT OUTER JOIN "ep_usr" u ON a.id = u.profileicon AND a.owner = u.id LEFT OUTER JOIN ( SELECT ar.artefact, ar.can_edit, ar.can_view, ar.can_republish FROM "ep_artefact_access_role" ar INNER JOIN "ep_group_member" gm ON ar.role = gm.role WHERE gm.group = ? AND gm.member = ? ) r ON r.artefact = a.id WHERE a.artefacttype IN ('image','profileicon','folder') AND a.group = ? AND a.owner IS NULL AND (r.can_view = 1 OR a.author = ?) AND a.parent IS NULL GROUP BY a.id, a.artefacttype, a.mtime, f.size, a.title, a.description, a.locked, a.allowcomments, u.profileicon, r.can_edit, r.can_view, r.can_republish, a.author and values was (1,2,1,2)

+++

When trying to open the Files Folder at the Group Site the following error message is printed
"A nonrecoverable error occured. This probably means you have encountered a bug in the system"

+++

I checked other groups. There all artefacts work. So it seems to be a special problem connected to that group. This is the main group where my students get their Inputs and works.

I tried to figure out that error statement. But I am no SQL expert and so I hope someone can give me help.

A more long term solution would be to reduce the number of joins made.

[Hugh Davenport (hugh-davenport)]

Solution would be to add SET SQL_BIG_SELECTS=1

This can be done in lib/dml.php round line 1478 (master)

[Hugh Davenport (hugh-davenport)]

see https://mahara.org/interaction/forum/topic.php?id=4949

[Kristina Hoeppner (kris-hoeppner)]

Hugh already provided a solution. So it should be simple to implement.

[Son Nguyen (ngson2000)]

Actually, the solution is proposed by Conrad Lienhardt, see https://mahara.org/interaction/forum/topic.php?id=4949

[Son Nguyen (ngson2000)]

I think this is just a temporary solution. We should optimize the SQL query to prevent big select queries like this.

[Aaron Wells (u-aaronw)]

I think it's a reasonable setting. MySQL throws the warning based on how many rows it thinks it will have to process, not how many it actually does process, and the setting is only there to prevent a user from accidentally running huge queries. We've already indexed every join column used in that query, so if we're processing a lot of rows, it's because there's just a lot of data.

https://reviews.mahara.org/2804

[Robert Lyon (robertl-9)]

I do note that the sql query listed in OP of this bug contains calls to artefact_attachment and artefact_access_role, which can be misisng the correct indexing/keys if upgraded from mahara 1.0 (see bug 1081947).

On saying that it - this problem may not due to that issue so adjusting the SQL_BIG_SELECTS to equal 1 may still need to be done.

[Mahara Bot (dev-mahara)]

Reviewed: https://reviews.mahara.org/2804
Committed: http://gitorious.org/mahara/mahara/commit/03f4ecd39601a66d5926fee9fbe81b1aff90f8a4
Submitter: Robert Lyon (<email address hidden>)
Branch: master

commit 03f4ecd39601a66d5926fee9fbe81b1aff90f8a4
Author: Aaron Wells <email address hidden>
Date: Mon Dec 16 13:57:38 2013 +1300

Override MySQL's check for accidental large queries

Bug 1070046: MySQL has an optional server config option, "MAX_JOIN_SIZE",
which throws an error if you try to run a SQL query that MySQL's strategizer
thinks will require it to evaluate more than MAX_JOIN_SIZE rows. This is
intended to prevent the user from accidentally running giant queries that
will never finish, but some Mahara queries (which are large but will finish)
can trip it. Adding SQL_BIG_SELECTS=1 tels it that our queries are *not*
accidentally large.

Change-Id: I6db4699ea765d3213d13eb93b8de098914db24e0

[Robert Lyon (robertl-9)]

Patches for previous versions:

v1.8 https://reviews.mahara.org/#/c/2867/
v1.7 https://reviews.mahara.org/#/c/2868/
v1.6 https://reviews.mahara.org/#/c/2869/

[Mahara Bot (dev-mahara)]

Reviewed: https://reviews.mahara.org/2867
Committed: http://gitorious.org/mahara/mahara/commit/7db6f5d5a4f9a3a0ff24fe9cb61c1346e37829eb
Submitter: Son Nguyen (<email address hidden>)
Branch: 1.8_STABLE

commit 7db6f5d5a4f9a3a0ff24fe9cb61c1346e37829eb
Author: Aaron Wells <email address hidden>
Date: Mon Dec 16 13:57:38 2013 +1300

Override MySQL's check for accidental large queries

Bug 1070046: MySQL has an optional server config option, "MAX_JOIN_SIZE",
which throws an error if you try to run a SQL query that MySQL's strategizer
thinks will require it to evaluate more than MAX_JOIN_SIZE rows. This is
intended to prevent the user from accidentally running giant queries that
will never finish, but some Mahara queries (which are large but will finish)
can trip it. Adding SQL_BIG_SELECTS=1 tels it that our queries are *not*
accidentally large.

Change-Id: I6db4699ea765d3213d13eb93b8de098914db24e0

[Mahara Bot (dev-mahara)]

Reviewed: https://reviews.mahara.org/2868
Committed: http://gitorious.org/mahara/mahara/commit/0b816e8f1e97d3d8b9785db0c1b20ffaba6f524f
Submitter: Son Nguyen (<email address hidden>)
Branch: 1.7_STABLE

commit 0b816e8f1e97d3d8b9785db0c1b20ffaba6f524f
Author: Aaron Wells <email address hidden>
Date: Mon Dec 16 13:57:38 2013 +1300

Override MySQL's check for accidental large queries

Bug 1070046: MySQL has an optional server config option, "MAX_JOIN_SIZE",
which throws an error if you try to run a SQL query that MySQL's strategizer
thinks will require it to evaluate more than MAX_JOIN_SIZE rows. This is
intended to prevent the user from accidentally running giant queries that
will never finish, but some Mahara queries (which are large but will finish)
can trip it. Adding SQL_BIG_SELECTS=1 tels it that our queries are *not*
accidentally large.

Also has order change for bug 1184450

Change-Id: I6db4699ea765d3213d13eb93b8de098914db24e0

[Mahara Bot (dev-mahara)]

Reviewed: https://reviews.mahara.org/2869
Committed: http://gitorious.org/mahara/mahara/commit/f4e4b44f8a414a34ff83ee9d863247a80278040b
Submitter: Son Nguyen (<email address hidden>)
Branch: 1.6_STABLE

commit f4e4b44f8a414a34ff83ee9d863247a80278040b
Author: Aaron Wells <email address hidden>
Date: Mon Dec 16 13:57:38 2013 +1300

Override MySQL's check for accidental large queries

Bug 1070046: MySQL has an optional server config option, "MAX_JOIN_SIZE",
which throws an error if you try to run a SQL query that MySQL's strategizer
thinks will require it to evaluate more than MAX_JOIN_SIZE rows. This is
intended to prevent the user from accidentally running giant queries that
will never finish, but some Mahara queries (which are large but will finish)
can trip it. Adding SQL_BIG_SELECTS=1 tels it that our queries are *not*
accidentally large.

Also has order change for bug 1184450

Change-Id: I6db4699ea765d3213d13eb93b8de098914db24e0