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

    • MOODLE_33_STABLE
    • MOODLE_32_STABLE, MOODLE_33_STABLE
    • wip-MDL-59561-master
    • 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.
    • 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

              abgreeve Adrian Greeve
              cfulton Charles Fulton
              Marina Glancy Marina Glancy
              Andrew Lyons Andrew Lyons
              John Okely John Okely
              Andrew Lyons, Huong Nguyen, Jun Pataleta, Michael Hawkins, Shamim Rezaie, Simey Lameze, Safat Shahin, Tim Hunt, Andrew Lyons, Huong Nguyen, Jun Pataleta, Michael Hawkins, Shamim Rezaie, Simey Lameze
              Votes:
              0 Vote for this issue
              Watchers:
              4 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:
                11/Sep/17