Currently the Debian packaging is set to move the previous datadir to another location (/var/lib/mysql-5.7) and start a new datadir from scratch. Should I revert this change and make MariaDB 10.2 to attempt to run with the MySQL 5.7 datadir?

The symptoms of this is visible in test_! step in buildbot for xenial and yakketty builders try to upgrade from MySQL 5.7 to MariaDB 10.2 and they fail, because even though they move the datadir away and start from scratch, they are unable to connect to the mysqld using the MySQL 5.7 root password as that has been reset in the database with a new mysql.user table.

Please advice what you want the Debian packaging to do or change the test to test for something else that is not bound to fail always (or for now, if this issue is not fixed).

Personally I don't think we need to mimick the MySQL 5.7 mysql.user table, but we should have a mysql_upgrade procedure that can convert the mysql.user table into our format so at least a very simple database can start as-is.

otto,

Personally I don't think we need to mimick the MySQL 5.7 mysql.user table, but we should have a mysql_upgrade procedure that can convert the mysql.user table into our format so at least a very simple database can start as-is.

Naturally mysql_upgrade should be run to fix the structure. The problem is, if the server does not start at all, as it is now, mysql_upgrade cannot be run. MariaDB needs to be able to start on the MySQL datadir. It is not guaranteed to continue working properly (and it won't), but it should be able to work well enough to accept the connection and updates from mysql_upgrade.

Please advice what you want the Debian packaging to do or change the test to test for something else that is not bound to fail always (or for now, if this issue is not fixed).

For 10.2, if we agree that the live upgrade from "simple" MySQL 5.7 is to be fixed, I think that the change will need to be reverted, but probably only after the fix has been pushed. Until then, I can disable the test for 10.2 since we already know it does not work.

Have you made a similar change in packaging for 10.0 and 10.1, so they can cross-grade from MySQL 5.7?
If so, for 10.0 and 10.1 I suppose we need to keep your packaging change and modify the buildbot test so that it does not use the old password. If you didn't make the change there and aren't planning to, we will disable the test, because I don't expect it will ever work.

Related to this I just noticed that the mysql root credentials assumption in buildbot for my ok-debpkg-10.2 branch is different from other branches:

case "ok-debpkg-10.2" in

*10.[12]e*)

  sudo mysql -uroot -e "select @@version"

  ;;

*)

  mysql -uroot -prootpass -e "select @@version"

  ;;

Why is that elenst dbart ratzpo ?

To my knowledge the unix authentication by default idea has been postponed (by Rasmus) to 10.3, so there is no idea in testing if it works in 10.1 or 10.2 on any branch.

otto,

It's not related to your branch. The logic in buildbot config is

case "%(branch)s" in

*10.[12]e*)

  sudo mysql -uroot -e "select @@version"

  ;;

*)

  mysql -uroot -prootpass -e "select @@version"

  ;;

esac

That is, it's the same for any non-enterprise branch. What you see in the log is already the result of substitution.

Thanks elenst for the clarification. With that help I can now read it checks for 10.1e and 10.2e branch names.

Any update on this one?

Assigning to svoj (temporarily – not necessarily to fix, but to provide the information as we discussed that upgrade test on Xenial and Yakkety is one of a few remaining persistent failures).

Currently the test fails because after Otto's fixes, upon upgrade 5.7 => 10.2 upgrade MySQL 5.7 directory gets backed up, and 10.2 starts from scratch. However, currently the test does not expect it, it "knows" that the old server had a root password, and attempts to use it.

It can be changed on the buildbot side, but I'm not sure that's what we want. I'm pretty sure we want the live upgrade from MySQL 5.7 to 10.2 work, it would be a big loss if it does not.

However, currently it does not work due to this bug, so even if we revert Otto's workaround, it will still fail. So, the bug should be fixed first, then we should revert the fix and see what remains to be done.

I am happy to revert/re-engineer the Debian maintainer scripts to support proper upgrading if you just notify me that 10.2 supports 5.7 mysql.user table format (or has some mysql_upgrade converts it or whatever). I agree with Elena that we want to have a bit better compatibility with 5.7 so that we don't need to throw away the whole of /var/lib/mysql at install/upgrade.

At a glance this particular bug looks fixable. Probably even easily fixable. Fixing this doesn't necessary mean 5.7->10.2 will work smoothly. Best assignee is probably Vicentiu, if he doesn't too much things currently.

Note to self: all yakkety and xenial tests at http://buildbot.askmonty.org/buildbot/grid?branch=10.2&category=main&category=experimental are still red because of this.

The newly added kvm-deb-xenial-ppc64le also fails the upgrade step due to this.

Reminder to cvicentiu:
Please keep in mind that technically tests now fail on a different reason – because they expect to find the old datadir (with the root password), but Otto's workaround moves it and replaces with a clean new one. It could be fixed on buildbot side, by making it forget the old password, but I won't do it, because we don't want it, we want MariaDB 10.2 to be able to work on MySQL 5.7 datadir, which does not happen due to the problem described in this bug report. So, when you fix it, the buildbot tests will still fail, don't get confused by it. After that, we'll need Otto's workaround to be reverted (for 10.2 only, for 10.1 it should remain in place!), and then the buildbot tests should supposedly pass. If not, then there are some other problems, which is quite possible of course.

Hi Sergei!

