sieve + defer-foreign-keys is broken

[abg]

Right now sieve --defer-foreign-keys takes a CREATE TABLE statement and extracts out the foreign key definitions. These are then emitted after the table data is loaded along with another other secondary indexes.

This fails in the case that some previous table in the dump has a foreign key references to a later table and that table does not have sufficient indexes defined. This can be shown with a simple test case:

-- Note: Without foreign_key_checks = 0, the initial create table will fail if `b` does not yet exist
--       With foreign_key_checks = 0, creating `a` will still fail if `b` exists with an incompatible schema
SET FOREIGN_KEY_CHECKS = 0;
CREATE TABLE  a (
    id int primary key,
        f int unsigned not null,
    FOREIGN KEY (`f`) REFERENCES b (`f_id`)
) ENGINE=InnoDB;

CREATE TABLE b (
    id integer primary key,
    f_id integer unsigned
) ENGINE=InnoDB;

This results in the error:

ERROR 1005 (HY000) at line 8: Can't create table `test`.`b` (errno: 150 "Foreign key constraint is incorrectly formed")

SHOW ENGINE INNODB STATUS shows why this is:

------------------------
LATEST FOREIGN KEY ERROR
------------------------
2015-09-16 16:30:45 7fa3ebf7c700 Error in foreign key constraint of table test/a:
there is no index in referenced table which would contain
the columns as the first columns, or the data types in the
referenced table do not match the ones in table. Constraint:
,
  CONSTRAINT "a_ibfk_1" FOREIGN KEY ("f") REFERENCES "b" ("f_id")
The index in the foreign key in table is "f"
See http://dev.mysql.com/doc/refman/5.6/en/innodb-foreign-key-constraints.html
for correct foreign key definition.

So there's no index on table b, but there is an existing table referencing it. InnoDB performs this check even with FOREIGN_KEY_CHECKS = 0.

For sieve to support this case we would need to defer adding foreign keys until the very end of the dump. Right now these are cached in memory on a per table basis, but caching foreign keys for an entire dump may require too much memory. These would need to be buffered in a temporary file, I think.

Indexes can still be added after data is loaded. The foreign key definitions can be tracked separately and just appended at the end - similar to how views are handled by mysqldump.