moodleou/moodle-mod_oublog

provider::delete_data_for_user() doesn't work on MySQL

micaherne opened this issue · 2 comments

micaherne commented

The \mod_oublog\privacy\provider::delete_data_for_user() method constructs a DELETE query here which is not valid in MySQL. It gives the error message "Error writing to database (You can't specify target table 'p_oublog_comments' for update in FROM clause)"

The constructed SQL is like this:

DELETE FROM p_oublog_comments WHERE id IN (
SELECT bc.id AS commentid
FROM p_oublog_comments bc
WHERE bc.postid IN (
SELECT bp.id AS postid
FROM p_course_modules cm
JOIN p_oublog b ON b.id = cm.instance
JOIN p_oublog_instances bi ON bi.oublogid = b.id
JOIN p_oublog_posts bp ON bp.oubloginstancesid = bi.id
WHERE cm.id = ? AND bi.userid = ?))

and MySQL doesn't allow using the table you're deleting from in the WHERE clause.

rlorenzo commented

Came to report the same issues when I try to run the latest plugin on MariaDB:

root@627f97625161:/var/www/html# vendor/bin/phpunit mod/oublog/tests/privacy_provider_test.php
Moodle 3.9dev+ (Build: 20200529), d04ee8221613060ed6a5b08203c3dcf485ba49b9
Php: 7.3.17, mariadb: 10.2.31, OS: Linux 4.19.76-linuxkit x86_64
PHPUnit 7.5.20 by Sebastian Bergmann and contributors.

.........E.....EEEEE 20 / 20 (100%)

Time: 55.42 seconds, Memory: 89.00 MB

There were 6 errors:

  1. mod_oublog\privacy_provider_testcase::test_delete_data_for_user
    dml_write_exception: Error writing to database (Table 't_oublog_comments' is specified twice, both as a target for 'DELETE' and as a separate source for data
    DELETE FROM t_oublog_comments WHERE id IN (
    SELECT bc.id AS commentid
    FROM t_oublog_comments bc
    WHERE bc.postid IN (
    SELECT bp.id AS postid
    FROM t_course_modules cm
    JOIN t_oublog b ON b.id = cm.instance
    JOIN t_oublog_instances bi ON bi.oublogid = b.id
    JOIN t_oublog_posts bp ON bp.oubloginstancesid = bi.id
    WHERE cm.id = ? AND bi.userid = ?))
    [array (
    0 => '157000',
    1 => '160000',
    )])

/var/www/html/lib/dml/moodle_database.php:489
/var/www/html/lib/dml/mysqli_native_moodle_database.php:1686
/var/www/html/mod/oublog/classes/privacy/provider.php:638
/var/www/html/mod/oublog/tests/privacy_provider_test.php:667
/var/www/html/lib/phpunit/classes/advanced_testcase.php:80

To re-run:
vendor/bin/phpunit "mod_oublog\privacy_provider_testcase" mod/oublog/tests/privacy_provider_test.php

  1. mod_oublog\privacy_provider_testcase::test_delete_data_for_users_oublog_post
    dml_write_exception: Error writing to database (Table 't_rating' is specified twice, both as a target for 'DELETE' and as a separate source for data
    DELETE FROM t_rating WHERE contextid = ? AND component = ? AND ratingarea = ? AND itemid IN (SELECT itemid FROM t_rating WHERE userid IN (?,?))
    [array (
    0 => 153001,
    1 => 'mod_oublog',
    2 => 'post',
    3 => '160000',
    4 => '160001',
    )])

/var/www/html/lib/dml/moodle_database.php:489
/var/www/html/lib/dml/mysqli_native_moodle_database.php:1686
/var/www/html/rating/classes/privacy/provider.php:231
/var/www/html/mod/oublog/classes/privacy/provider.php:905
/var/www/html/mod/oublog/tests/privacy_provider_test.php:1066
/var/www/html/lib/phpunit/classes/advanced_testcase.php:80

To re-run:
vendor/bin/phpunit "mod_oublog\privacy_provider_testcase" mod/oublog/tests/privacy_provider_test.php

  1. mod_oublog\privacy_provider_testcase::test_delete_data_for_users_oublog_links
    dml_write_exception: Error writing to database (Table 't_rating' is specified twice, both as a target for 'DELETE' and as a separate source for data
    DELETE FROM t_rating WHERE contextid = ? AND component = ? AND ratingarea = ? AND itemid IN (SELECT itemid FROM t_rating WHERE userid IN (?,?))
    [array (
    0 => 153001,
    1 => 'mod_oublog',
    2 => 'post',
    3 => '160000',
    4 => '160001',
    )])

