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

mysql_collation.php fails if prefix is blank

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Closed
    • Priority: Minor
    • Resolution: Fixed
    • Affects Version/s: 3.9.3, 3.10.3, 3.11, 4.0
    • Fix Version/s: 3.9.8, 3.10.5
    • Component/s: Database SQL/XMLDB
    • Labels:
    • Database:
      MySQL
    • Testing Instructions:
      Hide

      0) Requirements

      1. Developer is surely needed to test this.
      2. MySQL 8 up and running, can use docker image easily.
      3. Be able to run manual SQL commands against that MySQL 8 instance.
      4. Ability to modify some PHP scripts.

      1) Setting up

      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 pointing to the created database, either via the web interface or command line ensuring that the database table 'prefix' setting is set to blank (not 'mdl_').

      2) mysql_collation.php tests

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

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

      2. Verify that there aren't 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
      

      3) mysql_compressed_rows.php

      0. Note: The following 2 steps are to force the system to detect the groups table to require fixing.

      1. Connect to the MySQL from client and execute this SQL command:

      ALTER TABLE `groups` row_format = compact;
      COMMIT;
      

      2. Edit the admin/cli/mysql_compressed_rows.ph y change the THREE occurrences of:

      if ($size <= $generator::ANTELOPE_MAX_ROW_SIZE) {
      

      to:

      if ($size <= $generator::ANTELOPE_MAX_ROW_SIZE && $table != 'groups') {
      

      3. Run the CLI script:

      $ php admin/cli/mysql_compressed_rows.php --list
      

      4. Verify that, apart from other tables... the "groups" one is shown as follows:

      groups                          Compact     (needs fixing)
      

      5. Run the CLI script:

      php admin/cli/mysql_compressed_rows.php --showsql
      

      6. Verify that the suggested SQL to execute, include the groups table (with back-ticks) like this:

      ALTER TABLE `groups` ROW_FORMAT=Compressed;
      

      7. Execute the the SQL shown in the previous step in the MySQL client.

      8. Run again the cli script as it was in step 3.

      $ php admin/cli/mysql_compressed_rows.php --list
      

      9. Verify that the groups table is listed, but it now shows the "Compressed" format and it does not say "(needs fixing)" anymore.

      Note, we cannot use the --fix option of the CLI, because it uses some MySQL parameters not available in MySQL 8 anymore. MDL-71512 has been created to fix that.

      Show
      0) Requirements 1. Developer is surely needed to test this. 2. MySQL 8 up and running, can use docker image easily. 3. Be able to run manual SQL commands against that MySQL 8 instance. 4. Ability to modify some PHP scripts. 1) Setting up 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 pointing to the created database, either via the web interface or command line ensuring that the database table 'prefix' setting is set to blank (not 'mdl_'). 2) mysql_collation.php tests 1. After installation has completed, run the script to migrate to the utf8mb4 character set: php admin/cli/mysql_collation.php --collation=utf8mb4_unicode_ci 2. Verify that there aren't 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 3) mysql_compressed_rows.php 0. Note: The following 2 steps are to force the system to detect the groups table to require fixing. 1. Connect to the MySQL from client and execute this SQL command: ALTER TABLE `groups` row_format = compact; COMMIT; 2. Edit the admin/cli/mysql_compressed_rows.ph y change the THREE occurrences of: if ($size <= $generator::ANTELOPE_MAX_ROW_SIZE) { to: if ($size <= $generator::ANTELOPE_MAX_ROW_SIZE && $table != 'groups') { 3. Run the CLI script: $ php admin/cli/mysql_compressed_rows.php --list 4. Verify that, apart from other tables... the "groups" one is shown as follows: groups Compact (needs fixing) 5. Run the CLI script: php admin/cli/mysql_compressed_rows.php --showsql 6. Verify that the suggested SQL to execute, include the groups table (with back-ticks) like this: ALTER TABLE `groups` ROW_FORMAT=Compressed; 7. Execute the the SQL shown in the previous step in the MySQL client. 8. Run again the cli script as it was in step 3. $ php admin/cli/mysql_compressed_rows.php --list 9. Verify that the groups table is listed, but it now shows the "Compressed" format and it does not say "(needs fixing)" anymore. Note, we cannot use the --fix option of the CLI, because it uses some MySQL parameters not available in MySQL 8 anymore. MDL-71512 has been created to fix that.
    • Affected Branches:
      MOODLE_310_STABLE, MOODLE_311_STABLE, MOODLE_39_STABLE, MOODLE_400_STABLE
    • Fixed Branches:
      MOODLE_310_STABLE, MOODLE_39_STABLE
    • Pull from Repository:
    • Pull 3.9 Branch:
    • Pull 3.10 Branch:
      MDL-70181_310
    • Pull 3.11 Branch:
      MDL-70181_311
    • Pull Master Branch:

      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

        1. MDL-70181_2_2.png
          MDL-70181_2_2.png
          29 kB
        2. MDL-70181_3_4.png
          MDL-70181_3_4.png
          13 kB
        3. MDL-70181_3_6.png
          MDL-70181_3_6.png
          24 kB
        4. MDL-70181_3_9.png
          MDL-70181_3_9.png
          11 kB

          Issue Links

            Activity

              People

              Assignee:
              leonstr Leon Stringer
              Reporter:
              leonstr Leon Stringer
              Peer reviewer:
              Eloy Lafuente (stronk7) Eloy Lafuente (stronk7)
              Integrator:
              Adrian Greeve Adrian Greeve
              Tester:
              Mihail Geshoski Mihail Geshoski
              Participants:
              Component watchers:
              Andrew Lyons, Dongsheng Cai, Huong Nguyen, Jun Pataleta, Michael Hawkins, Shamim Rezaie, Simey Lameze
              Votes:
              0 Vote for this issue
              Watchers:
              7 Start watching this issue

                Dates

                Created:
                Updated:
                Resolved:
                Fix Release Date:
                12/Jul/21

                  Time Tracking

                  Estimated:
                  Original Estimate - 0 minutes
                  0m
                  Remaining:
                  Remaining Estimate - 0 minutes
                  0m
                  Logged:
                  Time Spent - 4 hours, 45 minutes
                  4h 45m