mysql_collation.php and groups table problem

mysql_collation.php and groups table problem

by Lukáš Mižoch -
Number of replies: 7

Hello, I tried to change collation for my Moodle by running this command:

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

Unfortunately I got bunch of errors with table groups:

Default exception handler: Chyba čtení z databáze Debug: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'groups WHERE collation IS NOT NULL' at line 1
SHOW FULL COLUMNS FROM groups WHERE collation IS NOT NULL
[array (
)]
Error code: dmlreadexception
* line 486 of /lib/dml/moodle_database.php: dml_read_exception thrown
* line 1186 of /lib/dml/mysqli_native_moodle_database.php: call to moodle_database->query_end()
* line 167 of /admin/cli/mysql_collation.php: call to mysqli_native_moodle_database->get_recordset_sql()

!!! Chyba čtení z databáze !!!
!! You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'groups WHERE collation IS NOT NULL' at line 1
SHOW FULL COLUMNS FROM groups WHERE collation IS NOT NULL
[array (
)]
Error code: dmlreadexception !!
!! Stack trace: * line 486 of /lib/dml/moodle_database.php: dml_read_exception thrown
* line 1186 of /lib/dml/mysqli_native_moodle_database.php: call to moodle_database->query_end()
* line 167 of /admin/cli/mysql_collation.php: call to mysqli_native_moodle_database->get_recordset_sql()
 !!

Only fix I could do was deleting table groups, run script again (without error) and then recreate groups from modified SQL backup.

Did somebody also run into this problem?

Average of ratings: -
In reply to Lukáš Mižoch

Re: mysql_collation.php and groups table problem

by Howard Miller -
Picture of Core developers Picture of Documentation writers Picture of Particularly helpful Moodlers Picture of Peer reviewers Picture of Plugin developers
Why (did you want to change your collation)?

The recommended collations for Moodle are utf8_unicode_ci or utf8mb4_unicode_ci. If you use something else then I'm not shocked that you run into problems (it will never have been tested)
Average of ratings: -
In reply to Howard Miller

Re: mysql_collation.php and groups table problem

by Lukáš Mižoch -

Because it's Czech server so we need Czech collation, not universal. E. g. letter CH is between H and I in Czech collation, but between C and D in universal. And I don't mention problem with accents.

Real question is what is wrong with table groups, because all other tables were converted correctly.

Average of ratings: -
In reply to Lukáš Mižoch

Re: mysql_collation.php and groups table problem

by Howard Miller -
Picture of Core developers Picture of Documentation writers Picture of Particularly helpful Moodlers Picture of Peer reviewers Picture of Plugin developers
Which mySQL version is this? I'm just wondering if you hit a MySQL 8 problem.

EDIT:
Just tried on my MySQL 8 setup

mysql> SHOW FULL COLUMNS FROM mdl_groups WHERE collation IS NOT NULL;
+--------------+--------------+--------------------+------+-----+---------+-------+---------------------------------+---------+
| Field        | Type         | Collation          | Null | Key | Default | Extra | Privileges                      | Comment |
+--------------+--------------+--------------------+------+-----+---------+-------+---------------------------------+---------+
| idnumber     | varchar(100) | utf8mb4_unicode_ci | NO   | MUL |         |       | select,insert,update,references |         |
| name         | varchar(254) | utf8mb4_unicode_ci | NO   |     |         |       | select,insert,update,references |         |
| description  | longtext     | utf8mb4_unicode_ci | YES  |     | NULL    |       | select,insert,update,references |         |
| enrolmentkey | varchar(50)  | utf8mb4_unicode_ci | YES  |     | NULL    |       | select,insert,update,references |         |
+--------------+--------------+--------------------+------+-----+---------+-------+---------------------------------+---------+
4 rows in set (0.01 sec)
Average of ratings: -
In reply to Howard Miller

Re: mysql_collation.php and groups table problem

by Lukáš Mižoch -

Yes, it's 8.0.19. In older versions there is no utf8mb4_cs_0900_ai_ci collation.

https://mysqlserverteam.com/new-collations-in-mysql-8-0-0/

https://dev.mysql.com/doc/refman/8.0/en/charset-collation-names.html

Average of ratings: -
In reply to Lukáš Mižoch

Re: mysql_collation.php and groups table problem

by Howard Miller -
Picture of Core developers Picture of Documentation writers Picture of Particularly helpful Moodlers Picture of Peer reviewers Picture of Plugin developers
The name 'groups' is a reserved word added in MySQL 8.0.2. I think you may have found a bug.

Although, I'm wondering why it isn't SHOW FULL COLUMNS FROM mdl_groups....  (with default settings anyway)
Average of ratings: Useful (2)
In reply to Howard Miller

Re: mysql_collation.php and groups table problem

by Lukáš Mižoch -

That's because I did not used prefix mdl_ when I installed Moodle. 😇

Average of ratings: -
In reply to Lukáš Mižoch

Re: mysql_collation.php and groups table problem

by Matteo Scaramuccia -
Picture of Core developers Picture of Peer reviewers Picture of Plugin developers

Hello Everyone,
it could have been probably addressed via MDL-66110 being the DB prefix not required when using MySQL.
Fixed in 3.6.6+ and 3.7.2+.

Otherwise, please file a new issue in the Tracker, linked to the above one and here.

HTH,
Matteo

Average of ratings: Useful (1)