Details
-
Bug
-
Status: Open (View Workflow)
-
Blocker
-
Resolution: Unresolved
-
11.5(EOL), 11.6(EOL), 11.7(EOL), 11.8, 12.3, 13.0
-
None
-
Related to install and upgrade
-
Q3/2026 Server Maintenance
Description
This is both as server issue and a documentation issue.
When moving tables with foreign key definitions from 10.6 to 'main' one can get the error
(errno: 150 "Foreign key constraint is incorrectly formed")
In latest 10.6, create a new data directory, start the server and execute:
CREATE TABLE t1(
id SERIAL,
msg VARCHAR(100) CHARACTER SET utf8mb3,
KEY(msg))ENGINE=InnoDB;
insert into t1 values(1,"a"),(2,"b");
CREATE TABLE t2(
id SERIAL,
msg varchar(100) CHARACTER SET utf8mb3,
msg_1 varchar(100) CHARACTER SET utf8mb3,
INDEX (msg_1),
INDEX (msg),
CONSTRAINT fk_t1 FOREIGN KEY (msg) REFERENCES t1 (msg)
ON DELETE CASCADE)ENGINE=InnoDB;
insert into t2 values(1,"a","aa"),(2,"b","bb");
Stop the 10.6 server and start a server from main on the same directory.
run mariadb_updgrade
inserts in t1 and t2 works as expected but:
CREATE TABLE t2(
id SERIAL,
msg varchar(100) CHARACTER SET utf8mb3,
msg_1 varchar(100) CHARACTER SET utf8mb3,
INDEX (msg_1),
INDEX (msg),
CONSTRAINT fk_t1 FOREIGN KEY (msg) REFERENCES t1 (msg)
ON DELETE CASCADE)ENGINE=InnoDB;
Gives the error:
ERROR 1005 (HY000): Can't create table `test`.`t3` (errno: 150 "Foreign key constraint is incorrectly formed")
Same error for:
CREATE TABLE t3(
id SERIAL,
msg varchar(100) CHARACTER SET utf8mb3,
msg_1 varchar(100) CHARACTER SET utf8mb3,
INDEX (msg_1),
INDEX (msg),
CONSTRAINT fk_t1 FOREIGN KEY (msg) REFERENCES t1 (msg)
ON DELETE CASCADE)ENGINE=InnoDB;
Changing the foreign key name to use fk_t2 fixes the issue
If tables are created in main then this problem does not exists
This issue is not documented in the documentation.
Note that when this happens, there is no error in the error log!
At least
https://mariadb.com/docs/server/reference/error-codes/mariadb-error-codes-1800-to-1899/e1834?q=%22Foreign+key+constraint+is+incorrectly+formed
and
https://mariadb.com/docs/server/architecture/server-constraints/foreign-key-constraints
should be updated.
The later entry has a "special cases" section which is not up to date with how newer versions of InnoDB foreign key are stored (there is no ibfk anymore).
It would be even better if mariadb_upgrade would fix the table to use the new format without ibfk. Another change would be to move to the new format when a ddl change is detected
Attachments
Issue Links
- is caused by
-
MDEV-25829 Change default Unicode collation to uca1400_ai_ci
-
- Closed
-
- relates to
-
MDEV-28933 Per-table unique FOREIGN KEY constraint names
-
- Closed
-
-
MDEV-38743 Document the implications of changing the default collation
-
- Confirmed
-