Problem with prepared statements in stats functions

Bug #1456849 reported by Robert Lyon on 2015-05-20
6
This bug affects 1 person
Affects Status Importance Assigned to Milestone
Mahara
High
Jono Mingard
15.04
High
Unassigned
15.10
High
Jono Mingard

Bug Description

 Failed to get a recordset: postgres8 error: [-1: number of parameters must be between 0 and 65535] in EXECUTE("
                SELECT (0.0 + COUNT(id)) / NULLIF(COUNT(DISTINCT "owner"), 0)
                FROM "view"
                WHERE id IN (?,? ... ?,?)
            and values was (2848,81339 ... 123,456)

The full error shows 65000+ question marks etc

There are a bunch of functions that generate stats but are poorly written (in lib/registration.php I think) where it tries to get info by passing in every user id that relates to the search as a prepared statement value - but when we have too many users (like on Mahara.org) it dies as there is a maximum number of ? we can have in prepared statement before the underlying database chokes.

Need to rewrite the stats so that they scale better

Jono Mingard (mingard) on 2015-06-05
Changed in mahara:
status: Confirmed → Won't Fix
status: Won't Fix → In Progress

Reviewed: https://reviews.mahara.org/4822
Committed: https://git.nzoss.org.nz/mahara/mahara/commit/8757a5c282e3f962b4d897476201142e6238769a
Submitter: Robert Lyon (<email address hidden>)
Branch: master

commit 8757a5c282e3f962b4d897476201142e6238769a
Author: Jono Mingard <email address hidden>
Date: Fri Jun 5 16:43:35 2015 +1200

Make user queries in registration report generation more scalable (Bug #1456849).

Users in an institution are now queried each time they are needed,
rather than being stored in an array and passed in with ?

Change-Id: I8e49c22d8247dbde3108f52d619f419cec5bb14c

Aaron Wells (u-aaronw) on 2015-07-22
Changed in mahara:
status: In Progress → Fix Committed

Reviewed: https://reviews.mahara.org/5004
Committed: https://git.nzoss.org.nz/mahara/mahara/commit/92b8f6ee3a29d4aaf415b3cc5025632022054969
Submitter: Robert Lyon (<email address hidden>)
Branch: 15.04_STABLE

commit 92b8f6ee3a29d4aaf415b3cc5025632022054969
Author: Jono Mingard <email address hidden>
Date: Fri Jun 5 16:43:35 2015 +1200

Make user queries in registration report generation more scalable (Bug #1456849).

Users in an institution are now queried each time they are needed,
rather than being stored in an array and passed in with ?

Change-Id: I8e49c22d8247dbde3108f52d619f419cec5bb14c
(cherry picked from commit 8757a5c282e3f962b4d897476201142e6238769a)

Robert Lyon (robertl-9) on 2016-11-06
Changed in mahara:
status: Fix Committed → Fix Released
To post a comment you must log in.
This report contains Public information  Edit
Everyone can see this information.

Other bug subscribers