xtrabackup export - support tables with more than 31 indexes

[Ryan Brothers]

I have a InnoDB table with 50 fields and many of the fields are foreign keys, so because of that, the table has a large number of indexes. I followed the instructions on http://www.percona.com/doc/percona-xtrabackup/xtrabackup_bin/exporting_importing_tables.html to export the table, but when I ran xtrabackup --prepare --export, I noticed that the table was skipped and an .exp file was not created.

Looking in xtrabackup.c, there's a error thrown that says "xtrabackup: error: sorry, cannot export over 31 indexes for now."

Can the export in xtrabackup please be modified to support > 31 indexes in a table?

Thanks,
Ryan

[Alexey Kopytov (akopytov)]

Yes, the limitation of 31 indexes is implied by the current format of .exp files. Currently, an .exp file is always a 16K page divided into 512-byte segments. The first segment contains the number of indexes and the table name. And the remaining 31 segments contain IDs, root page offsets and names for each index in the table.

This should be easy to fix, as I don't see a reason for an .exp file to take just one page.

[djay45 (djay45)]

I have the same problem with Xtrabackup latest version (2.4.3). I have a table with 47 indexes for customer needs.

Have you foresee a fix to increase that limitation?

Thanks
Jeremy

[Sergei Glushchenko (sergei.glushchenko)]

Hi Jeremy,

Since limitation is only for .exp (which is PS 5.5 specific format), we can omit this check when exporting 5.6 or 5.7 backups.

[djay45 (djay45)]

Hi Sergei,

Thank you for your response.

Effectively, it would be nice to remove this check in the next release of XtraBackup.

Currently, I use MySQL5.6 and soon MySQL5.7 and perhaps MariaDB10.

Regards,
Jeremy

[Sergei Glushchenko (sergei.glushchenko)]

As workaround you can import tablespaces without .cfg files. MySQL 5.6 and 5.7 allowing it. xtrabackup --prepare --export is usual prepare plus creation of .cfg files.

[djay45 (djay45)]

Hi Sergei,

OK for .cfg file, it is not necessary.

But, when executing "xtrabackup --prepare --export" command, I have on one table a .frm and .ibd but not an .exp file.

Can I import tablespaces without .exp file?

The procedure for restoring individual tables recommends that .exp file (these files are all you need to import the table into a server running Percona Server with XtraDB or MySQL 5.6).

Thanks,
Jeremy

[Sergei Glushchenko (sergei.glushchenko)]

Hi Jeremy,

You need .exp for Percona Server 5.5. For MySQL 5.6 and Percona Server 5.6 you need .cfg, but you still will be able to import even without .cfg. See http://dev.mysql.com/doc/refman/5.6/en/tablespace-copying.html :

ALTER TABLE ... IMPORT TABLESPACE does not require a .cfg metadata file to import a tablespace. However, metadata checks are not performed when importing without a .cfg file, and a warning similar to the following will be issued:

Message: InnoDB: IO Read error: (2, No such file or directory) Error opening '.\
test\t.cfg', will attempt to import without schema verification
1 row in set (0.00 sec)

[djay45 (djay45)]

Hi Sergei.

It is more clear for me :)

Just one precision after I stop...During preparing backup of one table, I have error messages on innodb stats tables of mysql db.
Do I need to take into account mysql database in my partial backup to delete errors or just ignoring InnoDb error messages?

Thanks a lot,
Jeremy

[Sergei Glushchenko (sergei.glushchenko)]

Hi Jeremy,

You can ignore error messages or you can specify the same filter you used for partial backup to suppress them.

[djay45 (djay45)]

thanks Sergei.

[Vasily Nemkov (vasily.nemkov)]

https://github.com/percona/percona-xtrabackup/pull/340

[Vasily Nemkov (vasily.nemkov)]

https://github.com/percona/percona-xtrabackup/pull/347

[Shahriyar Rzayev (rzayev-sehriyar)]

Percona now uses JIRA for bug reports so this bug report is migrated to: https://jira.percona.com/browse/PXB-58