Remove triggers from Mahara database

[Ghada El-Zoghbi]

I've created a forum post to discuss this:

* https://mahara.org/interaction/forum/topic.php?id=8324

Basically, my reasoning is:

1. Mahara supports two DB types - MySql and Postgres. Each DB has different syntax when it comes to creating/updating/deleting triggers. This makes it complicated to develop and maintain.

2. Mahara already has the concept of events in place. We can move the functionality of each trigger into an event. We would then have more control over the event. For example, don't action an event during an upgrade.

3. Triggers make it difficult to see what is actually happening. They are not transparent. If you weren't aware that there is a trigger on the table, it can make it difficult to track down why something is behaving in a certain way.

We can start slowly by removing one at a time. It doesn't have to happen all at once.

Just my thoughts.

Cheers.

[Darrin Hodges (dhodges2018)]

Hi all,
These triggers are causing db dumps to fail (in progresql) due to triggers being constantly deleted and created.

pg_dump -h mydbserver -U myuser mydb > /tmp/test.sql
pg_dump: [archiver (db)] query failed: ERROR: cache lookup failed for function 9184443
pg_dump: [archiver (db)] query was: SELECT tgname, tgfoid::pg_catalog.regproc AS tgfname, pg_catalog.pg_get_triggerdef(oid, false) AS tgdef, tgenabled, tableoid, oid FROM pg_catalog.pg_trigger t WHERE tgrelid = '8279073'::pg_catalog.oid AND NOT tgisinternal

We have a script that will show us which triggers are invalid:

ERROR: cache lookup failed for function 8279073
CRITICAL: Trigger search_elasticsearch_view on table catalyst_mahara_demo_stage.view in database mydb is invalid.
ERROR: cache lookup failed for function 8279073
CRITICAL: Trigger search_elasticsearch_artefact on table mydb.artefact in database mydb is invalid.

Can this issue be kicked along a bit, thanks.

Darrin

[Russell Boyatt (russell-boyatt)]

I'd certainly be in favour of removing triggers. We run Mahara on MySQL and have experienced a number of issues over the years with the DB layer. Transactions were disabled in MySQL (see https://bugs.launchpad.net/mahara/+bug/1514608) to help alleviate the issues.

We've always suspected the original problem was interaction of transactions and triggers on MySQL....but admittedly never quite got to the bottom of it.

[Kristina Hoeppner (kris-hoeppner)]

See https://reviews.mahara.org/#/c/9401/ for the patch for Elasticsearch

[Mahara Bot (dev-mahara)]

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

[Mahara Bot (dev-mahara)]

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

[Mahara Bot (dev-mahara)]

Reviewed: https://reviews.mahara.org/10018
Committed: https://git.mahara.org/mahara/mahara/commit/1dbbbd9fa15f5f9d9769320e17c50cc51b29abf5
Submitter: Robert Lyon (<email address hidden>)
Branch: master

commit 1dbbbd9fa15f5f9d9769320e17c50cc51b29abf5
Author: Robert Lyon <email address hidden>
Date: Thu May 16 09:47:25 2019 +1200

Bug 1785985: Offset the problematic cron jobs

As mentioned in forum post
https://mahara.org/interaction/forum/topic.php?id=8450#post33705

behatnotneeded

Change-Id: I700c295d6d4ced232a119c3f8dbbc922a73298d3
Signed-off-by: Robert Lyon <email address hidden>

[Mahara Bot (dev-mahara)]

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

[Mahara Bot (dev-mahara)]

Patch for "18.10_STABLE" branch: https://reviews.mahara.org/10023

[Mahara Bot (dev-mahara)]

Patch for "19.04_STABLE" branch: https://reviews.mahara.org/10024

[Mahara Bot (dev-mahara)]

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

[Mahara Bot (dev-mahara)]

Reviewed: https://reviews.mahara.org/10024
Committed: https://git.mahara.org/mahara/mahara/commit/4c620d4d4a93771ddf4932c1a8b34d9aa26fcdad
Submitter: Robert Lyon (<email address hidden>)
Branch: 19.04_STABLE

commit 4c620d4d4a93771ddf4932c1a8b34d9aa26fcdad
Author: Robert Lyon <email address hidden>
Date: Thu May 16 09:47:25 2019 +1200

Bug 1785985: Offset the problematic cron jobs

As mentioned in forum post
https://mahara.org/interaction/forum/topic.php?id=8450#post33705

behatnotneeded

Change-Id: I700c295d6d4ced232a119c3f8dbbc922a73298d3
Signed-off-by: Robert Lyon <email address hidden>

[Mahara Bot (dev-mahara)]

Reviewed: https://reviews.mahara.org/10023
Committed: https://git.mahara.org/mahara/mahara/commit/6c6bbc2526e4cde55c4dfab0f2eaa5411105c0e5
Submitter: Robert Lyon (<email address hidden>)
Branch: 18.10_STABLE

commit 6c6bbc2526e4cde55c4dfab0f2eaa5411105c0e5
Author: Robert Lyon <email address hidden>
Date: Thu May 16 09:47:25 2019 +1200

Bug 1785985: Offset the problematic cron jobs

As mentioned in forum post
https://mahara.org/interaction/forum/topic.php?id=8450#post33705

behatnotneeded

Change-Id: I700c295d6d4ced232a119c3f8dbbc922a73298d3
Signed-off-by: Robert Lyon <email address hidden>

[Mahara Bot (dev-mahara)]

Reviewed: https://reviews.mahara.org/10022
Committed: https://git.mahara.org/mahara/mahara/commit/a2f72b46ae62d107f2db9af3ddb55f9ea028f0c3
Submitter: Robert Lyon (<email address hidden>)
Branch: 18.04_STABLE

commit a2f72b46ae62d107f2db9af3ddb55f9ea028f0c3
Author: Robert Lyon <email address hidden>
Date: Thu May 16 09:47:25 2019 +1200

Bug 1785985: Offset the problematic cron jobs

As mentioned in forum post
https://mahara.org/interaction/forum/topic.php?id=8450#post33705

behatnotneeded

Change-Id: I700c295d6d4ced232a119c3f8dbbc922a73298d3
Signed-off-by: Robert Lyon <email address hidden>

[Mahara Bot (dev-mahara)]

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

[Mahara Bot (dev-mahara)]

Reviewed: https://reviews.mahara.org/9710
Committed: https://git.mahara.org/mahara/mahara/commit/68b575088d834216157918d57f192e90da62beff
Submitter: Cecilia Vela Gurovic (<email address hidden>)
Branch: master

commit 68b575088d834216157918d57f192e90da62beff
Author: Robert Lyon <email address hidden>
Date: Fri Apr 5 13:58:48 2019 +1300

Bug 1785985: Remove notification triggers

- building a php based way to update notification read count

behatnotneeded

Change-Id: Ibfdb79e36460a327372b1c9da3f624d7c8210d0c
Signed-off-by: Robert Lyon <email address hidden>

[Mahara Bot (dev-mahara)]

Reviewed: https://reviews.mahara.org/10280
Committed: https://git.mahara.org/mahara/mahara/commit/8e7a3135a1c866ce6856b95da19f9355d331c0ef
Submitter: Cecilia Vela Gurovic (<email address hidden>)
Branch: master

commit 8e7a3135a1c866ce6856b95da19f9355d331c0ef
Author: Robert Lyon <email address hidden>
Date: Fri Aug 30 14:00:16 2019 +1200

Bug 1785985: Remove quota triggers

- This looks like it was being handled by eventlistener_savedeleteartefact
and so didn't need to be a trigger function anyway

behatnotneeded

Change-Id: I837d2f4725c44ff0c525873b3406396033eb408c
Signed-off-by: Robert Lyon <email address hidden>

[Mahara Bot (dev-mahara)]

Reviewed: https://reviews.mahara.org/9401
Committed: https://git.mahara.org/mahara/mahara/commit/00d4372518ae2e7b8e97d9cecce13526a9294f37
Submitter: Cecilia Vela Gurovic (<email address hidden>)
Branch: master

commit 00d4372518ae2e7b8e97d9cecce13526a9294f37
Author: Robert Lyon <email address hidden>
Date: Tue Jan 8 14:51:12 2019 +1300

Bug 1785985: Remove Elasticsearch triggers

- building a php based way to update queue table

behatnotneeded

Change-Id: I199fbf2007489453b1bb24cc6cb324cf8562b8b2
Signed-off-by: Robert Lyon <email address hidden>

[Mahara Bot (dev-mahara)]

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

[Mahara Bot (dev-mahara)]

Reviewed: https://reviews.mahara.org/10545
Committed: https://git.mahara.org/mahara/mahara/commit/9be8c499a0a373e8742ae5dbcea177a088596715
Submitter: Cecilia Vela Gurovic (<email address hidden>)
Branch: master

commit 9be8c499a0a373e8742ae5dbcea177a088596715
Author: Cecilia Vela Gurovic <email address hidden>
Date: Thu Oct 31 13:57:28 2019 +1300

Bug 1785985: remove prefix from db table before adding to ES queue

behatnotneeded

Change-Id: I1fa4b026234fb1cdc06786d864e6cc655870c85a

[Mahara Bot (dev-mahara)]

Patch for "19.10_STABLE" branch: https://reviews.mahara.org/10546

[Mahara Bot (dev-mahara)]

Reviewed: https://reviews.mahara.org/10546
Committed: https://git.mahara.org/mahara/mahara/commit/62cda412d0d72d92bd7786d401d51ac897a37029
Submitter: Cecilia Vela Gurovic (<email address hidden>)
Branch: 19.10_STABLE

commit 62cda412d0d72d92bd7786d401d51ac897a37029
Author: Cecilia Vela Gurovic <email address hidden>
Date: Thu Oct 31 13:57:28 2019 +1300

Bug 1785985: remove prefix from db table before adding to ES queue

behatnotneeded

Change-Id: I1fa4b026234fb1cdc06786d864e6cc655870c85a
(cherry picked from commit 9be8c499a0a373e8742ae5dbcea177a088596715)

[Mahara Bot (dev-mahara)]

Patch for "19.10_STABLE" branch: https://reviews.mahara.org/10547

[Mahara Bot (dev-mahara)]

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

[Mahara Bot (dev-mahara)]

Reviewed: https://reviews.mahara.org/10548
Committed: https://git.mahara.org/mahara/mahara/commit/098699bc57f1aa1c925c5f25dee1c142c63eda9e
Submitter: Cecilia Vela Gurovic (<email address hidden>)
Branch: master

commit 098699bc57f1aa1c925c5f25dee1c142c63eda9e
Author: Cecilia Vela Gurovic <email address hidden>
Date: Thu Oct 31 16:00:09 2019 +1300

Bug 1785985: remove prefix from db table before adding to ES queue

behatnotneeded

Change-Id: I860ebcc892361a7af6b2edeed2d9f7a9b4f42d86

[Mahara Bot (dev-mahara)]

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

commit fabcf2bd29a38aecd49965766bfe1fd21c6b7d17
Author: Cecilia Vela Gurovic <email address hidden>
Date: Thu Oct 31 16:00:09 2019 +1300

Bug 1785985: remove prefix from db table before adding to ES queue

behatnotneeded

Change-Id: I860ebcc892361a7af6b2edeed2d9f7a9b4f42d86