Some background ... site has been marched from 1.9.x to 3.x. A couple of years ago, entity decided to 'go google' and all online training moved to Google Classroom. Intity had one teacher still using Moodle so not much done in the area of care/maintenance.
Recently, a new Teacher is interested and plans on using their Moodle.
Decided to attempt to 'future proof' their server since server would probably be on it's own for a long time. So upgraded the PHP to a supported version and upgraded MySQL to 5.7 ... from the MySQL-community repo (significant). OS is CentOS 6.10.
Checked things out (not everything, obviously) ... informed new Teacher ready to go and in 2 days time, informed that something was broke. Teacher attempting to upload users via a correctly formatted .csv file and the error above would appear after the upload of the .csv and after the screen where one chose character set - UTF8 was default, delimiter set to ',' ... ie, just the defaults. The other issue ... restoring a course backup that had been made from the same site and had only one resource in that backup.
This reported in debug ... of both upload users and restoring a course.
COLLATION 'utf8mb4_bin' is not valid for CHARACTER SET 'latin1'
In the restore of a course, this additional:
[Wed Jan 23 14:23:40 2019] [notice] [client ipaddress] Default exception handler: Error reading from database Debug: COLLATION 'utf8mb4_bin' is not valid for CHARACTER SET 'latin1'\nSELECT 'x' FROM mdl_user WHERE LOWER(email) LIKE LOWER(?) COLLATE utf8mb4_bin ESCAPE '|' LIMIT 0, 1\n[array (\n 0 => 'studentfirstname|_studentlastname@isd.net'
Student Email addresses were in the format of studentfirstname_studentlastname@isd.domain.
At first thought there was an issue with MySQL 5.7 escaping "_" - the pipe symbol in the error log just after students first name. But no. However, it was related to MySQL 5.7.
MySQL 5.7 -> do not provide the example cnf files ... small, medium, large ... thus the upgrade to 5.7 didn't leave any my.cnf.rpmsave file. Old my.cnf file (config for MySQL) hadn't changed.
Digging into docs for MySQL 5.7 ... many changes ... some of the my.cnf lines no longer required ... ok, fixed those restarted mysqld and no change. By this time !@#DCQWERQWEQWD!
Further investigation ... changes to defaults in my.cnf will not change/alter any existing database .... nor it's tables ... nor the columns in those tables.
CLI scripts in admin/cli/ of moodle code run, but still not going deep enough into tables/columns and setting what needed to be set.
Almost resorted to doing many queries like the following to fix:
ALTER TABLE mdl_user CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
ALTER TABLE mdl_user_devices CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
ALTER TABLE mdl_user_enrolments CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
ALTER TABLE mdl_user_info_category CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
ALTER TABLE mdl_user_info_data CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
ALTER TABLE mdl_user_info_field CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
ALTER TABLE mdl_user_lastaccess CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
ALTER TABLE mdl_user_password_history CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
ALTER TABLE mdl_user_password_resets CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
ALTER TABLE mdl_user_preferences CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
ALTER TABLE mdl_user_private_key CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
Notice I said 'almost' ... 400+ tables + dunno how many columns in those tables ... too much ... would spend a week doing above.
So the my.cnf file was set correctly, just had to find some way get the server to work for me ... not the other way round ...
Then I remembered a command I hadn't used in years!
mysqlcheck -u root -p [dbname] --auto-repair --optimize
Tables don't support optimize, doing recreate + analyze instead
is reported for every table .. will take a long time to run.
But, all is fixed ... database, tables in the DB, and columns in the tables.
This posting just to share an experience and hopefully provide a clue for someone else.
Note: more changes are forth coming in MySQL 8 ... know Moodle HQ talking about which/what now for required versions or minimal versions of MySQL/MariaDB in next release and future releases.
In reading up on changes/differences between MySQL 5.7 (dunno what happened to 5.8 or 5.9 ... or version 6 and 7) to 8, we won't be in Kansas any more ... again. Hopefully, there will be some Wizards of OZ around by then.
Anyhoo ... 2 cent sharing,
'spirit of sharing', Ken