maria-discuss team mailing list archive

[bapt x <baptx.is@xxxxxxxxx>]

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@xxxxxxxxx> 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.is@xxxxxxxxx> 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@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
>>>>>>
>>>>>