Migrating MariaDB from utf8 to utf8mb4 with Adminer?

bobbyfiend

I'm not knowledgeable about SQL, and an app (moodle) recommends upgrading my MariaDB database to utf8mb4. I have seen lots of tutorials online on how to do with with SQL code, but of course I'm drawn to easier, less error-prone ways of doing this (i.e., ways in which I am less likely to screw it up). I see that in Adminer for the database there is an "Alter Database" option that seems like it is to choose a new character set for the full database. There is a pulldown with several options, one of which is utf8mb4_general_ci

If I choose that option from the dropdown, with the database of interest specified in the other field (i.e., by logging in to Adminer as a user to that database), then press SAVE (I assume I don't want to click DROP?), Will that convert the full database to utf8mb4?

sean

bobbyfiend to alter the default character set of the DB:

Log in to DB in Adminer.
Click "Alter Database".
Select the character set that you need from the list.
Click Save.

To alter the character set of a table:

Log in to DB in Adminer.
Click the table name.
Click "Alter table".
Select the character set for the table and/or for whichever fields you need to alter.
Click Save.

bobbyfiend

sean Thanks! Did that. Moodle (from within the app) still says it's not changed. What I notice is that there are many, many tables in the db, and all of them are still set to utf8_general_ci. I think I perhaps converted the overall database characterset in some general way, but that didn't cascade down into any of the tables or columns. Doing this by hand will take me a couple of hours of clicking. I suppose I'll need to figure out how to submit SQL commands and follow one of the online guides for this.

bobbyfiend

Sean,
Thanks for the help so far. This was a while ago, I realize. The solution didn't quite work, though that wasn't critical until now, when moodle changed their upgrade process to fail (i.e. stop) if the database isn't sufficiently converted to utf8mb4. So I'm stuck. What I've done so far is:

Use Adminer to change the database character set to utf8mb4_unicode_ci

Run (and modify) a query I found on Stack Overflow to generate statements like this:


ALTER TABLE mariadb_moodle.mdl_adminpresets CONVERT TO CHARACTER SET utf8mb4;
###
ALTER TABLE mariadb_moodle.mdl_adminpresets COLLATE utf8mb4_unicode_ci;
###
ALTER TABLE mariadb_moodle.mdl_adminpresets MODIFY name varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE mariadb_moodle.mdl_adminpresets MODIFY comments LONGTEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE mariadb_moodle.mdl_adminpresets MODIFY site varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE mariadb_moodle.mdl_adminpresets MODIFY author varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE mariadb_moodle.mdl_adminpresets MODIFY moodleversion varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE mariadb_moodle.mdl_adminpresets MODIFY moodlerelease varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE mariadb_moodle.mdl_adminpresets_app_it_a MODIFY itemname varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE mariadb_moodle.mdl_adminpresets_app_plug MODIFY plugin varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

and so on, a few thousand statements total.

The statements run fine in Adminer (using the SQL query window). The results are "Query executed. 0 rows affected" for each statement.

I've succeeded in getting Adminer to show that the collation method for each table is utf8mb4_unicode_ci, but the moodle upgrade tool still says the database isn't fully converted to UTF8MB4, so it won't upgrade the application.

I note there are at least two variables for the database (mariadb_moodle) that I can't change and are still the wrong value:

character_set_server	latin1
character_set_system	utf8mb3

I don't think I can change these. It looks like I could change them with a my.cnf file for the mariaDB instance (maybe) or possibly my.ini (I don't know the difference). However, I don't know where to put such files, or if I have access on Opalstack's shared hosting.

The error I get from the moodle installer, by the way, is:
"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'."

Do you have any guidance for how I can convince moodle that the database really is now utf8mb4?

nick

The statements run fine in Adminer (using the SQL query window). The results are "Query executed. 0 rows affected" for each statement.

That usually means the command was executed correctly (if no other messages say otherwise), but no data was modified in the table directly.

Please also make sure your Moodle config.php is set to use the charset, the line for that should look something like this:

$CFG->dboptions = array('dbcollation' => 'utf8mb4_unicode_ci');

This query should give you a list of all the tables and columns in the mariadb_moodle database that are not using the utf8mb4_unicode_ci collation:

SELECT TABLE_NAME, COLUMN_NAME, COLLATION_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'mariadb_moodle' AND COLLATION_NAME != 'utf8mb4_unicode_ci';

Then this query should give you a list of ALTER TABLE commands you need to finish converting any leftovers to utf8mb4_unicode_ci. Please make backups before making any large scale changes like this.

SELECT DISTINCT CONCAT('ALTER TABLE ', TABLE_NAME, ' CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;') 
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'mariadb_moodle' AND COLLATION_NAME != 'utf8mb4_unicode_ci';

I note there are at least two variables for the database (mariadb_moodle) that I can't change and are still the wrong value:

character_set_server latin1
character_set_system utf8mb3

I don't think I can change these. It looks like I could change them with a my.cnf file for the mariaDB instance (maybe) or possibly my.ini (I don't know the difference). However, I don't know where to put such files, or if I have access on Opalstack's shared hosting.

If you keep getting messages from Moodle complaining about these settings you might need a private mariadb database since you/we are unable to change the shared server my.cnf values.

bobbyfiend

nick Thanks for this very helpful reply! Just changing the config.php file in moodle did the trick. All updated, now. I appreciate the time and thought you put into this.

nick

bobbyfiend Any time, happy to help and get you on your way 🙂