Cannot read database

Cannot read database

Yaroslav Dimitrov -
回帖数：4

Hello everyone,

I have a problem with my moodle installation. When I try to create a new user I get an error message "Cannot read database". I look in the forum for possible solutions of this problem and I found a thread that says that the problem I caused my a missconfigured Database coalotion. It suggests then to execute the following command to solve this issue:

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

When I do this it runs flawless untin the step where it check the groups table and then it throws the following error:

Default exception handler: Fehler beim Lesen der Datenbank 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 1212 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()

!!! Fehler beim Lesen der Datenbank !!!
!! 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 1212 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()
 !!

I tried to find anything about this error, but unfortunately without any success. Could someone tell me what went wrong here? Why the script fails?

Thanks in advace for any suggestion.

平均分： -
回复Yaroslav Dimitrov

Re: Cannot read database

Leon Stringer -
Core developers的头像 Particularly helpful Moodlers的头像

What could be causing this:

  1. The database is MySQL 8.0.
  2. In config.php the $CFG->prefix is blank ('').
  3. admin/cli/mysql_collation.php doesn't quote table names with backticks (``).

MySQL 8.0.2 introduced a new reserved word: GROUPS. Moodle has a table that would be called mdl_groups with the default prefix, but with no prefix would be groups conflicting with the reserved word.

SHOW FULL COLUMNS FROM `groups` WHERE collation IS NOT NULL (i.e. with backticks) should work, you could try this if you have database access to run queries.

If this is correct then I think it is a bug that needs fixing. It is unusual to have a blank $CFG->prefix, I don't think the standard installer allows it, you have to manually configure config.php for this.

I'm not sure what advice to give, if you're familiar with PHP you could go through mysql_collation.php and add backticks to table names starting at line 166:

166         $sql = "SHOW FULL COLUMNS FROM `$table->name` WHERE collation IS NOT NULL";
167         $rs2 = $DB->get_recordset_sql($sql);
平均分：Useful (1)
回复Leon Stringer

Re: Cannot read database

Yaroslav Dimitrov -
That's it!
The workaround with the backquotes worked perfectly. Thousend thanks!
I am using indeed MySQL 8.0.x. The interesting thing is that the installer let me install the moodle instance without db-prefix. I did not make the config.php file manually. I will have that in mind for future installations.

Anyway, thanks again. You really saved my day!
平均分： -