Can you please review the fix for this issue?
Some thoughts on the fix
http://lists.askmonty.org/pipermail/commits/2016-December/010260.html

Commit email:
http://lists.askmonty.org/pipermail/commits/2016-December/010259.html

elenst
With my proposed fix, when using a MySQL 5.7.6 (and up) datadir, one has to do:

Start server with --skip-grant-tables.
Run mysql_upgrade (with the updated changes)
Restart server without --skip-grant-tables.

Does this satisfy the issue? Or is it strictly necessary to have MariaDB work with MySQL 5.7 without mysql_upgrade, and without --skip-grant-tables?

The problem is that MySQL decided to randomly change the mysql.user table and remove the password column. What's worse is that they've introduced this braking change in a stable version.

cvicentiu,

It's not necessary that MariaDB actually works well without mysql_upgrade (and it won't, even if you fix the user table problem somehow); but it should start without mysql_upgrade and allow to run mysql_upgrade, otherwise it's a dead end.

For skip-grant-tables, I don't like it at all that it is necessary to set it manually in order to run mysql_upgrade, for most users it will be an obstacle annoying (or difficult) enough not to do the cross-grade. If we can solve it on the packaging level somehow (detect mysql-5.7+ directory and force the server start with skip-grant-tables and fix the system tables, including packages which now do not run mysql_upgrade automatically), then it should be fine I guess. In any case, I'll leave it to serg to decide – if he thinks it's okay to rely on the user to do it manually, I won't fight it.

cvicentiu We need a solution that "just works". Users should not be required to do much more than 'apt-get upgrade'.

In packaging we can detect an existing mysql-5.7 datadir because there will be a /var/lib/mysql/5.7.flag file, but I don't know any easy way to customize the init script so that invoking a mysqld restart would do the things you describe. Can't you make a normal mysql_upgrade work here?

Working on the "just works" solution. I wanted to avoid server logic changes, but I have thought about the alternative.

I'm running into the same issue upgrading from MySQL 5.7 to MariaDB version 10. I thought MariaDB was a drop-in replacement? That's hardly the case when the upgrade results in a loss of all databases and data.

Is there a fix for this yet?

own3mall,
Which 10.x are you running?

10.2 will be able to start on MySQL 5.7 datadir, but it is still beta, and there are bugs like this one which prevent it from working.
10.1 and older, in general, aren't able to start on MySQL 5.7 datadir, and won't be, just like MySQL 5.6 cannot.

10.0.28-0ubuntu0.16.04.1

That explains it. Ubuntu should be ashamed they released this considering it to be stable.

own3mall,

There is nothing for Ubuntu to be ashamed of.
It's not a bug that 10.0 cannot start on MySQL 5.7 datadir, it is not expected to.
Backward compatibility between major versions, in general, might work, but it has never been guaranteed, neither by MySQL, nor by MariaDB, nor by the majority of software products.
10.0 was first released years before MySQL 5.7, it couldn't possibly be designed to be compatible with a version which didn't even exist at the time.

I don't know why, but if I switch MySQL 5.7.x over to version 10 of MariaDB, it says the data isn't compatible, and after it installs, all the databases that were in 5.7 are gone. I don't know if it's datadir related. Is this normal?

This is something that Debian introduced in their MariaDB packages to avoid cross-grade failures which can leave the installation in inconsistent state.

If it discovers that an upgrade from MySQL 5.7 to 10.0 is performed, it backs up the old (5.7) datadir somewhere, and installs 10.0 on a clean datadir, so that the data could be converted/imported later if needed. I can't say off the top of my head which name it gives to the 5.7 datadir, but it should be easy to find.

Yeah, a dialog stated I should create a backup of all the databases first and then re-import it, but when I do that, MariaDB states the users table has issues and won't start, which would make sense based on MySQL 5.7.x changes. So, is there anyway to get the all of the databases re-imported along with the users table? I could have thousands of MySQL users...

Hi,
the following script permits to rollback to mysql-server-5.7 for users that are not able to import their DB into mariadb-server-10.0.
rollback_mysql5.7.sh

May I suggest that the installation of mariadb-server-10.0 do not overwrite the '/etc/mysql/debian-start' script and instead rename it to '/etc/mysql/debian-start-5.7' as it does for '/etc/mysql/debian.cnf' ?
This way we could remove the part in the script that need to download and extract the original '/etc/mysql/debian-start' script.

I just noticed the line

alter table mysql.user add column `Password` char(41) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL DEFAULT '' after `user`

in https://github.com/MariaDB/server/commit/ad4239cc3dc7ad5f6f264e1fb3cf6d24084bda90#diff-e5e5c9a25c77f21cce4305ecb13c1524 (added to scripts/mysql_to_mariadb.sql)
looks very similar to the line

ALTER TABLE user ADD Password char(41) character set latin1 collate latin1_bin NOT NULL default '' AFTER User;

in https://github.com/mariadb/server/commit/fdfdea40f1b9d029de59131f54096c1b044ffdfa#diff-31d16d61530087cf04e29a2667c5bda0 (added to scripts/mysql_system_tables_fix.sql)

cvicentiu Can you check if either one is a duplicate and should be cleaned away?

Related downstream issue: https://bugs.debian.org/cgi-bin/bugreport.cgi?bug=926231

Currently upgrading from MySQL 5.7 to MariaDB 10.3 requires and extra mysql_upgrade run.