sql exception when deleting a user

[Cecilia Vela Gurovic]

When trying to delete users with the delete_inactive_users script, I get the following error for a few of them

[WAR] f1 (lib/errors.php:859) Failed to get a recordset: postgres8 error: [-1: ERROR: column "aff1.artefact" must appear in the GROUP BY clause or be used in an aggregate function
[WAR] f1 (lib/errors.php:859) LINE 2: SELECT aff1.*, a.artefacttype
[WAR] f1 (lib/errors.php:859) ^] in adodb_throw(
[WAR] f1 (lib/errors.php:859) SELECT aff1.*, a.artefacttype
[WAR] f1 (lib/errors.php:859) FROM "artefact_file_files" aff1
[WAR] f1 (lib/errors.php:859) JOIN "artefact" a ON aff1.artefact = a.id
[WAR] f1 (lib/errors.php:859) WHERE artefact IN (<list of artefact ids>)
[WAR] f1 (lib/errors.php:859) GROUP BY fileid
[WAR] f1 (lib/errors.php:859) HAVING COUNT(aff1.artefact) IN
[WAR] f1 (lib/errors.php:859) (SELECT COUNT(aff2.artefact)
[WAR] f1 (lib/errors.php:859) FROM "artefact_file_files" aff2
[WAR] f1 (lib/errors.php:859) WHERE aff1.fileid = aff2.fileid), )Command was:
[WAR] f1 (lib/errors.php:859) SELECT aff1.*, a.artefacttype
[WAR] f1 (lib/errors.php:859) FROM "artefact_file_files" aff1
[WAR] f1 (lib/errors.php:859) JOIN "artefact" a ON aff1.artefact = a.id
[WAR] f1 (lib/errors.php:859) WHERE artefact IN (<list of artefact ids>)
[WAR] f1 (lib/errors.php:859) GROUP BY fileid
[WAR] f1 (lib/errors.php:859) HAVING COUNT(aff1.artefact) IN
[WAR] f1 (lib/errors.php:859) (SELECT COUNT(aff2.artefact)
[WAR] f1 (lib/errors.php:859) FROM "artefact_file_files" aff2
[WAR] f1 (lib/errors.php:859) WHERE aff1.fileid = aff2.fileid)
Call stack (most recent first):
  * log_message(string(size 1282), integer, true, true) at <site>/lib/errors.php:95
  * log_warn(string(size 1282)) at <site>/lib/errors.php:859
  * SQLException->__construct(string(size 1282)) at <site>/lib/dml.php:517
  * get_recordset_sql(string(size 510), null, string(size 0), string(size 0)) at <site>/lib/dml.php:697
  * get_records_sql_assoc(string(size 510)) at <site>/artefact/file/lib.php:1486
  * ArtefactTypeFile::bulk_delete(array(size 18)) at <site>/artefact/file/lib.php:2404
  * ArtefactTypeImage::bulk_delete(array(size 18)) at Unknown:0
  * call_user_func_array(array(size 2), array(size 1)) at <site>/lib/mahara.php:1809
  * call_static_method(string(size 17), string(size 11), array(size 18)) at <site>/artefact/lib.php:769
  * ArtefactType::delete_by_artefacttype(array(size 19)) at <site>/artefact/file/lib.php:2097
  * ArtefactTypeFolder->delete() at <site>/lib/user.php:1610
  * delete_user(string(size 4)) at <site>/admin/cli/delete_inactive_users.php:158

