Upgrade from 3.6 to 3.7 - sql syntax error

Upgrade from 3.6 to 3.7 - sql syntax error

autor Adam Pawelczak -
Počet odpovědí: 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

 

Průměr hodnocení: -
V odpovědi na Adam Pawelczak

Re: Upgrade from 3.6 to 3.7 - sql syntax error

autor Mike Churchward -
Obrázek: Core developers Obrázek: Plugin developers Obrázek: 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.
Průměr hodnocení: -
V odpovědi na Adam Pawelczak

Re: Upgrade from 3.6 to 3.7 - sql syntax error

autor Mike Churchward -
Obrázek: Core developers Obrázek: Plugin developers Obrázek: Testers
Fixed in Github. I will do a release today to fix this.
Průměr hodnocení: -
V odpovědi na Mike Churchward

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

autor 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
Průměr hodnocení: -
V odpovědi na Adam Pawelczak

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

autor Mike Churchward -
Obrázek: Core developers Obrázek: Plugin developers Obrázek: 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.

Průměr hodnocení: -
V odpovědi na Mike Churchward

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

autor Adam Pawelczak -
Exactly, Postgres doesn't accept table prefixes in set statements. It doesn't matter if it is alias or table name.
Now upgrade works properly. Thanks.
Adam
Průměr hodnocení: Useful (1)
V odpovědi na Adam Pawelczak

Odp: Upgrade from 3.6 to 3.7 - sql syntax error

autor 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
Průměr hodnocení: -
V odpovědi na Kate James

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

autor Mike Churchward -
Obrázek: Core developers Obrázek: Plugin developers Obrázek: Testers
I don't think this is the same issue, but does the error provide more information? There should be line number information.
Průměr hodnocení: -