Bug #110122 Start MySQL 8.0.32 second instance with temporary data directory
Submitted: 17 Feb 2023 14:22 Modified: 27 Feb 2023 13:14
Reporter: Shyam Bhat Email Updates:
Status: Unsupported Impact on me:
None 
Category:MySQL Server: mysqldump Command-line Client Severity:S3 (Non-critical)
Version:8.0.32 OS:Red Hat (Alma 8.7 (Valeri Polyakov))
Assigned to: CPU Architecture:x86
Tags: mysql8, mysqldump

[17 Feb 2023 14:22] Shyam Bhat
Description:
We are trying to start a second MySQL instance using a MySQL snapshot + a database dir - it always worked with various MySQL versions but it fully stopped once our server got upgraded from 8.0.31 to 8.0.32. 

We have copied files required to start MySQL:
[root@nl1-ts103:/home/sbhat]$ ls -1 mysql8dbrestore/
auto.cnf
ib_buffer_pool
ibdata1
ibtmp1
'#innodb_redo'
'#innodb_temp'
mysql
mysql8.sock
mysql8.sock.lock
mysql.error.log
mysql.ibd
remelife_RML2022 < database
undo_001
undo_002

I can start second mysql instance with recovery mode 6. I can repait|optimize or collect various MySQL information like mysqladmin var etc. 

However, when I try to mysqldump a database ( any db ) it fails and here are the full logs:

