[Maria-discuss] database corrupted when switching from MySQL to MariaDB on Ubuntu 19.04
Hello, On Ubuntu 19.04, which uses packages mariadb-server 10.3.17 and mysql-server 5.7.27, I noticed that if I wanted to switch from MySQL to MariaDB, the database is corrupted and there is a complete data loss even if I switch back to MySQL. In the previous version of Ubuntu, switching from MySQL to MariaDB did not manage to import data automatically (unlike Debian) but at least it created a backup of the data in /var/lib/mysql-5.7/ folder which is not done anymore. Here is the error message I saw during install when trying to use the database corrupted by MariaDB and switching back to MySQL: "MySQL has been frozen to prevent damage to your system. Please see /etc/mysql/FROZEN for help." And in /var/log/mysql/error.log: "[ERROR] InnoDB: Unsupported redo log format. The redo log was created with MariaDB 10.3.17. Please follow the instructions athttp://dev.mysql.com/doc/refman/5.7/en/upgrading-downgrading.html" I was able to reproduce the issue with a clean installation of Ubuntu 19.04 in VirtualBox. Do you know where the problem comes from and if it is possible to fix the binary data from */var/lib/mysql/* to make it work with either MySQL or MariaDB? It looks like MariaDB tried to convert the data ("[ERROR] InnoDB: Unsupported redo log format") but now it fails with both MySQL and MariaDB. Is it possible to revert the changes done by MariaDB to make the data work again with MySQL? (and then do a proper backup with mysqldump) Thanks.
I don't know if it is recoverable but it sounds like you missed the step of always needing a full, clean shutdown between upgrades with innodb_fast_shutdown=0. Then you can delete ib_logfile*, and upgrade. On Wed, 16 Oct 2019, 09:19 bapt x, <baptx.is@gmail.com> wrote:
Hello,
On Ubuntu 19.04, which uses packages mariadb-server 10.3.17 and mysql-server 5.7.27, I noticed that if I wanted to switch from MySQL to MariaDB, the database is corrupted and there is a complete data loss even if I switch back to MySQL. In the previous version of Ubuntu, switching from MySQL to MariaDB did not manage to import data automatically (unlike Debian) but at least it created a backup of the data in /var/lib/mysql-5.7/ folder which is not done anymore.
Here is the error message I saw during install when trying to use the database corrupted by MariaDB and switching back to MySQL: "MySQL has been frozen to prevent damage to your system. Please see /etc/mysql/FROZEN for help."
And in /var/log/mysql/error.log: "[ERROR] InnoDB: Unsupported redo log format. The redo log was created with MariaDB 10.3.17. Please follow the instructions athttp://dev.mysql.com/doc/refman/5.7/en/upgrading-downgrading.html"
I was able to reproduce the issue with a clean installation of Ubuntu 19.04 in VirtualBox.
Do you know where the problem comes from and if it is possible to fix the binary data from */var/lib/mysql/* to make it work with either MySQL or MariaDB? It looks like MariaDB tried to convert the data ("[ERROR] InnoDB: Unsupported redo log format") but now it fails with both MySQL and MariaDB. Is it possible to revert the changes done by MariaDB to make the data work again with MySQL? (and then do a proper backup with mysqldump)
Thanks.
_______________________________________________ Mailing list: https://launchpad.net/~maria-discuss Post to : maria-discuss@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp
Am 16.10.19 um 10:23 schrieb Gordan Bobic:
I don't know if it is recoverable but it sounds like you missed the step of always needing a full, clean shutdown between upgrades with innodb_fast_shutdown=0. Then you can delete ib_logfile*, and upgrade.
always? how comes that i didn't need that for the whole past decade which means MySQl 5.0 to MariaDB 10.3 and frankly i wouldn't expect it at all, this is not PostgreSQL
On Wed, Oct 16, 2019 at 10:17 AM Reindl Harald <h.reindl@thelounge.net> wrote:
Am 16.10.19 um 10:23 schrieb Gordan Bobic:
I don't know if it is recoverable but it sounds like you missed the step of always needing a full, clean shutdown between upgrades with innodb_fast_shutdown=0. Then you can delete ib_logfile*, and upgrade.
always?
Yes.
how comes that i didn't need that for the whole past decade which means MySQl 5.0 to MariaDB 10.3 and frankly i wouldn't expect it at all, this is not PostgreSQL
Short of an incredible amount of luck, resulting in the ib_logfiles being completely flushed at the point of shutdown (or you having innodb_fast_shutdown=0 set in your configs), I don't have an explanation for why for you. I have never seen an upgrade from MariaDB 10.1 and earlier to MariaDB 10.2 and later work without following the described process, and I have carried out dozens of such upgrades over the last few years. It is documented, and a simple yum update specifically refuses to upgrade the MariaDB-server package for this exact reason. You have to do a clean shutdown, manually remove the old MariaDB-server package and then install the new MariaDB-server package.
Am 16.10.19 um 11:29 schrieb Gordan Bobic:
On Wed, Oct 16, 2019 at 10:17 AM Reindl Harald <h.reindl@thelounge.net <mailto:h.reindl@thelounge.net>> wrote:
Am 16.10.19 um 10:23 schrieb Gordan Bobic: > I don't know if it is recoverable but it sounds like you missed the step > of always needing a full, clean shutdown between upgrades with > innodb_fast_shutdown=0. Then you can delete ib_logfile*, and upgrade.
always?
Yes.
nonsense
how comes that i didn't need that for the whole past decade which means MySQl 5.0 to MariaDB 10.3 and frankly i wouldn't expect it at all, this is not PostgreSQL
Short of an incredible amount of luck, resulting in the ib_logfiles being completely flushed at the point of shutdown (or you having innodb_fast_shutdown=0 set in your configs),
no, i don't
I don't have an explanation for why for you. I have never seen an upgrade from MariaDB 10.1 and earlier to MariaDB 10.2 and later work without following the described process, and I have carried out dozens of such upgrades over the last few years.
maybe just don't jump on early releases as fast as you can helps a lot, see below
It is documented, and a simple yum update specifically refuses to upgrade the MariaDB-server package for this exact reason. You have to do a clean shutdown, manually remove the old MariaDB-server package and then install the new MariaDB-server package.
https://mariadb.com/kb/en/library/upgrading-from-mariadb-101-to-mariadb-102/ Set innodb_fast_shutdown to 0. It can be changed dynamically with SET GLOBAL. For example: SET GLOBAL innodb_fast_shutdown=0; This step is not necessary when upgrading to MariaDB 10.2.5 or later. Omitting it can make the upgrade process far faster. See MDEV-12289 for more information.
I have seen failures when upgrading from 10.0, with the latest 10.1 -> 10.2 -> 10.3 -> 10.4 unless I issued a clean shutdown between the latest 10.1 and 10.2 as recently as last week, with the latest RPMs for each version. You are trying to make an argument analogous to "smoking can't be harmful because I've been smoking for 50 years and I'm not dead yet". What is your sample size? On Wed, 16 Oct 2019, 10:51 Reindl Harald, <h.reindl@thelounge.net> wrote:
Am 16.10.19 um 11:29 schrieb Gordan Bobic:
On Wed, Oct 16, 2019 at 10:17 AM Reindl Harald <h.reindl@thelounge.net <mailto:h.reindl@thelounge.net>> wrote:
Am 16.10.19 um 10:23 schrieb Gordan Bobic: > I don't know if it is recoverable but it sounds like you missed the step > of always needing a full, clean shutdown between upgrades with > innodb_fast_shutdown=0. Then you can delete ib_logfile*, and upgrade.
always?
Yes.
nonsense
how comes that i didn't need that for the whole past decade which
means
MySQl 5.0 to MariaDB 10.3 and frankly i wouldn't expect it at all,
this
is not PostgreSQL
Short of an incredible amount of luck, resulting in the ib_logfiles being completely flushed at the point of shutdown (or you having innodb_fast_shutdown=0 set in your configs),
no, i don't
I don't have an explanation for why for you. I have never seen an upgrade from MariaDB 10.1 and earlier to MariaDB 10.2 and later work without following the described process, and I have carried out dozens of such upgrades over the last few years.
maybe just don't jump on early releases as fast as you can helps a lot, see below
It is documented, and a simple yum update specifically refuses to upgrade the MariaDB-server package for this exact reason. You have to do a clean shutdown, manually remove the old MariaDB-server package and then install the new MariaDB-server package.
https://mariadb.com/kb/en/library/upgrading-from-mariadb-101-to-mariadb-102/
Set innodb_fast_shutdown to 0. It can be changed dynamically with SET GLOBAL. For example: SET GLOBAL innodb_fast_shutdown=0;
This step is not necessary when upgrading to MariaDB 10.2.5 or later. Omitting it can make the upgrade process far faster. See MDEV-12289 for more information.
_______________________________________________ Mailing list: https://launchpad.net/~maria-discuss Post to : maria-discuss@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp
Am 16.10.19 um 11:59 schrieb Gordan Bobic:
I have seen failures when upgrading from 10.0, with the latest 10.1 -> 10.2 -> 10.3 -> 10.4 unless I issued a clean shutdown between the latest 10.1 and 10.2 as recently as last week, with the latest RPMs for each version.
You are trying to make an argument analogous to "smoking can't be harmful because I've been smoking for 50 years and I'm not dead yet".
What is your sample size?
* 10 years * 12 setups with innodb * MySQL 5.0 -> 5.1 -> 5.5 * MariaDB 5.5 -> 10.0 -> 10.1 -> 10.2 -> 10.3 Mo Sep 02 2019: 10.2.26 -> 10.3.17 and frankly i expect whatever software to be able to read it's old data
On Wed, 16 Oct 2019, 10:51 Reindl Harald, <h.reindl@thelounge.net <mailto:h.reindl@thelounge.net>> wrote:
Am 16.10.19 um 11:29 schrieb Gordan Bobic: > On Wed, Oct 16, 2019 at 10:17 AM Reindl Harald <h.reindl@thelounge.net <mailto:h.reindl@thelounge.net> > <mailto:h.reindl@thelounge.net <mailto:h.reindl@thelounge.net>>> wrote: > > > Am 16.10.19 um 10:23 schrieb Gordan Bobic: > > I don't know if it is recoverable but it sounds like you missed > the step > > of always needing a full, clean shutdown between upgrades with > > innodb_fast_shutdown=0. Then you can delete ib_logfile*, and upgrade. > > always? > > > Yes.
nonsense
> how comes that i didn't need that for the whole past decade which means > MySQl 5.0 to MariaDB 10.3 and frankly i wouldn't expect it at all, this > is not PostgreSQL > > Short of an incredible amount of luck, resulting in the ib_logfiles > being completely flushed at the point of shutdown (or you having > innodb_fast_shutdown=0 set in your configs),
no, i don't
> I don't have an explanation > for why for you. I have never seen an upgrade from MariaDB 10.1 and > earlier to MariaDB 10.2 and later work without following the described > process, and I have carried out dozens of such upgrades over the last > few years.
maybe just don't jump on early releases as fast as you can helps a lot, see below
> It is documented, and a simple yum update specifically refuses to > upgrade the MariaDB-server package for this exact reason. You have to do > a clean shutdown, manually remove the old MariaDB-server package and > then install the new MariaDB-server package.
https://mariadb.com/kb/en/library/upgrading-from-mariadb-101-to-mariadb-102/
Set innodb_fast_shutdown to 0. It can be changed dynamically with SET GLOBAL. For example: SET GLOBAL innodb_fast_shutdown=0;
This step is not necessary when upgrading to MariaDB 10.2.5 or later. Omitting it can make the upgrade process far faster. See MDEV-12289 for more information.
On Wed, 16 Oct 2019, 11:07 Reindl Harald, <h.reindl@thelounge.net> wrote:
Am 16.10.19 um 11:59 schrieb Gordan Bobic:
I have seen failures when upgrading from 10.0, with the latest 10.1 -> 10.2 -> 10.3 -> 10.4 unless I issued a clean shutdown between the latest 10.1 and 10.2 as recently as last week, with the latest RPMs for each version.
You are trying to make an argument analogous to "smoking can't be harmful because I've been smoking for 50 years and I'm not dead yet".
What is your sample size?
* 10 years * 12 setups with innodb * MySQL 5.0 -> 5.1 -> 5.5 * MariaDB 5.5 -> 10.0 -> 10.1 -> 10.2 -> 10.3
Mo Sep 02 2019: 10.2.26 -> 10.3.17
I did 6 such upgrades for customers in the last month alone. Granted, I only re-tested for this issue on one of those 6 because it was on a setup easy to snapshot and rollback, I do such things most months (only when upgrading from <= 10.1 to => 10.2, obviously) since I first got bitten by it a few years ago. And I have yet to see it work without a clean shutdown. So clearly there must be at least an element of luck / write load in play. And I don't put much faith in luck.
and frankly i expect whatever software to be able to read it's old data
Famous last words...
Hello there, Bapt.
Hello,
On Ubuntu 19.04, which uses packages mariadb-server 10.3.17 and mysql-server 5.7.27, I noticed that if I wanted to switch from MySQL to MariaDB, the database is corrupted and there is a complete data loss even if I switch back to MySQL.
I noticed that if I wanted to switch from MySQL to MariaDB, the database is corrupted
The following text does not refer to any specific error at MySQL -> MariaDB mirgration. While it's of interest on its own, it also can bring in few details to what went wrong at the restoring attempt. Cheers, Andrei
In the previous version of Ubuntu, switching from MySQL to MariaDB did not manage to import data automatically (unlike Debian) but at least it created a backup of the data in /var/lib/mysql-5.7/ folder which is not done anymore.
Here is the error message I saw during install when trying to use the database corrupted by MariaDB and switching back to MySQL: "MySQL has been frozen to prevent damage to your system. Please see /etc/mysql/FROZEN for help."
And in /var/log/mysql/error.log: "[ERROR] InnoDB: Unsupported redo log format. The redo log was created with MariaDB 10.3.17. Please follow the instructions at http://dev.mysql.com/doc/refman/5.7/en/upgrading-downgrading.html"
I was able to reproduce the issue with a clean installation of Ubuntu 19.04 in VirtualBox.
Do you know where the problem comes from and if it is possible to fix the binary data from /var/lib/mysql/ to make it work with either MySQL or MariaDB? It looks like MariaDB tried to convert the data ("[ERROR] InnoDB: Unsupported redo log format") but now it fails with both MySQL and MariaDB. Is it possible to revert the changes done by MariaDB to make the data work again with MySQL? (and then do a proper backup with mysqldump)
Thanks.
_______________________________________________ Mailing list: https://launchpad.net/~maria-discuss Post to : maria-discuss@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp
participants (4)
-
andrei.elkin＠pp.inet.fi -
bapt x -
Gordan Bobic -
Reindl Harald