Details
-
New Feature
-
Status: In Review (View Workflow)
-
Critical
-
Resolution: Unresolved
-
None
Description
Implementation of foreign keys in partitioned tables with limitations is relatively simple and is covered by this task. Full support of foreign keys in partitioning is covered by MDEV-12483.
This task is basically divided into 3 parts:
1. Remove prohibiting code, this allows FKs to be created for partitions and DML to work;
2. Allow partitioned FKs at SQL layer for such functions as SHOW CREATE;
3. Implement correct handling of FKs when partitioning configuration changes.
Limitations
1. Foreign keys cannot refer partitioned table
Foreign keys cannot refer to 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 at 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 cannot work
In partitioned foreign table CASCADE and SET NULL actions cannot work as these actions update row data which is the subject of row placement into another partition and that cannot be done at InnoDB layer. DELETE CASCADE for SYSTEM_TIME partitioning requires the row to be moved from current to history partition. CASCADE and SET NULL actions disabled completely in CREATE TABLE for partitioned foreign tables in this task.
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.
Implement correct handling of FKs when partitioning configuration changes
ALTER operations such as ADD PARTITION, DROP PARTITION, REMOVE PARTITIONING , etc. must be reflected into correct configuration of foreign keys in InnoDB. Handling of foreign key ID for temporary tables in ALTER may be done similar to MDEV-28933.
Attachments
Issue Links
- is blocked by
-
-
- In Testing
-
-
MDEV-35854 Clean up some DDL code around FOREIGN KEY constraints
-
- Closed
-
- is duplicated by
-
-
- Closed
-
- relates to
-
-
- Stalled
-
-
MDEV-28933 CREATE OR REPLACE fails to recreate same constraint name
-
- Closed
-
Activity
333b49f63c2364e845b034f20a99d98cdf2ef3dc - OK
51f0965358efbd01a359b2248f9352ca98824667 - NO (no description, no reasoning,
irrelevant, harmful IMHO)
3fd81b73a99411198b1abc33f468d6cc11287bb6 - NO even worse than above also no
description, reasoning
1784f005e4a9cf8f929ea75bd88cb42d20e5cbe4 - not checked as it is innodb
fa432696c1e1629c20fd17660850a4b883975e95 - innodb
f2e7955615be7b838eb1a65d20ed7a9b006452ae - innodb
5a0ded1a6173cdcd1057d906e11e6f45069b2d30 - innodb
f78db9b4d7e7e33e7f1ced0c41feb8ba01e06b79
"ERROR HY000: Duplicate ALTER TABLE constraint name '???'" is absolutely
inacceptable error message, if you do not have name explain how or why
it happened (Duplicate constrain name due to clash with internal innodb
nameing or something like this)
37113f9bdee07cbf74649e6687078f968912d4bf - bad commit description,
what is cleaned up, why?
83c92bd6644be4be31c1772f7670c8fe0334bb86 - OK
6d534cca6ca4edbe87b3d38cca6adce111695822 -
- lines are longer than 80 characters
- assume that innodb part checked by the innodb team (including strchr)
otherwise it s ok
51f0965358efbd01a359b2248f9352ca98824667 - NO (no description, no reasoning,
irrelevant, harmful IMHO)
Dropped
3fd81b73a99411198b1abc33f468d6cc11287bb6 - NO even worse than above also no
description, reasoning
Dropped
f78db9b4d7e7e33e7f1ced0c41feb8ba01e06b79
"ERROR HY000: Duplicate ALTER TABLE constraint name '???'" is absolutely
inacceptable error message, if you do not have name explain how or why
it happened (Duplicate constrain name due to clash with internal innodb
nameing or something like this)
Fixed
37113f9bdee07cbf74649e6687078f968912d4bf - bad commit description,
what is cleaned up, why?
Fixed
6d534cca6ca4edbe87b3d38cca6adce111695822 -
lines are longer than 80 characters
Fixed
https://github.com/MariaDB/server/pull/3641
The reviewers may find the task in bb-main-midenok-MDEV-19191
I believe that this is blocked by an upcoming InnoDB change in MDEV-25292.
The common problem with these two works is that the InnoDB SYS_FOREIGN table defines PRIMARY KEY(ID) and until now, the SYS_FOREIGN.ID was constructed as schemaname/constraintname.
Two days ago in a discussion with monty, we decided to change the format to schemaname/tablename\xffconstraintname. I am yet to implement this change.
This change would make all constraint names unique within the referencing table (or in the case of this work, within the referencing partition or subpartition). All disagnostics messages would simply display constraintname, which from that point on could be ‘duplicated’ within a schema.
The reviewers may find the task in bb-11.7-MDEV-19191-fk_partitioning