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

Upgrade to 3.3 fails because of very long index in mdl_question_categories on MariaDB in utf8mb4_general_ci

    XMLWordPrintable

    Details

    • Testing Instructions:
      Hide
      • The test requires mysql or mariadb.
      1. Create a 3.1 installation of moodle.
      2. Next we are going to make sure that the row format for mdl_question_categories is set to 'compact'. Run the following query on your database:

        ALTER TABLE `mdl_question_categories` ROW_FORMAT=COMPACT
        

      3. If you wish to check that the query was successful run the following:

        SELECT row_format
        FROM information_schema.tables
        WHERE table_schema = '{NameOfYourDatabase}' AND table_name = 'mdl_question_categories'
        

      4. If your collation is utf8_unicode_ci or something similar (3 byte not 4). Then run the collation cli script: php admin/cli/mysql_collation.php --collation=utf8mb4_unicode_ci
      5. Check out / upgrade to the branch that you are testing (upgrade from 3.1 to 3.2 and from 3.1 to 3.3 etc.). The upgrade process should not fail.
      Show
      The test requires mysql or mariadb. Create a 3.1 installation of moodle. Next we are going to make sure that the row format for mdl_question_categories is set to 'compact'. Run the following query on your database: ALTER TABLE `mdl_question_categories` ROW_FORMAT=COMPACT If you wish to check that the query was successful run the following: SELECT row_format FROM information_schema.tables WHERE table_schema = '{NameOfYourDatabase}' AND table_name = 'mdl_question_categories' If your collation is utf8_unicode_ci or something similar (3 byte not 4). Then run the collation cli script: php admin/cli/mysql_collation.php --collation=utf8mb4_unicode_ci Check out / upgrade to the branch that you are testing (upgrade from 3.1 to 3.2 and from 3.1 to 3.3 etc.). The upgrade process should not fail.
    • Affected Branches:
      MOODLE_33_STABLE
    • Fixed Branches:
      MOODLE_32_STABLE, MOODLE_33_STABLE
    • Pull from Repository:
    • Pull Master Branch:
      wip-MDL-59561-master
    • Sprint:
      3.4 Sprint 2, 3.4 Sprint 3

      Description

       

      I received the following stack trace when attempting to upgrade my development environment from Moodle 3.1.5 to Moodle 3.3.1:

       

      Debug info: Index column size too large. The maximum column size is 767 bytes.
      CREATE UNIQUE INDEX mdl_quescate_consta_uix ON mdl_question_categories (contextid, stamp)
      Error code: ddlexecuteerror
      Stack trace:
      line 492 of /lib/dml/moodle_database.php: ddl_change_structure_exception thrown
      line 1000 of /lib/dml/mysqli_native_moodle_database.php: call to moodle_database->query_end()
      line 77 of /lib/ddl/database_manager.php: call to mysqli_native_moodle_database->change_database_structure()
      line 826 of /lib/ddl/database_manager.php: call to database_manager->execute_sql_arr()
      line 2165 of /lib/db/upgrade.php: call to database_manager->add_index()
      line 1694 of /lib/upgradelib.php: call to xmldb_main_upgrade()
      line 523 of /admin/index.php: call to upgrade_core()

      The specific task it failed on was "Updating duplicate question category stamp - 9/9".

      I had previously gone through the steps at https://docs.moodle.org/33/en/MySQL_full_unicode_support to convert the database to utf8mb4. My database server has required configuration:

      innodb_file_per_table=1
      innodb_file_format=barracuda
      innodb_large_prefix=on

      All the relevant environment checks passed prior to attempting the upgrade. This is what the script said about mdl_question_categories:

      mdl_question_categories - CONVERTED
      name - NO CHANGE
      info - NO CHANGE
      stamp - NO CHANGE

      I'm running MariaDB 10.1.18.

       

        Attachments

          Issue Links

            Activity

              People

              • Votes:
                0 Vote for this issue
                Watchers:
                4 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved:
                  Fix Release Date:
                  11/Sep/17