Upgrade from MariaDB 10.3 to 10.4 on FreeNAS

Authentication Behaviour

Upgrading from MariaDB 10.3.23 to a later release

Output of mysqladmin version -p

Output of SELECT host, user FROM mysql.user;

Output of show grants for 'root'@'localhost';

A new installation of MariaDB 10.3.24

A new installation of MariaDB 10.5.5

The Upgrade Journey

MariaDB 10.4 uses an improved authentication model. It uses operating system credentials for the root account rather than requiring a separate root db user.

It’s recommended that you backup the MariaDB databases before proceeding. You can use phpMyAdmin to accomplish this. Once you’ve completed the backup, proceed with the following steps.

Upgrade from MariaDB 10.3.23

1. Stop MariaDB service mysql-server stop

2. Remove MariaDB 10.3 pkg delete mariadb103-server and its dependencies pkg autoremove

3. Install MariaDB 10.4 pkg update and pkg install mariadb104-server

Start MariaDB service mysql-server start

So far, so good. However, attempting to connect to the site barry.udance.com.au results in this error:

This FreeBSD forum post Can’t connect to mysql database after upgrading to mariadb 10.4.13_3 provided the clue. After extensive experimentation, to get past the error, I needed to add the line mysqli.default_socket = /var/run/mysql/mysql.sock to /usr/local/etc/php.ini and the restart PHP services service php-fpm restart.

It’s now possible to connect to the site. However, there’s still the requirement to do a database upgrade between MariaDB revisions. Here’s the second stumbling block.

To get around this, I need to supply the root db password. It’s clear for this that the original authentication scheme is still in play.

Observed Upgrade Behaviour

In summary, this is what I’ve observed:

1. When installing MariaDB 10.4 (WP installation script 2.x), the new passwordless authentication scheme is the default.
2. When installing MariaDB 10.3 (WP installation script 1.x), the legacy password authentication scheme is used.
3. When upgrading from MariaDB 10.3 to a newer release, the legacy password authentication scheme is retained.

To enable a password for the db root account:

mysql> SET PASSWORD FOR 'root'@'localhost' = PASSWORD('abracadbra');

To enable passwordless access to MariaDB:

mysql> ALTER USER root@localhost IDENTIFIED VIA unix_socket;

References

1. Upgrading from MariaDB 10.3 to MariaDB 10.4
2. Authentication from MariaDB 10.4
3. User Accounts Created by Default
4. Changes and Improvements in MariaDB 10.4
5. mysqladmin
6. mysql.global_priv Table
7. Fix MariaDB Plugin ‘unix_socket’ is not loaded Error on Ubuntu 17.04 | 17.10
8. How to Install MariaDB on FreeBSD
9. How to see/get a list of MySQL/MariaDB users accounts
10. Can’t connect to mysql database after upgrading to mariadb 10.4.13_3