2

I want to recover sql tables from ibd file.

I followed these steps but it didn't work. First I discarded tablespace using command Alter table users disard tablespace. Then, i deleted newly generated users.idb file Then, I copied required users.ibd file to that database in mysql/data/onlinelogistics folder then, i tried to import tablespace but it showed this error

#1815 - Internal error: Drop all secondary indexes before importing table onlinelogistics/users when .cfg file is missing.

Error Image

Luuk
  • 12,245
  • 5
  • 22
  • 33
Ajit Pandey
  • 21
  • 1
  • 2
  • Corrected the tags, because this is clearly about `MySQL`, (and not so much about `sql`. – Luuk Dec 24 '21 at 07:04
  • And `ibd` file also contains info about indexes uses in the table(s). You should not mess around with those `ibd` files. (unless you really like this kind of errors) – Luuk Dec 24 '21 at 07:07
  • I do see that in the docs from 5.6 this is actually a way of [Importing a Tablespace](https://dev.mysql.com/doc/refman/5.6/en/innodb-table-import.html#innodb-table-import-example), which describes how to mess around with `ibd` files, but at pint 4 they say: "Copy the .ibd file **and** .cfg metadata". you should NOT copy a `ibd` file if you do not have the `cfg` file which belong together. – Luuk Dec 24 '21 at 07:14
  • You can import Tablespace without cfg. You can read it in the link above by @Luuk You can read that if you want to restore from a crash you don't need cfg. When you create your table you need to leave the secondary keys. Then you can import the table and the tablespace too. If the keys are important you need to solve it after importing. – AttilaBorbás Jan 18 '22 at 22:16

2 Answers2

0

This is caused by an additional check which was included in MariaDB 10.5.13 to fix MDEV-26131: SEGV in ha_innobase::discard_or_import_tablespace

There doesn't seem to be a "fix" or workaround except for dropping all indices before attempting to import that ibd. Alternatively, you could use an MariaDB server that does not include this change

Sjon
  • 4,989
  • 6
  • 28
  • 46
0

For restoring/importing your .ibd file based on .frm file, simply follow exactly the instruction here and then you should REMOVE ALL FORIEGN KEYS on your table structure before running

ALTER TABLE example_table IMPORT TABLESPACE;

command.

ghazyy
  • 649
  • 2
  • 7
  • 22