The admin/cli/ scripts for DB uses the user in config.php of your Moodle.
Edit config.php and add lines for db user/password for your superuser of the DB server. Comment out the lines for DB user and DB password for later reverting - add // in front of those lines.
The script you are trying to run with current DB user/password settings probably requires a higher access level to the DB where by the user can alter all the tables and columns in those tables of the DB for your moodle.
After conversion, comment out super creds in config.php (add // in front of those lines) ... re-enable the DB user/pass you had by removing the // characters in front of those lines.
'SoS', Ken
Command did not work using 'superuser' credentials added to config.php of site?
In config.php what is the current setting for db collation?
Can you use mysql client (not the php script) and alter the database to use utf8mb4 character set and utf8mb4_general_ci?
Do you have any settings in my.cnf for system wide character set collation? How about a 'client' section in my.cnf settings for same?
Reason for using script .. it loops through all tables and columns in the DB ... other wise, one has to issue alter commands per table/columns to convert ... and there's just too many tables/columns to do that one at a time.
Suggest making a sql dump of database for moodle. Import that to a DB by a different name - change config.php to point to the db by different name and tinker some more.
Do you see any mysql DB errors?
And just to make sure ... what version of MySQL are you running?
Check PM on this system for additional assistance with this.
'SoS', Ken
Always more than one way to 'skin a cat'!
Please see:
https://docs.moodle.org/31/en/Converting_your_MySQL_database_to_UTF8
There are two other ways besides the script using a valid sql dump of the database ... described in link above - a sed command or editing the sql file with a text editor.
However, rather than using 'vi' editor as shown in link above, one can use 'nano' (which is normally present on RH family servers and am gonna say 'more user friendly' [debatable as usual]). Nano has search and replace.
After successful search and replace with an editor, import DB by a new DB name. Change config.php to point to new DB. Then access site and do an environment check ... or run that same command with -i (for information).
'SoS', Ken
That version of MariaDB is supposed to be a 'drop in replacement' for MySQL. The following commands are the same: mysql, mysqldump, mysqladmin.
At any rate, editing the sql dump with nano and doing search and replace with nano is outside of DB service. You only have to import the edited sql into a new DB for the moodle:
mysql -u root -p
At the mysql> prompt create a new DB for the moodle:
mysql> create database newmoodle character set utf8mb4 collate utf8mb4_general_ci;
Quit the client ... \q [ENTER]
Now import the edited sql into the 'newmoodle' DB;
mysql -u root -p newmoodle < editedsqldumpfile.sql [ENTER]
Quit client: \q [ENTER]
Edit config.php file and change the DB name to 'newmoodle'.
To check, use the same php script you used before with the -l (lower case "L") option for listing.
php mysql_collation.php -l
screen will scroll showing all tables and columns in those tables. At the very end you should see something similar to:
Table collations summary for [yoursite]:
utf8mb4_general_ci: 1547
'SoS', Ken
According to:
https://docs.moodle.org/31/en/Converting_your_MySQL_database_to_UTF8
referenced earlier ... all on that page shows 'utf8mb4_general_ci'
check it out.
'SoS', Ken
Hope you don't mind me butting in:
I'm pretty sure it should be utf8mb4_unicode_ci.
That document Converting_your_MySQL_database_to_UTF8 is mainly from 2012 (Moodle 3.1), the current instructions are MySQL_full_unicode_support.
Both that latter document and the installation instructions use utf8mb4_unicode_ci.
@Leon ... you can butt in anytime!
Guess leaving old docs around not such a good idea.
unicode if you plan to use emoj's or have courses using Asian languages? If the use of emoj's is that important in online education then what can I say!
'SoS', Ken
The docs do need some love, there's some out-of-date and contradictory information in there.
I think it's the utf8mb4 character set that's important for storing emojis, and other newer characters. The collation is important for the sort order, as far as I can work out utf8mb4_unicode_ci is favoured over utf8mb4_general_ci in general not just for Moodle – the MySQL docs describe the latter as "faster, but slightly less correct".
Can re-call having issues with 2.x -> 3.0.x upgrades due to this.
fgrep 'dbcollation' ./moodle30/config-dist.php
returns nothing
note that's 3.0.x
Beginning 3.1 ...
fgrep 'dbcollation' ./moodle3?/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
./moodle37/config-dist.php: 'dbcollation' => 'utf8mb4_unicode_ci', // MySQL has partial and full UTF-8
./moodle38/config-dist.php: 'dbcollation' => 'utf8mb4_unicode_ci', // MySQL has partial and full UTF-8
./moodle39/config-dist.php: 'dbcollation' => 'utf8mb4_unicode_ci', // MySQL has partial and full UTF-8
So, as it was pointed out, it looks like utf8mb4_unicode_ci is correct and should be the 'standard' now.
Comments on that line in config-dist.php
"'dbcollation' => 'utf8mb4_unicode_ci', // MySQL has partial and full UTF-8
// support. If you wish to use partial UTF-8
// (three bytes) then set this option to
// 'utf8_unicode_ci', otherwise this option
// can be removed for MySQL (by default it will
// use 'utf8mb4_unicode_ci'. This option should
// be removed for all other databases.
Hmmmm ... is default for MySQL still latin and latin_swedish_ci?
https://dev.mysql.com/doc/refman/8.0/en/charset-applications.html
default MySQL character
set and collation (utf8mb4,
utf8mb4_0900_ai_ci)
https://dev.mysql.com/doc/refman/5.7/en/charset-applications.html
default MySQL character
set and collation (latin1,
latin1_swedish_ci)
'SoS', Ken
