Xtrabackup --prepare crash with federate database

We have few backup of db running but when we try to restore one backup with federated failed. I try from master and slave intance . and both have same issue to restore in other server.

xtrabackup --prepare --target-dir=/backup/database

xtrabackup: recognized server arguments: --innodb_checksum_algorithm=crc32 --innodb_log_checksums=1 --innodb_data_file_path=ibdata1:12M:autoextend --innodb_log_files_in_group=2 --innodb_log_file_size=50331648 --innodb_page_size=16384 --innodb_undo_directory=./ --innodb_undo_tablespaces=2 --server-id=21 --innodb_log_checksums=ON --innodb_redo_log_encrypt=0 --innodb_undo_log_encrypt=0
xtrabackup: recognized client arguments: --prepare=1 --target-dir=/backup/database
xtrabackup version 8.0.23-16 based on MySQL server 8.0.23 Linux (x86_64) (revision id: 934bc8f)
xtrabackup: cd to /backup/database
xtrabackup: This target seems to be not prepared yet.
Number of pools: 1
xtrabackup: xtrabackup_logfile detected: size=8388608, start_lsn=(4792455009376)
xtrabackup: using the following InnoDB configuration for recovery:
xtrabackup: innodb_data_home_dir = .
xtrabackup: innodb_data_file_path = ibdata1:12M:autoextend
xtrabackup: innodb_log_group_home_dir = .
xtrabackup: innodb_log_files_in_group = 1
xtrabackup: innodb_log_file_size = 8388608
xtrabackup: using the following InnoDB configuration for recovery:
xtrabackup: innodb_data_home_dir = .
xtrabackup: innodb_data_file_path = ibdata1:12M:autoextend
xtrabackup: innodb_log_group_home_dir = .
xtrabackup: innodb_log_files_in_group = 1
xtrabackup: innodb_log_file_size = 8388608
xtrabackup: Starting InnoDB instance for recovery.
xtrabackup: Using 104857600 bytes for buffer pool (set by --use-memory parameter)
PUNCH HOLE support available
Uses event mutexes
GCC builtin __atomic_thread_fence() is used for memory barrier
Compressed tables use zlib 1.2.11
Number of pools: 1
Using CPU crc32 instructions
Directories to scan ‘./’
Scanning ‘./’
Completed space ID check of 111 files.
Initializing buffer pool, total size = 128.000000M, instances = 1, chunk size =128.000000M
Completed initialization of buffer pool
page_cleaner coordinator priority: -20
page_cleaner worker priority: -20
page_cleaner worker priority: -20
page_cleaner worker priority: -20
The log sequence number 4788139721173 in the system tablespace does not match the log sequence number 4792455009376 in the ib_logfiles!
Database was not shutdown normally!
Starting crash recovery.
Starting to parse redo log at lsn = 4792455009323, whereas checkpoint_lsn = 4792455009376 and start_lsn = 4792455009280
Doing recovery: scanned up to log sequence number 4792456451303
Log background threads are being started…
Applying a batch of 811 redo log records …
10%
20%
30%
40%
50%
60%
70%
80%
90%
100%
Apply batch completed!
Using undo tablespace ‘./undo_001’.
Using undo tablespace ‘./undo_002’.
Opened 2 existing undo tablespaces.
GTID recovery trx_no: 376009489
Creating shared tablespace for temporary tables
Setting file ‘./ibtmp1’ size to 12 MB. Physically writing the file full; Please wait …
File ‘./ibtmp1’ size is now 12 MB.
Scanning temp tablespace dir:’./#innodb_temp/’
Created 128 and tracked 128 new rollback segment(s) in the temporary tablespace. 128 are now active.
8.0.23 started; log sequence number 4792456451303
Page cleaner took 5338ms to flush 0 and evict 0 pages
Allocated tablespace ID 9221686 for sportsDB/tbPeriodDescriptionsByLeague, old maximum was 0
InnoDB: Assertion failure: dict0dict.cc:1213:table2 == nullptr
InnoDB: thread 140693118307456InnoDB: We intentionally generate a memory trap.
InnoDB: Submit a detailed bug report to https://jira.percona.com/projects/PXB.
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.
16:51:04 UTC - mysqld got signal 6 ;
Most likely, you have hit a bug, but this error can also be caused by malfunctioning hardware.
Thread pointer: 0x55e57e460820
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 = 7ffdad3f8c60 thread_stack 0x46000
xtrabackup(my_print_stacktrace(unsigned char const*, unsigned long)+0x41) [0x55e57ba9c271]
xtrabackup(handle_fatal_signal+0x313) [0x55e57a6d4523]
/lib/x86_64-linux-gnu/libpthread.so.0(+0x153c0) [0x7ff5abf843c0]
/lib/x86_64-linux-gnu/libc.so.6(gsignal+0xcb) [0x7ff5ab5d918b]
/lib/x86_64-linux-gnu/libc.so.6(abort+0x12b) [0x7ff5ab5b8859]
xtrabackup(+0xdce77a) [0x55e57a0e477a]
xtrabackup(dict_table_add_to_cache(dict_table_t*, unsigned long, mem_block_info_t*)+0x11c) [0x55e57a884b2c]
xtrabackup(dd_table_create_on_dd_obj(dd::Table const*, dd::Partition const*, std::__cxx11::basic_string<char, std::char_traits, Stateless_allocator<char, dd::String_type_alloc, My_free_functor> > const*, bool, unsigned int)+0x1d30) [0x55e57a8afa80]
xtrabackup(+0x159b0c0) [0x55e57a8b10c0]
xtrabackup(dd_table_load_on_dd_obj(dd::cache::Dictionary_client*, unsigned int, dd::Table const&, dict_table_t*&, THD*, std::__cxx11::basic_string<char, std::char_traits, Stateless_allocator<char, dd::String_type_alloc, My_free_functor> > const*, bool)+0xff) [0x55e57a8b120f]
xtrabackup(dict_load_tables_from_space_id(unsigned int, THD*, trx_t*)+0x445) [0x55e57a182305]
xtrabackup(+0xe6cf87) [0x55e57a182f87]
xtrabackup(+0xe6e1e4) [0x55e57a1841e4]
xtrabackup(main+0x136e) [0x55e57a13c05e]
/lib/x86_64-linux-gnu/libc.so.6(__libc_start_main+0xf3) [0x7ff5ab5ba0b3]
xtrabackup(_start+0x2e) [0x55e57a16c40e]

