To bypass the last error I previously tried `sudo /etc/init.d/mysql stop`, `sudo mysqld_safe --skip-grant-tables &` and `mysql -u root` (from https://askubuntu.com/questions/705458/ubuntu-15-10-mysql-error-1524-unix-socket) but mysqld_safe returned the error: "mysqld_safe Directory '/var/run/mysqld' for UNIX socket file don't exists.".
I fixed the issue with these commands and I am now able to see the data (from https://stackoverflow.com/questions/42153059/mysqld-safe-directory-var-run-mysqld-for-unix-socket-file-dont-exists ): mkdir -p /var/run/mysqld chown mysql:mysql /var/run/mysqld Thanks for your help on recovering the data, should I create a bug report so the issue gets fixed? On Mon, 21 Oct 2019 at 16:32, bapt x <baptx...@gmail.com> wrote: > 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
