mahara-contributors team mailing list archive

[Aaron Wells <1070046@xxxxxxxxxxxxxxxxxx>]

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