~~REDIRECT>percona-server:features:innodb_expand_import~~

====== innodb_expand_import ======

===== Description =====
This patch enables to import arbitrary **.ibd** file exported by XtraBackup '[[percona-xtrabackup:xtrabackup_manual#--export]]' option. 
**innodb_expand_import** variable makes to convert **.ibd** file during import process.

The normal version can import only the backuped **.ibd** file at the same place.

===== Variables Provided =====

==== innodb_expand_import ====
 (default //**0**//) - if set //**1**//, **.ibd** file is converted (space id, index id, etc.) with index information in **.exp** file during the import process (ALTER TABLE ... IMPORT TABLESPACE command).

===== Example =====
innodb_expand_import should be '1'.

the files (**.ibd** and **.exp**) are prepared by "xtrabackup --prepare [[percona-xtrabackup:xtrabackup_manual#--export]]" command.

  * create **"exactly same"** structured tables to the target database.

  * discard the tables as preparation of import
<code>
(command example)
mysql> set FOREIGN_KEY_CHECKS=0;
Query OK, 0 rows affected (0.00 sec)

mysql> alter table customer discard tablespace;
Query OK, 0 rows affected (0.01 sec)

mysql> alter table district discard tablespace;
Query OK, 0 rows affected (0.01 sec)

mysql> alter table history discard tablespace;
Query OK, 0 rows affected (0.00 sec)

...
</code>

  * put the **.ibd** and **.exp** files at the same place to **.frm** file.

  * import the tables
<code>
(command example)
mysql> set FOREIGN_KEY_CHECKS=0;
Query OK, 0 rows affected (0.00 sec)

mysql> alter table customer import tablespace;
Query OK, 0 rows affected (0.17 sec)

mysql> alter table district import tablespace;
Query OK, 0 rows affected (0.00 sec)

mysql> alter table history import tablespace;
Query OK, 0 rows affected (0.04 sec)

...
</code>
<code>
(.err file example)
InnoDB: import: extended import of tpcc2/customer is started.
InnoDB: import: 2 indexes are detected.
InnoDB: Progress in %: 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 done.
InnoDB: import: extended import of tpcc2/district is started.
InnoDB: import: 1 indexes are detected.
InnoDB: Progress in %: 16 33 50 66 83 100 done.
InnoDB: import: extended import of tpcc2/history is started.
InnoDB: import: 3 indexes are detected.
InnoDB: Progress in %: 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 done.
...
</code>

===== Patch information =====
|  Author/Origin| Percona |
|     Bugs fixed| |
|   Dependencies| |
| Introduced in | |

===== TODO =====
Make XtraDB to be enable to export **.exp** file by itself.

== Suggestion 2 (expand "alter table ... discard tablespace") ==

New variable "innodb_export_at_discard = [0|1]". When **1**, XtraDB close the tablespace cleanly (no data in insertbuffer or to purge) and output **.exp** file at the same place to the **.ibd** file instead of deleting **.ibd** file only (default behavior), when "ALTER TABLE ... DISCARD TABLESPACE".

I think The default value should be **1** for safety, because **0** deletes the table data...
LOCK TABLE also may be needed before the operation (error when doesn't have LOCK?).

(example: move database named 'example')

Source: (innodb_export_at_discard should be **1**)
  lock all tables in the database 'example'
  "ALTER TABLE ... DISCARD TABLESPACE" for all tables in 'exmple'
  unlock all tables in the database 'example'
   (and we need to get all create table clause (e.g. "mysqldump --no-data"))

  obtain *.ibd *.exp as exported files


Target: (innodb_expand_import should be **1**)
  create all tables in 'example'
  "ALTER TABLE ... DISCARD TABLESPACE" for all tables in 'exmple'
  overwrite *.ibd and put *.exp from the Target
  "ALTER TABLE ... IMPORT TABLESPACE" for all tables in 'exmple'


**//I think making the shell to do the above operations automatically is
much easier than implement the new SQLs to do them...//**



== Suggestion 1 (at shutdown [too simple... **rejected**...]) ==

New variable "innodb_export_exp_at_shutdown = [0|1]". When **1**, XtraDB outputs **.exp** files for all InnoDB tables at clean shutdown. (works file_per_table mode inly)

XtraDB must treat also **.exp** files along with **.ibd** files. (e.g. delete files when delete table)