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

    • 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

       

      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.

       

            abgreeve Adrian Greeve
            cfulton Charles Fulton
            Marina Glancy Marina Glancy
            Andrew Lyons Andrew Lyons
            John Okely John Okely
            Votes:
            0 Vote for this issue
            Watchers:
            4 Start watching this issue

              Created:
              Updated:
              Resolved:

                Error rendering 'clockify-timesheets-time-tracking-reports:timer-sidebar'. Please contact your Jira administrators.