Restoring database leads to a couple of corrupted ibd

Hello,

I’m working on improving the way we do backup of our mysql server using xtrabackup, which includes testing restoration scenario.

the restoration I just did gave me an error about 2 ibd files (among 200 tables) which are reported this way

2021-03-03T09:25:18.724912Z 28 [ERROR] InnoDB: Failed to find tablespace for table `db_name`.`table_name` in the cache. Attempting to load the tablespace with space id 50561
2021-03-03T09:25:18.724992Z 28 [ERROR] InnoDB: Attempted to open a previously opened tablespace. Previous tablespace other_db/table_name at filepath: ./other_db/table_name.ibd uses space ID: 50563. Cannot open filepath: ./db_name/table_name.ibd which uses the same space ID.
2021-03-03T09:25:18.724999Z 28 [ERROR] InnoDB: Operating system error number 2 in a file operation.
2021-03-03T09:25:18.725002Z 28 [ERROR] InnoDB: The error means the system cannot find the path specified.
2021-03-03T09:25:18.725005Z 28 [ERROR] InnoDB: Could not find a valid tablespace file for `db_name/table_name`. Please refer to http://dev.mysql.com/doc/refman/5.7/en/innodb-troubleshooting-datadict.html for how to resolve the issue.
2021-03-03T09:25:18.725027Z 28 [Warning] InnoDB: Cannot calculate statistics for table `db_name`.`table_name` because the .ibd file is missing. Please refer to http://dev.mysql.com/doc/refman/5.7/en/innodb-troubleshooting.html for how to resolve the issue.

The specified idb files are on the disk and not-zero sized.
This seems to have a big impact on the server as I can see since CPU spike and such exception message

Thread pointer: 0x7f24e4000d40
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 = 7f3c5c285e50 thread_stack 0x40000
/usr/sbin/mysqld(my_print_stacktrace+0x3b)[0xeb8fdb]
/usr/sbin/mysqld(handle_fatal_signal+0x377)[0x77bb47]
/lib/x86_64-linux-gnu/libpthread.so.0(+0x12980)[0x7f3c68660980]
/lib/x86_64-linux-gnu/libc.so.6(gsignal+0xc7)[0x7f3c6795cfb7]
/lib/x86_64-linux-gnu/libc.so.6(abort+0x141)[0x7f3c6795e921]
/usr/sbin/mysqld[0x751edc]
/usr/sbin/mysqld(_ZN2ib5fatalD1Ev+0x66)[0x1090356]
/usr/sbin/mysqld[0x113c47d]
/usr/sbin/mysqld[0x113cc6d]
/usr/sbin/mysqld(_Z6fil_ioRK9IORequestbRK9page_id_tRK11page_size_tmmPvS8_+0x322)[0x1148de2]
/usr/sbin/mysqld(_Z13buf_read_pageRK9page_id_tRK11page_size_t+0xdd)[0x10fbb4d]
/usr/sbin/mysqld(_Z16buf_page_get_genRK9page_id_tRK11page_size_tmP11buf_block_tmPKcmP5mtr_tb+0x475)[0x10cd7a5]
/usr/sbin/mysqld(_Z31btr_cur_open_at_index_side_funcbP12dict_index_tmP9btr_cur_tmPKcmP5mtr_t+0x333)[0x10a7de3]
/usr/sbin/mysqld(_Z22row_search_max_autoincP12dict_index_tPKcPm+0x1b0)[0xffd780]
/usr/sbin/mysqld(_ZN11ha_innobase27innobase_initialize_autoincEv+0xb6)[0xef89b6]
/usr/sbin/mysqld(_ZN11ha_innobase4openEPKcij+0xca7)[0xeff627]
/usr/sbin/mysqld(_ZN7handler7ha_openEP5TABLEPKcii+0x33)[0x7d1ad3]
/usr/sbin/mysqld(_Z21open_table_from_shareP3THDP11TABLE_SHAREPKcjjjP5TABLEb+0x7f3)[0xd0cc03]
/usr/sbin/mysqld(_Z10open_tableP3THDP10TABLE_LISTP18Open_table_context+0x1176)[0xc16876]
/usr/sbin/mysqld(_Z11open_tablesP3THDPP10TABLE_LISTPjjP19Prelocking_strategy+0x542)[0xc183f2]
/usr/sbin/mysqld(_Z21open_tables_for_queryP3THDP10TABLE_LISTj+0x59)[0xc18e09]
/usr/sbin/mysqld[0xccafa4]
/usr/sbin/mysqld(_Z14get_all_tablesP3THDP10TABLE_LISTP4Item+0x818)[0xccb9b8]
/usr/sbin/mysqld[0xcb641c]
/usr/sbin/mysqld(_Z24get_schema_tables_resultP4JOIN23enum_schema_table_state+0x225)[0xcc7625]
/usr/sbin/mysqld(_ZN4JOIN14prepare_resultEv+0xc5)[0xcac105]
/usr/sbin/mysqld(_ZN4JOIN4execEv+0x99)[0xc390a9]
/usr/sbin/mysqld(_Z12handle_queryP3THDP3LEXP12Query_resultyy+0x23a)[0xcaca6a]
/usr/sbin/mysqld[0x746966]
/usr/sbin/mysqld(_Z21mysql_execute_commandP3THDb+0x48ca)[0xc6d23a]
/usr/sbin/mysqld(_Z11mysql_parseP3THDP12Parser_state+0x3a5)[0xc6f8a5]
/usr/sbin/mysqld(_Z16dispatch_commandP3THDPK8COM_DATA19enum_server_command+0xaf6)[0xc70456]
/usr/sbin/mysqld(_Z10do_commandP3THD+0x220)[0xc71f40]
/usr/sbin/mysqld(handle_connection+0x298)[0xd37c18]
/usr/sbin/mysqld(pfs_spawn_thread+0x154)[0x121e9d4]
/lib/x86_64-linux-gnu/libpthread.so.0(+0x76db)[0x7f3c686556db]
/lib/x86_64-linux-gnu/libc.so.6(clone+0x3f)[0x7f3c67a3f71f]

I’m backing up this way

xtrabackup --compress --backup --stream=xbstream --parallel 10 --encrypt=AES256 --encrypt-threads=4 --encrypt-key="xxx" --databases="db_name" | xbcloud put db_name_full_2021030201 --parallel 10 --swift-container="database_backup_db_name_2021030201"

and restoring this way
(I use Restoring Individual Tables as reference as I assumed restoring a database is just restoring a list of tables)

# sh: xbcloud get --parallel=10 --swift-container=database_backup_db_name_2021030301 db_name_full_2021030301 --parallel=10 | xbstream -xv -C /data/backups/full_db_db_name --parallel=10
# sh: xtrabackup --decompress --decrypt=AES256 --encrypt-key="xxxx" --target-dir=/data/backups/full_db_db_name --remove-original
# sh: xtrabackup --prepare --target-dir=/data/backups/full_db_db_name --use-memory 2G --export
mysql> SELECT CONCAT( 'ALTER TABLE ', a.TABLE_SCHEMA, '.', a.TABLE_NAME, ' DISCARD TABLESPACE;' ) FROM information_schema.tables a WHERE a.TABLE_SCHEMA='db_name';
# sh: cp -a full_db_db_name/db_name/* /var/lib/mysql/db_name/
# sh: chown mysql:mysql -R  /var/lib/mysql/db_name/
mysql> SELECT CONCAT( 'ALTER TABLE ', a.TABLE_SCHEMA, '.', a.TABLE_NAME, ' IMPORT TABLESPACE;' ) FROM information_schema.tables a WHERE a.TABLE_SCHEMA='db_name';

I’m using a different server (and also different version) for backup and restore.

Software stack

  • percona-xtrabackup: 2.4.21
  • mysql source server: 5.6.43
  • mysql destination server: 5.7.33

Is there something I’m doing wrong in the backup or restore process ?

Best

1 Like

I am not sure this will work if source and target are different versions. Can you try using the same version?
Also do ALL tables exist on the target server? if not you need to pre-create them before dropping the table spaces.

1 Like

having the same version for source and target works indeed, so I assume having a different version does not work.

is it possible to create the tables from thefrm files stored in the backup ? is there a tool for that ?

1 Like

You might want to have a look at How to recover table structure from .frm files with MySQL Utilities

2 Likes