Details
-
Bug
-
Status: Stalled (View Workflow)
-
Major
-
Resolution: Unresolved
-
11.4.2, 10.5, 10.6, 10.11, 11.4
-
docker run --detach --publish 3306:3306 --name mariadb --env MARIADB_ROOT_PASSWORD='...' mariadb
# on M3Max Macbook Pro
Description
The following code works on RDS MariaDB version 10.11.7 and fails on MariaDB version 11.4.2-MariaDB-ubu2404 with
[HY000][1005] (conn=6) Can't create table `kasaExample`.`projection` (errno: 150 "Foreign key constraint is incorrectly formed")
|
I can create the table on 11.4.2 if I remove either:
constraint fk_projection_project foreign key ( projectId ) references project( id )
|
or
unique un_Projection_oneActivePerProject( oneActive ) ignored
|
I see no issue with either of the two definitions or their combination.
The following is a stand-alone code.
drop schema if exists kasaExample;
|
create schema if not exists kasaExample;
|
use kasaExample;
|
|
|
create table project(
|
id serial primary key,
|
name text not null unique ,
|
note text
|
) with system versioning;
|
|
|
create table projection(
|
id serial primary key,
|
projectId bigint unsigned not null , constraint fk_projection_project foreign key ( projectId ) references project( id ),
|
isActive boolean not null default false comment 'Is this the actively used projection for real world payments and expectations or just a regular test projection?',
|
name varchar( 768 ) not null ,
|
note text ,
|
oneActive bigint unsigned as ( if( isActive, projectId, null ) ) invisible,
|
unique un_Projection_oneActivePerProject( oneActive ) ignored,
|
unique un_projectionName_per_project( projectId, name )
|
) with system versioning;
|
Attachments
Issue Links
- duplicates
-
-
- Closed
-
- is caused by
-
MDEV-371 Unique indexes for blobs
-
- Closed
-
- relates to
-
-
- Closed
-
Activity
serg, what exactly did you run to get that output? I tried the following on MariaDB Server 10.5 (the earliest supported major version):
--source include/have_innodb.inc
|
--source include/default_charset.inc
|
|
|
create table t1(a serial) engine=innodb; |
--error ER_NO_AUTOINCREMENT_WITH_UNIQUE
|
create table t2(a serial references t1(a), |
c varchar(768) not null, unique(a,c)) engine=innoDB |
default charset=utf8mb4; |
|
|
create table t2(a bigint unsigned primary key, c varchar(768) not null, |
unique(a,c))engine=innodb default charset=utf8mb4; |
|
|
drop table t2; |
|
|
create table t2(a bigint unsigned primary key, b int, |
ab bigint unsigned as (if(b, a, null)) unique, |
c varchar(768) not null, unique(a,c)) |
engine=innodb default charset=utf8mb4; |
|
|
drop table t2,t1; |
I also tried to add with system versioning, but it did not make a difference. With my test, I got a wrong error message:
|
10.5 ce9efb4e0223c978dcf4511ed33f5118e3b3c3ce |
create table t1(a serial) engine=innodb;
|
create table t2(a serial references t1(a),
|
c varchar(768) not null, unique(a,c)) engine=innoDB
|
default charset=utf8mb4;
|
ERROR HY000: AUTO_INCREMENT column `c` cannot be used in the UNIQUE index `a_2`
|
create table t2(a bigint unsigned primary key, c varchar(768) not null,
|
unique(a,c))engine=innodb default charset=utf8mb4;
|
A VARCHAR column cannot possibly be AUTO_INCREMENT. That column name should be a, because SERIAL is an alias for something like BIGINT UNSIGNED AUTO_INCREMENT.
I suspect that this problem is somehow connected with MDEV-371 and its incompatibility with FOREIGN KEY constraints. Would the scenario that serg reproduced be a duplicate of MDEV-33658?
--source include/have_innodb.inc
|
|
|
set @@character_set_server = utf8mb4; |
|
|
create schema if not exists db1; |
use db1; |
|
CREATE TABLE t1( id serial PRIMARY KEY, name text NOT NULL UNIQUE , note text) engine=innodb; |
|
|
CREATE TABLE t2( |
id serial primary key, |
t1Id bigint unsigned not null , constraint fk_t2_t1 foreign key ( t1Id ) references t1( id ), |
isActive boolean not null default false, |
name varchar( 768 ) not null , |
note text ,
|
oneActive bigint unsigned as ( if( isActive, t1Id, null ) ) invisible, |
unique ( oneActive ) ignored, |
unique ( t1Id, name ) |
) engine=innodb;
|
|
|
drop database db1; |
alice, thank you. If I remove the keyword ignored that seems to have been introduced in MDEV-25075 (10.6.0), the test reproduces the problem on 10.5 already. I reduced the test further to the following:
--source include/have_innodb.inc
|
CREATE TABLE t1(id INT PRIMARY KEY) Engine=InnoDB; |
--error ER_CANT_CREATE_TABLE
|
CREATE TABLE t2( |
t1id INT REFERENCES t1(id), name VARCHAR(768), UNIQUE (t1id,name) |
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8mb4; |
SHOW WARNINGS;
|
DROP TABLE t1; |
|
10.5 238798d978dccaa7c4d47fbb34f36d01d8cb10f0 |
ERROR HY000: Can't create table `db`.`t2` (errno: 150 "Foreign key constraint is incorrectly formed")
|
SHOW WARNINGS;
|
Level Code Message
|
Warning 150 Create table `db`.`t2` with foreign key (t1id) constraint failed. There is no index in the referenced table where the referenced columns appear as the first columns.
|
Error 1005 Can't create table `db`.`t2` (errno: 150 "Foreign key constraint is incorrectly formed")
|
Warning 1215 Cannot add foreign key constraint for `t2`
|
This looks pretty much like MDEV-33658. I also checked where exactly that warning is being reported:
|
10.5 238798d978dccaa7c4d47fbb34f36d01d8cb10f0 |
#0 push_warning_printf (thd=thd@entry=0x7f1e38000f98, level=level@entry=Sql_state_errno_level::WARN_LEVEL_WARN, code=150,
|
format=format@entry=0x7f1e3821bfb8 "Create table `test`.`t2` with foreign key (t1id) constraint failed. There is no index in the referenced table where the referenced columns appear as the first columns.")
|
at /mariadb/10.5/sql/sql_error.cc:767
|
#1 0x0000562037f7b090 in ib_foreign_warn (trx=<optimized out>, error=DB_CANNOT_ADD_CONSTRAINT, table_name=0x7f1e4fefbb50 "`test`.`t2`", format=<optimized out>)
|
at /mariadb/10.5/storage/innobase/handler/ha_innodb.cc:21509
|
#2 0x0000562037f7b688 in foreign_push_index_error (trx=<optimized out>, operation=0x5620362b47a4 "Create ", create_name=0x7f1e4fefbb50 "`test`.`t2`", fk_text=0x7f1e4fefb9c0 "(t1id)", columns=<optimized out>,
|
index_error=index_error@entry=FK_INDEX_NOT_FOUND, err_col=<optimized out>, err_index=<optimized out>, table=<optimized out>) at /mariadb/10.5/storage/innobase/handler/ha_innodb.cc:12014
|
#3 0x0000562037f7a47c in create_table_info_t::create_foreign_keys (this=<optimized out>, this@entry=0x7f1e4fefde70) at /mariadb/10.5/storage/innobase/handler/ha_innodb.cc:12287
|
#4 0x0000562037f7bef5 in create_table_info_t::create_table (this=this@entry=0x7f1e4fefde70, create_fk=true) at /mariadb/10.5/storage/innobase/handler/ha_innodb.cc:12689
|
#5 0x0000562037fadc23 in ha_innobase::create (this=<optimized out>, name=0x7f1e4feff7e0 "./test/t2", form=0x7f1e4fefe420, create_info=0x7f1e4feffb20, file_per_table=true, trx=0x7f1e63002350)
|
at /mariadb/10.5/storage/innobase/handler/ha_innodb.cc:13137
|
I checked further, and dict_foreign_find_index() fails to find a suitable index candidate in t2. The candidates would be GEN_CLUST_INDEX(DB_ROW_ID) and t1id(DB_ROW_HASH_1,DB_ROW_ID). The DB_ROW_HASH_1 is something that is implicitly created by the MDEV-371 logic for the too-long column name, and the DB_ROW_ID is a generated PRIMARY KEY because I did not specify one for t2. This bug is thus confirmed as a duplicate of MDEV-33658.
Dimitar, you can run SHOW ENGINE INNODB STATUS to get a better error description:
2024-06-05 19:24:49 0x7f77304846c0 Error in foreign key constraint of table `test`.`t2`:Create table `test`.`t2` with foreign key `fk1` constraint failed. Field type or character set for column 'b' does not match referenced column 'a'.------------(I used table names t1 and t2 in my test above).
marko, perhaps InnoDB could do my_error() with that message? SHOW ENGINE INNODB STATUS is such an obscure trick.