Debian 8 (Jessie) and OpenSuSE 13.2

You mentioned both Debian and openSUSE, which one was it that you experienced the problem on? They are essentially different in regard to mysql_upgrade.

As I understand, both Debian Jessie and openSUSE 13.2 have MariaDB 10.0.
How exactly did you perform the upgrade on each of the systems?

Are you getting this error every time you start the server, or did you get it only once?

It happened on both, Debian and openSuSE.

Correct. Both started with Distro MariaDB 10.0. Then they added the MariaDB repo and did a zypper/apt-get update/upgrade as far as I have seen.

Then we found the error message in the MariaDB error log. And as far as I can tell the problem was not solved after a DB restart.

So we had to do mysql_upgrade ourself. So pretty straight forward. You use the distro version. Then you want to go to a newer version. Nothing exotic. So the question is: Should a package post_install script upgrade the database or not...

It's not really as straight-forward as it seems.

Debian upgrade 10.0 => 10.1 is indeed supposed to work and among other things run mysql_upgrade, at least in packages produced by MariaDB. I'll check what's happening there.

But on RPM-based systems we generally don't support major upgrade (such as 10.0 => 10.1). It's meant to be changed, and there is a task for that, but it's not in the main trees yet. Usually it will just refuse to run the upgrade and say that manual intervention is required. Also, since it is not expected to do major upgrade, it does not run mysql_upgrade automatically either. Maybe there are exceptions, particularly with zypper, when it decides it can upgrade after all, but still does not run mysql_upgrade, I will take a look.

Tried openSUSE. It was a bit cumbersome, since openSUSE 13 is already EOL-ed, so maybe my experiment wasn't clean enough.

Expectedly, zypper update didn't work, it said

Upgrading directly from MySQL 10.0 to MariaDB 10.1 may not

be safe in all cases.  A manual dump and restore using mysqldump is

recommended.  It is important to review the MariaDB manual's Upgrading

section for version-specific incompatibilities.

A manual upgrade is required.

When I uninstalled 10.0 and installed 10.1 – also expectedly, install didn't run mysql_upgrade, since it's a "manual upgrade" anyway. Running it manually made the error go away.

I'll try Debian next, it is more interesting.

Indeed, on Debian packages get upgrade to 10.1/10.2, but don't run mysql_upgrade (at least MariaDB's packages). 10.0 ran mysql_upgrade.

I assume it's a part of otto's changes in 10.1. Otto, was it intentional that mysql_upgrade does not run upon upgrade anymore?

mysql_upgrade is called after starting the safe_mysqld in both sysvrc and sysvinit scripts.

But running this after starting the mysqld for users creates a race condition that existed there for a long time.

E.g. when testing, the mysql_upgrade is not running as a part of the upgrade, but as part of the startup script.

JFTR the downstream Debian packages (10.1.23-7) run mysql_upgrade:

with added set -x set +x around upgrade_system_tables_if_necessary call

May 13 09:57:39 lettie mysqld[133325]: 2017-05-13  9:57:39 140039564005952 [Note] /usr/sbin/mysqld (mysqld 10.1.23-MariaDB-7) starting as process 133325 ...

May 13 09:57:41 lettie debian-start[133352]: + upgrade_system_tables_if_necessary

May 13 09:57:41 lettie debian-start[133352]: + set -e

May 13 09:57:41 lettie debian-start[133352]: + set -u

May 13 09:57:41 lettie debian-start[133352]: + logger -p daemon.info -i -t/etc/mysql/debian-start 'Upgrading MySQL tables if necessary.'

May 13 09:57:41 lettie debian-start[133352]: + LC_ALL=C

May 13 09:57:41 lettie debian-start[133352]: + /usr/bin/mysql_upgrade --defaults-extra-file=/etc/mysql/debian.cnf

May 13 09:57:41 lettie debian-start[133352]: + egrep -v '^(1|@had|ERROR (1054|1060|1061))'

May 13 09:57:41 lettie debian-start[133352]: + logger -p daemon.warn -i -t/etc/mysql/debian-start

May 13 09:57:43 lettie systemd[1]: Started MariaDB database server.

I can't test upgrade path with systemd, but on Debian jessie upgrading from 10.0 to 10.1 works as expected:

Setting up libmysqlclient18 (10.0.30+maria-1~jessie) ...

