[MDEV-18114] Foreign Key Constraint actions don't affect Virtual Column - Jira

Details

Type: Bug
Status: Closed (View Workflow)
Priority: Critical
Resolution: Fixed
Affects Version/s: 10.3.11, 10.0(EOL), 10.1(EOL), 10.2(EOL), 10.3(EOL), 10.4(EOL)
Fix Version/s: 10.5.22, 10.6.15, 10.9.8, 10.10.6, 10.11.5, 11.0.3, 11.1.2
Component/s: Storage Engine - InnoDB, Virtual Columns
Labels:
None
Environment:
CentOS 7

Description

One of my tables has several columns with FK ON DELETE SET NULL constraints on them, plus one virtual column with mildly complex expression based on these FK columns.

When a DELETE is issued on the foreign table, the FK fields get set to NULL but the STORED Virtual column is not updated.

There is no trace of this behaviour on the documentation.

Attachments

Issue Links

causes

MDEV-31931 FK fields cannot be used anymore in generated columns

Closed

MDEV-32030 Error in restoring SQL dump with virtual columns after upgrade to version 10.6.15

Closed

includes

MDEV-31322 FKs with 'ON UPDATE CASCADE' can be set in some contexts but not others

Closed

MDEV-32764 Segmentation fault at /mariadb-11.3.0/strings/ctype-utf8.c:831

Closed

is duplicated by

MDEV-31434 stored generated columns are not properly updated after update

Closed

relates to

MDEV-22602 WITHOUT OVERLAPS constraint is ignored when update is performed via foreign key cascade

Closed

MDEV-30674 Implement CHECK constraints validation on cascading updates

Closed

MDEV-31654 Support STORED generated columns with FK cascade changes

Open

MDEV-31112 vcol circular references lead to stack overflow

Closed

(4 relates to)

Activity

Ascending order - Click to sort in descending order

Aurelien Marchand added a comment - 2018-12-31 16:53 - edited

CREATE TABLE `t1` ( `id` int(11) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB;

CREATE TABLE `t2` (

  `id` int(11) NOT NULL,

  `f_id` int(11) DEFAULT NULL,

  `v_id` int(11) GENERATED ALWAYS AS (`f_id` MOD 2) STORED,

  PRIMARY KEY (`id`),

  KEY `f_id` (`f_id`),

  CONSTRAINT `t2_ibfk_2` FOREIGN KEY (`f_id`) REFERENCES `t1` (`id`) ON DELETE SET NULL ON UPDATE CASCADE

) ENGINE=InnoDB;

INSERT INTO t1 VALUES (1),(2),(3),(4);

INSERT INTO t2 VALUES (1,1,NULL), (2,2,NULL),(3,3,NULL),(4,4,NULL);

SELECT * FROM t2;

DELETE FROM t1 WHERE id IN (2,3);

SELECT * FROM t2;

Aurelien Marchand added a comment - 2018-12-31 16:53 - edited CREATE TABLE `t1` ( `id` int (11) NOT NULL , PRIMARY KEY (`id`) ) ENGINE=InnoDB; CREATE TABLE `t2` ( `id` int (11) NOT NULL , `f_id` int (11) DEFAULT NULL , `v_id` int (11) GENERATED ALWAYS AS (`f_id` MOD 2) STORED, PRIMARY KEY (`id`), KEY `f_id` (`f_id`), CONSTRAINT `t2_ibfk_2` FOREIGN KEY (`f_id`) REFERENCES `t1` (`id`) ON DELETE SET NULL ON UPDATE CASCADE ) ENGINE=InnoDB; INSERT INTO t1 VALUES (1),(2),(3),(4); INSERT INTO t2 VALUES (1,1, NULL ), (2,2, NULL ),(3,3, NULL ),(4,4, NULL ); SELECT * FROM t2; DELETE FROM t1 WHERE id IN (2,3); SELECT * FROM t2;

Elena Stepanova added a comment - 2019-01-23 19:30

Thanks for the report, reproducible as described.

MySQL 5.7 rejects the attempt to create a table with a foreign key (with a cascade action) defined on a base column of a virtual column, even though I can't find this particular limitation in MySQL manual. MySQL 8.0 rejects this CREATE TABLE as well, although it's less verbose about the reason.

Elena Stepanova added a comment - 2019-01-23 19:30 Thanks for the report, reproducible as described. MySQL 5.7 rejects the attempt to create a table with a foreign key (with a cascade action) defined on a base column of a virtual column, even though I can't find this particular limitation in MySQL manual. MySQL 8.0 rejects this CREATE TABLE as well, although it's less verbose about the reason.

Sergei Golubchik added a comment - 2023-07-13 12:32 - edited

please, review these five commits:

b4b547bb14c MDEV-18114 Foreign Key Constraint actions don't affect Virtual Column

757a0e4cba1 cleanup: key->key_create_info.check_for_duplicate_indexes -> key->old

e1b389612cd cleanup: Item_field::check_vcol_func_processor()

a4fd56df7a1 cleanup: put db/table_name into Alter_info

1633ac631f3 cleanup: reorder enum_fk_option

as usual "cleanup" means "internal code rearrangement, no user visible effects"

Sergei Golubchik added a comment - 2023-07-13 12:32 - edited please, review these five commits: b4b547bb14c MDEV-18114 Foreign Key Constraint actions don't affect Virtual Column 757a0e4cba1 cleanup: key->key_create_info.check_for_duplicate_indexes -> key->old e1b389612cd cleanup: Item_field::check_vcol_func_processor() a4fd56df7a1 cleanup: put db/table_name into Alter_info 1633ac631f3 cleanup: reorder enum_fk_option as usual "cleanup" means "internal code rearrangement, no user visible effects"

Nikita Malyavin added a comment - 2023-08-02 12:38

The fix looks correct and exhaustive. Ok to push after the minor issues are resolved

Nikita Malyavin added a comment - 2023-08-02 12:38 The fix looks correct and exhaustive. Ok to push after the minor issues are resolved

People

Assignee:: Elena Stepanova

Reporter:: Aurelien Marchand

Votes:: 0 Vote for this issue

Watchers:: 5 Start watching this issue

Dates

Created:: 2018-12-31 16:33

Updated:: 2023-11-10 16:54

Resolved:: 2023-08-02 13:26

Git Integration

Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.

MariaDB Server