Error Reading from Database - Questionnaire Submission

Error Reading from Database - Questionnaire Submission

by Raymond Frangie -
Number of replies: 10

Hi All,

Testing a questionnaire submission to see if I get a notification email and on submission, I get "Error reading from database".

Activating debug shows the following when attempted again.


Debug info: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'rank
FROM wsu_moodle_cybersec_questionnaire_response qr
' at line 3
SELECT CONCAT_WS('_', qr.id, 'checkboxes', qrm.id) AS id,
qr.submitted, qr.complete, qr.grade, qr.userid, u.firstnamephonetic, u.lastnamephonetic, u.middlename, u.alternatename, u.firstname, u.lastname, u.username, u.department, u.institution, u.id as usrid, qr.id AS rid, qrm.question_id,
qrm.choice_id, qro.response AS response, 0 AS rank
FROM wsu_moodle_cybersec_questionnaire_response qr
JOIN wsu_moodle_cybersec_questionnaire_resp_multiple qrm ON qrm.response_id = qr.id
AND qr.survey_id = ? AND qr.complete = ?
LEFT JOIN wsu_moodle_cybersec_questionnaire_response_other qro ON qro.response_id = qr.id AND qro.choice_id = qrm.choice_id
LEFT JOIN wsu_moodle_cybersec_user u ON u.id = qr.userid
WHERE qr.id = ? UNION ALL 
SELECT CONCAT_WS('_', qr.id, 'textbox', qrt.id) AS id,
qr.submitted, qr.complete, qr.grade, qr.userid, u.firstnamephonetic, u.lastnamephonetic, u.middlename, u.alternatename, u.firstname, u.lastname, u.username, u.department, u.institution, u.id as usrid, qr.id AS rid, qrt.question_id,
0 AS choice_id, qrt.response AS response, 0 AS rank
FROM wsu_moodle_cybersec_questionnaire_response qr
JOIN wsu_moodle_cybersec_questionnaire_response_text qrt
ON qrt.response_id = qr.id
AND qr.survey_id = ? AND qr.complete = ?
LEFT JOIN wsu_moodle_cybersec_user u ON u.id = qr.userid
WHERE qr.id = ? ORDER BY usrid, id
[array (
0 => '2',
1 => 'y',
2 => 3,
3 => '2',
4 => 'y',
5 => 3,
)]
Error code: dmlreadexception
Stack trace:
line 486 of /lib/dml/moodle_database.php: dml_read_exception thrown
line 1184 of /lib/dml/mysqli_native_moodle_database.php: call to moodle_database->query_end()
line 2597 of /mod/questionnaire/questionnaire.class.php: call to mysqli_native_moodle_database->get_recordset_sql()
line 2782 of /mod/questionnaire/questionnaire.class.php: call to questionnaire->get_survey_all_responses()
line 1902 of /mod/questionnaire/questionnaire.class.php: call to questionnaire->generate_csv()
line 1599 of /mod/questionnaire/questionnaire.class.php: call to questionnaire->response_send_email()
line 277 of /mod/questionnaire/questionnaire.class.php: call to questionnaire->submission_notify()
line 75 of /mod/questionnaire/complete.php: call to questionnaire->view()


Now my SQL is not the best but 0 AS rank? What's 0? Shouldn't that be a column name? Also didn't get a notification via email of this submission so I don't know if this error is blocking it or whether that's another issue altogether.

This server is

  • Moodle 3.5.1+ (Build: 20180720)
  • MySQL 8.0.12
  • PHP 7.2.8.1.16.04.1.1

This server is also a live production server currently running a semester unit; appreciate a prompt response and resolution.

Thanks in Advance!

Cheers,
Ray.

Average of ratings: -
In reply to Raymond Frangie

Re: Error Reading from Database - Questionnaire Submission

by Conn Warwicker -
Picture of Core developers Picture of Plugin developers

"rank" is a reserved word in Mysql 8+. So the plugin creator would need to fix that, unless you wanted to try and change their code.

Average of ratings: -
In reply to Conn Warwicker

Re: Error Reading from Database - Questionnaire Submission

by Raymond Frangie -

Why the hell would they want to reserve the word rank?

If memory serves me right, surrounding it with [ ] should fix that issue. This is the default questionnaire plugin though which is in Moodle core, will this break if I make the change and then an update comes through?

Cheers,

Ray

Average of ratings: -
In reply to Raymond Frangie

Re: Error Reading from Database - Questionnaire Submission

by Conn Warwicker -
Picture of Core developers Picture of Plugin developers

Hi,

The questionnaire module is not in core: https://github.com/moodle/moodle/tree/master/mod

It's an additional plugin: https://moodle.org/plugins/mod_questionnaire


If you make a change to the module code yourself and then update the module again at a later date, it will overwrite it yes, unless you use git to manage the update.


Probably best to contact the plugin developer.

Average of ratings: -
In reply to Conn Warwicker

Re: Error Reading from Database - Questionnaire Submission

by Raymond Frangie -

Well, it wasn't the bracket but the backtick... although that seems to be only the first issue, now it's holding up at another sad


