Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.2(EOL)
Description
create or replace table a (
|
cola int(10) primary key,
|
v_cola int(10) as (cola mod 10) virtual,
|
p_cola int(10) as (cola mod 10) persistent
|
);
|
|
|
create index v_cola on a (v_cola);
|
create index p_cola on a (p_cola);
|
|
|
create or replace table b(
|
cola int(10),
|
v_cola int(10),
|
p_cola int(10)
|
);
|
|
|
alter table b add constraint `p_cola_fk`
|
foreign key (p_cola) references a (p_cola)
|
on delete restrict
|
on update restrict;
|
|
|
alter table b add constraint `v_cola_fk`
|
foreign key (v_cola) references a (v_cola)
|
on delete restrict
|
on update restrict;
|
ERROR 1005 (HY000): Can't create table `test`.`#sql-181_8` (errno: 150 "Foreign key constraint is incorrectly formed")
|
MariaDB [test]> show warnings;
|
+---------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
| Level | Code | Message |
|
+---------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
| Warning | 150 | Alter table `test`.`b` with foreign key constraint failed. Parse error in '
|
foreign key (v_cola) references a (v_cola)
|
on delete restrict
|
on update restrict' near 'v_cola)
|
on delete restrict
|
on update restrict'. |
|
| Error | 1005 | Can't create table `test`.`#sql-181_8` (errno: 150 "Foreign key constraint is incorrectly formed") |
|
| Warning | 1215 | Cannot add foreign key constraint |
|
+---------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
3 rows in set (0.00 sec)
|
|
Attachments
Activity
| Field | Original Value | New Value |
|---|---|---|
| Status | Open [ 1 ] | In Progress [ 3 ] |
| Assignee | Jan Lindström [ jplindst ] | Marko Mäkelä [ marko ] |
| Status | In Progress [ 3 ] | In Review [ 10002 ] |
| Description |
{noformat}
create or replace table a ( cola int(10) primary key, v_cola int(10) as (cola mod 10) virtual, p_cola int(10) as (cola mod 10) persistent ); create index v_cola on a (v_cola); create index p_cola on a (p_cola); create or replace table b( cola int(10), v_cola int(10), p_cola int(10) ); alter table b add constraint `p_cola_fk` foreign key (p_cola) references a (p_cola) on delete restrict on update restrict; alter table b add constraint `v_cola_fk` foreign key (v_cola) references a (v_cola) on delete restrict on update restrict; {noformat} |
{noformat}
create or replace table a ( cola int(10) primary key, v_cola int(10) as (cola mod 10) virtual, p_cola int(10) as (cola mod 10) persistent ); create index v_cola on a (v_cola); create index p_cola on a (p_cola); create or replace table b( cola int(10), v_cola int(10), p_cola int(10) ); alter table b add constraint `p_cola_fk` foreign key (p_cola) references a (p_cola) on delete restrict on update restrict; alter table b add constraint `v_cola_fk` foreign key (v_cola) references a (v_cola) on delete restrict on update restrict; {noformat} {code} ERROR 1005 (HY000): Can't create table `test`.`#sql-181_8` (errno: 150 "Foreign key constraint is incorrectly formed") MariaDB [test]> show warnings; +---------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Level | Code | Message | +---------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Warning | 150 | Alter table `test`.`b` with foreign key constraint failed. Parse error in ' foreign key (v_cola) references a (v_cola) on delete restrict on update restrict' near 'v_cola) on delete restrict on update restrict'. | | Error | 1005 | Can't create table `test`.`#sql-181_8` (errno: 150 "Foreign key constraint is incorrectly formed") | | Warning | 1215 | Cannot add foreign key constraint | +---------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 3 rows in set (0.00 sec) {code} |
| Labels | 10.2-rc |
| Fix Version/s | 10.2.4 [ 22116 ] | |
| Fix Version/s | 10.2 [ 14601 ] |
Is it possible to match the columns by position (with a debug assertion that the names match with strcmp), instead of looping over all columns and looking for a matching name using a case-insensitive search?
Also, please import (and if necessary, adapt) all the virtual column tests from MySQL 5.7 that are related to foreign keys, such as innodb.virtual_basic, innodb.virtual_fk, innodb.virtual_fk_restart. We want to make sure that the patch is not breaking other functionality.
| Assignee | Marko Mäkelä [ marko ] | Jan Lindström [ jplindst ] |
| Status | In Review [ 10002 ] | Stalled [ 10000 ] |
I could not find easy way to map column positions to virtual column number. Tests you mention are already part of the gcol suite. Note that MySQL 5.7 does not support the above syntax or foreign keys to virtual columns:
jan@jan-laptop-asus:~/mysql/mysql-server/mysql-test$ perl -I. -U mysql-test-run.pl --vardir=/dev/shm jfk
|
Logging: mysql-test-run.pl --vardir=/dev/shm jfk
|
2017-01-31T09:21:54.420345Z 0 [Warning] Changed limits: max_open_files: 1024 (requested 5000)
|
2017-01-31T09:21:54.420405Z 0 [Warning] Changed limits: table_open_cache: 431 (requested 2000)
|
MySQL Version 5.7.17
|
Checking supported features...
|
- SSL connections supported
|
- binaries are debug compiled
|
Collecting tests...
|
Checking leftover processes...
|
Removing old var directory...
|
Creating var directory '/dev/shm'...
|
Installing system database...
|
Using parallel: 1
|
|
|
==============================================================================
|
|
|
TEST RESULT TIME (ms) or COMMENT
|
--------------------------------------------------------------------------
|
|
|
worker[1] Using MTR_BUILD_THREAD 300, with reserved ports 13000..13009
|
|
|
create table a (
|
cola int(10) primary key,
|
v_cola int(10) as (cola mod 10) virtual,
|
p_cola int(10) as (cola mod 10)
|
) engine=innodb;
|
create index v_cola on a (v_cola);
|
create index p_cola on a (p_cola);
|
create table b(
|
cola int(10),
|
v_cola int(10),
|
p_cola int(10)
|
) engine=innodb;
|
show create table a;
|
Table Create Table
|
a CREATE TABLE `a` (
|
`cola` int(10) NOT NULL,
|
`v_cola` int(10) GENERATED ALWAYS AS ((`cola` % 10)) VIRTUAL,
|
`p_cola` int(10) GENERATED ALWAYS AS ((`cola` % 10)) VIRTUAL,
|
PRIMARY KEY (`cola`),
|
KEY `v_cola` (`v_cola`),
|
KEY `p_cola` (`p_cola`)
|
) ENGINE=InnoDB DEFAULT CHARSET=latin1
|
alter table b add constraint `p_cola_fk`
|
foreign key (p_cola) references a (p_cola)
|
on delete restrict
|
on update restrict;
|
ERROR HY000: Cannot add foreign key constraint
|
show warnings;
|
Level Code Message
|
Error 1215 Cannot add foreign key constraint
|
show create table b;
|
Table Create Table
|
b CREATE TABLE `b` (
|
`cola` int(10) DEFAULT NULL,
|
`v_cola` int(10) DEFAULT NULL,
|
`p_cola` int(10) DEFAULT NULL
|
) ENGINE=InnoDB DEFAULT CHARSET=latin1
|
alter table b add constraint `v_cola_fk`
|
foreign key (v_cola) references a (v_cola)
|
on delete restrict
|
on update restrict;
|
ERROR HY000: Cannot add foreign key constraint
|
show warnings;
|
Level Code Message
|
Error 1215 Cannot add foreign key constraint
|
show create table b;
|
Table Create Table
|
b CREATE TABLE `b` (
|
`cola` int(10) DEFAULT NULL,
|
`v_cola` int(10) DEFAULT NULL,
|
`p_cola` int(10) DEFAULT NULL
|
) ENGINE=InnoDB DEFAULT CHARSET=latin1
|
| Status | Stalled [ 10000 ] | In Progress [ 3 ] |
commit 45f451c769668e6a351b0c023a994fdf9c07b1f7
Author: Jan Lindström <jan.lindstrom@mariadb.com>
Date: Fri Jan 20 12:10:13 2017 +0200
MDEV-11850: Can't create foreign key referencing a virtual column
Both dict_foreign_find_index and dict_foreign_qualify_index
did not consider virtual columns as possible foreign key
columns and there was assertion to disable virtual columns.
Fixed by also looking referencing and referenced column
from virtual columns if needed.
| Resolution | Fixed [ 1 ] | |
| Status | In Progress [ 3 ] | Closed [ 6 ] |
| Workflow | MariaDB v3 [ 79290 ] | MariaDB v4 [ 151566 ] |
http://lists.askmonty.org/pipermail/commits/2017-January/010471.html