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 ** Changed in: mahara Milestone: 1.8.1 => 1.8.2 ** Also affects: mahara/1.6 Importance: Undecided Status: New ** Also affects: mahara/1.7 Importance: Undecided Status: New ** Also affects: mahara/1.9 Importance: Medium Status: Confirmed ** Changed in: mahara/1.6 Milestone: None => 1.6.9 ** Also affects: mahara/1.8 Importance: Undecided Status: New ** Changed in: mahara/1.7 Milestone: None => 1.7.5 ** Changed in: mahara/1.8 Milestone: None => 1.8.2 ** Changed in: mahara/1.9 Milestone: 1.8.2 => 1.9.0 -- 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: Confirmed Status in Mahara 1.6 series: New Status in Mahara 1.7 series: New Status in Mahara 1.8 series: New Status in Mahara 1.9 series: Confirmed 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
