Closed micaherne closed 3 years ago
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
2) 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
3) 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
4) 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
5) 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
6) 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.
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.