Skip to content

dmlreadexception with PostgreSQL accessing reports #78

@schubling

Description

@schubling

After updating to the latest version of the OpenAI Chat block plugin (2025012300) on Moodle 4.4.2+, we encounter a DML read exception error when accessing the report page. This issue did not occur in previous versions of the plugin.

`
Database read error

More information about this error
Debug Info ERROR: Column 'ocl.timecreated' must appear in GROUP BY clause or must be used in an aggregate function
LINE 4: ...HERE c.contextlevel = 50 AND co.id = 169 ORDER BY ocl.timecr...
^
SELECT COUNT(1) FROM mdl_block_openai_chat_log ocl
JOIN mdl_user u ON u.id = ocl.userid
JOIN mdl_context c ON c.id = ocl.contextid
LEFT JOIN mdl_course co ON co.id = c.instanceid WHERE c.contextlevel = 50 AND co.id = 169 ORDER BY ocl.timecreated DESC
[array (
)]
Error code: dmlreadexception
Stack trace

line 494 of /lib/dml/moodle_database.php: dml_read_exception thrown
line 293 of /lib/dml/moodle_read_slave_trait.php: call to moodle_database->query_end()
line 358 of /lib/dml/pgsql_native_moodle_database.php: call to pgsql_native_moodle_database->read_slave_query_end()
line 1044 of /lib/dml/pgsql_native_moodle_database.php: call to pgsql_native_moodle_database->query_end()
line 1684 of /lib/dml/moodle_database.php: call to pgsql_native_moodle_database->get_records_sql()
line 1757 of /lib/dml/moodle_database.php: call to moodle_database->get_record_sql()
line 1982 of /lib/dml/moodle_database.php: call to moodle_database->get_field_sql()
line 2150 of /lib/tablelib.php: call to moodle_database->count_records_sql()
line 2205 of /lib/tablelib.php: call to table_sql->query_db()
line 111 of /blocks/openai_chat/report.php: call to table_sql->out()

`
If I comment out the following lines in report.php, the error disappears (but obviously removes the default sort):

if (!$tsort) { // $where .= " ORDER BY ocl.timecreated DESC"; }

It looks like ORDER BY ocl.timecreated DESC is included in the count query, which PostgreSQL does not allow unless the column is part of an aggregate or in the GROUP BY clause. Removing ORDER BY fixes the error, but we lose the default ordering. Ideally, the count query and the main data query should be handled separately (i.e., the count query should not include ORDER BY).

Thank you for your support!

Metadata

Metadata

Assignees

No one assigned

    Labels

    No labels
    No labels

    Projects

    No projects

    Milestone

    No milestone

    Relationships

    None yet

    Development

    No branches or pull requests

    Issue actions