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

(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

   [...]