Setting up libdbd-mysql-perl (4.028-2+deb8u2) ...

Setting up libmariadbclient18 (10.0.30+maria-1~jessie) ...

Setting up mariadb-client-core-10.0 (10.0.30+maria-1~jessie) ...

Setting up mariadb-client-10.0 (10.0.30+maria-1~jessie) ...

Setting up mariadb-server-core-10.0 (10.0.30+maria-1~jessie) ...

Setting up mariadb-server-10.0 (10.0.30+maria-1~jessie) ...

Setting up mariadb-server (10.0.30+maria-1~jessie) ...

Processing triggers for libc-bin (2.19-18+deb8u7) ...

[...]

root@lettie:/# /etc/init.d/mysql start

[ ok ] Starting MariaDB database server: mysqld.

+ upgrade_system_tables_if_necessary

+ set -e

+ set -u

+ logger -p daemon.info -i -t/etc/mysql/debian-start 'Upgrading MySQL tables if necessary.'

+ LC_ALL=C

+ /usr/bin/mysql_upgrade --defaults-extra-file=/etc/mysql/debian.cnf

+ egrep -v '^(1|@had|ERROR (1054|1060|1061))'

+ logger -p daemon.warn -i -t/etc/mysql/debian-start

[info] Checking for corrupt, not cleanly closed and upgrade needing tables..

+ set +x

[...]

root@lettie:/# add-apt-repository 'deb [arch=amd64,i386,ppc64el] http://mirror.vpsfree.cz/mariadb/repo/10.1/debian jessie main'

root@lettie:/# apt-get -y install mariadb-server

Setting up mariadb-server-10.1 (10.1.23+maria-1~jessie) ...

Installing new version of config file /etc/init.d/mysql ...

[ ok ] Stopping MariaDB database server: mysqld.

[ ok ] Starting MariaDB database server: mysqld.

+ upgrade_system_tables_if_necessary

+ set -e

+ set -u

+ logger -p daemon.info -i -t/etc/mysql/debian-start 'Upgrading MySQL tables if necessary.'

+ LC_ALL=C

+ /usr/bin/mysql_upgrade --defaults-extra-file=/etc/mysql/debian.cnf

+ egrep -v '^(1|@had|ERROR (1054|1060|1061))'

+ logger -p daemon.warn -i -t/etc/mysql/debian-start

[info] Checking for corrupt, not cleanly closed and upgrade needing tables..

Setting up mariadb-server (10.1.23+maria-1~jessie) ...

Processing triggers for libc-bin (2.19-18+deb8u7) ...

oli How big were the tables during the training that got upgraded? E.g. how long did the manual mysql_upgrade run took?

Here is the opposite complaint about 10.0: MDEV-12275. This one is just about output, but it's not the first complaint about mysql_upgrade.

In general, this discussion has been back and forth for long time, either decision has its disadvantages.
When we turn off the automatic mysql_upgrade, many installations stay with the old schema, and when their users get mysterious problems, we observe months of errors in the log.
When we turn on the automatic upgrade, people report that on big installations it takes days, if not weeks, during which time their instance is out of order, often unexpectedly.

Hi elenst,
is there any update on this?

I think this is clearly linked to what you are describing:
https://bugs.debian.org/cgi-bin/bugreport.cgi?bug=897428

elenst, I'd optimize for a common use case. And that's a user who doesn't have a huge amount of data or high concurrent load, and is not MariaDB expert.

That means, automatically run mysql_upgrade during upgrade. I'd even say — run mysql_upgrade during upgrade if mysqld is up. So MariaDB experts can stop mysqld for the duration of the upgrade and that'll disable auto-mysql_upgrade too. Or there could be an option somewhere in /etc/mysql/?

faust, what do you think?

serg if possible (and compatible with OS upgrade guidelines) I think we should always ask users what we should do (expert or not).

For me, message should contain the following:

• brief explanation on what is going to happen and why we need user interaction;
• warning that on huge base myslq_upgrade process could be very long and progress indicator if possible (Michael Schaller suggested this https://bugs.debian.org/cgi-bin/bugreport.cgi?bug=865534#15).

Expert users can always bypass these interaction with specific apt-get option (for example '--force-yes') if they perfectly know what they are doing.

Missed finding this when I created MDEV-24172. This has been corrected.