[WAR] f1 (lib/dml.php:517) Failed to get a recordset: postgres8 error: [-1: ERROR: column "aff1.artefact" must appear in the GROUP BY clause or be used in an aggregate function
[WAR] f1 (lib/dml.php:517) LINE 2: SELECT aff1.*, a.artefacttype
[WAR] f1 (lib/dml.php:517) ^] in adodb_throw(
[WAR] f1 (lib/dml.php:517) SELECT aff1.*, a.artefacttype
[WAR] f1 (lib/dml.php:517) FROM "artefact_file_files" aff1
[WAR] f1 (lib/dml.php:517) JOIN "artefact" a ON aff1.artefact = a.id
[WAR] f1 (lib/dml.php:517) WHERE artefact IN (<list of artefact ids>)
[WAR] f1 (lib/dml.php:517) GROUP BY fileid
[WAR] f1 (lib/dml.php:517) HAVING COUNT(aff1.artefact) IN
[WAR] f1 (lib/dml.php:517) (SELECT COUNT(aff2.artefact)
[WAR] f1 (lib/dml.php:517) FROM "artefact_file_files" aff2
[WAR] f1 (lib/dml.php:517) WHERE aff1.fileid = aff2.fileid), )Command was:
[WAR] f1 (lib/dml.php:517) SELECT aff1.*, a.artefacttype
[WAR] f1 (lib/dml.php:517) FROM "artefact_file_files" aff1
[WAR] f1 (lib/dml.php:517) JOIN "artefact" a ON aff1.artefact = a.id
[WAR] f1 (lib/dml.php:517) WHERE artefact IN (<list of artefact ids>)
[WAR] f1 (lib/dml.php:517) GROUP BY fileid
[WAR] f1 (lib/dml.php:517) HAVING COUNT(aff1.artefact) IN
[WAR] f1 (lib/dml.php:517) (SELECT COUNT(aff2.artefact)
[WAR] f1 (lib/dml.php:517) FROM "artefact_file_files" aff2
[WAR] f1 (lib/dml.php:517) WHERE aff1.fileid = aff2.fileid)
Call stack (most recent first):
  * get_recordset_sql(string(size 510), null, string(size 0), string(size 0)) at <site>/lib/dml.php:697
  * get_records_sql_assoc(string(size 510)) at <site>/artefact/file/lib.php:1486
  * ArtefactTypeFile::bulk_delete(array(size 18)) at <site>/artefact/file/lib.php:2404
  * ArtefactTypeImage::bulk_delete(array(size 18)) at Unknown:0
  * call_user_func_array(array(size 2), array(size 1)) at <site>/lib/mahara.php:1809
  * call_static_method(string(size 17), string(size 11), array(size 18)) at <site>/artefact/lib.php:769
  * ArtefactType::delete_by_artefacttype(array(size 19)) at <site>/artefact/file/lib.php:2097
  * ArtefactTypeFolder->delete() at <site>/lib/user.php:1610
  * delete_user(string(size 4)) at <site>/admin/cli/delete_inactive_users.php:158

A nonrecoverable error occurred. This probably means you have encountered a bug in the system

[Mahara Bot (dev-mahara)]

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

[Cecilia Vela Gurovic (ceciliavg)]

to test it follow instructions in https://bugs.launchpad.net/mahara/+bug/1026864

[Mahara Bot (dev-mahara)]

Reviewed: https://reviews.mahara.org/8892
Committed: https://git.mahara.org/mahara/mahara/commit/5f6a89a79c1e18a5b3b83cb0f6d1c690ccaf7a50
Submitter: Robert Lyon (<email address hidden>)
Branch: master

commit 5f6a89a79c1e18a5b3b83cb0f6d1c690ccaf7a50
Author: Cecilia Vela Gurovic <email address hidden>
Date: Fri May 25 09:04:06 2018 +1200

Bug 1773247: bad sql query in files bulk_delete

behatnotneeded

Change-Id: Ide581c47d02772478fe04a85673e8d72cdd919e0

[Mahara Bot (dev-mahara)]

Patch for "18.04_STABLE" branch: https://reviews.mahara.org/8896

[Mahara Bot (dev-mahara)]

Reviewed: https://reviews.mahara.org/8896
Committed: https://git.mahara.org/mahara/mahara/commit/e14c42524579f57beb2fa0edc3100418f337bc64
Submitter: Cecilia Vela Gurovic (<email address hidden>)
Branch: 18.04_STABLE

commit e14c42524579f57beb2fa0edc3100418f337bc64
Author: Cecilia Vela Gurovic <email address hidden>
Date: Fri May 25 09:04:06 2018 +1200

Bug 1773247: bad sql query in files bulk_delete

behatnotneeded

Change-Id: Ide581c47d02772478fe04a85673e8d72cdd919e0
(cherry picked from commit 5f6a89a79c1e18a5b3b83cb0f6d1c690ccaf7a50)