Error appears when deleting Institution collections that have been auto-copied to members accounts

[Dianne Tennent]

This happens in both MySQL and Postgres

To replicate:
1. Create an institution template collection (set to 'Current auto-copied template') with portfolio completion.
2. Create a new account and run the cron so that the portfolio is created in the new member's account.
3. As admin, try to delete the institution template collection.

Result
 - SQL error messages on screen. There is a foreign key constraint with collection_template.originaltemplate = collection.id, so it won't allow you to delete from collection table until collection_template table entries are cleared.

Expected:
 - Perhaps there should be a warning to institution admins that the collection has been auto-copied before deleting it.

- I think we need to delete from the collection_template table where originaltemplate = $this->id, rather than where collection = $this->id (htdocs/lib/collection.php:324). Then we'll be able to delete from collection table.

Kristina mentioned that a Behat test for this would be good, since we don't regularly come across this scenario.

[Kristina Hoeppner (kris-hoeppner)]

I created a wishlist item for the special deletion warning, which is a good idea, at bug #1969701.

I confirmed the test instructions and got the problem as well on main.

The warning and stacktrace:

[WAR] 4d (lib/errors.php:853) Failed to get a recordset: postgres9 error: [-1: ERROR: update or delete on table "collection" violates foreign key constraint "colltemp_ori_fk" on table "collection_template"
DETAIL: Key (id)=(1) is still referenced from table "collection_template".] in EXECUTE("DELETE FROM "collection" WHERE "id" = ? ")Command was: DELETE FROM "collection" WHERE "id" = ? and values was (0:1)
Call stack (most recent first):

    log_message("Failed to get a recordset: postgres9 error: [-1: E...", 8, true, true) at /home/kristina/code/mahara/htdocs/lib/errors.php:89
    log_warn("Failed to get a recordset: postgres9 error: [-1: E...") at /home/kristina/code/mahara/htdocs/lib/errors.php:853
    SQLException->__construct("Failed to get a recordset: postgres9 error: [-1: E...") at /home/kristina/code/mahara/htdocs/lib/dml.php:1045
    delete_records("collection", "id", "1") at /home/kristina/code/mahara/htdocs/lib/collection.php:326
    Collection->delete(true) at /home/kristina/code/mahara/htdocs/collection/delete.php:97
    deletecollection_submit(object(Pieform), array(size 2)) at /home/kristina/code/mahara/htdocs/lib/pieforms/pieform.php:577
    Pieform->__construct(array(size 3)) at /home/kristina/code/mahara/htdocs/lib/pieforms/pieform.php:173
    Pieform::process(array(size 3)) at /home/kristina/code/mahara/htdocs/lib/mahara.php:5754
    pieform(array(size 3)) at /home/kristina/code/mahara/htdocs/collection/delete.php:77

[Kristina Hoeppner (kris-hoeppner)]

Right now, the template is needed in case there are portfolios in the queue that are waiting to be copied. portfolio_auto_copy function is involved.

So if we are wanting to make a change, we would need to remove portfolios that are currently queued from the queue or alert the person deleting the portfolio that they can't right now because it is still being copied.

https://reviews.mahara.org/c/mahara/+/11673 points to the patch in which we removed the line to delete a collection.

[Joost Elshoff (joostelshoff)]

In the original description, it says this applies to Postgres and MySQL, but I think this is not about which database type used: we encountered the same in MariaDB.

We'd be in favour of having a proper message telling the authorized user they cannot delete a portfolio while automated copies are pending (or other relations have been established between template and copies).

Are we correct in establishing this is a low to medium impact bug?

[Kristina Hoeppner (kris-hoeppner)]

Hi Joost,

This is not actively worked on as it is most likely not going to happen too frequently. Happy to bump up the priority if your institution wants to pay for the fix. :-)

Cheers
Kristina