What does the "dmlreadexception" error mean on Moodle live logs when I try to pull from an external db log? And any suggestions to fix?

Question

I am trying to use the external database log plugin in Moodle to copy over the standard log table into an external database for easier access to do some analytics work.

I activated the external db log and added all correct settings on the settings page. I clicked "test connection" and it connected successfully and returned the table column headers successfully. But if I click around and make some logs, they are visible in the standard log store but my external db table is still empty.

So I tried connecting to my external db locally in TablePlus using identical credentials as I put in the external db log store settings, and I could connect and write successfully.

Next I went into the live logs and picked standard logs, and they showed up just fine. Then I clicked on external db logs (nothing inside except for 2 manually entered rows of data), and got this error:

URL: https://ohsu.mrooms3.net/
Debug info: ERROR: syntax error at or near "{" LINE 1: SELECT COUNT('x') FROM {OpenLMSLog} WHERE courseid = $1 AND ... ^ SELECT COUNT('x') FROM {OpenLMSLog} WHERE courseid = $1 AND timecreated > $2 AND anonymous = $3 [array ( 0 => '1', 1 => 1604556625, 2 => 0, )] Error code: dmlreadexception
Stack trace:
 * line 486 of /lib/dml/moodle_database.php: dml_read_exception thrown
 * line 329 of /lib/dml/pgsql_native_moodle_database.php: call to moodle_database->query_end()
 * line 920 of /lib/dml/pgsql_native_moodle_database.php: call to pgsql_native_moodle_database->query_end()
 * line 1624 of /lib/dml/moodle_database.php: call to pgsql_native_moodle_database->get_records_sql()
 * line 1697 of /lib/dml/moodle_database.php: call to moodle_database->get_record_sql()
 * line 1912 of /lib/dml/moodle_database.php: call to moodle_database->get_field_sql()
 * line 1895 of /lib/dml/moodle_database.php: call to moodle_database->count_records_sql()
 * line 262 of /admin/tool/log/store/database/classes/log/store.php: call to moodle_database->count_records_select()
 * line 329 of /report/loglive/classes/table_log.php: call to logstore_database\log\store->get_events_select_count()
 * line 48 of /report/loglive/classes/table_log_ajax.php: call to report_loglive_table_log->query_db()
 * line 59 of /report/loglive/classes/renderer_ajax.php: call to report_loglive_table_log_ajax->out()
 * line 462 of /lib/outputrenderers.php: call to report_loglive_renderer_ajax->render_report_loglive()
 * line 53 of /report/loglive/loglive_ajax.php: call to plugin_renderer_base->render()

This is the only error message I get even after turning on debugging in the developer settings. My goal is to successfully configure an external db to track logs, but due to a lack of error messages when testing the connection it is hard to debug.

Environment configuration: Open LMS 3.8 MP2 (Build: 20201008) The external db is a postgres db so we set it on the postgres driver.

Answer 1

It looks like the SQL used by the external db log store plugin is not compatible with Postgres, despite using the Postgres driver in the external db log store plugin settings, as shown by the '{' error in the SQL shown in the question. See here for documentation.

To fix this, we used MariaDB instead of Postgres, and we ended up getting the external db log store working.

Another note, you have to match your columns and data types exactly to the schema in the Moodle db, and then you have to set the ID column of your db table to auto-increment. If you don't know what the schema looks like, there is an Admin SQL interface under "Reports" that lets you run SQL. On the side, there a button to view the schema for any table in the db.