select query uses more than MAX_JOIN_SIZE on mysql
Affects | Status | Importance | Assigned to | Milestone | |
---|---|---|---|---|---|
| Mahara |
Medium
|
Unassigned | ||
| 1.6 |
Medium
|
Unassigned | ||
| 1.7 |
Medium
|
Unassigned | ||
| 1.8 |
Medium
|
Unassigned |
Bug Description
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_
+++
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) wrote : | #1 |
Hugh Davenport (hugh-davenport) wrote : | #2 |
Changed in mahara: | |
milestone: | 1.7.0 → 1.8.0 |
Changed in mahara: | |
milestone: | 1.8rc1 → 1.8.0 |
Kristina Hoeppner (kris-hoeppner) wrote : | #3 |
Hugh already provided a solution. So it should be simple to implement.
Changed in mahara: | |
milestone: | 1.8.0 → 1.8.1 |
Son Nguyen (ngson2000) wrote : | #4 |
Actually, the solution is proposed by Conrad Lienhardt, see https:/
Son Nguyen (ngson2000) wrote : | #5 |
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) wrote : | #6 |
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.
Changed in mahara: | |
milestone: | 1.8.1 → 1.8.2 |
Robert Lyon (robertl-9) wrote : | #7 |
I do note that the sql query listed in OP of this bug contains calls to artefact_attachment and artefact_
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.
Reviewed: https:/
Committed: http://
Submitter: Robert Lyon (<email address hidden>)
Branch: master
commit 03f4ecd39601a66
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: I6db4699ea765d3
Robert Lyon (robertl-9) wrote : | #9 |
Patches for previous versions:
v1.8 https:/
v1.7 https:/
v1.6 https:/
Mahara Bot (dev-mahara) wrote : | #10 |
Reviewed: https:/
Committed: http://
Submitter: Son Nguyen (<email address hidden>)
Branch: 1.8_STABLE
commit 7db6f5d5a4f9a3a
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: I6db4699ea765d3
Mahara Bot (dev-mahara) wrote : | #11 |
Reviewed: https:/
Committed: http://
Submitter: Son Nguyen (<email address hidden>)
Branch: 1.7_STABLE
commit 0b816e8f1e97d3d
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: I6db4699ea765d3
Mahara Bot (dev-mahara) wrote : | #12 |
Reviewed: https:/
Committed: http://
Submitter: Son Nguyen (<email address hidden>)
Branch: 1.6_STABLE
commit f4e4b44f8a414a3
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: I6db4699ea765d3
no longer affects: | mahara/1.9 |
Changed in mahara: | |
status: | Fix Committed → Fix Released |
Solution would be to add SET SQL_BIG_SELECTS=1
This can be done in lib/dml.php round line 1478 (master)