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

mysql_collation.php fails if prefix is blank

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Waiting for peer review
    • Priority: Minor
    • Resolution: Unresolved
    • Affects Version/s: 3.9.3
    • Fix Version/s: None
    • Component/s: Database SQL/XMLDB
    • Labels:
    • Database:
      MySQL
    • Testing Instructions:
      Hide

      Steps

      1. Set up a server with MySQL 8.0.2 or greater as the Moodle database.

      2. Create the Moodle database using the utf8 character set and utf8_unicode_ci default collation, e.g.:

      CREATE DATABASE `mdl70181.test` DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci;
      CREATE USER 'mdl70181.test'@'localhost' IDENTIFIED BY 'yourpassword';
      GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,CREATE TEMPORARY TABLES,DROP,INDEX,ALTER ON `mdl70181.test`.* TO 'mdl70181.test'@'localhost';

      3. Install Moodle either via the web interface or command line ensuring that the database table 'prefix' setting is set to blank (not 'mdl_').

      4. After installation has completed, run the script to migrate to the utf8mb4 character set:

      php admin/cli/mysql_collation.php --collation=utf8mb4_unicode_ci

      Expected results

      There should be no errors. Example last five lines tested with Moodle 3.9:
      workshopform_rubric_config - CONVERTED
      layout - NO CHANGE
      workshopform_rubric_levels - CONVERTED
      definition - NO CHANGE
      Converted: 428, skipped: 1119, errors: 0

      Show
      Steps 1. Set up a server with MySQL 8.0.2 or greater as the Moodle database. 2. Create the Moodle database using the utf8 character set and utf8_unicode_ci default collation, e.g.: CREATE DATABASE `mdl70181.test` DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci; CREATE USER 'mdl70181.test'@'localhost' IDENTIFIED BY 'yourpassword'; GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,CREATE TEMPORARY TABLES,DROP,INDEX,ALTER ON `mdl70181.test`.* TO 'mdl70181.test'@'localhost'; 3. Install Moodle either via the web interface or command line ensuring that the database table 'prefix' setting is set to blank (not 'mdl_'). 4. After installation has completed, run the script to migrate to the utf8mb4 character set: php admin/cli/mysql_collation.php --collation=utf8mb4_unicode_ci Expected results There should be no errors. Example last five lines tested with Moodle 3.9: workshopform_rubric_config - CONVERTED layout - NO CHANGE workshopform_rubric_levels - CONVERTED definition - NO CHANGE Converted: 428, skipped: 1119, errors: 0
    • Affected Branches:
      MOODLE_39_STABLE
    • Pull from Repository:
    • Pull 3.9 Branch:
      MDL-70181-quote_db_objects-39
    • Pull 3.9 Diff URL:
    • Pull 3.10 Branch:
      MDL-70181-quote_db_objects-310
    • Pull 3.10 Diff URL:
    • Pull Master Branch:
      MDL-70181-quote_db_objects
    • Pull Master Diff URL:

      Description

      With MySQL 8.0 and $CFG->prefix = '' the admin/cli/mysql_collation.php script fails with "Tried to convert groups, but there was a problem. Please check the details of this table and try again.".

      See also: Cannot read database.

      Note also a related issue: if the database has a character such as a '.' in the name (e.g. moodle.site1) then the script fails in a similar way, i.e. ALTER DATABASE moodle.site1 ... fails but ALTER DATABASE `moodle.site1` ... will work.

      To reproduce:

      1. Create a new Moodle database on MySQL 8.0 with character set utf8 and default collation utf8_unicode_ci.
      2. Install a Moodle site using this database with $CFG->prefix set to an empty string.
      3. Run php admin/cli/mysql_collation.php --collation=utf8mb4_unicode_ci

      Expected results: The database is migrated to the utf8mb4 character set with utf8mb4_unicode_ci default collation.

      Actual results:
      {{groups - Error: Tried to convert groups, but there was a problem. Please check the details of this
      table and try again.}}

        Attachments

          Activity

            People

            Assignee:
            leonstr Leon Stringer
            Reporter:
            leonstr Leon Stringer
            Participants:
            Component watchers:
            Andrew Nicols, Jun Pataleta, Michael Hawkins, Shamim Rezaie, Simey Lameze
            Votes:
            0 Vote for this issue
            Watchers:
            1 Start watching this issue

              Dates

              Created:
              Updated: