Mahara

Excessive number of joins when looking up latest pages

Bug #889547 reported by Conrad Lienhardt
6
This bug affects 1 person
Affects Status Importance Assigned to Milestone
Mahara
Fix Released
Medium
Richard Mansfield
Mahara 1.5.0

Bug Description

This is the error log

[Wed Nov 09 13:10:08 2011] [error] [client **.**.**.**] [WAR] f4 (lib/errors.php:745) 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 COUNT (DISTINCT v.id)

The error only affects one dashboard

I´d to delete the Latest Pages Block in my Dashboard Page via MySQL to be able to load the page. But the bug persists. Draggin that blog again to my Dashboard Page the page view is killed.

I don't know, whether this bug is caused by/ or causes a bug in sending emails.
This is the error message sent by the mahara system:
Exception 'EmailException' with message 'Couldn't send email to ****** with subject e-Portfolio *****: Neuer Ansichtenzugriff. Error from phpmailer was: SMTP Error: Data not accepted.
SMTP server error: ' in /home/.sites/383/site150/web/portfolio/lib/user.php:457
But: Sending Emails were never a problem.

Since then every mail has been sent twice. In settings I can't disable Email sending. It doesn't work. Disabled, the emails are sent like in enabling mode. No difference.

See also:
http://mahara.org/interaction/forum/topic.php?id=4156

Mahara Version: 1.4.0 -
Linux
PHP 5.2.16
MySQL 5.1.52
Friefox 7.1 (but also with Chrome 15.0.874.106 m)

Tags: mysql
François Marier (fmarier)
Changed in mahara:
milestone: none → 1.5.0
importance: Undecided → Medium
status: New → Triaged
tags: added: mysql
Revision history for this message
Melissa Draper (melissa) wrote :

Hi Conrad,

Can you tell us more about the setup you have? Is this shared hosting? How big is the site?

Thanks,
Melissa.

Changed in mahara:
status: Triaged → Incomplete
Revision history for this message
Richard Mansfield (richard-mansfield) wrote :

Reproduced by putting in a lot of view_access data and setting SQL_BIG_SELECTS=0 and SQL_MAX_JOIN_SIZE to 100000000 (default on my mysql server is 18446744073709551615).

Changed in mahara:
status: Incomplete → Confirmed
assignee: nobody → Richard Mansfield (richard-mansfield)
Revision history for this message
Richard Mansfield (richard-mansfield) wrote :

https://reviews.mahara.org/877

Changed in mahara:
status: Confirmed → In Progress
Revision history for this message
Mahara Bot (dev-mahara) wrote : A change has been merged

Reviewed: https://reviews.mahara.org/877
Committed: http://gitorious.org/mahara/mahara/commit/5362403f242a386830df33b8f95e36a386035dc4
Submitter: Francois Marier (<email address hidden>)
Branch: master

commit 5362403f242a386830df33b8f95e36a386035dc4
Author: Richard Mansfield <email address hidden>
Date: Tue Nov 22 15:33:29 2011 +1300

    Remove excess joins from latest pages query (bug #889547)

    When a user has many views shared with them, the view_search query
    may exceed mysql's max_join_size and cause an unrecoverable error on
    a user's dashboard, among other places. Replacing a bunch of these
    outer joins with subselects fixes the issue, and in a few tests on a
    large postgres db, also improved performance.

    Change-Id: Ic3b82518e4571807ceae4747ab086eb4a9baa5db
    Signed-off-by: Richard Mansfield <email address hidden>

François Marier (fmarier)
Changed in mahara:
status: In Progress → Fix Committed
François Marier (fmarier)
summary: - dashboard/ page unavailable - Latest Pages
+ Excessive number of joins when looking up latest pages
Melissa Draper (melissa)
Changed in mahara:
status: Fix Committed → Fix Released
To post a comment you must log in.
This report contains Public information  
Everyone can see this information.
Subscribing...

Other bug subscribers

Remote bug watches

Bug watches keep track of this bug in other bug trackers.