I did the optimize and repair and I still get this message after I login.
Did a test upgrade from 3.4.6+ to latest 3.6.x ... MySQL 5.7.20
My charset is: utf8mb4
My collation is: utf8mb4_unicode_ci
Debug info: COLLATION 'utf8mb4_bin' is not valid for CHARACTER SET 'utf8'
SELECT * FROM mdl_user WHERE LOWER(username) COLLATE utf8mb4_bin = LOWER(?) AND deleted <> 1 AND mnethostid = ?
0 => 'lng',
1 => '4',
Error code: dmlreadexception
line 486 of /lib/dml/moodle_database.php: dml_read_exception thrown
line 1245 of /lib/dml/mysqli_native_moodle_database.php: call to moodle_database->query_end()
line 1571 of /lib/dml/moodle_database.php: call to mysqli_native_moodle_database->get_records_sql()
line 1543 of /lib/dml/moodle_database.php: call to moodle_database->get_record_sql()
line 4810 of /lib/moodlelib.php: call to moodle_database->get_record_select()
line 4319 of /lib/moodlelib.php: call to get_complete_user_data()
line 143 of /login/index.php: call to authenticate_user_login()
Did a ../admin/cli/mysql_collation.php --collation=utf8mb4_unicode_ci
and the mdl_user table came back with
- Error: Tried to convert mdl_user, but there was a problem. Please check the details of this table and try again.
Where do I check the details of this table? and how do I troubleshoot?
I think I need to replace something in my .sql file that I was using to import it into its database. A search or replace of some sort?
SHOW VARIABLES WHERE Variable_name LIKE 'character\_set\_%' OR Variable_name LIKE 'collation%';
| Variable_name | Value |
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character_set_database | utf8mb4 |
| character_set_filesystem | binary |
| character_set_results | utf8 |
| character_set_server | utf8 |
| character_set_system | utf8 |
| collation_connection | utf8_general_ci |
| collation_database | utf8mb4_unicode_ci |
| collation_server | utf8_general_ci |
Good news I fixed it by simply deleting the index associate with the mnethost and then the alter table and collation fix now works
Updating this morning, I've experienced this problem too with 3.6.3. I resolve this problem changing 'dbcollation' in config.php from utf8mb4_general_ci to utf8_general_ci. Now, I can connect again to moodle 3.6.3.
Note : I've now a warning with admin/environment.php, but it work well.
I change in config.php utf8mb4_unicode_ci to utf8_unicode_ci and work. But I think it must have some problem if the data collation is in utf8mb4_unicode_ci.
while both utf8 and utf8mb4 are, stictly speaking, unicode (UTF-8), in MySQL, the former is really limited and does not support a lot of characters (for example, emojis). The later does support the complete unicode charset, but has some extra requirements in the MySQL side.
So, it's really important to ensure that, for any MySQL installation, all the database / table and column collations match and, also, that config.php, when configured... has the very same dbcollation option (it may not be needed if MySQL defaults are ok).
There is a utility that will help you to get all the tables/columns converted to the collation that you want: admin/cli/mysql_collation.php.
But first, it's good to read the Docs above in order to understand the problem and then choose the collation that better suits your needs (moodle contents and mysql configuration). And make all them to match with the config.php one (if set).
Hope it helps, ciao
So which "collation" is the "correct" or preferred one?
I ran the collation.php and to no avail.. the error message is too generic... "something wrong with this table, have a look.." pretty clear as mud LOL
my config.php had db collation settings as well though I commented it now and even when turned on, made no difference.. mine was a duped index
$CFG->dboptions = array(
'dbcollation' => 'utf8mb4_unicode_ci',
** MAJOR CONTRIBUTING FACTOR FOR CONFUSION! **
Failure of OP to check environment. Optional reference in environment.xml since 3.0 of Moodle.
Out of sight ... out of mind. Environment check should have a link in Notifications page for it's there
that OP get's the 'itch' to upgrade.
But even so, the langugae/description of the opional reference had no link for further
explanation to an admin who was NOT a certfified DB admin.
"The current setup of MySQL or MariaDB is using 'utf8'. This character set does not support four byte characters which include some emoji. Trying to use these characters will result in an error when updating a record, and any information being sent to the database will be lost. Please consider changing your settings to 'utf8mb4'. See the documentation for full details."
IF DB version known to Moodle discovery, couldn't a link be made to 'official' MySQL docs?
Or Moodle's docs ...
Unsolicited historical info ... we learn from out past:
The dbcollation variable in config.php made it's first appearance as far back
fgrep 'dbcollation' ./moodle??/config-dist.php
./moodle31/config-dist.php: 'dbcollation' => 'utf8mb4_unicode_ci', // MySQL has partial and full UTF-8
./moodle32/config-dist.php: 'dbcollation' => 'utf8mb4_unicode_ci', // MySQL has partial and full UTF-8
./moodle33/config-dist.php: 'dbcollation' => 'utf8mb4_unicode_ci', // MySQL has partial and full UTF-8
./moodle34/config-dist.php: 'dbcollation' => 'utf8mb4_unicode_ci', // MySQL has partial and full UTF-8
./moodle35/config-dist.php: 'dbcollation' => 'utf8mb4_unicode_ci', // MySQL has partial and full UTF-8
./moodle36/config-dist.php: 'dbcollation' => 'utf8mb4_unicode_ci', // MySQL has partial and full UTF-8
No mention of this 'signficant' DB change in release notes of 3.1 (may 2016)
But it only pertained to MySQL.
Changing my.cnf config applied to new databases only ... didn't convert existing DB's.
Using Global commands to accomplish good only for the DB server session instance. DB server
restarted, then global commands no longer in affect.
Databases that needed to be changed needed changes at all levels:
DB, the tables, and the columns in every table ... which is why
Interesting to note that that script first appeared in version 2.3 of Moodle.
mysql_compressed_rows.php first appeared in 26 of Moodle.
The general method which describes upgrading moodle says to keep the config.php file of existing site.
Standard install package
3. Copy your old config.php file back to the new Moodle directory.
The config.php file from your installation should work fine but if you take a look at config-dist.php that came with Moodle 3.0 there are more/different options available (e.g. database drivers and settings). It's a good idea to map your old config.php settings to a new one based on the 3.0 config-dist.php.
Note that says 'after upgrading' ... but the catch 22 came during upgrading.
It has been advised more than once in community forums to use a copy of config-dist.php as the new
config.php but edited inserting variables known to OP as required.
No reference to that 'trick' in docs.
Even if folks followed that 'trick', still a 'catch 22' if variable included and database (+tables +columns in those tables) hadn't been converted.
in the 'spirit of learning from our past' ... and 2 cent opinion, Ken
Thank you for your explanation.
I read your answer carefully and checked all the steps and discovered the problem.
I had all the tables converted, but did not have the columns of those tables converted into utf8mb4_unicode_ci.
To solve, I run the CLI script to convert:
$ php admin/cli/mysql_collation.php --collation=utf8mb4_unicode_ci
and this work for me. Thanks
Just running this solved the problem for me:
$ php admin/cli/mysql_collation.php --collation=utf8mb4_unicode_ci
It worked well for me too, thank you for the tips !