search

mdjnelson / moodle-mod_customcert

Enables the creation of dynamically generated certificates with complete customisation via the web browser.
https://moodle.org/plugins/mod_customcert
GNU General Public License v3.0
94 stars 160 forks source link

Column 'moodle_customcert.templateid' is invalid. Not contained in either an aggregate function or the GROUP BY clause. #649

Open ZXRoxas opened 1 month ago

ZXRoxas commented 1 month ago

Execute scheduled task: Issue certificates task (mod_customcert\task\issue_certificates_task) ... started 19:11:25. Current memory use 29.3 MB. Debugging increased temporarily due to faildelay of 86400 ... used 1 dbqueries ... used 2.8524348735809 seconds Scheduled task failed: Issue certificates task (mod_customcert\task\issue_certificates_task),Error reading from database (SQLState: 42000
Error Code: 8120
Message: [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Column 'moodle_customcert.templateid' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

SELECT c.id, c.templateid, c.course, c.requiredtime, c.emailstudents, c.emailteachers, c.emailothers, ct.id AS templateid, ct.name AS templatename, ct.contextid, co.id AS courseid, co.fullname AS coursefullname, co.shortname AS courseshortname FROM moodle_customcert c JOIN moodle_customcert_templates ct ON c.templateid = ct.id JOIN moodle_course co ON c.course = co.id JOIN moodle_course_categories cat ON co.category = cat.id LEFT JOIN moodle_customcert_issues ci ON c.id = ci.customcertid WHERE (c.emailstudents = '1' OR c.emailteachers = '1' OR LEN(c.emailothers) >= 3) AND co.visible = 1 AND cat.visible = 1 AND (co.enddate > '1697681485' OR (co.enddate = 0 AND ci.timecreated > '1697681485')) GROUP BY c.id, ct.id, ct.name, ct.contextid, co.id, co.fullname, co.shortname [array ( 0 => 1, 1 => 1, 2 => 1697681485, 3 => 1697681485, )]) Debug info: SQLState: 42000
Error Code: 8120
Message: [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Column 'moodle_customcert.templateid' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

SELECT c.id, c.templateid, c.course, c.requiredtime, c.emailstudents, c.emailteachers, c.emailothers, ct.id AS templateid, ct.name AS templatename, ct.contextid, co.id AS courseid, co.fullname AS coursefullname, co.shortname AS courseshortname FROM moodle_customcert c JOIN moodle_customcert_templates ct ON c.templateid = ct.id JOIN moodle_course co ON c.course = co.id JOIN moodle_course_categories cat ON co.category = cat.id LEFT JOIN moodle_customcert_issues ci ON c.id = ci.customcertid WHERE (c.emailstudents = '1' OR c.emailteachers = '1' OR LEN(c.emailothers) >= 3) AND co.visible = 1 AND cat.visible = 1 AND (co.enddate > '1697681485' OR (co.enddate = 0 AND ci.timecreated > '1697681485')) GROUP BY c.id, ct.id, ct.name, ct.contextid, co.id, co.fullname, co.shortname [array ( 0 => 1, 1 => 1, 2 => 1697681485, 3 => 1697681485, )] Backtrace:

ZXRoxas commented 1 month ago

Plugin Version: 4.4.3 (2024042207). Moodle Version: 4.4.3+ (Build: 20240920).

eiconsult commented 1 month ago

Plugin Version: 4.4.3 (2024042207). Moodle Version: 4.4.3+ (Build: 20240920).

I was able to resolve this by doing so:

MSSQL is a bit strict with Group By functions and requires columns be listed in the group by statement if it is referenced in the select statement. In this case, the c.* retrieves all columns in the select statement.

Potential "quick" workaround

line 91: \mod\customcert\classes\task\issue_certificates_task.php

from: $sql .= " GROUP BY c.id, ct.id, ct.name, ct.contextid, co.id, co.fullname, co.shortname";

to: $sql .= " GROUP BY c.id, ct.id, ct.name, ct.contextid, co.id, co.fullname, co.shortname, c.course,c.templateid,c.intro,c.introformat,c.requiredtime, c.name, ,c.verifyany,c.emailstudents,c.emailteachers,c.emailothers,c.protection,c.timecreated,c.timemodified,c.deliveryoption,c.language";

mdjnelson commented 1 month ago

@eiconsult can you try with the latest version which includes https://github.com/mdjnelson/moodle-mod_customcert/commit/44250554e3dd74c31d06816930c94dec90f7ffeb? I dont have access to an easily accessible SQL database without a bunch of docker configurations? If you can provide a diff, or even a PR that would be great.

eiconsult commented 1 month ago

Ah. You've got it. I see 2024042206 replaced with 2024042207.

I'll drop a note when confirmed

-Eseosa

From: Mark Nelson @.> Sent: Friday, October 18, 2024 11:56:25 AM To: mdjnelson/moodle-mod_customcert @.> Cc: Eseosa Iyare @.>; Mention @.> Subject: Re: [mdjnelson/moodle-mod_customcert] Column 'moodle_customcert.templateid' is invalid. Not contained in either an aggregate function or the GROUP BY clause. (Issue #649)

@eiconsulthttps://github.com/eiconsult can you try with the latest version which includes 4425055https://github.com/mdjnelson/moodle-mod_customcert/commit/44250554e3dd74c31d06816930c94dec90f7ffeb? I dont have access to an easily accessible SQL database without a bunch of docker configurations? If you can provide a diff, or even a PR that would be great.

— Reply to this email directly, view it on GitHubhttps://github.com/mdjnelson/moodle-mod_customcert/issues/649#issuecomment-2422783533, or unsubscribehttps://github.com/notifications/unsubscribe-auth/BMHPYGRCSTJEHSHT3JZF3BDZ4EVSTAVCNFSM6AAAAABQGFS7LKVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMZDIMRSG44DGNJTGM. You are receiving this because you were mentioned.Message ID: @.***>

mdjnelson commented 1 month ago

Hey @eiconsult, any update? :) Would like to fix this if it wasn't actually fixed by the patch I listed.

eiconsult commented 1 month ago

:) client owns the MSSQL service. It will take a few to get this situated.

Thank you -Eseosa

From: Mark Nelson @.> Sent: Monday, October 21, 2024 10:20:38 AM To: mdjnelson/moodle-mod_customcert @.> Cc: Eseosa Iyare @.>; Mention @.> Subject: Re: [mdjnelson/moodle-mod_customcert] Column 'moodle_customcert.templateid' is invalid. Not contained in either an aggregate function or the GROUP BY clause. (Issue #649)

Hey @eiconsulthttps://github.com/eiconsult, any update? :) Would like to fix this if it wasn't actually fixed by the patch I listed.

— Reply to this email directly, view it on GitHubhttps://github.com/mdjnelson/moodle-mod_customcert/issues/649#issuecomment-2426827865, or unsubscribehttps://github.com/notifications/unsubscribe-auth/BMHPYGX4ETH2W3ETFDABBP3Z4UETNAVCNFSM6AAAAABQGFS7LKVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMZDIMRWHAZDOOBWGU. You are receiving this because you were mentioned.Message ID: @.***>

mdjnelson commented 1 month ago

Some time on the weekend ill get Moodle docker setup and run it against MSSQL and Oracle. I've just been too lazy to do that. 😅

rlowry commented 3 weeks ago

Unfortunately the Oracle patch does not fix this for SQL Server. With v4.4.3 installed I still get the following error:

`Execute scheduled task: Issue certificates task (mod_customcert\task\issue_certificates_task) ... started 00:48:54. Current memory use 38.0 MB. Debugging increased temporarily due to faildelay of 86400 ... used 1 dbqueries ... used 0.41620993614197 seconds Scheduled task failed: Issue certificates task (mod_customcert\task\issue_certificates_task),Error reading from database (SQLState: 42000
Error Code: 8120
Message: [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Column 'mdl_customcert.templateid' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

SELECT c.id, c.templateid, c.course, c.requiredtime, c.emailstudents, c.emailteachers, c.emailothers, ct.id AS templateid, ct.name AS templatename, ct.contextid, co.id AS courseid, co.fullname AS coursefullname, co.shortname AS courseshortname FROM mdl_customcert c JOIN mdl_customcert_templates ct ON c.templateid = ct.id JOIN mdl_course co ON c.course = co.id JOIN mdl_course_categories cat ON co.category = cat.id LEFT JOIN mdl_customcert_issues ci ON c.id = ci.customcertid WHERE (c.emailstudents = '1' OR c.emailteachers = '1' OR LEN(c.emailothers) >= 3) AND co.visible = 1 AND cat.visible = 1 AND (co.enddate > '1699231734' OR (co.enddate = 0 AND ci.timecreated > '1699231734')) GROUP BY c.id, ct.id, ct.name, ct.contextid, co.id, co.fullname, co.shortname [array ( 0 => 1, 1 => 1, 2 => 1699231734, 3 => 1699231734, )]) Debug info: SQLState: 42000
Error Code: 8120
Message: [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Column 'mdl_customcert.templateid' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

SELECT c.id, c.templateid, c.course, c.requiredtime, c.emailstudents, c.emailteachers, c.emailothers, ct.id AS templateid, ct.name AS templatename, ct.contextid, co.id AS courseid, co.fullname AS coursefullname, co.shortname AS courseshortname FROM mdl_customcert c JOIN mdl_customcert_templates ct ON c.templateid = ct.id JOIN mdl_course co ON c.course = co.id JOIN mdl_course_categories cat ON co.category = cat.id LEFT JOIN mdl_customcert_issues ci ON c.id = ci.customcertid WHERE (c.emailstudents = '1' OR c.emailteachers = '1' OR LEN(c.emailothers) >= 3) AND co.visible = 1 AND cat.visible = 1 AND (co.enddate > '1699231734' OR (co.enddate = 0 AND ci.timecreated > '1699231734')) GROUP BY c.id, ct.id, ct.name, ct.contextid, co.id, co.fullname, co.shortname [array ( 0 => 1, 1 => 1, 2 => 1699231734, 3 => 1699231734, )] Backtrace: