Cron error with over 65535 users

[Jono M]

I discovered another case of things failing when there are too many users to substitute IDs as question marks in a postgres query - specifically, when running cron on a site with ~68000 users I get:

[WAR] 3f (lib/dml.php:480) Failed to get a recordset: postgres8 error: [-1: number of parameters must be between 0 and 65535] in EXECUTE("SELECT COUNT(*) FROM "usr" WHERE lastaccess >= DATE(?) AND lastaccess < DATE(?)+ INTERVAL '1 day' AND id IN (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,...)
Command was: SELECT COUNT(*) FROM "usr" WHERE lastaccess >= DATE(?) AND lastaccess < DATE(?)+ INTERVAL '1 day' AND id IN (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,...)

Call stack (most recent first):

    get_recordset_sql("SELECT COUNT(*) FROM "usr" WHERE lastaccess >= DAT...", array(size 66885)) at /home/jonom/mahara/mahara/htdocs/lib/dml.php:269
    count_records_sql("SELECT COUNT(*) FROM "usr" WHERE lastaccess >= DAT...", array(size 66885)) at /home/jonom/mahara/mahara/htdocs/lib/dml.php:251
    count_records_select("usr", "lastaccess >= DATE(?) AND lastaccess < DATE(?)+ IN...", array(size 66885)) at /home/jonom/mahara/mahara/htdocs/lib/mahara.php:3782
    cron_institution_data_daily() at /home/jonom/mahara/mahara/htdocs/lib/cron.php:142

Should be a similar fix to Bug #1456849

[Jono M (jonom)]

This also affects the Institution Statistics page, so for a more dramatic demonstration, create more than 65535 users and try to view statistics for "No Institution"

[Mahara Bot (dev-mahara)]

Patch for "master" branch: https://reviews.mahara.org/5481

[Mahara Bot (dev-mahara)]

Reviewed: https://reviews.mahara.org/5481
Committed: https://git.nzoss.org.nz/mahara/mahara/commit/75d9375a03d051f3e47ba36a795f2c61300c566a
Submitter: Aaron Wells (<email address hidden>)
Branch: master

commit 75d9375a03d051f3e47ba36a795f2c61300c566a
Author: Jono Mingard <email address hidden>
Date: Thu Oct 8 16:14:34 2015 +1300

Make institution member queries more scalable (Bug #1497053)

Use a subquery instead of passing in all user IDs as question marks
Also add behat test for viewing basic institution statistics

Change-Id: I006493d291a9e2d75d4ad66d946df8ac3978eeb8

[Mahara Bot (dev-mahara)]

Patch for "15.10_STABLE" branch: https://reviews.mahara.org/5493

[Mahara Bot (dev-mahara)]

Patch for "15.04_STABLE" branch: https://reviews.mahara.org/5494

[Aaron Wells (u-aaronw)]

I believe this problem is present in 1.9 and 1.10 as well, but the fix gets a merge conflict when trying to backport it, so since it's not a trivial fix we won't be backporting it.

[Mahara Bot (dev-mahara)]

Reviewed: https://reviews.mahara.org/5493
Committed: https://git.nzoss.org.nz/mahara/mahara/commit/faf171e1c8c4302bee7c34d1b493f67d7a4a5fca
Submitter: Robert Lyon (<email address hidden>)
Branch: 15.10_STABLE

commit faf171e1c8c4302bee7c34d1b493f67d7a4a5fca
Author: Jono Mingard <email address hidden>
Date: Thu Oct 8 16:14:34 2015 +1300

Make institution member queries more scalable (Bug #1497053)

Use a subquery instead of passing in all user IDs as question marks
Also add behat test for viewing basic institution statistics

Change-Id: I006493d291a9e2d75d4ad66d946df8ac3978eeb8
(cherry picked from commit 75d9375a03d051f3e47ba36a795f2c61300c566a)

[Mahara Bot (dev-mahara)]

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

commit aba6982423b7a7eecff79c1687503db99f5decfe
Author: Jono Mingard <email address hidden>
Date: Thu Oct 8 16:14:34 2015 +1300

Make institution member queries more scalable (Bug #1497053)

Use a subquery instead of passing in all user IDs as question marks
Also add behat test for viewing basic institution statistics

Change-Id: I006493d291a9e2d75d4ad66d946df8ac3978eeb8
(cherry picked from commit 75d9375a03d051f3e47ba36a795f2c61300c566a)