search

ngandrass / moodle-quiz_archiver

Archives quiz attempts as PDF and HTML files for long-term storage independent of Moodle
GNU General Public License v3.0
5 stars 3 forks source link

SQL Exception #9

Closed bbdev2 closed 6 months ago

bbdev2 commented 6 months ago

Moodle: 4.1.18 (from https://download.moodle.org/windows/) DB: MariaDB 10.4.28 OS: Windows 11 I tested your plugin yesterday. When I had created a job I caught an exception: An unknown error occurred while enqueueing the job at the remote archive worker. Error writing to database (You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'key,value) VALUES ('5','num_attempts','4'),('5','export_attempts','1'),('5','...' at line 1 INSERT INTO mdl_quiz_archiver_job_settings (jobid,key,value) VALUES (?,?,?),(?,?,?),(?,?,?),(?,?,?),(?,?,?),(?,?,?),(?,?,?),(?,?,?),(?,?,?),(?,?,?),(?,?,?),(?,?,?),(?,?,?) [array ( 0 => 5, 1 => 'num_attempts', 2 => '4', 3 => 5, 4 => 'export_attempts', 5 => '1', 6 => 5, 7 => 'export_report_section_header', 8 => '1', 9 => 5, 10 => 'export_report_section_quiz_feedback', 11 => '1', 12 => 5, 13 => 'export_report_section_question', 14 => '1', 15 => 5, 16 => 'export_report_section_question_feedback', 17 => '1', 18 => 5, 19 => 'export_report_section_general_feedback', 20 => '1', 21 => 5, 22 => 'export_report_section_rightanswer', 23 => '1', 24 => 5, 25 => 'export_report_section_history', 26 => '1', 27 => 5, 28 => 'export_report_section_attachments', 29 => '1', 30 => 5, 31 => 'export_quiz_backup', 32 => '1', 33 => 5, 34 => 'export_course_backup', 35 => '0', 36 => 5, 37 => 'archive_autodelete', 38 => '0', )])

I think it was because words 'key' and 'value' are reserved words in MySQL. You can check it by trying to run this query: INSERT INTO mdl_quiz_archiver_job_settings (jobid,key,value) VALUES (4,'num_attempts','4') And you will catch the error: ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'key,value) VALUES (4,'num_attempts','4')' at line 1

If you put the words in quotation marks you will lose Moodle database independence, because Mysql use '`' quotation mark and MSSQL Server use '[',']' quotation marks. I think there is no need to use the words "key" and "value" in any SQL queries. Please rename them.

ngandrass commented 6 months ago

Thanks for your bug report! I'll look into it :)

ngandrass commented 6 months ago

I was able to reproduce the error. Which is super weird, since I'm using the Moodle ORM instead of raw SQL for this operation. Anyhow, seems like it should be changed nonetheless.

Will be fixed with the next release :)

ngandrass commented 6 months ago

Fixed with release v1.2.3 :tada: