# Restoring database leads to a couple of corrupted ibd

**URL:** <https://forums.percona.com/t/restoring-database-leads-to-a-couple-of-corrupted-ibd/9519>\
**Category:** Percona XtraBackup\
**Created:** [March 3, 2021, 9:41am UTC](https://forums.percona.com/t/restoring-database-leads-to-a-couple-of-corrupted-ibd/9519 "2021-03-03T09:41:40Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![Baptiste\_Mille-Mathi](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/baptiste_mille-mathi/32/9237_2.png) [@Baptiste\_Mille-Mathi](https://forums.percona.com/u/Baptiste_Mille-Mathi)\
**Post date:** [March 3, 2021, 9:41am UTC](https://forums.percona.com/t/restoring-database-leads-to-a-couple-of-corrupted-ibd/9519/1 "2021-03-03T09:41:40Z")

</div>

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 - Percona XtraBackup](https://www.percona.com/doc/percona-xtrabackup/2.4/xtrabackup_bin/restoring_individual_tables.html) 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

---

<div class="post-metadata">

**Author:** ![Ivan\_Groenewold](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/ivan_groenewold/32/6299_2.png) [@Ivan\_Groenewold](https://forums.percona.com/u/Ivan_Groenewold)\
**Post date:** [March 10, 2021, 11:31am UTC](https://forums.percona.com/t/restoring-database-leads-to-a-couple-of-corrupted-ibd/9519/2 "2021-03-10T11:31:28Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![Baptiste\_Mille-Mathi](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/baptiste_mille-mathi/32/9237_2.png) [@Baptiste\_Mille-Mathi](https://forums.percona.com/u/Baptiste_Mille-Mathi)\
**Post date:** [March 11, 2021, 7:10am UTC](https://forums.percona.com/t/restoring-database-leads-to-a-couple-of-corrupted-ibd/9519/3 "2021-03-11T07:10:16Z")

</div>

> [@Ivan\_Groenewold](#):
>
> am not sure this will work if source and target are different versions. Can you try using the same version?

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

> [@Ivan\_Groenewold](#):
>
> Also do ALL tables exist on the target server? if not you need to pre-create them before dropping the table spaces.

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

---

<div class="post-metadata">

**Author:** ![Ivan\_Groenewold](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/ivan_groenewold/32/6299_2.png) [@Ivan\_Groenewold](https://forums.percona.com/u/Ivan_Groenewold)\
**Post date:** [March 11, 2021, 10:59am UTC](https://forums.percona.com/t/restoring-database-leads-to-a-couple-of-corrupted-ibd/9519/4 "2021-03-11T10:59:43Z")

</div>

You might want to have a look at [How to recover table structure from .frm files with MySQL Utilities](https://www.percona.com/blog/2014/01/02/recover-table-structure-frm-files-mysql-utilities/)