Problems with Dataform in Moodle 2.6.2 on PostgresSQL

Problems with Dataform in Moodle 2.6.2 on PostgresSQL

by Paul K -
Number of replies: 7

Hi,

I'm getting a very strange SQL query error, when I'm trying to edit previously saved data in the view. It does not matter, whether I'm doing this as an administrator or as a course participant. The error message is in German, but it means nothing else as a "Column "e.id" needs to be included in the Group-By clause or in an aggregate function. System configuration: Windows 2012 R2, IIS, PHP 5.5.10, PostgresSQL 9.3.2, Moodle 2.6.2 (Build 24.04.2014).

I will be really grateful for any suggestion.

//////

Debug info: FEHLER: Spalte „e.id“ muss in der GROUP-BY-Klausel erscheinen oder in einer Aggregatfunktion verwendet werden
LINE 3: ... WHERE e.dataid = $1 AND e.id = $2 ORDER BY e.id ASC
^
SELECT COUNT(e.id) FROM mdl_dataform_entries e
JOIN mdl_user u ON u.id = e.userid
LEFT JOIN mdl_groups g ON g.id = e.groupid WHERE e.dataid = $1 AND e.id = $2 ORDER BY e.id ASC
[array (
0 => '3',
1 => '11',
)]
Error code: dmlreadexception

 

Stack trace:
  • line 443 of \lib\dml\moodle_database.php: dml_read_exception thrown
  • line 243 of \lib\dml\pgsql_native_moodle_database.php: call to moodle_database->query_end()
  • line 748 of \lib\dml\pgsql_native_moodle_database.php: call to pgsql_native_moodle_database->query_end()
  • line 313 of \mod\dataform\classes\entry_manager.php: call to pgsql_native_moodle_database->get_records_sql()
  • line 131 of \mod\dataform\classes\entry_manager.php: call to mod_dataform_entry_manager->fetch_entries()
  • line 316 of \mod\dataform\classes\pluginbase\dataformview.php: call to mod_dataform_entry_manager->set_content()
  • line 390 of \mod\dataform\classes\dataform.php: call to mod_dataform\pluginbase\dataformview->display()
  • line 71 of \mod\dataform\view.php: call to mod_dataform_dataform->display()
Average of ratings: -
In reply to Paul K

Re: Problems with Dataform in Moodle 2.6.2 on PostgresSQL

by Itamar Tzadok -

Interestingly, the next mini-minor (~ weekly) release of the Dataform (today or tomorrow) includes an unrelated fix (CONTRIB-5009) which removes the query which seems to be the cause of the problem you're getting. Please upgrade when released and let me know if the error persists. smile

Average of ratings: -
In reply to Itamar Tzadok

Re: Problems with Dataform in Moodle 2.6.2 on PostgresSQL

by Paul K -

Great, thanks for reply. First of all this is really a fantastic extension. But coming back to the issue.

This seems to be an overall problem with the SQL Group-By clause, as I have made some minor changes in the php code in the meantime, and I was able to partially solve the above mentioned issue. Nevertheless, I have currently identical problem with the query located in the entry_manager.php file:

Debug info: FEHLER: Spalte „u.id“ muss in der GROUP-BY-Klausel erscheinen oder in einer Aggregatfunktion verwendet werden
LINE 1: ....timecreated, e.timemodified, e.userid, e.groupid,u.id AS ui...
^
SELECT DISTINCT e.id, e.dataid, e.state, e.timecreated, e.timemodified, e.userid, e.groupid,u.id AS uid ,u.picture,u.firstname,u.lastname,u.firstnamephonetic,u.lastnamephonetic,u.middlename,u.alternatename,u.imagealt,u.email,u.idnumber,u.username,g.idnumber AS groupidnumber, g.name AS groupname, g.hidepicture AS grouphidepic, g.picture AS grouppic FROM mdl_dataform_entries e
JOIN mdl_user u ON u.id = e.userid
LEFT JOIN mdl_groups g ON g.id = e.groupid WHERE e.dataid = $1 GROUP BY e.id
[array (
0 => '3',
)]
Error code: dmlreadexception

 

It seems that PostgreSQL at least version 9.3 have some problems with the Group By clause. I will check this directly after getting the "mini-minor" release.

Average of ratings: -
In reply to Paul K

Re: Problems with Dataform in Moodle 2.6.2 on PostgresSQL

by Itamar Tzadok -

Not sure where

LEFT JOIN mdl_groups g ON g.id = e.groupid WHERE e.dataid = $1 GROUP BY e.id

comes from.

A typical entries fetching in the Dataform doesn't GROUP BY, so this bit of sql probably comes from a field. Which fields do you have in the activity?

smile

Average of ratings: -
In reply to Itamar Tzadok

Re: Problems with Dataform in Moodle 2.6.2 on PostgresSQL

by Paul K -

I am using the following fields in the activity:

- select

- file

- text

- entry state

 

In my humble opinion it is not the reason of the left join. I have tried to create an activity with only one text field but the problem still persist.

Average of ratings: -
In reply to Paul K

Re: Problems with Dataform in Moodle 2.6.2 on PostgresSQL

by Itamar Tzadok -

Is it the same location? Can you post the stack trace? smile

Average of ratings: -
In reply to Itamar Tzadok

Re: Problems with Dataform in Moodle 2.6.2 on PostgresSQL

by Paul K -

Dear Itamar,

sure, here it is:

Debug info: FEHLER: Spalte „u.id“ muss in der GROUP-BY-Klausel erscheinen oder in einer Aggregatfunktion verwendet werden
LINE 1: ....timecreated, e.timemodified, e.userid, e.groupid,u.id AS ui...
^
SELECT DISTINCT e.id, e.dataid, e.state, e.timecreated, e.timemodified, e.userid, e.groupid,u.id AS uid ,u.picture,u.firstname,u.lastname,u.firstnamephonetic,u.lastnamephonetic,u.middlename,u.alternatename,u.imagealt,u.email,u.idnumber,u.username,g.idnumber AS groupidnumber, g.name AS groupname, g.hidepicture AS grouphidepic, g.picture AS grouppic FROM mdl_dataform_entries e
JOIN mdl_user u ON u.id = e.userid
LEFT JOIN mdl_groups g ON g.id = e.groupid WHERE e.dataid = $1 GROUP BY e.id
[array (
0 => '3',
)]
Error code: dmlreadexception

 

Stack trace:
  • line 443 of \lib\dml\moodle_database.php: dml_read_exception thrown
  • line 243 of \lib\dml\pgsql_native_moodle_database.php: call to moodle_database->query_end()
  • line 748 of \lib\dml\pgsql_native_moodle_database.php: call to pgsql_native_moodle_database->query_end()
  • line 373 of \mod\dataform\classes\entry_manager.php: call to pgsql_native_moodle_database->get_records_sql()
  • line 131 of \mod\dataform\classes\entry_manager.php: call to mod_dataform_entry_manager->fetch_entries()
  • line 316 of \mod\dataform\classes\pluginbase\dataformview.php: call to mod_dataform_entry_manager->set_content()
  • line 390 of \mod\dataform\classes\dataform.php: call to mod_dataform\pluginbase\dataformview->display()
  • line 71 of \mod\dataform\view.php: call to mod_dataform_dataform->display()
Average of ratings: -
In reply to Paul K

Re: Problems with Dataform in Moodle 2.6.2 on PostgresSQL

by Paul K -

Dear Itamar,

it works with the newest version of this plugin! I don't know what was the issue but right now I am able to edit the fields and save them, without any problems. Thank you very much for your help!

Average of ratings: -