Ad-hoc database queries

I don't need that. You need to work out why files are disappearing from your server's moodledata folder.

Hi Tim,
Do you require any additional logs or server details?
Paul

The $ may just be Apache (unhelpfully) truncating the lines in the error log to a fixed lenght.

Hi Tim,
The moodle site is setup on a Ubuntu 16.04 LTS server. There is ample free space on the server with over 100GB provided of which we are using only about 19GB at the moment.
The Moodledata folder is in the path /var/moodledata. The permissions are set at 777 for the parent and subfolders.
When I examined the path referenced in the line [Mon Sep 07 11:09:56.152745 2020] [php7:warn] [pid 4674] [client 192.168.1.70:47480] PHP Warning: fopen(/var/moodledata/admin_report_customsql/temp/114/20200907-110951.csv): failed to open stream: No such file or directory in /var/www$, I was able to confirm that the path /var/moodledata/admin_report_customsql/temp and the file 20200807-110951.csv do exist. However, I am not sure where /var/www$ is coming from....
Thank you for your assistance on this issue!
Paul

So, the error is coming from here: https://github.com/moodleou/moodle-report_customsql/blob/v3.9/download.php#L49. So, it is failing to find the CSV data for the report on disc. How is your Moodle site set up? What sort of storage is used for the moodledata folder?

Hi Tim,
From /var/log/apache2/error.log:
[Mon Sep 07 06:25:02.736876 2020] [mpm_prefork:notice] [pid 16451] AH00163: Apache/2.4.18 (Ubuntu) OpenSSL/1.0.2g configured -- resuming normal operations
[Mon Sep 07 06:25:02.736915 2020] [core:notice] [pid 16451] AH00094: Command line: '/usr/sbin/apache2'
[Mon Sep 07 11:04:50.717559 2020] [php7:notice] [pid 4677] [client 192.168.1.70:47036] PHP Notice: Invalid get_string() identifier: 'fontsans' or component 'mod_certificate'. Perhaps you are missing $string['fontsans'] = ''; in mod/ce$
[Mon Sep 07 11:04:50.717617 2020] [php7:notice] [pid 4677] [client 192.168.1.70:47036] PHP Notice: Invalid get_string() identifier: 'fontsans_desc' or component 'mod_certificate'. Perhaps you are missing $string['fontsans_desc'] = '';$
[Mon Sep 07 11:04:50.717646 2020] [php7:notice] [pid 4677] [client 192.168.1.70:47036] PHP Notice: Invalid get_string() identifier: 'fontserif' or component 'mod_certificate'. Perhaps you are missing $string['fontserif'] = ''; in mod/$
[Mon Sep 07 11:04:50.717668 2020] [php7:notice] [pid 4677] [client 192.168.1.70:47036] PHP Notice: Invalid get_string() identifier: 'fontserif_desc' or component 'mod_certificate'. Perhaps you are missing $string['fontserif_desc'] = '$
[Mon Sep 07 11:05:21.130167 2020] [php7:warn] [pid 4673] [client 192.168.1.70:47102] PHP Warning: fopen(/var/moodledata/admin_report_customsql/temp/114/20200907-110508.csv): failed to open stream: No such file or directory in /var/www$
[Mon Sep 07 11:05:21.130575 2020] [php7:notice] [pid 4673] [client 192.168.1.70:47102] Default exception handler: Unknown download file. Debug: \nError code: unknowndownloadfile\n* line 482 of /lib/setuplib.php: moodle_exception thrown$
[Mon Sep 07 11:09:24.954536 2020] [php7:notice] [pid 4672] [client 192.168.1.70:47418] PHP Notice: Invalid get_string() identifier: 'fontsans' or component 'mod_certificate'. Perhaps you are missing $string['fontsans'] = ''; in mod/ce$
[Mon Sep 07 11:09:24.954615 2020] [php7:notice] [pid 4672] [client 192.168.1.70:47418] PHP Notice: Invalid get_string() identifier: 'fontsans_desc' or component 'mod_certificate'. Perhaps you are missing $string['fontsans_desc'] = '';$
[Mon Sep 07 11:09:24.954645 2020] [php7:notice] [pid 4672] [client 192.168.1.70:47418] PHP Notice: Invalid get_string() identifier: 'fontserif' or component 'mod_certificate'. Perhaps you are missing $string['fontserif'] = ''; in mod/$
[Mon Sep 07 11:09:24.954666 2020] [php7:notice] [pid 4672] [client 192.168.1.70:47418] PHP Notice: Invalid get_string() identifier: 'fontserif_desc' or component 'mod_certificate'. Perhaps you are missing $string['fontserif_desc'] = '$
[Mon Sep 07 11:09:31.032453 2020] [php7:notice] [pid 4677] [client 192.168.1.70:47426] PHP Notice: Invalid get_string() identifier: 'fontsans' or component 'mod_certificate'. Perhaps you are missing $string['fontsans'] = ''; in mod/ce$
[Mon Sep 07 11:09:31.032507 2020] [php7:notice] [pid 4677] [client 192.168.1.70:47426] PHP Notice: Invalid get_string() identifier: 'fontsans_desc' or component 'mod_certificate'. Perhaps you are missing $string['fontsans_desc'] = '';$
[Mon Sep 07 11:09:31.032536 2020] [php7:notice] [pid 4677] [client 192.168.1.70:47426] PHP Notice: Invalid get_string() identifier: 'fontserif' or component 'mod_certificate'. Perhaps you are missing $string['fontserif'] = ''; in mod/$
[Mon Sep 07 11:09:31.032557 2020] [php7:notice] [pid 4677] [client 192.168.1.70:47426] PHP Notice: Invalid get_string() identifier: 'fontserif_desc' or component 'mod_certificate'. Perhaps you are missing $string['fontserif_desc'] = '$
[Mon Sep 07 11:09:56.152745 2020] [php7:warn] [pid 4674] [client 192.168.1.70:47480] PHP Warning: fopen(/var/moodledata/admin_report_customsql/temp/114/20200907-110951.csv): failed to open stream: No such file or directory in /var/www$
[Mon Sep 07 11:09:56.153032 2020] [php7:notice] [pid 4674] [client 192.168.1.70:47480] Default exception handler: Unknown download file. Debug: \nError code: unknowndownloadfile\n* line 482 of /lib/setuplib.php: moodle_exception thrown$
Paul

