Error: Error reading from database

Error: Error reading from database

by Zhipeng Zhang -
Number of replies: 5

When I update my site from 3.6.1 to 3.6.3+, I got the following error:


It seem that I can't log in. When I click "continue" I will get back to the log in page.

My site is running on window2008r2+IIS7.5+php7.1.21+MySQL5.7.14.

I can visit the database with PHPMyAdin:

Leon Stringer suggested that I try to "Force a debugging mode" by add lines in config.php, which I can't understand at all. Am I doing right as shown in the following?


Then I got the following clues:


Can anyone tell me what is going on? 

What shall I do ?

thank you so much.

Average of ratings: -
In reply to Zhipeng Zhang

Re: Error: Error reading from database

by Ken Task -
Picture of Particularly helpful Moodlers

3.6.3+ seems to be more sensitive to DB settings.
Default character set for MySQL5.7.14 isn't utf8mb4 with collation utf8m84_general_ci;

Check the config.php file for the DB settings.
is there a 'dbcollation' => 'utf8mb4_unicode_ci' line?

There is a script in moodlecode/admin/cli/

php mysql_collation.php

will display help screen.

-l, --list Show table and column information

All tables and columns must now have same collation.

When you run command with -l it will display every table
and ever column in each table.

Tail end of command shuld look something like:

mdl_workshopform_rubric_config          utf8mb4_general_ci
    layout                              utf8mb4_general_ci
mdl_workshopform_rubric_levels          utf8mb4_general_ci
    definition                          utf8mb4_general_ci

Table collations summary for urltosite:
utf8mb4_general_ci: 1453

Same script with: --collation=utf8mb4_general_ci will Convert MySQL tables and their columns to utf8mb4_general_ci collation

Shouldn't have to restart mysql service ... just attempt to login again.

'spirit of sharing', Ken


Average of ratings: Useful (2)
In reply to Ken Task

回复: Re: Error: Error reading from database

by Zhipeng Zhang -

Thank you very much Ken for your kind instructions.

I got an interesting finding: When I get the error report, and I change the character set in config.php from utf8mb4 to utf8, I can log in, and it seems to work normally. But if I restart the IIS after the change, the same error comes again.

So, what is going on?

By the way, i found a lot of the tables in my database are still of utf8 instead of utf8mb4. Does it matter?

Since i am really a layman in IT, how can I convert all the tables from utf8 to utf8mb4 once and for all?

thank you!

Average of ratings: -
In reply to Zhipeng Zhang

Re: 回复: Re: Error: Error reading from database

by Ken Task -
Picture of Particularly helpful Moodlers

Since you appear to be from an Asian country, I assume using utf8mb4 and utf8mb4_unicode_ci collation would be best.

Please see:
https://docs.moodle.org/36/en/MySQL_full_unicode_support

If the line is left in config.php, one *must* change settings in my.ini/cnf
config file for MySQL to make them permanant, but those apply only to new databases.
Changes to my.cnf/my.ini require restart of MySQL daemon/service.

After restarting mysqld suggest you check it's log to see if there any errors reported on restart.

Your Moodle DB already exist and needs to be converted using the  script previously mentioned.

Yes, it makes a difference.

'spirit of sharing', Ken


Average of ratings: Useful (2)
In reply to Ken Task

回复: Re: 回复: Re: Error: Error reading from database

by Zhipeng Zhang -

Thanks a lot Ken. 

When I managed to convert all the tables in the DB from utf8_unicode_ci  to  utf8mb4_unicode_ci, problem solved.

It seems that we could draw a conclusion: the breakdown was caused by the character set of data tables.

Thank you again Ken for your great help.

Zhang

Average of ratings: -
In reply to Zhipeng Zhang

Re: 回复: Re: 回复: Re: Error: Error reading from database

by Caleb Lee -

Those scripts didn't work for my website. so many error message, and even enabling debugging didn't show any thing else. Also, Permission Denied message, etc, etc.

I went to phpmyadmin, and selected all tables (The half was already utf8mb4_unicode_ci, but the other was still utf8_unicode_ci), and then changed to utf8mb4_unicode_ci in operation function.

It was really easier to deal with Unix shell scripts.

Average of ratings: -