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

Converting Database encoding with minus("-") in Database does not work

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Development in progress
    • Priority: Minor
    • Resolution: Unresolved
    • Affects Version/s: 3.7.5, 3.8.2
    • Fix Version/s: None
    • Component/s: Database SQL/XMLDB
    • Labels:
      None
    • Database:
      MySQL
    • Testing Instructions:
      Hide

      (difficulty: medium, requires administrator access to the database)

      Prepare the test environment

      1. Create the environment required to install a Moodle instance e.g. using the Moodle Docker Toolbox but altering its original setup to use a DB name with an hyphen e.g. moo-dle and preventing the creation of that DB since we need a different collation from the one set into the DB server:

        # git diff
        diff --git a/base.yml b/base.yml
        index 2ede0f9..b8d6ce6 100755
        --- a/base.yml
        +++ b/base.yml
        @@ -9,7 +9,7 @@ services:
               - "${ASSETDIR}/web/apache2_faildumps.conf:/etc/apache2/conf-enabled/apache2_faildumps.conf"
             environment:
               MOODLE_DOCKER_DBTYPE: pgsql
        -      MOODLE_DOCKER_DBNAME: moodle
        +      MOODLE_DOCKER_DBNAME: moo-dle
               MOODLE_DOCKER_DBUSER: moodle
               MOODLE_DOCKER_DBPASS: "m@0dl3ing"
               MOODLE_DOCKER_BROWSER: firefox
        diff --git a/db.mysql.yml b/db.mysql.yml
        index 368785f..bf6d099 100644
        --- a/db.mysql.yml
        +++ b/db.mysql.yml
        @@ -16,4 +16,4 @@ services:
               MYSQL_ROOT_PASSWORD: "m@0dl3ing"
               MYSQL_USER: moodle
               MYSQL_PASSWORD: "m@0dl3ing"
        -      MYSQL_DATABASE: moodle
        +      #MYSQL_DATABASE: moodle
        

      2. Start the Moodle Docker environment:

        # bin/moodle-docker-compose up -d
        

      3. Create a DB with a "wrong" collation, to let the conversion tool start doing its job:

        # bin/moodle-docker-compose exec db mysql -u root -pm@0dl3ing -e 'CREATE DATABASE `moo-dle` DEFAULT CHARACTER SET utf8 DEFAULT COLLATE utf8_general_ci;'
        

      4. Let the moodle user access that DB and check for its wrong collation:

        # bin/moodle-docker-compose exec db mysql -u root -pm@0dl3ing -e "GRANT ALL PRIVILEGES ON \`moo-dle\`.* TO 'moodle'@'%'; FLUSH PRIVILEGES;"
        # bin/moodle-docker-compose exec db mysql -u moodle -pm@0dl3ing moo-dle -e 'SELECT @@character_set_database, @@collation_database;'
        +--------------------------+----------------------+
        | @@character_set_database | @@collation_database |
        +--------------------------+----------------------+
        | utf8                     | utf8_general_ci      |
        +--------------------------+----------------------+
        

      5. Set the Moodle schema into that DB:

        # bin/moodle-docker-compose exec webserver php admin/cli/install_database.php --agree-license --fullname="Docker moodle" --shortname="docker_moodle" --adminpass="test" --adminemail="admin@example.com"
        

      6. Check the collation of the tables being "wrong" i.e. not like what the tool will set by default (see below):

        # bin/moodle-docker-compose exec db mysql -u moodle -pm@0dl3ing moo-dle -e "SHOW TABLE STATUS WHERE Name LIKE BINARY 'm_%'"
        +--------------------------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-------------+----------+-----------------------+--------------------------------------------------------------+
        | Name                           | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time         | Update_time         | Check_time | Collation   | Checksum | Create_options        | Comment                                                      |
        +--------------------------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-------------+----------+-----------------------+--------------------------------------------------------------+
        | m_analytics_indicator_calc     | InnoDB |      10 | Compressed |    0 |              0 |        8192 |               0 |        16384 |         0 |              1 | 2020-04-27 16:57:06 | NULL                | NULL       | utf8mb4_bin |     NULL | row_format=COMPRESSED | Stored indicator calculations
        [...]
        

      Before applying the fix

      1. Run the Conversion Tool and expect an error like the one in this issue:

        # bin/moodle-docker-compose exec webserver php admin/cli/mysql_collation.php --collation=utf8mb4_unicode_ci
        Converting database to 'utf8mb4_unicode_ci' for http://localhost:8000:
        Error: Tried to alter the database with no success. Please try manually changing the database
                            to the new collation and character set and then run this script again.
        

      After applying the fix

      1. Run again the Conversion Tool and expect no errors at all:

         # bin/moodle-docker-compose exec webserver php admin/cli/mysql_collation.php --collation=utf8mb4_unicode_ci
        [...]
        

      2. Check for the table collation, now being changed compared to what reported above:

        # bin/moodle-docker-compose exec db mysql -u root -pm@0dl3ing moo-dle -e "SHOW TABLE STATUS WHERE Name LIKE BINARY 'm_%'"
        +--------------------------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+--------------------+----------+-----------------------+--------------------------------------------------------------+
        | Name                           | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time         | Update_time         | Check_time | Collation          | Checksum | Create_options        | Comment                                                      |
        +--------------------------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+--------------------+----------+-----------------------+--------------------------------------------------------------+
        | m_analytics_indicator_calc     | InnoDB |      10 | Compressed |    0 |              0 |        8192 |               0 |        16384 |         0 |              1 | 2020-04-27 17:01:21 | NULL                | NULL       | utf8mb4_unicode_ci |     NULL | row_format=COMPRESSED | Stored indicator calculations                            
        [...]
        

      Show
      (difficulty: medium, requires administrator access to the database) Prepare the test environment Create the environment required to install a Moodle instance e.g. using the Moodle Docker Toolbox but altering its original setup to use a DB name with an hyphen e.g. moo-dle and preventing the creation of that DB since we need a different collation from the one set into the DB server: # git diff diff --git a/base.yml b/base.yml index 2ede0f9..b8d6ce6 100755 --- a/base.yml +++ b/base.yml @@ -9,7 +9,7 @@ services: - "${ASSETDIR}/web/apache2_faildumps.conf:/etc/apache2/conf-enabled/apache2_faildumps.conf" environment: MOODLE_DOCKER_DBTYPE: pgsql - MOODLE_DOCKER_DBNAME: moodle + MOODLE_DOCKER_DBNAME: moo-dle MOODLE_DOCKER_DBUSER: moodle MOODLE_DOCKER_DBPASS: "m@0dl3ing" MOODLE_DOCKER_BROWSER: firefox diff --git a/db.mysql.yml b/db.mysql.yml index 368785f..bf6d099 100644 --- a/db.mysql.yml +++ b/db.mysql.yml @@ -16,4 +16,4 @@ services: MYSQL_ROOT_PASSWORD: "m@0dl3ing" MYSQL_USER: moodle MYSQL_PASSWORD: "m@0dl3ing" - MYSQL_DATABASE: moodle + #MYSQL_DATABASE: moodle Start the Moodle Docker environment: # bin/moodle-docker-compose up -d Create a DB with a "wrong" collation, to let the conversion tool start doing its job: # bin/moodle-docker-compose exec db mysql -u root -pm@0dl3ing -e 'CREATE DATABASE `moo-dle` DEFAULT CHARACTER SET utf8 DEFAULT COLLATE utf8_general_ci;' Let the moodle user access that DB and check for its wrong collation: # bin/moodle-docker-compose exec db mysql -u root -pm@0dl3ing -e "GRANT ALL PRIVILEGES ON \`moo-dle\`.* TO 'moodle'@'%'; FLUSH PRIVILEGES;" # bin/moodle-docker-compose exec db mysql -u moodle -pm@0dl3ing moo-dle -e 'SELECT @@character_set_database, @@collation_database;' +--------------------------+----------------------+ | @@character_set_database | @@collation_database | +--------------------------+----------------------+ | utf8 | utf8_general_ci | +--------------------------+----------------------+ Set the Moodle schema into that DB: # bin/moodle-docker-compose exec webserver php admin/cli/install_database.php --agree-license --fullname="Docker moodle" --shortname="docker_moodle" --adminpass="test" --adminemail="admin@example.com" Check the collation of the tables being "wrong" i.e. not like what the tool will set by default (see below): # bin/moodle-docker-compose exec db mysql -u moodle -pm@0dl3ing moo-dle -e "SHOW TABLE STATUS WHERE Name LIKE BINARY 'm_%'" +--------------------------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-------------+----------+-----------------------+--------------------------------------------------------------+ | Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment | +--------------------------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-------------+----------+-----------------------+--------------------------------------------------------------+ | m_analytics_indicator_calc | InnoDB | 10 | Compressed | 0 | 0 | 8192 | 0 | 16384 | 0 | 1 | 2020-04-27 16:57:06 | NULL | NULL | utf8mb4_bin | NULL | row_format=COMPRESSED | Stored indicator calculations [...] Before applying the fix Run the Conversion Tool and expect an error like the one in this issue: # bin/moodle-docker-compose exec webserver php admin/cli/mysql_collation.php --collation=utf8mb4_unicode_ci Converting database to 'utf8mb4_unicode_ci' for http://localhost:8000: Error: Tried to alter the database with no success. Please try manually changing the database to the new collation and character set and then run this script again. After applying the fix Run again the Conversion Tool and expect no errors at all: # bin/moodle-docker-compose exec webserver php admin/cli/mysql_collation.php --collation=utf8mb4_unicode_ci [...] Check for the table collation, now being changed compared to what reported above: # bin/moodle-docker-compose exec db mysql -u root -pm@0dl3ing moo-dle -e "SHOW TABLE STATUS WHERE Name LIKE BINARY 'm_%'" +--------------------------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+--------------------+----------+-----------------------+--------------------------------------------------------------+ | Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment | +--------------------------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+--------------------+----------+-----------------------+--------------------------------------------------------------+ | m_analytics_indicator_calc | InnoDB | 10 | Compressed | 0 | 0 | 8192 | 0 | 16384 | 0 | 1 | 2020-04-27 17:01:21 | NULL | NULL | utf8mb4_unicode_ci | NULL | row_format=COMPRESSED | Stored indicator calculations [...]
    • Affected Branches:
      MOODLE_37_STABLE, MOODLE_38_STABLE
    • Pull from Repository:
    • Pull Master Branch:
      m39_MDL-68531_MySQL_Tool_Collation_Hyphen_DB_Name

      Description

      Moodle Version: 3.7.5
      mariadb: 10.1.44
      System: Ubuntu 18.04

      I try to convert database encoding from utf8 to utf8mb4 with this manual: https://docs.moodle.org/37/en/MySQL_full_unicode_support

      With one Installation no problem with the other I get an error:

      /opt/plesk/php/7.3/bin/php admin/cli/mysql_collation.php --collation=utf8mb4_unicode_ci
      Converting database to 'utf8mb4_unicode_ci' for ********:
      Error: Tried to alter the database with no success. Please try manually changing the database
                          to the new collation and character set and then run this script again.

      I have take a look on the code:

      https://github.com/moodle/moodle/blob/master/admin/cli/mysql_collation.php#L122

      If i change

      $sql = "ALTER DATABASE $CFG->dbname DEFAULT CHARACTER SET $charset DEFAULT COLLATE = $collation";

      to

      $sql = "ALTER DATABASE `$CFG->dbname` DEFAULT CHARACTER SET $charset DEFAULT COLLATE = $collation";

      Now it works. The Database name must be escaped with ``.

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              matteo Matteo Scaramuccia
              Reporter:
              svennissel Sven Nissel
              Peer reviewer:
              Neill Magill
              Participants:
              Component watchers:
              Andrew Nicols, Jun Pataleta, Michael Hawkins, Shamim Rezaie, Simey Lameze
              Votes:
              1 Vote for this issue
              Watchers:
              3 Start watching this issue

                Dates

                Created:
                Updated: