Uploaded image for project: 'Moodle'
  1. Moodle
  2. MDL-29332

Calculated Questions Require Case Sensitive Data Tables

    XMLWordPrintable

    Details

    • Database:
      MySQL, Microsoft SQL
    • Testing Instructions:
      Hide

      You need to use Mysql database with case-insensitive collation (for example utf8_general_ci )

      1. MASTER: Run unittests from lib/dml/tests/dml_test.php, the test test_sql_equal() should pass (no matter there is an unrelated failure with test_unique_index_collation_trouble()).
      2. ALL VERSIONS: Create a course, open question bank
      3. Add calculated question as in example https://docs.moodle.org/31/en/Calculated_question_type
      4. Add another calculated question but use the variables that differ only in the case, for example

        {x} and {X}

      5. Preview question, edit, duplicate
      6. Create quiz with this question and attempt this quiz
      7. Make sure there are no errors anywhere

      On MSSQL (Master only)

      1. Run unittests from lib/dml/tests/dml_test.php
      2. Repeat with some CI or AI collation, again test test_sql_equal() should pass

      On all databases:

      1. Verify after install AND upgrade that there isn't any unique index on question_attempt_step_data with the columns 'attemptstepid' AND 'name' ('attemptstepid' non-unique index alone is ok).
      Show
      You need to use Mysql database with case-insensitive collation (for example utf8_general_ci ) MASTER: Run unittests from lib/dml/tests/dml_test.php, the test test_sql_equal() should pass (no matter there is an unrelated failure with test_unique_index_collation_trouble() ). ALL VERSIONS: Create a course, open question bank Add calculated question as in example https://docs.moodle.org/31/en/Calculated_question_type Add another calculated question but use the variables that differ only in the case, for example {x} and {X} Preview question, edit, duplicate Create quiz with this question and attempt this quiz Make sure there are no errors anywhere On MSSQL (Master only) Run unittests from lib/dml/tests/dml_test.php Repeat with some CI or AI collation, again test test_sql_equal() should pass On all databases: Verify after install AND upgrade that there isn't any unique index on question_attempt_step_data with the columns 'attemptstepid' AND 'name' ('attemptstepid' non-unique index alone is ok).
    • Workaround:
      Hide

      Don't use variable whose names differ only by case.

      Change the collation on the question_attempt_step_data table specifically
      ALTER TABLE mdl_question_attempt_step_data CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
      (change mdl_ to the right prefix for your DB. This will not cause any unwanted side-effects.

      Show
      Don't use variable whose names differ only by case. Change the collation on the question_attempt_step_data table specifically ALTER TABLE mdl_question_attempt_step_data CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin; (change mdl_ to the right prefix for your DB. This will not cause any unwanted side-effects.
    • Affected Branches:
      MOODLE_21_STABLE, MOODLE_23_STABLE, MOODLE_30_STABLE, MOODLE_31_STABLE, MOODLE_32_STABLE
    • Fixed Branches:
      MOODLE_30_STABLE, MOODLE_31_STABLE
    • Pull Master Branch:
      wip-MDL-29332-master-3
    • Sprint:
      3.2 Sprint 5

      Description

      If you enter a formula in a quiz question using the calculated question format, where the formula uses variables with the same characters in different cases, then a quiz using that question will be broken.

      See http://moodle.org/mod/forum/discuss.php?d=183056 for other details...

      For example, a question using "

      {I}

      +

      {i}

      " can be created and added to a quiz, but it will break the quiz. It will generate the error:

      Debug info: Duplicate entry '2-_var_I' for key 'mdl_quesattestepdata_attna_uix'
      INSERT INTO mdl_question_attempt_step_data (attemptstepid,name,value) VALUES(?,?,?)
      [array (
      0 => 2,
      1 => '_var_I',
      2 => '7.5',
      )]
      Stack trace:
       
          * line 397 of /lib/dml/moodle_database.php: dml_write_exception thrown
          * line 878 of /lib/dml/mysqli_native_moodle_database.php: call to moodle_database->query_end()
          * line 920 of /lib/dml/mysqli_native_moodle_database.php: call to mysqli_native_moodle_database->insert_record_raw()
          * line 130 of /question/engine/datalib.php: call to mysqli_native_moodle_database->insert_record()
          * line 98 of /question/engine/datalib.php: call to question_engine_data_mapper->insert_question_attempt_step()
          * line 71 of /question/engine/datalib.php: call to question_engine_data_mapper->insert_question_attempt()
          * line 93 of /question/engine/lib.php: call to question_engine_data_mapper->insert_questions_usage_by_activity()
          * line 198 of /mod/quiz/startattempt.php: call to question_engine::save_questions_usage_by_activity()

      The problem in 2.1 is that databases can use case insensitive tables, and the question engine creates a unique index on the variable names that are used in the calculations. In fact the standard Moodle setup instructions recommend using the "utf8_unicode_ci" collation for MySQL which is case insensitive (see http://docs.moodle.org/20/en/Create_Moodle_site_database). And the question documentation does not point out that variables need to be unique, ignoring case.

      In versions prior to 2.1, this problem was not as apparent, as the unique index did not exist. There still must have been problem, but they didn't seem to be as noticeable. Now, in 2.1, the quizzes break completely. This will be a major problem for users upgrading from earlier versions of Moodle that have these types of questions defined.

      I'm not sure how to solve this. Perhaps a case-sensitive case in the code? Or something that makes sure that the tables are not case insensitive?

        Attachments

          Issue Links

            Activity

              People

              • Votes:
                8 Vote for this issue
                Watchers:
                20 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved:
                  Fix Release Date:
                  14/Nov/16