When I upgrade Moodle from 3.5.4+ to 3.5.5+ and I login after the upgrade, I get a database error and can't login.
With DEBUG mode on I see there's a problem with collation of MySQL tables that have changed. Any idea why this problem occurs with this upgrade? Will there be a fix for this problem the next Moodle build?
I checked the collation of all Moodle tables with admin/cli/mysql_collation.php and saw that several tables had different collation values (mdl_user for example). When I changed the collation of all the tables with command /usr/bin/php admin/cli/mysql_collation.php --collation=my-own-db-collation I could login again. But this isn't the way I want to upgrade to the new 3.5.5+ version!
Please explain: '...this isn't the way I want to upgrade to the new 3.5.5+ version!"
And, what was 'my-own-db-collation'? (if that's something you can provide in forums).
When you upgrade are you using git or the 'older' method?
'spirit of sharing', Ken
Well if I upgrade Moodle to a new version I don't expect a database error like this one.
I can't provide the collation, but I compared the collation of all the tables with 3.5.4+ and they were different for several
tables (mdl_user, mdl_config and mdl_config_plugins).
I don't use git, the 'old' way I assume.
Future of MySQL - version 8 (has been released).
From:
https://dev.mysql.com/doc/refman/8.0/en/charset-unicode-sets.html
utf8: An alias for utf8mb3.
Note
The utf8mb3 character set is deprecated and will be removed in a future MySQL release. Please use utf8mb4 instead. Although utf8 is currently an alias for utf8mb3, at that point utf8 will become a reference to utf8mb4. To avoid ambiguity about the meaning of utf8, consider specifying utf8mb4 explicitly for character set references instead of utf8.
The way I read that ... if staying with MySQL ... and eventually upgrading it to 8 ...
the moodle db will have to be utf8mb4.
Before updating/upgrading, check server environment.
Extra ... way more ...
The 'old way' ... keep config.php (archived) and any plugin not part of core.
Acquire new code. Unzip or whatver, copy back into new code config.php and any plugins
not part of core. Run the upgrade.
It's always been advised to check the config-dist.php file with the new code for
changes/additions not present before.
Git - no files move. Git acquires newer version of 5.5.4+ -> 5.5.5+ core code in place.
Less prone to human error in archiving all tha needs to be archived prior to upgdate/upgrade ... op forgot to copy back an addon plugin - get missing from disk.
Checking plugins is also a good idea prior to updating/upgrading.
(* if one has customized code, one could stash or run their own git repo)
I do this and it's served me well to avoid issues on updates and espcially upgrades.
(Don't have any sites with major customizations/hacks to core code)
Create a directory in moodle code called 'addons'.
In 'addons', create a addons.txt file which is nohing more than the 'shortname' of
all the addons in the site.
Looks like:
mod_hvp
mod_questionnaire
mod_customcert
Create an executable script that uses moosh to check addon versions.
'checkaddons'
#!/bin/bash
#
echo 'Add-on listing: ';
cat ./addons.txt;
echo '---------------------';
for i in `cat ./addons.txt`
do
echo "Addon in que: $i";
moosh -n plugin-list |grep $i
done
When run, looks like:
Add-on listing:
mod_hvp
mod_questionnaire
mod_customcert
---------------------
Addon in que: mod_hvp
mod_hvp,2.7,2.8,2.9,3.0,3.1,3.2,3.3,3.4,3.5,3.6,https://moodle.org/plugins/download.php/19174/mod_hvp_moodle36_2019031301.zip
Addon in que: mod_questionnaire
mod_questionnaire,1.9,2.0,2.2,2.3,2.4,2.5,2.6,2.7,2.8,2.9,3.0,3.1,3.2,3.3,3.4,3.5,3.6,https://moodle.org/plugins/download.php/19206/mod_questionnaire_moodle36_2018050109.zip
Addon in que: mod_customcert
mod_customcert,2.9,3.0,3.1,3.4,3.5,3.6,https://moodle.org/plugins/download.php/18626/mod_customcert_moodle36_2018120301.zip
I then use the above url's to build a 'getlist' file which looks like:
https://moodle.org/plugins/download.php/19174/mod_hvp_moodle36_2019031301.zip
https://moodle.org/plugins/download.php/19206/mod_questionnaire_moodle36_2018050109.zip
https://moodle.org/plugins/download.php/18626/mod_customcert_moodle36_2018120301.zip
And then use wget to get them:
wget -i getlist
Using the acquired zips:
mod_hvp_moodle36_2019031301.zip
mod_questionnaire_moodle36_2018050107.zip
report_questionnairestats_moodle34_2018060700.zip
I upgrade manually. I hide the original plugin directory first for easier recovery
from a plugin compat issue
Put site in maintenance mode via CLI - that stops cron from running and any 'normal' access by students/teachers.
cd mod
mv hvp .hvp
Then copy the zip for hvp into mod.
Unzip
change owneerships/permissions.
After I get all those in place, use:
cd moodleoode/admin/cli/
php upgrade.php
I've now gotten into habit of purging caches from CLI
Then take the site out of maintenance mode:
php maintenance.php --disable
And check the running of cron via CLI.
Then check Notifications and Plugins via browser.
Yeah, I like getting my hands 'dirty' a little. Makes me feel like
I've 'accomplished' something ... or 'earned my pay' ... so to speak.
'spirit of sharing', Ken
Thanks for your explanation Ken, my question about why the collation was changed with the upgrade still keeps me busy. I would like to know why the upgrade couldn’t be done without this error. Maybe the next build of Moodle doesn’t give this error, we’ll see.
Please see:
https://moodle.org/mod/forum/discuss.php?d=383652#p1546850
Once DB is converted shouldn't have any more issues like that during updates or upgrades ... but it still kinda begs the question about why only some tables had different settings. Quirk of MySQL? or code? Can't answer your question really ... can't speak for Moodle HQ and am not trying to. Do know that I finally got around to making the default character set/collation to that which has been in config-dist.php since version 3.1.
Did have some issues with those settings when upgrading MySQL to 5.7 and in researching 8.0 looks like I'll be doing some more.
Eventually, the 'dust will settle' though ... I hope! ;)
'spirit of sharing', Ken
Thanks for the link about this DB issue. I think converting the tables with mysql_collation.php script will solve the problem permanently.
And I'll check the 3.5.4 and previous installations if there are different collations used. Probably with Moodle 3.5.5+ this is causing problems with tables that have a different collation then expected.
Glad you’ve got it sorted - but just chipping in here because you need to be aware that technology and software changes over time and upgrades will not always be pain-free!
But fortunately, the Moodle docs are VERY clear, and ALWAYS worth a read before upgrading.
For example...
Before upgrading - We advise that you test the upgrade first on a COPY of your production site, to make sure it works as you expect.
Backup important data
Move your old Moodle software program files to another location. Do NOT copy new files over the old files.
And in addition, the upgrading notes specifically say:
If you use MySQL or MariaDB, make sure your database supports full UTF-8 (utf8mb4) if you install a new instance of Moodle. CLI script may be used to convert to utf8mb4 if you're upgrading. You may choose to keep using 'utf8_*', but then a warning will show that the database isn't using full UTF-8 support and suggest moving to 'utf8mb4_unicode_ci'. See MySQL full unicode support for details. If you do enable utf8mb4 you *must* use the Barracuda file format.
... so on the assumption that you did actually read the docs, the issue you describe shouldn’t really have been a surprise, should it? 🤔
Jon, we test the upgrade always in a lab environment first. That’s where I got the error. All previous upgrades went smootlhy without problems, but it’s always good to take time to prepare the upgrade to a new version!
We’re already using the collation that is advised, but two different collations caused my DB error (and I’m not the only one here that got this error). So I had to investigate why I had two different collations on my tables.