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.is@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-so...) 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-m... ): 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.is@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.is@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.is@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 <greenlion@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.is@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.bobic@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.is@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.is@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.bobic@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.bobic@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.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 >>>> >>> _______________________________________________ >> 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