Details
-
Bug
-
Status: Closed (View Workflow)
-
Critical
-
Resolution: Fixed
-
10.6.15, 10.6.17, 10.4(EOL), 10.5
-
None
Description
Consider this test case:
MariaDB [test]> create table A
|
-> (
|
-> IDA int(19) primary key,
|
-> someTextA varchar(1000)
|
-> ) engine = innodb;
|
Query OK, 0 rows affected (0.095 sec)
|
|
|
MariaDB [test]> create table B ( IDB int primary key, someTextB varchar(1000), someInt1B int, someInt2B int, fk_IDA int(19)
|
) engine = innodb default charset utf8mb4;
|
Query OK, 0 rows affected (0.085 sec)
|
|
|
MariaDB [test]> alter table B add constraint unique_B unique key (fk_IDA, someTextB);
|
Query OK, 0 rows affected (0.196 sec)
|
Records: 0 Duplicates: 0 Warnings: 0
|
|
|
MariaDB [test]> show create table B\G
|
*************************** 1. row ***************************
|
Table: B
|
Create Table: CREATE TABLE `B` (
|
`IDB` int(11) NOT NULL,
|
`someTextB` varchar(1000) DEFAULT NULL,
|
`someInt1B` int(11) DEFAULT NULL,
|
`someInt2B` int(11) DEFAULT NULL,
|
`fk_IDA` int(19) DEFAULT NULL,
|
PRIMARY KEY (`IDB`),
|
UNIQUE KEY `unique_B` (`fk_IDA`,`someTextB`) USING HASH
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
|
1 row in set (0.003 sec)
|
|
|
MariaDB [test]> alter table B add constraint B_fk_ida foreign key (fk_IDA) references A (IDA);
|
ERROR 1005 (HY000): Can't create table `test`.`b` (errno: 150 "Foreign key constraint is incorrectly formed")
|
If we check INNODB STATUS we'll see the "reason":
MariaDB [test]> show engine innodb status\G
|
*************************** 1. row ***************************
|
Type: InnoDB
|
Name:
|
Status:
|
=====================================
|
2024-03-12 15:12:21 0x700001255000 INNODB MONITOR OUTPUT
|
=====================================
|
Per second averages calculated from the last 34 seconds
|
-----------------
|
BACKGROUND THREAD
|
-----------------
|
srv_master_thread loops: 0 srv_active, 0 srv_shutdown, 388039 srv_idle
|
srv_master_thread log flush and writes: 388039
|
----------
|
SEMAPHORES
|
----------
|
------------------------
|
LATEST FOREIGN KEY ERROR
|
------------------------
|
2024-03-12 15:12:11 0x700001255000 Error in foreign key constraint of table `test`.`b`:
|
Alter table `test`.`b` with foreign key `B_fk_ida` constraint failed. There is no index in the referenced table where the referenced columns appear as the first columns.------------
|
TRANSACTIONS
|
------------
|
Trx id counter 906470
|
Purge done for trx's n:o < 906468 undo n:o < 0 state: running but idle
|
History list length 0
|
...
|
and it's weird as the referenced table obviously has referenced column as a primary key:
MariaDB [test]> show create table A\G
|
*************************** 1. row ***************************
|
Table: A
|
Create Table: CREATE TABLE `A` (
|
`IDA` int(19) NOT NULL,
|
`someTextA` varchar(1000) DEFAULT NULL,
|
PRIMARY KEY (`IDA`)
|
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
|
1 row in set (0.000 sec)
|
Moreover, if we drop UNIQUE key we can add the foreign key constraint:
MariaDB [test]> alter table B drop key `unique_B`;
|
Query OK, 0 rows affected (0.182 sec)
|
Records: 0 Duplicates: 0 Warnings: 0
|
|
|
MariaDB [test]> alter table B add constraint B_fk_ida foreign key (fk_IDA) references A (IDA);
|
Query OK, 0 rows affected (0.203 sec)
|
Records: 0 Duplicates: 0 Warnings: 0
|
|
|
MariaDB [test]> show create table B\G
|
*************************** 1. row ***************************
|
Table: B
|
Create Table: CREATE TABLE `B` (
|
`IDB` int(11) NOT NULL,
|
`someTextB` varchar(1000) DEFAULT NULL,
|
`someInt1B` int(11) DEFAULT NULL,
|
`someInt2B` int(11) DEFAULT NULL,
|
`fk_IDA` int(19) DEFAULT NULL,
|
PRIMARY KEY (`IDB`),
|
KEY `B_fk_ida` (`fk_IDA`),
|
CONSTRAINT `B_fk_ida` FOREIGN KEY (`fk_IDA`) REFERENCES `A` (`IDA`)
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
|
1 row in set (0.003 sec)
|
The real reason has something to do with the unique key with long enough varchar(N) column, either due to large N or character set. This works, for example:
MariaDB [test]> drop table B;
|
Query OK, 0 rows affected (0.102 sec)
|
|
|
MariaDB [test]> create table B ( IDB int primary key, someTextB varchar(1000), someInt1B int, someInt2B int, fk_IDA int(19)
|
) engine = innodb default charset utf8;
|
Query OK, 0 rows affected (0.073 sec)
|
|
|
MariaDB [test]> alter table B add constraint unique_B unique key (fk_IDA, someTextB);
|
Query OK, 0 rows affected (0.177 sec)
|
Records: 0 Duplicates: 0 Warnings: 0
|
|
|
MariaDB [test]> alter table B add constraint B_fk_ida foreign key (fk_IDA) references A (IDA);
|
Query OK, 0 rows affected (0.180 sec)
|
Records: 0 Duplicates: 0 Warnings: 0
|
|
|
MariaDB [test]> show create table B\G
|
*************************** 1. row ***************************
|
Table: B
|
Create Table: CREATE TABLE `B` (
|
`IDB` int(11) NOT NULL,
|
`someTextB` varchar(1000) DEFAULT NULL,
|
`someInt1B` int(11) DEFAULT NULL,
|
`someInt2B` int(11) DEFAULT NULL,
|
`fk_IDA` int(19) DEFAULT NULL,
|
PRIMARY KEY (`IDB`),
|
UNIQUE KEY `unique_B` (`fk_IDA`,`someTextB`),
|
CONSTRAINT `B_fk_ida` FOREIGN KEY (`fk_IDA`) REFERENCES `A` (`IDA`)
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci
|
1 row in set (0.003 sec)
|
Something clearly wrong goes on here, with checking the existing UNIQUE key as the one to support FOREIGN KEY constraint.
In any case, what prevents the creation of proper single column index for the foreign key if UNIQUE is not suitable for the purpose, or presenting proper reason of the error at least?
Attachments
Issue Links
- causes
-
-
- Open
-
-
-
- Confirmed
-
- is caused by
-
MDEV-371 Unique indexes for blobs
-
- Closed
-
- is duplicated by
-
-
- Closed
-
-
-
- Stalled
-
-
-
- Closed
-
Activity
I agree. Generally, if a user adds an FK and the usable key doesn't exist, the server creates it automatically.
It is not happening here — the user adds an FK, the usable key doesn't exist, but the server doesn't create it. Because it's confused by the HASH key. Looks like a bug to me.
Thanks for explaining valerii and serg. Yes, now I can see why it's a bug. I also checked the behavior with other unsuitable indexes, like unique key (idb, fk_IDA) - innodb creates an extra KEY in this case as well, so it's just really confused by the vcol index (not even by the USING HASH part – which passes as a FK index, when created manually)
I understand the limitations for normal BTREE indexes in InnoDB. Surely I can create a different index that may be used for the foreign key checks (on just `fk_IDA` column or on it followed by prefix of that ;long column). My questions remain:
1. Why server itself does NOT create index on just `fk_IDA` if UNIQUE ... USING HASH one can not be used for the purpose, as it normally does without any indexes on the column?
2. How this error message:
"------------------------
LATEST FOREIGN KEY ERROR
------------------------
2024-03-12 15:12:11 0x700001255000 Error in foreign key constraint of table `test`.`b`:
Alter table `test`.`b` with foreign key `B_fk_ida` constraint failed. There is no index in the referenced table where the referenced columns appear as the first columns.
helps to understand what the problem really is?