Hi Tim,
I activated Developer Mode and received the following details:
Debug info:
Error code: unknowndownloadfile
×Stack trace:
line 482 of /lib/setuplib.php: moodle_exception thrown
line 50 of /report/customsql/download.php: call to print_error()
Does this provide sufficient clues, or do I need to find details elsewhere?
Paul

You need to find what the error really is. Where are you seeing that error message? In the web browser, or somewhere else?
Try turning https://docs.moodle.org/en/Debugging up to full.
If you are getting some sort of file downloaded, try opening it in a plain-text editor, to see what is really in there.

I have tried to download the results from some of the contributed reports as a comma separated value (.csv) file, but get a general errror : unknown download file.
The same error occurs when I select any other download format.
Any guidance would be much appreciated.
Cheers!
Paul

Hi, I'm new to moodle 3.9 internals after moving from 1.9 version. I made there a quick hack by copying and twicking download.php to have a feature of public download of report csv file restricted by IP address (done .httaccess) using a simple GET query. Now I need this feature to the new instance. I can just copy and addapt old hacked file or other simply way without ugly hacking is just using customdir export feture and there some short index.php script, like below one and .htaccess.
------------------------------- <?php
// report id
$id = (int)($_GET['id'] ?? 0);
// lastone or date, default - currend day
$lastone = isset($_GET['lastone']) ? true : false;
$date = (int)($_GET['date'] ?? date('Ymd'));
$pattern = sprintf('%s-%s*.csv', $id, $lastone ? '' : $date.'-');
if(!$files = glob($pattern)) {
header("HTTP/1.0 404 Not Found");
exit($pattern.': files not found!'.PHP_EOL);
}
$file = end($files);
header('Content-Disposition: attachment; filename="'.$file.'"');
header('Content-Type: text/csv; charset=UTF-8');
readfile($file);
--------------------------------------------------
But I am wondering is it a way to do it better using some new/ native stuff of 3.9 version, maybe webservices or just simple extention of plugin by adding new options in edit_form.php and adjusting download.php (?). After all if this feature is considered usefull I can do some contrib to the plugin. Any advice what would be the finest way to achive this?
--
regards
Pawel Suwinski

Excelent!!!

Hi Tim,
Just FYI I figured it out and made it work, by a minor change:
In locallib.php send_email_notification function:
changed $eventdata = new stdClass() to $eventdata = new core/message/message()
Thanks.

Hi Tim,
Thanks for pointing that out. We did find the errors of value on the table, and once they are corrected the Scheduled Tasks page shows all the entries.
My question is still with the Ad-hoc report itself:
For the ‘Automatically email to’ field, if enter an email, it prompts with message of User with the username 'lehuang@ssw.rutgers.edu' does not exist
If enter an username, and ran the corn job, below error was received:
“Exception - Argument 1 passed to message_send() must be an instance of core\message\message…”
We have Moodle 3.6.2. Thanks.

That page should list a lot of tasks, including report_customsql / run_reports. If you don't see the task there, there must be some problem with how Moodle installed the plugin. I have never seen that page be blank.
... Ah! if that page is blank, then perhaps a fatal error is happening. Check your logs, perhaps in conunction with turning on Debugging.

Thanks Tim for your quick response! Under Admin / server / Scheduled tasks, nothing is there, no entries. Is there any other place the tasks should be set?
On the DB table mdl_report_customsql_queries, I do see the report with runable 'daily' and time etc.