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

Calculated Questions Require Case Sensitive Data Tables

    XMLWordPrintable

Details

    • MySQL, Microsoft SQL
    • MOODLE_21_STABLE, MOODLE_23_STABLE, MOODLE_30_STABLE, MOODLE_31_STABLE, MOODLE_32_STABLE
    • MOODLE_30_STABLE, MOODLE_31_STABLE
    • wip-MDL-29332-master-3
    • 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.
    • 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).
    • 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

              marina Marina Glancy
              mchurch Mike Churchward
              Simey Lameze Simey Lameze
              Eloy Lafuente (stronk7) Eloy Lafuente (stronk7)
              Adrian Greeve Adrian Greeve
              Safat Shahin, Tim Hunt, Andrew Lyons, Huong Nguyen, Jun Pataleta, Michael Hawkins, Shamim Rezaie, Simey Lameze, Stevani Andolo
              Votes:
              8 Vote for this issue
              Watchers:
              21 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:
                14/Nov/16