/var/www/html/lib/dml/moodle_database.php:489
/var/www/html/lib/dml/mysqli_native_moodle_database.php:1686
/var/www/html/rating/classes/privacy/provider.php:231
/var/www/html/mod/oublog/classes/privacy/provider.php:905
/var/www/html/mod/oublog/tests/privacy_provider_test.php:1108
/var/www/html/lib/phpunit/classes/advanced_testcase.php:80

To re-run:
vendor/bin/phpunit "mod_oublog\privacy_provider_testcase" mod/oublog/tests/privacy_provider_test.php

  1. mod_oublog\privacy_provider_testcase::test_delete_data_for_users_oublog_comments
    dml_write_exception: Error writing to database (Table 't_rating' is specified twice, both as a target for 'DELETE' and as a separate source for data
    DELETE FROM t_rating WHERE contextid = ? AND component = ? AND ratingarea = ? AND itemid IN (SELECT itemid FROM t_rating WHERE userid IN (?,?))
    [array (
    0 => 153001,
    1 => 'mod_oublog',
    2 => 'post',
    3 => '160000',
    4 => '160001',
    )])

/var/www/html/lib/dml/moodle_database.php:489
/var/www/html/lib/dml/mysqli_native_moodle_database.php:1686
/var/www/html/rating/classes/privacy/provider.php:231
/var/www/html/mod/oublog/classes/privacy/provider.php:905
/var/www/html/mod/oublog/tests/privacy_provider_test.php:1147
/var/www/html/lib/phpunit/classes/advanced_testcase.php:80

To re-run:
vendor/bin/phpunit "mod_oublog\privacy_provider_testcase" mod/oublog/tests/privacy_provider_test.php

  1. mod_oublog\privacy_provider_testcase::test_delete_data_for_users_oublog_ratings
    dml_write_exception: Error writing to database (Table 't_rating' is specified twice, both as a target for 'DELETE' and as a separate source for data
    DELETE FROM t_rating WHERE contextid = ? AND component = ? AND ratingarea = ? AND itemid IN (SELECT itemid FROM t_rating WHERE userid IN (?,?))
    [array (
    0 => 153001,
    1 => 'mod_oublog',
    2 => 'post',
    3 => '160000',
    4 => '160001',
    )])

/var/www/html/lib/dml/moodle_database.php:489
/var/www/html/lib/dml/mysqli_native_moodle_database.php:1686
/var/www/html/rating/classes/privacy/provider.php:231
/var/www/html/mod/oublog/classes/privacy/provider.php:905
/var/www/html/mod/oublog/tests/privacy_provider_test.php:1191
/var/www/html/lib/phpunit/classes/advanced_testcase.php:80

To re-run:
vendor/bin/phpunit "mod_oublog\privacy_provider_testcase" mod/oublog/tests/privacy_provider_test.php

  1. mod_oublog\privacy_provider_testcase::test_delete_data_for_users_oublog_edits
    dml_write_exception: Error writing to database (Table 't_rating' is specified twice, both as a target for 'DELETE' and as a separate source for data
    DELETE FROM t_rating WHERE contextid = ? AND component = ? AND ratingarea = ? AND itemid IN (SELECT itemid FROM t_rating WHERE userid IN (?,?))
    [array (
    0 => 153001,
    1 => 'mod_oublog',
    2 => 'post',
    3 => '160000',
    4 => '160001',
    )])

/var/www/html/lib/dml/moodle_database.php:489
/var/www/html/lib/dml/mysqli_native_moodle_database.php:1686
/var/www/html/rating/classes/privacy/provider.php:231
/var/www/html/mod/oublog/classes/privacy/provider.php:905
/var/www/html/mod/oublog/tests/privacy_provider_test.php:1234
/var/www/html/lib/phpunit/classes/advanced_testcase.php:80

To re-run:
vendor/bin/phpunit "mod_oublog\privacy_provider_testcase" mod/oublog/tests/privacy_provider_test.php

ERRORS!
Tests: 20, Assertions: 158, Errors: 6.

jason-platts commented

Fixed in 7969101