Unit test failures in mySQL and php 7.4 moodle 3.9 - cron_task_test.php

[kristian-94]

root@a5c379bd43f6:/siteroot# vendor/bin/phpunit "cron_task_test" mod/oublog/tests/cron_task_test.php
Moodle 3.9.3+ (Build: 20201121), 96286b2e712f3944a7458efcfdad5b7247d54ed3
Php: 7.4.3, mysqli: 5.7.32, OS: Linux 5.4.0-53-generic x86_64
PHPUnit 7.5.20 by Sebastian Bergmann and contributors.

EE.                                                                 3 / 3 (100%)

Time: 32.15 seconds, Memory: 78.50 MB

There were 2 errors:

1) cron_task_test::test_cron_task
dml_write_exception: Error writing to database (You can't specify target table 'phpu_oublog_comments' for update in FROM clause
DELETE FROM phpu_oublog_comments WHERE id IN (
                                SELECT bc.id AS commentid
                                  FROM phpu_oublog_comments bc
                                 WHERE bc.postid IN (?))
[array (
  0 => '455000',
)])

/siteroot/lib/dml/moodle_database.php:489
/siteroot/lib/dml/mysqli_native_moodle_database.php:1686
/siteroot/mod/oublog/classes/task/cron_task.php:77
/siteroot/mod/oublog/tests/cron_task_test.php:128
/siteroot/lib/phpunit/classes/advanced_testcase.php:80

To re-run:
 vendor/bin/phpunit "cron_task_test" mod/oublog/tests/cron_task_test.php

2) cron_task_test::test_cron_with_many_data
dml_write_exception: Error writing to database (You can't specify target table 'phpu_oublog_comments' for update in FROM clause
DELETE FROM phpu_oublog_comments WHERE id IN (
                                SELECT bc.id AS commentid
                                  FROM phpu_oublog_comments bc
                                 WHERE bc.postid IN (?))
[array (
  0 => '455000',
)])

/siteroot/lib/dml/moodle_database.php:489
/siteroot/lib/dml/mysqli_native_moodle_database.php:1686
/siteroot/mod/oublog/classes/task/cron_task.php:77
/siteroot/mod/oublog/tests/cron_task_test.php:190
/siteroot/lib/phpunit/classes/advanced_testcase.php:80

To re-run:
 vendor/bin/phpunit "cron_task_test" mod/oublog/tests/cron_task_test.php

ERRORS!
Tests: 3, Assertions: 10, Errors: 2.

This sql here: https://github.com/moodleou/moodle-mod_oublog/blob/master/classes/task/cron_task.php#L77 Will not work with mysql. MySQL doesn't allow using the table you're deleting from in the WHERE clause.

We should use get_in_or_equal

[sammarshallou]

There is an API $DB->delete_with_subquery in supported Moodle point versions which should probably be used for these (although the slow get-id-list-then-delete-it approach still also works obviosuly). The new API works properly on Postgres and in a weird way on mysql.