By the way, is there not way to use mysqldump when we are using "innodb_force_recovery = 2"? I got this error: mysqldump: Couldn't execute 'SHOW VARIABLES LIKE 'gtid\_mode'': Table 'performance_schema.session_variables' doesn't exist (1146) Otherwise I think I will backup the data with phpMyAdmin, which was able to create an SQL file when I tested.
On Mon, 21 Oct 2019 at 17:43, bapt x <baptx...@gmail.com> wrote: > 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
