MS SQL support for async settings

[geobuc1]

Hi,

Moodle version 3.9 Build: 20200615. Plugin version report_coursesize 4.1 2021030807.

Not sure if this the appropriate place to raise issues like this (or on the plugin page better?) but getting this error when try to run scheduled task in CLI (\report_coursesize\task\report_async):

SQLState: 42000

Error Code: 102

Message: [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Incorrect syntax near 'rc'.

UPDATE mdl_report_coursesize rc
SET backupsize = (SELECT bf.filesize FROM (SELECT id AS course, SUM(filesize) AS filesize
FROM (SELECT c.id, f.filesize
FROM mdl_course c
JOIN mdl_context cx ON cx.contextlevel = 50 AND cx.instanceid = c.id
JOIN mdl_files f ON f.contextid = cx.id AND f.component = 'backup') x
GROUP BY id) bf WHERE bf.course = rc.course)
[array (
)]
Backtrace:

• line 324 of \lib\dml\sqlsrv_native_moodle_database.php: call to moodle_database->query_end()
• line 431 of \lib\dml\sqlsrv_native_moodle_database.php: call to sqlsrv_native_moodle_database->query_end()
• line 820 of \lib\dml\sqlsrv_native_moodle_database.php: call to sqlsrv_native_moodle_database->do_query()
• line 70 of \report\coursesize\classes\task\report_async.php: call to sqlsrv_native_moodle_database->execute()
• line 248 of \lib\cronlib.php: call to report_coursesize\task\report_async->execute()
• line 150 of \admin\cli\scheduled_task.php: call to cron_run_inner_scheduled_task()

Not sure if issue with driver? or version of plugin. I have tried this with a Dev Moodle version 3.11 also and get same message.

Any thoughts or advice would be greatly appreciated.

Regards

geobuc

[danmarsden]

Hi there,

Thanks for the report - that looks like an incompatibility with the sql being used to build that query.

You could try the calcmethod: "live" option in the plugin admin settings which might work for you depending on how large the site is that you are working with.

We don't reccommend using MS Sql for Moodle's back-end database, historically we've found the PHP driver for MS Sql to be buggy and pretty bad on performance compared to MySql and PostGres.

Unfortunately this also means we don't test any of our plugins using MS Sql - I'm happy to review a pull request if you would like to try and modify the code to work in MS Sql (and also continue functioning in PostGres and MySql) but MS Sql based support isn't something we spend any volunteer time on.

thanks!

[geobuc1]

Hi,
Thanks for the quick reply. I appreciate the fact you don't test\support MS SQL. MS not high on anyone's list in Moodle ecosystem! Unfortunately, we are on Windows\MS Sql environment for time being. Will take a look to see if can modify the code to work in MS Sql and get back with pull request. Thanks.

PS When you say 'You could try the calcmethod: "live" option' do you just mean change option in Plugins>reports>coursesize to Page Load rather than scheduled task? Sorry if I have misunderstood.

Thanks

[danmarsden]

the coursesize plugin allows 2 methods of building the report - one that runs on scheduled task (which is the query that is failing) the other allows the report to be built "live" which uses a slightly different query. in admin > plugins > reports > course size on the settings page for the "course size" report you can change the calcmethod on that page which might work ok for you.

[geobuc1]

Hi Dan, Thanks for the reply and information. I ran the report to be built 'live' on a DEV version, but the data folder is only a couple of months old, so pretty accurate in giving me the results that I needed. The workaround has given me the results that I needed for a one off report, so thanks for your help. Keep up the good work.

…

On Sun, May 1, 2022 at 10:45 PM Dan Marsden ***@***.***> wrote: the coursesize plugin allows 2 methods of building the report - one that runs on scheduled task (which is the query that is failing) the other allows the report to be built "live" which uses a slightly different query. in admin > plugins > reports > course size on the settings page for the "course size" report you can change the calcmethod on that page which might work ok for you. — Reply to this email directly, view it on GitHub <#39 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AD7ESEYEVEYFRMYB4SEA3KDVH33QBANCNFSM5USNUZWA> . You are receiving this because you authored the thread.Message ID: ***@***.***>