MDEV-19191 Partial support of foreign keys in partitioned tables

[midenok]

trafficstars

The patch adds the ability to run foreign keys in partitioned tables with
limitations.

Example:

  create or replace table t1 (id int primary key) engine innodb;
  create or replace table t2 (fk int references t1(id)) engine innodb
  partition by hash (fk) partitions 2;

Limitations:

  1. Foreign keys cannot refer partitioned table even SYSTEM_TIME-partitioned
  still. create_foreign_keys() at InnoDB layer receives Foreign_key about
  referenced table T, but no such table exists in InnoDB layer, only partition
  tables T#P#p0, T#P#p1, ..., T#P#pn. Finding out it is SYSTEM_TIME partitioning
  and current partition at that point is impossible without modification of
  SYS_TABLES or opening TABLE object of referenced table. Both should be avoided
  as this is superseded by MDEV-12483.

  2. CASCADE and SET NULL actions are disabled in partitioned foreign table as
  these actions update row data and this is the subject of row placement into
  another partition but it cannot be done in InnoDB layer. DELETE CASCADE for
  SYSTEM_TIME partitioning requires the row to be moved from current to history
  partition.

The task is basically divided into 3 parts:

  1. Remove prohibiting code, allow FKs to be created for partitions;
  2. Allow partitioned FKs at SQL layer for such functions as SHOW CREATE;
  3. Implement correct handling of FKs when partitioning configuration changes.

1. Remove prohibiting code, allow FKs to be created for partitions

  In SYS_FOREIGN table foreign key records are unique by ID which was taken from
  constraint name or automatically generated. Normally foreign ID and constraint
  name are identical, but that's not the case for partitioned table as InnoDB
  holds foreign keys per dict_table_t object and each partition is a different
  table for InnoDB. So for each foreign key in SQL layer there is a set of foreign
  keys in InnoDB layer per each partition (except SYSTEM_TIME partitioning where
  we keep foreign keys only for current partition). To constitute unique foreign
  ID at InnoDB layer we concatenate constraint name with partition suffix, the one
  what is added to partition table name beginning with #P# and optionally
  containing #SP# for subpartitions. Constraint name and partitioning suffix are
  separated by \xFF character which is the safe character code non-clashing with
  identifier character set.

  When we return back foreign ID to SQL layer this partitioning suffix is stripped
  off the constraint name and SQL output receives the name similar to
  non-partitioned table.

  User may see a bit more truthful version of foreign ID in
  INFORMATION_SCHEMA.INNODB_SYS_FOREIGN with \xFF replaced by ':' and #P# or
  everything starting from #P# and ending by #SP# chopped out. So he may see
  corresponding partition name or subpartition name in ID of INNODB_SYS_FOREIGN.

2. Allow partitioned FKs at SQL layer for such functions as SHOW CREATE

  Through standard handler interface get_foreign_key_list() foreign keys are
  returned to SQL layer. For SYSTEM_TIME partitioning from current partition, for
  any other partitioning from first read-marked partition.

3. Implement correct handling of FKs when partitioning configuration changes

  ALTER operations such as ADD PARTITION, DROP PARTITION, REMOVE PARTITIONING,
  etc. are reflected into correct configuration of foreign keys in InnoDB.
  Handling of foreign key ID for temporary tables in ALTER was done based on
  MDEV-28933.