For information, I have to remove the file /var/lib/mysql/debian-10.3.flag before switching back to mysql, otherwise the installation fails with theses message:
Downgrade from (at least) 10.3 to 5.7 is not possible. MySQL has been frozen to prevent damage to your system. Please see /etc/mysql/FROZEN for help. Can someone confirm the issue so I can create a bug report? Is there a way to recover the data? On Sun, 20 Oct 2019 at 20:36, bapt x <[email protected]> wrote: > Hello, the error messages I got when trying to go to the previous version > are the one shared in my original message. > > I tried removing the ib_logfile files that you asked and also some other > files created because of the switch to mariadb: > > /var/lib/mysql/ib_logfile0 > /var/lib/mysql/ib_logfile1 > /var/lib/mysql/debian-10.3.flag > /etc/mysql/FROZEN > > And here are the error messages I can see in /var/log/mysql/error.log > after doing apt remove mysql-server, apt autoremove and reinstalling with > apt install mysql-server: > > Query (0): 2019-10-20T18:12:46.572904Z 0 [Warning] TIMESTAMP with implicit > DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp > server option (see documentation for more details). > 2019-10-20T18:12:46.574301Z 0 [Note] /usr/sbin/mysqld (mysqld > 5.7.27-0ubuntu0.19.04.1) starting as process 1105 ... > 2019-10-20T18:12:46.583607Z 0 [Note] InnoDB: PUNCH HOLE support available > 2019-10-20T18:12:46.583660Z 0 [Note] InnoDB: Mutexes and rw_locks use GCC > atomic builtins > 2019-10-20T18:12:46.583673Z 0 [Note] InnoDB: Uses event mutexes > 2019-10-20T18:12:46.583685Z 0 [Note] InnoDB: GCC builtin > __atomic_thread_fence() is used for memory barrier > 2019-10-20T18:12:46.583695Z 0 [Note] InnoDB: Compressed tables use zlib > 1.2.11 > 2019-10-20T18:12:46.583706Z 0 [Note] InnoDB: Using Linux native AIO > 2019-10-20T18:12:46.584031Z 0 [Note] InnoDB: Number of pools: 1 > 2019-10-20T18:12:46.584134Z 0 [Note] InnoDB: Using CPU crc32 instructions > 2019-10-20T18:12:46.592222Z 0 [Note] InnoDB: Initializing buffer pool, > total size = 128M, instances = 1, chunk size = 128M > 2019-10-20T18:12:46.607998Z 0 [Note] InnoDB: Completed initialization of > buffer pool > 2019-10-20T18:12:46.619710Z 0 [Note] InnoDB: If the mysqld execution user > is authorized, page cleaner thread priority can be changed. See the man > page of setpriority(). > 2019-10-20T18:12:46.640030Z 0 [Note] InnoDB: Highest supported file format > is Barracuda. > 2019-10-20T18:12:46.641175Z 0 [Note] InnoDB: Log scan progressed past the > checkpoint lsn 3046924 > 2019-10-20T18:12:46.641194Z 0 [Note] InnoDB: Doing recovery: scanned up to > log sequence number 3046933 > 2019-10-20T18:12:46.641203Z 0 [Note] InnoDB: Database was not shutdown > normally! > 2019-10-20T18:12:46.641211Z 0 [Note] InnoDB: Starting crash recovery. > 2019-10-20T18:12:46.766437Z 0 [Note] InnoDB: Removed temporary tablespace > data file: "ibtmp1" > 2019-10-20T18:12:46.766468Z 0 [Note] InnoDB: Creating shared tablespace > for temporary tables > 2019-10-20T18:12:46.766539Z 0 [Note] InnoDB: Setting file './ibtmp1' size > to 12 MB. Physically writing the file full; Please wait ... > 2019-10-20T18:12:46.838038Z 0 [Note] InnoDB: File './ibtmp1' size is now > 12 MB. > 2019-10-20T18:12:46.839313Z 0 [Note] InnoDB: 96 redo rollback segment(s) > found. 96 redo rollback segment(s) are active. > 2019-10-20T18:12:46.839340Z 0 [Note] InnoDB: 32 non-redo rollback > segment(s) are active. > 2019-10-20T18:12:46.839768Z 0 [Note] InnoDB: Waiting for purge to start > 18:12:46 UTC - mysqld got signal 11 ; > This could be because you hit a bug. It is also possible that this binary > or one of the libraries it was linked against is corrupt, improperly built, > or misconfigured. This error can also be caused by malfunctioning hardware. > Attempting to collect some information that could help diagnose the > problem. > As this is a crash and something is definitely wrong, the information > collection process might fail. > > key_buffer_size=16777216 > read_buffer_size=131072 > max_used_connections=0 > max_threads=151 > thread_count=0 > connection_count=0 > It is possible that mysqld could use up to > key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = > 76388 K bytes of memory > Hope that's ok; if not, decrease some variables in the equation. > > Thread pointer: 0x7f429c000b20 > Attempting backtrace. You can use the following information to find out > where mysqld died. If you see no messages after this, something went > terribly wrong... > stack_bottom = 7f42a77fddc0 thread_stack 0x30000 > /usr/sbin/mysqld(my_print_stacktrace+0x3b)[0xed086b] > /usr/sbin/mysqld(handle_fatal_signal+0x453)[0x7bbc63] > /lib/x86_64-linux-gnu/libpthread.so.0(+0x13f40)[0x7f42ccd10f40] > > /usr/sbin/mysqld(_Z28trx_undo_rec_get_partial_rowPKhP12dict_index_tPP8dtuple_tmP16mem_block_info_t+0x1c4)[0x106e7a4] > /usr/sbin/mysqld(_Z14row_purge_stepP9que_thr_t+0xb48)[0x100d128] > /usr/sbin/mysqld(_Z15que_run_threadsP9que_thr_t+0xc54)[0xfbe164] > /usr/sbin/mysqld(_Z9trx_purgemmb+0x7ab)[0x106ae9b] > /usr/sbin/mysqld(srv_purge_coordinator_thread+0xad5)[0x1041e85] > /lib/x86_64-linux-gnu/libpthread.so.0(+0x9182)[0x7f42ccd06182] > /lib/x86_64-linux-gnu/libc.so.6(clone+0x3f)[0x7f42cc8e4b1f] > > Trying to get some variables. > Some pointers may be invalid and cause the dump to abort. > > > On Thu, 17 Oct 2019 at 19:57, Justin Swanhart <[email protected]> wrote: > >> Hi, >> >> What is the error message from MySQL when you go back to the prior >> version? >> >> Depending on the error, it is probably possible to simply remove the >> logfiles before restarting the old version. I don't have an ubuntu DVD on >> hand and am on slow internet so I can't test right now. >> >> On Wed, Oct 16, 2019 at 10:15 AM bapt x <[email protected]> wrote: >> >>> You said "This step is not necessary when upgrading to MariaDB 10.2.5 or >>> later." and in my case, I was upgrading to mariadb-server 10.3.17, so I >>> guess I should not need "set global innodb_fast_shutdown=0;"? >>> Can someone reproduce the issue with Ubuntu 19.04 on VirtualBox? >>> >>> On Wed, 16 Oct 2019 at 11:05, Gordan Bobic <[email protected]> >>> wrote: >>> >>>> I'm not sure if you accidentally omitted it, but the part I was >>>> referring to is documented here: >>>> >>>> https://mariadb.com/kb/en/library/upgrading-from-mariadb-101-to-mariadb-102/ >>>> >>>> Specifically: >>>> Set innodb_fast_shutdown >>>> <https://mariadb.com/kb/en/xtradbinnodb-server-system-variables/#innodb_fast_shutdown> >>>> to 0. It can be changed dynamically with SET GLOBAL >>>> <https://mariadb.com/kb/en/set/#global-session>. For example: >>>> SET GLOBAL innodb_fast_shutdown=0; >>>> >>>> - This step is not necessary when upgrading to MariaDB 10.2.5 >>>> <https://mariadb.com/kb/en/mariadb-1025-release-notes/> or later. >>>> >>>> >>>> Can you confirm this is reproducible if you: >>>> >>>> MariaDB> set global innodb_fast_shutdown=0; >>>> # systemctl stop mariadb >>>> # rm /var/lib/mysql/ib_logfile* >>>> >>>> and then do the package upgrade and restart? >>>> >>>> Can you back up the full data set (or snapshot it)? >>>> If so, remove the ib_logfile* files and see if that lets you start up >>>> mysqld? Failing that, you may have to crank up innodb_force_recover=6 >>>> (because this is level to avoid redo log replay), and then mysqldump the >>>> data. You will lose any recent transactions that haven't made it from the >>>> transaction log to the tablespaces. >>>> >>>> >>>> >>>> On Wed, Oct 16, 2019 at 9:46 AM bapt x <[email protected]> wrote: >>>> >>>>> @Andrei all the error messages I found were included in my original >>>>> email, let me know how I can provide additional information if no one can >>>>> reproduce the problem. >>>>> I forgot to include [email protected], you can see my >>>>> reply below. >>>>> >>>>> ---------- Forwarded message --------- >>>>> From: bapt x <[email protected]> >>>>> Date: Wed, 16 Oct 2019 at 10:35 >>>>> Subject: Re: [Maria-discuss] database corrupted when switching from >>>>> MySQL to MariaDB on Ubuntu 19.04 >>>>> To: Gordan Bobic <[email protected]> >>>>> >>>>> >>>>> Thanks for the information. It looks like I did everything properly >>>>> since I was able to reproduce the problem several times with a clean >>>>> install of Ubuntu 19.04 on VirtualBox. I think if someone else tries the >>>>> steps I explained, he can reproduce the problem. Now it would be nice to >>>>> know if there is a way to recover the data. If MariaDB was able to corrupt >>>>> the data, there should be a way to reverse engineer the process and >>>>> restore >>>>> the data. Maybe a developer that knows well MariaDB upgrade system has a >>>>> solution. >>>>> >>>>> On Wed, 16 Oct 2019 at 10:23, Gordan Bobic <[email protected]> >>>>> wrote: >>>>> >>>>>> 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, <[email protected]> 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 : [email protected] >>>>>>> Unsubscribe : https://launchpad.net/~maria-discuss >>>>>>> More help : https://help.launchpad.net/ListHelp >>>>>>> >>>>>> _______________________________________________ >>>>> Mailing list: https://launchpad.net/~maria-discuss >>>>> Post to : [email protected] >>>>> Unsubscribe : https://launchpad.net/~maria-discuss >>>>> More help : https://help.launchpad.net/ListHelp >>>>> >>>> _______________________________________________ >>>> Mailing list: https://launchpad.net/~maria-discuss >>>> Post to : [email protected] >>>> Unsubscribe : https://launchpad.net/~maria-discuss >>>> More help : https://help.launchpad.net/ListHelp >>>> >>> _______________________________________________ >>> Mailing list: https://launchpad.net/~maria-discuss >>> Post to : [email protected] >>> Unsubscribe : https://launchpad.net/~maria-discuss >>> More help : https://help.launchpad.net/ListHelp >>> >>
_______________________________________________ Mailing list: https://launchpad.net/~maria-discuss Post to : [email protected] Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp
