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.
