maria-discuss team mailing list archive

[bapt x <baptx.is@xxxxxxxxx>]

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
>>>>>
>>>>