maria-discuss team mailing list archive

[bapt x <baptx.is@xxxxxxxxx>]

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@xxxxxxxxx> 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@xxxxxxxxx> 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@xxxxxxxxx>
>> 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@xxxxxxxxx> 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@xxxxxxxxx>
>>>> 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@xxxxxxxxx> 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@xxxxxxxxxxxxxxxxxxx, you can see
>>>>>> my reply below.
>>>>>>
>>>>>> ---------- Forwarded message ---------
>>>>>> From: bapt x <baptx.is@xxxxxxxxx>
>>>>>> 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@xxxxxxxxx>
>>>>>>
>>>>>>
>>>>>> 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@xxxxxxxxx>
>>>>>> 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@xxxxxxxxx> 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@xxxxxxxxxxxxxxxxxxx
>>>>>>>> Unsubscribe : https://launchpad.net/~maria-discuss
>>>>>>>> More help   : https://help.launchpad.net/ListHelp
>>>>>>>>
>>>>>>> _______________________________________________
>>>>>> Mailing list: https://launchpad.net/~maria-discuss
>>>>>> Post to     : maria-discuss@xxxxxxxxxxxxxxxxxxx
>>>>>> Unsubscribe : https://launchpad.net/~maria-discuss
>>>>>> More help   : https://help.launchpad.net/ListHelp
>>>>>>
>>>>> _______________________________________________
>>>>> Mailing list: https://launchpad.net/~maria-discuss
>>>>> Post to     : maria-discuss@xxxxxxxxxxxxxxxxxxx
>>>>> Unsubscribe : https://launchpad.net/~maria-discuss
>>>>> More help   : https://help.launchpad.net/ListHelp
>>>>>
>>>> _______________________________________________
>>>> Mailing list: https://launchpad.net/~maria-discuss
>>>> Post to     : maria-discuss@xxxxxxxxxxxxxxxxxxx
>>>> Unsubscribe : https://launchpad.net/~maria-discuss
>>>> More help   : https://help.launchpad.net/ListHelp
>>>>
>>>