Details
-
Task
-
Status: Stalled (View Workflow)
-
Critical
-
Resolution: Unresolved
-
None
-
None
Description
MDEV-34309 got stalled in 11.8 partially because there was no clear understanding when exactly foreign keys should be allowed and when not, the documentation is very vague in this regard, and implementation is even more so.
The KB says
The foreign key columns and the referenced columns must be of the same type, or similar types
which does not help, as it doesn't say what "similar types" means.
The MySQL manual is similarly obscure.
The implementation sometimes demonstrates an odd approach to this, when for example TIME and INET6 data types are considered similar enough (MDEV-35908). Another, less exotic, uncertainty is about varchar(M) <=> char(N) and so on.
There is also a question of other field/table options, e.g. character sets etc.
All in all, for MDEV-34309 functional testing, we need to know when CHECK TABLE .. EXTENDED should alert about a problem and when it should return success. It was decided that it doesn't make sense to follow the current implementation blindly, but instead we should try to make CHECK TABLE ... EXTENDED return really correct diagnostics regarding foreign keys, and fix (possibly in earlier versions) legacy inconsistencies.
Attachments
Issue Links
- blocks
-
MDEV-34309 CHECK TABLE should check that foreign key relations are correct
-
- In Testing
-
Activity
I'm closing it as "won't do" as i've run out of arguments, the specification for MDEV-34309 testing will be as above,
"correct" is whatever InnoDB accepts as correct
and PASS/FAIL criteria will be chosen accordingly (interpolating it to "whatever InnoDB doesn't accept as correct is incorrect").
As discussed elsewhere, the meaning of "InnoDB acceptance" is that the table can be re-created with the given structure.
mariadb-dump is irrelevant and cannot be used for verification, as it runs with FOREIGN_KEY_CHECKS=0 because it otherwise it might not be able to create even valid structures.
Amendment
Before MDEV-34309, foreign keys were strictly internal InnoDB business and "correct" was whatever InnoDB was fine with.
But MDEV-34309 introduced FK-checks into the server layer. Now the server must ensure FK definition correctness, because the server might need to run FK-checks later.
I believe there are a few things required to be checked at CREATE TABLE, related to the parent (i.e. referenced) table, ensuring the relation semantic:
- type compatibility (no inet4->time pointers, even if sizes match)
create table t1 (a time, key(a)) engine=innodb;create table t2 (b inet6 unique references t1(a)) engine=innodb; - privilege check on the referenced column
- other restrictions, like "don't point at system invisible column"
create table t1(x text unique) engine=innodb;
insert t1(x) values(1);
--error ER_BAD_FIELD_ERRORselect DB_ROW_HASH_1 from t1;
create table t2(x bigint unsigned, key(x), foreign key(x) references t1(DB_ROW_HASH_1) on update cascade on delete cascade) engine=innodb;
show create table t2;
- semantic table matching (don't point at sequences, or [upcoming] global temporary tables)
This will require opening a parent table in sql layer during CREATE TABLE, which hasn't been done ever before.
I'd say, the definition is — one can do mariadb-dump of everything and then load it back.
That is, "correct" is whatever InnoDB accepts as correct. And "wrong" is whatever InnoDB doesn't accept.