It is recommended to setup moodle with CREATE DATABASE moodle DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;. For the table mdl_question_numerical_units a unique index mdl_quesnumeunit_queuni_uix over the columns question and unit is created. If the user adds a numerical question with unit handling set to The unit must be given, and will be graded. and at least two units, which are mapped to the same value, an "error writing to database" occurs.
- create a new question of question type numerical
- fill in the required fields
- set unit handling to The unit must be given, and will be graded.
- add value m3 for unit 1 (this is "m" + "three")
- add value m³ for unit 2 (this is "m" + "superscript three")
- click save changes
m3 is U+006D LATIN SMALL LETTER M and U+0033 DIGIT THREE
m³ is U+006D LATIN SMALL LETTER M and U+00B3 SUPERSCRIPT THREE
WHAT WAS EXPECTED
- The question is saved to the database
WHAT ACTUALLY HAPPENS
- Error writing to the database
When using the collation unicode_ci both characters U+00B3 SUPERSCRIPT THREE and U+0033 DIGIT THREE are considered to be equal, and the uniqueness constraint for the index mdl_quesnumeunit_queuni_uix is no longer met.
MDL-36208 Case-insensitive collations in the database cause problems and should be changed to case-sensitive