Trying to get some variables.
Some pointers may be invalid and cause the dump to abort.
Query (0): Connection ID (thread ID): 0
Status: NOT_KILLED

Please report a bug at https://jira.percona.com/projects/PXB

INFO:

[mysqld@db2]
pid-file = /var/run/mysqld/mysql_db2.pid
socket = /var/run/mysqld/mysql_db2.sock
datadir = /var/lib/mysql_db2
log-error = /var/log/mysql_db2/error.log
log-bin = /var/log/mysql_db2/mysql-bin.log
port = 3307
server_id = 41
mysqlx=0
skip-slave-start
replicate_ignore_db = ppm

log-error-verbosity=1
long_query_time = 10
slow_query_log = ON
slow_query_log_file = /var/log/mysql_db2/db2-slow.log

innodb_buffer_pool_size = 32G
innodb_buffer_pool_instances = 16
symbolic-links=0

event-scheduler = ON
max_connections = 4096
group_concat_max_len = 2048
transaction-isolation = READ-COMMITTED
innodb_print_all_deadlocks
federated
table_open_cache = 32768
open_files_limit = 65536
#skip-slave-start

max_allowed_packet = 256M
sort_buffer_size = 2097152

How I make the backups:

xtrabackup --backup --user=lb --password=XXXX --compress --compress-threads=32 --parallel=32 --target-dir=/backup/db2 --slave-info --safe-slave-backup --datadir=/var/lib/mysql_db2 --socket=/var/run/mysqld/mysql_db2.sock

and descompress don’t have any error
xtrabackup --decompress --remove-original --parallel=16 --decompress-threads=16 --target-dir=/backup/db2

if error is federated database, how I excluse that database (dbfed) from backup? if federate don’t cause the error how I can know what is the error?

thanks for anyhelp

Hello @Figueroa
https://www.percona.com/doc/percona-xtrabackup/8.0/xtrabackup_bin/xbk_option_reference.html#cmdoption-databases-exclude

Report back if that solves your issue.

Just to check, you are using xtrabackup-8 with MySQL 8, correct? Is this table sportsDB/tbPeriodDescriptionsByLeague innodb? Can you run an OPTIMIZE TABLE on it and try the backup again?

1 Like

xtrabackup -v

xtrabackup version 8.0.23-16 based on MySQL server 8.0.23 Linux (x86_64) (revision id: 934bc8f)

mysql -V

mysql Ver 8.0.21-12 for Linux on x86_64 (Percona Server (GPL), Release ‘12’, Revision ‘7ddfdfe’)

yes, that table is innodb

1 Like

And what happens after you optimize the table and run a backup? Still crashes? If so, I highly recommend you perform a logical dump of the table, drop the table, recreate it and import the dump. Your backup should work then with xb.

1 Like

Yes, it still crashing, How I make logical dump of the table? I supposed it is mysqldump

1 Like

Yes, use mysqldump or better, mydumper

1 Like

about the --databases-exclude=
I try it “–databases-exclude=dbfed,dbfed2” but it still adding the DB.
are we need put in special format?

1 Like

We restore that DB and we still have de issue.
I don’t think is relate to table tbPeriodDescriptionsByLeague. Maybe is relate to:

InnoDB: Assertion failure: dict0dict.cc:1213:table2 == nullptr
InnoDB: thread 140693118307456InnoDB: We intentionally generate a memory trap.

Beacuse I see the message
“Allocated tablespace ID 9221686 for sportsDB/tbPeriodDescriptionsByLeague, old maximum was 0”
in other servers when I make the backup.

if I try backup one by one of all DBs in the instance, it is working

xtrabackup --backup --user=… --databases=db1
xtrabackup --backup --user=… --databases=db2
xtrabackup --backup --user=… --databases=db3

but if I try full backup I have same issue

xtrabackup --backup …

InnoDB: Assertion failure: dict0dict.cc:1213:table2 == nullptr
InnoDB: thread 140693118307456InnoDB: We intentionally generate a memory trap.
InnoDB: Submit a detailed bug report to https://jira.percona.com/projects/PXB.
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.

I am worried that is bug in mysql or error scheme, impact the future.

1 Like

Unfortunately, I don’t think I can assist further over forum posts. If this is indeed a bug, you’ll need to submit a ticket at https://jira.percona.com but be aware that team will need a repeatable test case and possibly a copy of your table space in order to diagnose the issue.

1 Like

thanks for you help, Do you think is a bug? and not that the some files, tables are corrupt

1 Like

Well, xtrabackup is telling us there is some type of corruption. But you said you dropped the table and re-imported it and it’s still causing an issue. There is probably corruption somewhere in the ibdata1 file then. The error says “table2” so I’m not sure if that is an internal pointer or if you have an actual table called “table2”.

1 Like