-------------------------------------------------
2023-02-17T14:00:14.855680Z 1 [Note] [MY-012356] [InnoDB] Scanned 111603 tablespaces. Validated 141. Found 111462 missing.
2023-02-17T14:00:14.855979Z 1 [Warning] [MY-011018] [InnoDB] Skip updating information_schema metadata in InnoDB read-only mode.
2023-02-17T14:00:14.856014Z 1 [Warning] [MY-010005] [Server] Skip re-populating collations and character sets tables in InnoDB read-only mode.
2023-02-17T14:00:14.856120Z 1 [Note] [MY-010006] [Server] Using data dictionary with version '80023'.
2023-02-17T14:00:15.061250Z 0 [ERROR] [MY-011292] [Server] Plugin mysqlx reported: 'Preparation of I/O interfaces failed, X Protocol won't be accessible'
2023-02-17T14:00:15.061304Z 0 [ERROR] [MY-011300] [Server] Plugin mysqlx reported: 'Setup of socket: '/var/run/mysqld/mysqlx.sock' failed, another process with PID 3438480 is using UNIX socket file'
2023-02-17T14:00:15.061313Z 0 [Note] [MY-011322] [Server] Plugin mysqlx reported: 'Please see the MySQL documentation for 'mysqlx_socket' system variables to fix the error'
2023-02-17T14:00:15.061556Z 2 [Warning] [MY-011018] [Server] Skip updating information_schema metadata in InnoDB read-only mode.
2023-02-17T14:00:15.062202Z 0 [Warning] [MY-010970] [Server] Skipped updating resource group metadata in InnoDB read only mode.
2023-02-17T14:00:15.062220Z 0 [Warning] [MY-010970] [Server] Skipped updating resource group metadata in InnoDB read only mode.
2023-02-17T14:00:15.062226Z 0 [Warning] [MY-010970] [Server] Skipped updating resource group metadata in InnoDB read only mode.
2023-02-17T14:00:15.066629Z 0 [Note] [MY-013911] [Server] Crash recovery finished in InnoDB engine. No attempts to commit, rollback or prepare any transactions.
2023-02-17T14:00:15.309728Z 0 [Note] [MY-010305] [Server] Auto generated SSL certificates are placed in data directory.
2023-02-17T14:00:15.310701Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.
2023-02-17T14:00:15.310733Z 0 [System] [MY-013602] [Server] Channel mysql_main configured to support TLS. Encrypted connections are now supported for this channel.
2023-02-17T14:00:15.338703Z 0 [Note] [MY-010309] [Server] Auto generated RSA key files through --sha256_password_auto_generate_rsa_keys are placed in data directory.
2023-02-17T14:00:15.338746Z 0 [Note] [MY-010308] [Server] Skipping generation of RSA key pair through --caching_sha2_password_auto_generate_rsa_keys as key files are present in data directory.
2023-02-17T14:00:15.346195Z 0 [Note] [MY-011025] [Repl] Failed to start slave threads for channel ''.
2023-02-17T14:00:15.346638Z 0 [Note] [MY-011240] [Server] Plugin mysqlx reported: 'Using SSL configuration from MySQL Server'
2023-02-17T14:00:15.347237Z 0 [Note] [MY-011243] [Server] Plugin mysqlx reported: 'Using OpenSSL for TLS connections'
2023-02-17T14:00:15.347343Z 0 [System] [MY-010931] [Server] /usr/sbin/mysqld: ready for connections. Version: '8.0.32-cll-lve'  socket: '/home/sbhat/mysql8dbrestore/mysql8.sock'  port: 0  MySQL Community Server - GPL.
2023-02-17T14:01:42.796004Z 8 [ERROR] [MY-013183] [InnoDB] Assertion failure: btr0pcur.cc:336:btr_page_get_prev(next_page, mtr) == get_block()->page.id.page_no() thread 139677891168000
InnoDB: We intentionally generate a memory trap.
InnoDB: Submit a detailed bug report to http://bugs.mysql.com.
InnoDB: If you get repeated assertion failures or crashes, even
InnoDB: immediately after the mysqld startup, there may be
InnoDB: corruption in the InnoDB tablespace. Please refer to
InnoDB: http://dev.mysql.com/doc/refman/8.0/en/forcing-innodb-recovery.html
InnoDB: about forcing recovery.
2023-02-17T14:01:42Z UTC - mysqld got signal 6 ;
Most likely, you have hit a bug, but this error can also be caused by malfunctioning hardware.
BuildID[sha1]=568ced699a8b77ad3edb265e241ec89f673778b8
Thread pointer: 0x7f08c8001370
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 = 7f094b15fc10 thread_stack 0x49000
/usr/sbin/mysqld(my_print_stacktrace(unsigned char const*, unsigned long)+0x41) [0x2140ad1]
/usr/sbin/mysqld(print_fatal_signal(int)+0x387) [0xfe1837]
/usr/sbin/mysqld(my_server_abort()+0x7e) [0xfe198e]
/usr/sbin/mysqld(my_abort()+0xe) [0x213a86e]
/usr/sbin/mysqld(ut_dbg_assertion_failed(char const*, char const*, unsigned long)+0x33a) [0x243fd8a]
/usr/sbin/mysqld(btr_pcur_t::move_to_next_page(mtr_t*)+0x19f) [0x248b38f]
/usr/sbin/mysqld(btr_pcur_t::move_to_next(mtr_t*)+0x4c) [0x23afffc]
/usr/sbin/mysqld(row_search_mvcc(unsigned char*, page_cur_mode_t, row_prebuilt_t*, unsigned long, unsigned long)+0xdaf) [0x23b997f]
/usr/sbin/mysqld(ha_innobase::general_fetch(unsigned char*, unsigned int, unsigned int)+0x218) [0x224b088]
/usr/sbin/mysqld(handler::ha_rnd_next(unsigned char*)+0x1cc) [0x10f193c]
/usr/sbin/mysqld(TableScanIterator::Read()+0x7b) [0x124679b]
/usr/sbin/mysqld(FilterIterator::Read()+0x18) [0x13b8a68]
/usr/sbin/mysqld(filesort(THD*, Filesort*, RowIterator*, unsigned long, unsigned long long, Filesort_info*, Sort_result*, unsigned long long*)+0x718) [0x10def38]
/usr/sbin/mysqld(SortingIterator::DoSort()+0x72) [0x1247a62]
/usr/sbin/mysqld(SortingIterator::Init()+0x34) [0x1247af4]
/usr/sbin/mysqld(NestedLoopIterator::Init()+0x18) [0x13b93f8]
/usr/sbin/mysqld(NestedLoopIterator::Init()+0x18) [0x13b93f8]
/usr/sbin/mysqld(NestedLoopIterator::Init()+0x18) [0x13b93f8]
/usr/sbin/mysqld(NestedLoopIterator::Init()+0x18) [0x13b93f8]
/usr/sbin/mysqld(NestedLoopIterator::Init()+0x18) [0x13b93f8]
/usr/sbin/mysqld(Query_expression::ExecuteIteratorQuery(THD*)+0x21c) [0xf4a7cc]
/usr/sbin/mysqld(Query_expression::execute(THD*)+0x30) [0xf4abc0]
/usr/sbin/mysqld(Sql_cmd_dml::execute(THD*)+0x189) [0xed64e9]
/usr/sbin/mysqld(mysql_execute_command(THD*, bool)+0xb92) [0xe73172]
/usr/sbin/mysqld(dispatch_sql_command(THD*, Parser_state*)+0x54a) [0xe76c4a]
/usr/sbin/mysqld(dispatch_command(THD*, COM_DATA const*, enum_server_command)+0xd3c) [0xe77fcc]
/usr/sbin/mysqld(do_command(THD*)+0x207) [0xe7a277]
/usr/sbin/mysqld() [0xfd1790]
/usr/sbin/mysqld() [0x29120d9]
/lib64/libpthread.so.0(+0x81cf) [0x7f096bc7f1cf]
/lib64/libc.so.6(clone+0x43) [0x7f096a023e73]

