Reviewed: https://reviews.mahara.org/2804 Committed: http://gitorious.org/mahara/mahara/commit/03f4ecd39601a66d5926fee9fbe81b1aff90f8a4 Submitter: Robert Lyon ([email protected]) Branch: master
commit 03f4ecd39601a66d5926fee9fbe81b1aff90f8a4 Author: Aaron Wells <[email protected]> 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 -- You received this bug notification because you are a member of Mahara Contributors, which is subscribed to Mahara. Matching subscriptions: Subscription for all Mahara Contributors -- please ask on #mahara-dev or mahara.org forum before editing or unsubscribing it! https://bugs.launchpad.net/bugs/1070046 Title: select query uses more than MAX_JOIN_SIZE on mysql Status in Mahara ePortfolio: Fix Committed Status in Mahara 1.6 series: Confirmed Status in Mahara 1.7 series: Confirmed Status in Mahara 1.8 series: Confirmed Status in Mahara 1.9 series: Fix Committed 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_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. To manage notifications about this bug go to: https://bugs.launchpad.net/mahara/+bug/1070046/+subscriptions _______________________________________________ Mailing list: https://launchpad.net/~mahara-contributors Post to : [email protected] Unsubscribe : https://launchpad.net/~mahara-contributors More help : https://help.launchpad.net/ListHelp
