mahara-contributors team mailing list archive

[Hugh Davenport <1070046@xxxxxxxxxxxxxxxxxx>]

Public bug reported:

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.

** Affects: mahara
     Importance: Medium
         Status: Confirmed


** Tags: mysql

-- 
You received this bug notification because you are a member of Mahara
Contributors, which is subscribed to Mahara.
https://bugs.launchpad.net/bugs/1070046

Title:
  select query uses more than MAX_JOIN_SIZE on mysql

Status in Mahara ePortfolio:
  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