Trying to get some variables.
Some pointers may be invalid and cause the dump to abort.
Query (7f08c8009d50): show fields from `wp20_actionscheduler_actions`
Connection ID (thread ID): 8
Status: NOT_KILLED

The manual page at http://dev.mysql.com/doc/mysql/en/crashing.html contains
information that should help you find out what is causing the crash.

-------------------------------------------------

How to repeat:
1. Copy a good data mysql snapshot including full datadir + database files.
2. Using the data dir, try start mysql 8.0.32 binary. 
Command/script we use:

tempdatadir="/home/mysql-temp-data-dir"
mysql_sockfile=mysql8.sock

/usr/sbin/mysqld --innodb-read-only=off --log-error-verbosity=3 --innodb --pid-file=${tempdatadir}/mysql.pid --skip-networking --innodb_data_home_dir=${tempdatadir} --innodb_data_file_path=ibdata1:10M:autoextend --innodb_file_per_table --innodb_log_group_home_dir=${tempdatadir} --socket=${tempdatadir}/${mysql_sockfile} --datadir=${tempdatadir} -u root --skip-grant-tables --innodb_force_recovery=6 --innodb_file_per_table=1 --innodb-change-buffering=none --skip-innodb-stats-persistent --innodb_buffer_pool_size=256M --log-error=${tempdatadir}/mysql.error.log

MySQL starts and repair, check works, dump fails. 

Suggested fix:
Not found yet, have been trying a lot to fix this, we have many servers upgraded to 8.0.32 and wondering if community can advice something.
[20 Feb 2023 13:27] MySQL Verification Team
Hi Mr. Bhat,

Thank you for your bug report.

MySQL is designed in such a manner that the instances can not share any file at all, including the temporary files. There were few locks that were missing that were fixed in 8.0.32.

Also, moving snapshot is not supported, as we have metadata tablespaces that keep the info on the tablespace and file location.

If you want to run two or more instances on Linux, we recommend you to use Docker.

Unsupported.
[20 Feb 2023 13:28] MySQL Verification Team
This is all described in our Reference Manual.
[27 Feb 2023 13:14] Shyam Bhat
Actually were not using a production datadir to start second instance. 

What we do is: 
1. We copy full /var/lib/mysql snapshot including all mysql system files like ibdata, undo, redo, ib* etc. 

2. We use this snapshot as copy into a secondary directory like: /var/lib/mysql_second_dir

3. Then we would start MySQLd with recovery 6 and /var/lib/mysql_second_dir as data dir. 

This used to work always :) It stopped working, mainly because:
1. Databases were mysql 8.0.31 or 8.0.23 and we tried to start secondary instance 8.0.32 - here, DD upgrade fails. 

2. I think this Data Dictionary Initialization fails if mysql version is different. 

Thanks!
Shyam Bhat
[28 Feb 2023 14:25] MySQL Verification Team
Yes, this method of moving MySQL data is unsupported in 8.0,