I also tried to create a file /etc/mysql/conf.d/mysqld.cnf with this content: [mysqld] innodb_force_recovery = 2
And then I was able to start mysql (innodb_force_recovery = 0 and innodb_force_recovery = 1 did not work). But I cannot log in with the command "sudo mysql" (even when using greater values innodb_force_recovery = 6), I get this error: ERROR 1524 (HY000): Plugin 'unix_socket' is not loaded On Sun, 20 Oct 2019 at 21:19, bapt x <baptx...@gmail.com> wrote: > 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 <baptx...@gmail.com> 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 <greenl...@gmail.com> >> 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 <baptx...@gmail.com> 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 <gordan.bo...@gmail.com> >>>> 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 <baptx...@gmail.com> 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 maria-discuss@lists.launchpad.net, you can see >>>>>> my reply below. >>>>>> >>>>>> ---------- Forwarded message --------- >>>>>> From: bapt x <baptx...@gmail.com> >>>>>> 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 <gordan.bo...@gmail.com> >>>>>> >>>>>> >>>>>> 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 <gordan.bo...@gmail.com> >>>>>> 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, <baptx...@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 >>>>>>>> >>>>>>> _______________________________________________ >>>>>> 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 >>>>>> >>>>> _______________________________________________ >>>>> 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 >>>>> >>>> _______________________________________________ >>>> 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 >>>> >>>
_______________________________________________ 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
