Uploaded image for project: 'Plugins'
  1. Plugins
  2. CONTRIB-7933

Database error when importing a mod_questionnaire activity on Postgres

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Resolved
    • Priority: Minor
    • Resolution: Fixed
    • Affects Version/s: 3.7.3
    • Fix Version/s: 3.7.4
    • Component/s: Module: Questionnaire
    • Labels:
      None
    • Affected Branches:
      MOODLE_37_STABLE
    • Fixed Branches:
      MOODLE_37_STABLE

      Description

      Hi Mike,

      a user of ours wanted to restore a mod_questionnaire activity instance from another Moodle system in our Moodle system and ran into a database error during restore.

      The php-fpm error log showed this error:

      [13-Dec-2019 13:42:24 Europe/Berlin] Default exception handler: Fehler beim Schreiben der Datenbank Debug: ERROR:  column "qr" of relation "mdl_questionnaire_response_rank" does not exist
      LINE 1: UPDATE mdl_questionnaire_response_rank AS qr SET qr.rankvalu...
                                                               ^
      UPDATE mdl_questionnaire_response_rank AS qr SET qr.rankvalue = (qr.rankvalue + 1) WHERE (qr.rankvalue >= 0) AND (question_id IN ($1,$2,$3,$4,$5,$6,$7,$8,$9))
      [array (
        0 => 2236,
        1 => 2237,
        2 => 2238,
        3 => 2216,
        4 => 2217,
        5 => 2218,
        6 => 2262,
        7 => 2296,
        8 => 2299,
      )]
      Error code: dmlwriteexception
      * 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 407 of /mod/questionnaire/backup/moodle2/restore_questionnaire_stepslib.php: call to mod_questionnaire\question\rate::move_all_nameddegree_choices()
      * line 410 of /backup/util/plan/restore_structure_step.class.php: call to restore_questionnaire_activity_structure_step->after_execute()
      * line 113 of /backup/util/plan/restore_structure_step.class.php: call to restore_structure_step->launch_after_execute_methods()
      * line 181 of /backup/util/plan/base_task.class.php: call to restore_structure_step->execute()
      * line 210 of /backup/moodle2/restore_activity_task.class.php: call to base_task->execute()
      * line 178 of /backup/util/plan/base_plan.class.php: call to restore_activity_task->execute()
      * line 168 of /backup/util/plan/restore_plan.class.php: call to base_plan->execute()
      * line 377 of /backup/controller/restore_controller.class.php: call to restore_plan->execute()
      * line 219 of /backup/util/ui/restore_ui.class.php: call to restore_controller->execute_plan()
      * line 141 of /backup/restore.php: call to restore_ui->execute()
       
      [13-Dec-2019 13:42:24 Europe/Berlin] Potential coding error - existing temptables found when disposing database. Must be dropped!
      

      We are running on Postgres and a) I am unsure if adding a table alias without AS is allowed in Postgres and b) I am quite sure that the table alias is needed in this query at all.

      That's why we hotfixed this problem in production this way:

      diff --git a/mod/questionnaire/classes/question/rate.php b/mod/questionnaire/classes/question/rate.php
      index 79cb342..97c3251 100644
      --- a/mod/questionnaire/classes/question/rate.php
      +++ b/mod/questionnaire/classes/question/rate.php
      @@ -1046,9 +1046,17 @@ class rate extends question {
       
               // If we're doing this step, let's do it.
               if (!$skip) {
      +/* KIZ MODIFICATION START
      +   REASON: Database error when importing a mod_questionnaire activity on Postgres - LMS-4251 */
      +            $select = 'UPDATE {questionnaire_response_rank} ' .
      +                'SET rankvalue = (rankvalue + 1) ' .
      +                'WHERE (rankvalue >= 0)';
      +/* KIZ MODIFICATION END */
      +/* ORIGINAL START
                   $select = 'UPDATE {questionnaire_response_rank} qr ' .
                       'SET qr.rankvalue = (qr.rankvalue + 1) ' .
                       'WHERE (qr.rankvalue >= 0)';
      +   ORIGINAL END */
                   if ($surveyid !== null) {
                       $select .= ' AND (question_id ' . $qsql . ')';
                   } else {
      
      

      I would be grateful if you could have a look at the hotfix and, if you agree that it's also fine for other database systems like MySQL, I will be happy to submit it as pull request in Github.

        Attachments

          Activity

            People

            Assignee:
            mchurch Mike Churchward
            Reporter:
            abias Alexander Bias
            Participants:
            Component watchers:
            Mike Churchward
            Votes:
            2 Vote for this issue
            Watchers:
            4 Start watching this issue

              Dates

              Created:
              Updated:
              Resolved:
              Fix Release Date:
              13/Jan/20