Postgres Syntax Error

Postgres Syntax Error

by Rudie IMM GSM -
Number of replies: 2

Hi All

We have recently decided to move to from MariaDB to Postgres as our Database.

I have setup a new Moodle instance version 3.9.2 Build: 20200914 and Postgres 12.4.

Everything seemed to work well with no issues, till today one of the Moodle Admins created a new user with a customized role, when attempting to log in as this user a "ERROR Reading from database" occurs.

In the Postgres Logs I can see the following. 

ERROR:  syntax error at or near "REGEXP" at character 173
STATEMENT:  SELECT count(ra.id) teacher FROM mdl_role_assignments ra
                  JOIN mdl_role r ON ra.roleid = r.id
                 WHERE ra.userid = $1
                   AND r.archetype REGEXP 'editingteacher|teacher'

Any advice as to what is causing this or how to fix this ?


Average of ratings: -
In reply to Rudie IMM GSM

Re: Postgres Syntax Error

by Leon Stringer -
Picture of Core developers Picture of Particularly helpful Moodlers

The operator REGEXP is specific to MySQL (or MariaDB) and doesn't work with PostgreSQL.

I'm pretty sure core Moodle won't be generating the statement in the error, are there any additional plugins installed on the site? Maybe one of these doesn't work (or doesn't work well) with PostgreSQL.

Also, try enabling debugging, hopefully there'll be more details on screen when the error occurs.

Average of ratings: -
In reply to Leon Stringer

Re: Postgres Syntax Error

by Rudie IMM GSM -

Hi Leon

Thanks for the reply, It was an external plugin I found literally 5Min ago. Going through all the plugins now to see if any others might have PGSql issues. Was just strange as it was happening right at login thought it might have been a Postgres version issue.

Average of ratings: -