Upgrading mariadb 5.5 to mariadb 10.6 to support moodle 3.11

First you need to stop the running 5.5. version:

service mariadb stop;

Make a backup of your mariadb datafolder:

cd /var/lib/mysql; tar -cf mysql.tar; gzip mysql.tar; mv mysql.tar.gz /backup-folder;

Then remove the 5.5 version:

yum erase mariadb*

Download the MariaDB Package Repository Setup Script:

curl -LO https://downloads.mariadb.com/MariaDB/mariadb_repo_setup

Then make it executable and run it:

chown 750  mariadb_repo_setup 

./mariadb_repo_setup

It will then create a /etc/yum.repos.d/mariadb.repo file with content adjusted for your system. In our system it look like this:

[mariadb-main]
name = MariaDB Server
baseurl = https://dlm.mariadb.com/repo/mariadb-server/10.6/yum/rhel/7/x86_64
gpgkey = file:///etc/pki/rpm-gpg/MariaDB-Server-GPG-KEY
gpgcheck = 1
enabled = 1


[mariadb-maxscale]
# To use the latest stable release of MaxScale, use "latest" as the version
# To use the latest beta (or stable if no current beta) release of MaxScale, use "beta" as the version
name = MariaDB MaxScale
baseurl = https://dlm.mariadb.com/repo/maxscale/latest/yum/rhel/7/x86_64
gpgkey = file:///etc/pki/rpm-gpg/MariaDB-MaxScale-GPG-KEY
gpgcheck = 1
enabled = 1

[mariadb-tools]
name = MariaDB Tools
baseurl = https://downloads.mariadb.com/Tools/rhel/7/x86_64
gpgkey = file:///etc/pki/rpm-gpg/MariaDB-Enterprise-GPG-KEY
gpgcheck = 1
enabled = 1

Now you can install the newer version of MariaDB server:

yum install mariadb-server; yum install mariadb-client;

If you want to check that MariaDB is installed, you can run this:

yum list installed | egrep -i mariadb

Then start the MariaDB server:

service mariadb start

Then upgrade the MariaDB server:

mysql_upgrade

Troubleshooting:

In our case he connection to the MariaDB-server failed with: Error writing to database

From the /var/log/mariadb.log file:

2021-09-24  9:35:32 0 [ERROR] mariadbd: Can't create/write to file '/var/run/mariadb/mariadb.pid' (Errcode: 2 "No such file or directory")
2021-09-24  9:35:32 0 [ERROR] Can't start server: can't create PID file: No such file or directory

Solution:

service mariadb stop;
mkdir -p /var/run/mariadb
chown -R mysql:mysql /var/run/mariadb
service mariadb start;

And then in the mariadb log file:

2021-09-24  9:39:09 0 [Note] /usr/sbin/mariadbd: ready for connections.

Next problem was that Moodle didn’t start and responded with:

Error writing to database

The solution to this was to first add debugging to the moodle config.php file:

@ini_set('display_errors', '1');    // NOT FOR PRODUCTION SERVERS!
$CFG->debug = 32767;
$CFG->debugdisplay = true;

Then accessing the webpage once more. This time the error message was explained in detail:

Debug info: InnoDB refuses to write tables with ROW_FORMAT=COMPRESSED or KEY_BLOCK_SIZE.
UPDATE mdl_sessions SET timemodified = ? WHERE id=?
[array (
0 => 1632469425,
1 => '1126',
)]
Error code: dmlwriteexception
Stack trace:
line 489 of /lib/dml/moodle_database.php: dml_write_exception thrown
line 1587 of /lib/dml/mysqli_native_moodle_database.php: call to moodle_database->query_end()
line 1619 of /lib/dml/mysqli_native_moodle_database.php: call to mysqli_native_moodle_database->update_record_raw()
line 476 of /lib/classes/session/manager.php: call to mysqli_native_moodle_database->update_record()
line 137 of /lib/classes/session/manager.php: call to core\session\manager::initialise_user_session()
line 111 of /lib/classes/session/manager.php: call to core\session\manager::start_session()
line 808 of /lib/setup.php: call to core\session\manager::start()
line 1158 of /config.php: call to require_once()
line 30 of /index.php: call to require_once()

The solution to this is to:

Add innodb_read_only_compressed=OFF to the MariaDB configuration file and restart MariaDB, or run SET GLOBAL innodb_read_only_compressed=OFF.

So:

vi /etc/my.cnf

Add the line:

 innodb_read_only_compressed=OFF

And finally:

service mariadb restart;

Then the Moodle 3.11 upgrade started with no errors.

Reference:
* https://mariadb.com/kb/en/upgrading-from-mariadb-55-to-mariadb-100/
* https://tracker.moodle.org/browse/MDL-72131