Error Deleting External App - Foreign Key Constraint

Bug #1808935 reported by Ghada El-Zoghbi on 2018-12-18
6
This bug affects 1 person
Affects Status Importance Assigned to Milestone
Mahara
High
Cecilia Vela Gurovic
18.10
High
Unassigned
19.04
High
Cecilia Vela Gurovic

Bug Description

Mahara: 18.10.0
OS: Linux
DB: Postgres
Browser: FF

I created an LTI integration with a vanilla Moodle 3.5 and set it up as an external app.

I did log in with it from Moodle with an assignment. I think decided to delete the app. I get the following error:

[WAR] ff (lib/errors.php:858) Failed to get a recordset: postgres8 error: [-1: ERROR: update or delete on table "oauth_server_registry" violates foreign key constraint "ltiasse_oau_fk" on table "lti_assessment"
DETAIL: Key (id)=(1) is still referenced from table "lti_assessment".] in EXECUTE("
                    DELETE FROM "oauth_server_registry"
                    WHERE consumer_key = ?
                      AND (userid = ? OR userid IS NULL)
                    ")Command was:
                    DELETE FROM "oauth_server_registry"
                    WHERE consumer_key = ?
                      AND (userid = ? OR userid IS NULL)
                    and values was (0:96a91f9a865716feca5030a456f7154505c131e2d,1:45349)
Call stack (most recent first):

    log_message("Failed to get a recordset: postgres8 error: [-1: E...", 8, true, true) at /var/www/var/www/mahara/htdocs/lib/errors.php:95
    log_warn("Failed to get a recordset: postgres8 error: [-1: E...") at /var/www/var/www/mahara/htdocs/lib/errors.php:858
    SQLException->__construct("Failed to get a recordset: postgres8 error: [-1: E...") at /var/www/var/www/mahara/htdocs/lib/dml.php:1025
    delete_records_sql(" DELETE FROM "oauth_server_reg...", array(size 2)) at /var/www/var/www/mahara/htdocs/webservice/libs/oauth-php/store/OAuthStoreMahara.php:156
    OAuthStoreMahara->deleteServer("96a91f9a865716feca5030a456f7154505c131e2d", "45349", true) at /var/www/var/www/mahara/htdocs/webservice/admin/oauthv1sregister.php:120
    webservices_server_submit(object(Pieform), array(size 4)) at /var/www/var/www/mahara/htdocs/lib/pieforms/pieform.php:542
    Pieform->__construct(array(size 7)) at /var/www/var/www/mahara/htdocs/lib/pieforms/pieform.php:164
    Pieform::process(array(size 7)) at /var/www/var/www/mahara/htdocs/lib/mahara.php:5258
    pieform(array(size 7)) at /var/www/var/www/mahara/htdocs/webservice/admin/oauthv1sregister.php:469
    webservice_server_list_form(array(size 9), array(size 3)) at /var/www/var/www/mahara/htdocs/webservice/admin/oauthv1sregister.php:58

[WAR] ff (lib/dml.php:1025) Failed to get a recordset: postgres8 error: [-1: ERROR: update or delete on table "oauth_server_registry" violates foreign key constraint "ltiasse_oau_fk" on table "lti_assessment"
DETAIL: Key (id)=(1) is still referenced from table "lti_assessment".] in EXECUTE("
                    DELETE FROM "oauth_server_registry"
                    WHERE consumer_key = ?
                      AND (userid = ? OR userid IS NULL)
                    ")Command was:
                    DELETE FROM "oauth_server_registry"
                    WHERE consumer_key = ?
                      AND (userid = ? OR userid IS NULL)
                    and values was (0:96a91f9a865716feca5030a456f7154505c131e2d,1:45349)
Call stack (most recent first):

    delete_records_sql(" DELETE FROM "oauth_server_reg...", array(size 2)) at /var/www/var/www/mahara/htdocs/webservice/libs/oauth-php/store/OAuthStoreMahara.php:156
    OAuthStoreMahara->deleteServer("96a91f9a865716feca5030a456f7154505c131e2d", "45349", true) at /var/www/var/www/mahara/htdocs/webservice/admin/oauthv1sregister.php:120
    webservices_server_submit(object(Pieform), array(size 4)) at /var/www/var/www/mahara/htdocs/lib/pieforms/pieform.php:542
    Pieform->__construct(array(size 7)) at /var/www/var/www/mahara/htdocs/lib/pieforms/pieform.php:164
    Pieform::process(array(size 7)) at /var/www/var/www/mahara/htdocs/lib/mahara.php:5258
    pieform(array(size 7)) at /var/www/var/www/mahara/htdocs/webservice/admin/oauthv1sregister.php:469
    webservice_server_list_form(array(size 9), array(size 3)) at /var/www/var/www/mahara/htdocs/webservice/admin/oauthv1sregister.php:58

The delete function needs to also remove records from the lti_assessment table due to the foreign key constraint.

But, I'm not sure if that is the correct way to go. If there are assignments, do we want to keep them and perhaps disable the external app?

To delete, add the following to /htdocs/webservice/admin.oauthv1sregister.php at line 120:

delete_records_sql('DELETE FROM {lti_assessment} WHERE oauthserver IN (SELECT id FROM {oauth_server_registry} WHERE consumer_key = ?)', array($dbserver->consumer_key));

description: updated
Changed in mahara:
status: New → Triaged
importance: Undecided → High

When we delete a group, we delete the records in 'lti_assessment' if there was any lti submissions to the group. The same when deleting a collection.

I think we should also delete the 'lti_assessment' records in this case.

I made a patch with Ghada as the author for this solution:

  To delete, add the following to /htdocs/webservice/admin.oauthv1sregister.php at line 120:

  delete_records_sql('DELETE FROM {lti_assessment} WHERE oauthserver IN (SELECT id FROM {oauth_server_registry} WHERE consumer_key = ?)', array($dbserver->consumer_key));

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

commit f994c8629fd5b52bc3054b07d2ad8c34a1d66245
Author: Ghada El-Zoghbi <email address hidden>
Date: Wed Mar 13 11:02:51 2019 +1300

Bug 1808935: Error Deleting External App - Foreign Key Constraint

behatnotneeded

Change-Id: Ifb11779659e707753ab5305684a4f1281f03931f

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

commit 73ca19ddc1a2955943bbe14f45005c7895c72a1e
Author: Ghada El-Zoghbi <email address hidden>
Date: Wed Mar 13 11:02:51 2019 +1300

Bug 1808935: Error Deleting External App - Foreign Key Constraint

behatnotneeded

Change-Id: Ifb11779659e707753ab5305684a4f1281f03931f
(cherry picked from commit f994c8629fd5b52bc3054b07d2ad8c34a1d66245)

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

Other bug subscribers