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

Bug #1850786 reported by hermannkm on 2019-10-31
6
This bug affects 1 person
Affects Status Importance Assigned to Milestone
Mahara
Status tracked in 20.04
19.10
High
Unassigned
20.04
High
Cecilia Vela Gurovic

Bug Description

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.

hermannkm (h-klocker) on 2019-10-31
description: updated
description: updated
hermannkm (h-klocker) on 2019-11-01
description: updated
hermannkm (h-klocker) on 2019-11-01
description: updated
hermannkm (h-klocker) on 2019-11-02
description: updated
summary: - Upgrade script to 19.10 from 19.04.1 fails when converting artefact
- audio
+ Upgrade script to 19.10 from 19.04.1 fails when converting artifact
+ audio - elasticsearch triggers

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.

hermannkm (h-klocker) on 2019-11-02
summary: - Upgrade script to 19.10 from 19.04.1 fails when converting artifact
+ Upgrade script to 19.10 from 19.04.1 fails when converting artefact
audio - elasticsearch triggers
description: updated
Robert Lyon (robertl-9) wrote :

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

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

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

To post a comment you must log in.
This report contains Public information  Edit
Everyone can see this information.

Other bug subscribers