mahara-contributors team mailing list archive

[Mahara Bot <1070046@xxxxxxxxxxxxxxxxxx>]

Reviewed:  https://reviews.mahara.org/2868
Committed: http://gitorious.org/mahara/mahara/commit/0b816e8f1e97d3d8b9785db0c1b20ffaba6f524f
Submitter: Son Nguyen (son.nguyen@xxxxxxxxxxxxxxx)
Branch:    1.7_STABLE

commit 0b816e8f1e97d3d8b9785db0c1b20ffaba6f524f
Author: Aaron Wells <aaronw@xxxxxxxxxxxxxxx>
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

-- 
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:
  Fix Committed
Status in Mahara 1.7 series:
  Fix Committed
Status in Mahara 1.8 series:
  Fix Committed
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