mysql> SELECT CONCAT_WS('_', qr.id, 'checkboxes', qrm.id) AS id,
    -> qr.submitted, qr.complete, qr.grade, qr.userid, u.firstnamephonetic, u.lastnamephonetic, u.middlename, u.alternatename, u.firstname, u.lastname, u.username, u.department, u.institution, u.id as usrid, qr.id AS rid, qrm.question_id,
    -> qrm.choice_id, qro.response AS response, 0 AS `rank`
    -> FROM wsu_moodle_cybersec_questionnaire_response qr
    -> JOIN wsu_moodle_cybersec_questionnaire_resp_multiple qrm ON qrm.response_id = qr.id
    -> AND qr.survey_id = ? AND qr.complete = ?
    -> LEFT JOIN wsu_moodle_cybersec_questionnaire_response_other qro ON qro.response_id = qr.id AND qro.choice_id = qrm.choice_id
    -> LEFT JOIN wsu_moodle_cybersec_user u ON u.id = qr.userid
    -> WHERE qr.id = ? UNION ALL 
    -> SELECT CONCAT_WS('_', qr.id, 'textbox', qrt.id) AS id,
    -> qr.submitted, qr.complete, qr.grade, qr.userid, u.firstnamephonetic, u.lastnamephonetic, u.middlename, u.alternatename, u.firstname, u.lastname, u.username, u.department, u.institution, u.id as usrid, qr.id AS rid, qrt.question_id,
    -> 0 AS choice_id, qrt.response AS response, 0 AS `rank`
    -> FROM wsu_moodle_cybersec_questionnaire_response qr
    -> JOIN wsu_moodle_cybersec_questionnaire_response_text qrt
    -> ON qrt.response_id = qr.id
    -> AND qr.survey_id = ? AND qr.complete = ?
    -> LEFT JOIN wsu_moodle_cybersec_user u ON u.id = qr.userid
    -> WHERE qr.id = ? ORDER BY usrid, id
    -> [array (
    -> 0 => '2',
    -> 1 => 'y',
    -> 2 => 3,
    -> 3 => '2',
    -> 4 => 'y',
    -> 5 => 3,
    -> )];
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '? AND qr.complete = ?
LEFT JOIN wsu_moodle_cybersec_questionnaire_response_other' at line 6
mysql>


Thoughts?

Cheers,
Ray

Average of ratings: -
In reply to Raymond Frangie

Re: Error Reading from Database - Questionnaire Submission

by Conn Warwicker -
Picture of Core developers Picture of Plugin developers

Hi,

Are you running it through the mysql command line, with the "?" placeholders?

You'd need to replace those with the actual values from the php array.

-----

SELECT CONCAT_WS('_', qr.id, 'checkboxes', qrm.id) AS id,

     qr.submitted, qr.complete, qr.grade, qr.userid, u.firstnamephonetic, u.lastnamephonetic, u.middlename, u.alternatename, u.firstname, u.lastname, u.username, u.department, u.institution, u.id as usrid, qr.id AS rid, qrm.question_id,

     qrm.choice_id, qro.response AS response, 0 AS `rank`

     FROM wsu_moodle_cybersec_questionnaire_response qr

     JOIN wsu_moodle_cybersec_questionnaire_resp_multiple qrm ON qrm.response_id = qr.id

     AND qr.survey_id = 2 AND qr.complete = 'y'

     LEFT JOIN wsu_moodle_cybersec_questionnaire_response_other qro ON qro.response_id = qr.id AND qro.choice_id = qrm.choice_id

     LEFT JOIN wsu_moodle_cybersec_user u ON u.id = qr.userid

     WHERE qr.id = 3 UNION ALL 

     SELECT CONCAT_WS('_', qr.id, 'textbox', qrt.id) AS id,

     qr.submitted, qr.complete, qr.grade, qr.userid, u.firstnamephonetic, u.lastnamephonetic, u.middlename, u.alternatename, u.firstname, u.lastname, u.username, u.department, u.institution, u.id as usrid, qr.id AS rid, qrt.question_id,

     0 AS choice_id, qrt.response AS response, 0 AS `rank`

     FROM wsu_moodle_cybersec_questionnaire_response qr

     JOIN wsu_moodle_cybersec_questionnaire_response_text qrt

     ON qrt.response_id = qr.id

     AND qr.survey_id = 2 AND qr.complete = 'y'

     LEFT JOIN wsu_moodle_cybersec_user u ON u.id = qr.userid

     WHERE qr.id = 3 ORDER BY usrid, id


Average of ratings: -
In reply to Conn Warwicker

Re: Error Reading from Database - Questionnaire Submission

by Raymond Frangie -

*facepalm* and that's what you get for working at 2am...

The command works... it's only the backticks around rank which is the problem!

I've contacted the developer.

Thanks for your help!

Cheers,
Ray.

Average of ratings: -
In reply to Raymond Frangie

Re: Error Reading from Database - Questionnaire Submission

by Debbie Unterseher -

I need to get this fixed as well.  I guess I don't know how to go about to do that!  Any help appreciated.

Average of ratings: -
In reply to Debbie Unterseher

Re: Error Reading from Database - Questionnaire Submission

by Raymond Frangie -

I've had no reply from the developer unfortunately... have had a look at the module code but it's over my head as I'm not familiar with the structure of the plugin...

I'll try and chase up again.

Cheers,

Ray

Average of ratings: -
In reply to Raymond Frangie

Re: Error Reading from Database - Questionnaire Submission

by Mike Churchward -
Picture of Core developers Picture of Plugin developers Picture of Testers

Please follow along with the discussion here - https://moodle.org/mod/forum/discuss.php?d=374991

Average of ratings: -