Upgrade from 3.6 to 3.7 - sql syntax error

Upgrade from 3.6 to 3.7 - sql syntax error

بواسطة - Adam Pawelczak
عدد الردود: 10

Hi ,

we get a sql syntax error during questionnaire upgrade from 3.6 to 3.7.2:

Debug info: BŁĄD: błąd składni w lub blisko "INNER"
LINE 1: UPDATE mdl_questionnaire_question qq INNER JOIN mdl_question...
^
UPDATE mdl_questionnaire_question qq INNER JOIN mdl_questionnaire_response_rank qrr ON qq.id = qrr.question_id SET qrr.rankvalue = (qrr.rankvalue + 1) WHERE qq.type_id = $1 AND qrr.rankvalue >= $2
[array (
0 => 8,
1 => 0,
)]
Error code: dmlwriteexception

We use postgresql 11.

Adam

 

متوسط التقييمات: -
رداً على Adam Pawelczak

Re: Upgrade from 3.6 to 3.7 - sql syntax error

بواسطة - Mike Churchward
صورة Core developers صورة Plugin developers صورة Testers
Ah. Thanks for the report. Looks like my upgrade statement doesn't work on Postgres out of the box. I'll get an update out soon.
متوسط التقييمات: -
رداً على Adam Pawelczak

Re: Upgrade from 3.6 to 3.7 - sql syntax error

بواسطة - Mike Churchward
صورة Core developers صورة Plugin developers صورة Testers
Fixed in Github. I will do a release today to fix this.
متوسط التقييمات: -
رداً على Mike Churchward

Odp: Re: Upgrade from 3.6 to 3.7 - sql syntax error

بواسطة - Adam Pawelczak
Hi,
thanks for quick reaction, but now we get another error:

Debug info: Error: column "qr" of relation "mdl_questionnaire_response_rank" doesn't exist
LINE 1: UPDATE mdl_questionnaire_response_rank qr SET qr.rankvalue =...
^
UPDATE mdl_questionnaire_response_rank qr SET qr.rankvalue = (qr.rankvalue + 1) WHERE (qr.rankvalue >= 0)
[array (
)]
Error code: dmlwriteexception
×Stack trace:
line 489 of /lib/dml/moodle_database.php: dml_write_exception thrown
line 259 of /lib/dml/pgsql_native_moodle_database.php: call to moodle_database->query_end()
line 719 of /lib/dml/pgsql_native_moodle_database.php: call to pgsql_native_moodle_database->query_end()
line 1057 of /mod/questionnaire/classes/question/rate.php: call to pgsql_native_moodle_database->execute()
line 823 of /mod/questionnaire/db/upgrade.php: call to mod_questionnaire\question\rate::move_all_nameddegree_choices()
line 818 of /lib/upgradelib.php: call to xmldb_questionnaire_upgrade()
line 524 of /lib/upgradelib.php: call to upgrade_plugins_modules()
line 1869 of /lib/upgradelib.php: call to upgrade_plugins()
line 694 of /administracjamoodle/index.php: call to upgrade_noncore()

Again potgress 11.
Adam
متوسط التقييمات: -
رداً على Adam Pawelczak

Re: Odp: Re: Upgrade from 3.6 to 3.7 - sql syntax error

بواسطة - Mike Churchward
صورة Core developers صورة Plugin developers صورة Testers

Looks like Postgres does not like using aliases in set statements. Back to the code.

Edit - Can you pull this commit. It removes the table aliases which should work with all databases.

متوسط التقييمات: -
رداً على Adam Pawelczak

Odp: Upgrade from 3.6 to 3.7 - sql syntax error

بواسطة - Kate James
Hi

I have a problem with version 3.9.0 with Postgres:

Debug info: BŁĄD: błąd składni w lub blisko "INNER"
LINE 1: UPDATE mdl_questionnaire_response qr INNER JOIN mdl_question...
^
UPDATE mdl_questionnaire_response qr INNER JOIN mdl_questionnaire_attempts qa ON qr.id = qa.rid SET qr.questionnaireid = qa.qid
[array (
)]
Error code: dmlwriteexception
متوسط التقييمات: -