Upgrade script to 19.10 from 19.04.1 fails when converting artefact audio - elasticsearch triggers

[hermannkm]

My upgrade script for 19.10 fails and leaves mahara in a unusable state.
The upgrade from cli stops with the following message(s) (see below).
*Update*: I managed to let the script finish and got my site up again by adding

            drop_elasticsearch_triggers();
            $funname($data);
            create_elasticsearch_triggers();

around Line 274 in lib/mahara.php. Now the output of my console reads

[INF] 5a (admin/cli/upgrade.php:70) Upgrading Mahara
[INF] 5a (lib/mahara.php:240) artefact.file wird aktualisiert
[DBG] 5a (lib/mahara.php:5625) Dropping elasticsearch triggers
[DBG] 5a (artefact/file/db/upgrade.php:514) Update m4a, m4b, m4p and m4r file types to be audio/mp4 mimetype
[INF] 5a (artefact/file/lib.php:236) Synced filetype list with filetypes.xml
[DBG] 5a (lib/mahara.php:5640) Adding elasticsearch triggers back in
[INF] 5a (lib/mahara.php:240) artefact.plans wird aktualisiert
[DBG] 5a (lib/mahara.php:5625) Dropping elasticsearch triggers
[DBG] 5a (lib/mahara.php:5640) Adding elasticsearch triggers back in
[INF] 5a (lib/mahara.php:240) auth.saml wird aktualisiert
[DBG] 5a (lib/mahara.php:5625) Dropping elasticsearch triggers
[DBG] 5a (lib/mahara.php:5640) Adding elasticsearch triggers back in

and everything seems to be ok.

Previous failure of script gave this output:
--------------------------------------------------- start of console
root@server:/var/www/mahara/htdocs/admin# sudo -u www-data php cli/upgrade.php -f
[INF] 96 (admin/cli/upgrade.php:70) Upgrading Mahara
[INF] 96 (lib/mahara.php:240) artefact.file wird aktualisiert
[DBG] 96 (artefact/file/db/upgrade.php:514) Update m4a, m4b, m4p and m4r file types to be audio/mp4 mimetype
[DBG] 96 (lib/dml.php:180) mysqli error: [1305: PROCEDURE u57370db5nd.search_elasticsearch_queue_artefact_trigger does not exist] in EXECUTE("UPDATE "artefact"
[DBG] 96 (lib/dml.php:180) SET artefacttype = 'audio'
[DBG] 96 (lib/dml.php:180) WHERE id IN (SELECT artefact FROM "artefact_file_files"
[DBG] 96 (lib/dml.php:180) WHERE oldextension IN ('m4a', 'm4b', 'm4p', 'm4r'));
[DBG] 96 (lib/dml.php:180) ")Command was: UPDATE "artefact"
[DBG] 96 (lib/dml.php:180) SET artefacttype = 'audio'
[DBG] 96 (lib/dml.php:180) WHERE id IN (SELECT artefact FROM "artefact_file_files"
[DBG] 96 (lib/dml.php:180) WHERE oldextension IN ('m4a', 'm4b', 'm4p', 'm4r'));
[DBG] 96 (lib/dml.php:180)
[WAR] 96 (lib/errors.php:859) Could not execute command: UPDATE "artefact"
[WAR] 96 (lib/errors.php:859) SET artefacttype = 'audio'
[WAR] 96 (lib/errors.php:859) WHERE id IN (SELECT artefact FROM "artefact_file_files"
[WAR] 96 (lib/errors.php:859) WHERE oldextension IN ('m4a', 'm4b', 'm4p', 'm4r'));
[WAR] 96 (lib/errors.php:859)
Call stack (most recent first):
  * log_message(string(size 261), integer, true, true) at /var/www/mahara-19.10.0/htdocs/lib/errors.php:95
  * log_warn(string(size 261)) at /var/www/mahara-19.10.0/htdocs/lib/errors.php:859
  * SQLException->__construct(string(size 261)) at /var/www/mahara-19.10.0/htdocs/lib/dml.php:181
  * execute_sql(string(size 234)) at /var/www/mahara-19.10.0/htdocs/artefact/file/db/upgrade.php:516
  * xmldb_artefact_file_upgrade(string(size 10)) at /var/www/mahara-19.10.0/htdocs/lib/upgrade.php:453
  * upgrade_plugin(object(stdClass)) at /var/www/mahara-19.10.0/htdocs/lib/mahara.php:274
  * upgrade_mahara(array(size 1)) at /var/www/mahara-19.10.0/htdocs/admin/cli/upgrade.php:74

[WAR] 96 (lib/dml.php:181) Could not execute command: UPDATE "artefact"
[WAR] 96 (lib/dml.php:181) SET artefacttype = 'audio'
[WAR] 96 (lib/dml.php:181) WHERE id IN (SELECT artefact FROM "artefact_file_files"
[WAR] 96 (lib/dml.php:181) WHERE oldextension IN ('m4a', 'm4b', 'm4p', 'm4r'));
[WAR] 96 (lib/dml.php:181)
Call stack (most recent first):
  * execute_sql(string(size 234)) at /var/www/mahara-19.10.0/htdocs/artefact/file/db/upgrade.php:516
  * xmldb_artefact_file_upgrade(string(size 10)) at /var/www/mahara-19.10.0/htdocs/lib/upgrade.php:453
  * upgrade_plugin(object(stdClass)) at /var/www/mahara-19.10.0/htdocs/lib/mahara.php:274
  * upgrade_mahara(array(size 1)) at /var/www/mahara-19.10.0/htdocs/admin/cli/upgrade.php:74

A nonrecoverable error occurred. This probably means you have encountered a bug in the systemroot@server:/var/www/mahara/htdoc
--------------------------------------------------- end of console

I am using:
PHP 7.3.11-1+ubuntu16.04.1
Mysql 10.1.41-MariaDB-1
Ubuntu 16.04.6 LTS (GNU/Linux 4.4.0-159-generic x86_64)

A short introspection into my database shows that there are three rows with old extension 'm4a' set to filetype 'video/mp4' which are selected from artefact_file_files.

[Kristina Hoeppner (kris-hoeppner)]

Thank you for the report, Hermann. This sounds like it's related to bug #1838483 where we introduced the embedding of .m4a. We'll take a look.

[Robert Lyon (robertl-9)]

Hi,
Looking at the line in the error info you posted

 mysqli error: [1305: PROCEDURE u57370db5nd.search_elasticsearch_queue_artefact_trigger does not exist] in EXECUTE("UPDATE "artefact"

It seems like you had a trigger on the 'artefact' table that pointed to a procedure that no longer existed.

In v19.10 we got rid of the elasticsearch triggers / procedures (instead handle things at PHP level).

So it sounds like procedure was removed before trigger during upgrade - and placing the drop_elasticsearch_triggers(); line in the code dropped them correctly

The create_elasticsearch_triggers(); doesn't actually do anything anymore as the ElasticsearchIndexing::create_triggers($type); also just calls drop_triggers()

Those function will be removed in a future release

[Mahara Bot (dev-mahara)]

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

[Mahara Bot (dev-mahara)]

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

commit de2d7fc4c357dac062ad57281806c57cb0272961
Author: Cecilia Vela Gurovic <email address hidden>
Date: Mon Nov 11 18:12:25 2019 +1300

Bug 1850786: remove plugin ES triggers

we need to remove the plugin ES triggers in lib/db/upgrade.php
when we remove the core triggers,
so they are not present on the DB when the plugins get upgraded

This is the same as calling drop_elasticsearch_triggers() but without
the searchplugin check - in case someone manually changed the search
type in db

behatnotneeded

Change-Id: I47df9cacfa69dd6f7532f570fe17b6a66e5d0fea

[Mahara Bot (dev-mahara)]

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

[Mahara Bot (dev-mahara)]

Reviewed: https://reviews.mahara.org/10596
Committed: https://git.mahara.org/mahara/mahara/commit/c29f5745ce71593326b494a33fb01c74a0e7a9f0
Submitter: Robert Lyon (<email address hidden>)
Branch: 19.10_STABLE

commit c29f5745ce71593326b494a33fb01c74a0e7a9f0
Author: Cecilia Vela Gurovic <email address hidden>
Date: Mon Nov 11 18:12:25 2019 +1300

Bug 1850786: remove plugin ES triggers

we need to remove the plugin ES triggers in lib/db/upgrade.php
when we remove the core triggers,
so they are not present on the DB when the plugins get upgraded

This is the same as calling drop_elasticsearch_triggers() but without
the searchplugin check - in case someone manually changed the search
type in db

behatnotneeded

Change-Id: I47df9cacfa69dd6f7532f570fe17b6a66e5d0fea