[MDEV-34392] modification of the column fails to check foreign key constraint - Jira

Details

Type: Bug
Status: Closed (View Workflow)
Priority: Critical
Resolution: Fixed
Affects Version/s: 10.11.7, 11.3.2, 10.11.8, 11.4.2
Fix Version/s: 10.5.27, 10.6.20, 10.11.10, 11.2.6, 11.4.4
Component/s: Data Definition - Alter Table
Labels:
- regression
Environment:
MariaDB-deb12

Description

Testcase with 2 tables

create table tab1 (

    t1id int unsigned not null auto_increment,

    t1val varchar(30) not null,

    constraint pkt1 primary key (t1id)

);

create table tab2 (

    t2id int unsigned not null auto_increment,

    t1id int unsigned,

    t2tval datetime,

    t2nval int,

    constraint pkt2 primary key (t2id)

);

alter table tab2 add index FkIdxt2t1id (t1id), add constraint Fkt2t1id foreign key (t1id) references tab1 (t1id);

Altering columns in one statement failes

alter table tab2 modify t1id int unsigned not null, modify t2tval timestamp(3), modify t2nval bigint;

Cannot change column 't1id': used in a foreign key constraint 'Fkt2t1id'

alter not null in seperate statement works, also 2 modify data types

alter table tab2 modify t1id int unsigned not null;

alter table tab2 modify t2tval timestamp(3), modify t2nval bigint;

Attachments

- Sort By Name
- Sort By Date
- Ascending
- Descending
- Thumbnails
- List
- Download All

test_fail.sql
0.5 kB
2024-06-14 09:58
test_fix.sql
0.5 kB
2024-06-14 09:58

Issue Links

causes

MDEV-35351 Remove const from get_foreign_key_list/get_parent_foreign_key_list by merging 10.11 to 11.2

Closed

MDEV-35445 Foreign key regression from 10.11.9 to 10.11.10 (and 11.4.4)

Closed

is caused by

MDEV-31086 MODIFY COLUMN can break FK constraints, and lead to unrestorable dumps

Closed

Activity

Ascending order - Click to sort in descending order

Marko Mäkelä added a comment - 2024-06-14 15:07

Thank you. I can confirm this, and it looks like this error was introduced by ~~MDEV-31086~~. The function fk_check_column_changes() would return FK_COLUMN_DATA_CHANGE because the NOT NULL attribute is changing. Here is a simpler test:

--source include/have_innodb.inc

create table tab1 (t1id int unsigned primary key) engine=innodb;

create table tab2 (

    t2id int unsigned primary key,

    t1id int unsigned references tab1(t1id),

    t2tval datetime

) engine=innodb;

alter table tab2 modify t1id int unsigned not null, modify t2tval timestamp(3);

show create table tab2;

If I declare tab2.t1id with not null to begin with, or if I omit one of the modify clauses, this will succeed. Else:

10.11 5b89cab44f409d3774b6c8255dfd2c72b2ade1af

mysqltest: At line 10: query 'alter table tab2 modify t1id int unsigned not null, modify t2tval timestamp(3)' failed: ER_FK_COLUMN_CANNOT_CHANGE (1832): Cannot change column 't1id': used in a foreign key constraint 'tab2_ibfk_1'

This looks like an unintended regression due to ~~MDEV-31086~~, and regressions should be fixed at a critical priority.

Marko Mäkelä added a comment - 2024-06-14 15:07 Thank you. I can confirm this, and it looks like this error was introduced by MDEV-31086 . The function fk_check_column_changes() would return FK_COLUMN_DATA_CHANGE because the NOT NULL attribute is changing. Here is a simpler test: --source include/have_innodb.inc create table tab1 (t1id int unsigned primary key ) engine=innodb; create table tab2 ( t2id int unsigned primary key , t1id int unsigned references tab1(t1id), t2tval datetime ) engine=innodb; alter table tab2 modify t1id int unsigned not null , modify t2tval timestamp (3); show create table tab2; If I declare tab2.t1id with not null to begin with, or if I omit one of the modify clauses, this will succeed. Else: 10.11 5b89cab44f409d3774b6c8255dfd2c72b2ade1af mysqltest: At line 10: query 'alter table tab2 modify t1id int unsigned not null, modify t2tval timestamp(3)' failed: ER_FK_COLUMN_CANNOT_CHANGE (1832): Cannot change column 't1id': used in a foreign key constraint 'tab2_ibfk_1' This looks like an unintended regression due to MDEV-31086 , and regressions should be fixed at a critical priority.

Thirunarayanan Balathandayuthapani added a comment - 2024-06-18 11:16

https://github.com/MariaDB/server/pull/3341

Thirunarayanan Balathandayuthapani added a comment - 2024-06-18 11:16 https://github.com/MariaDB/server/pull/3341

Marko Mäkelä added a comment - 2024-06-20 12:55

As far as I can tell, we should allow the addition or removal of NOT NULL in parent or child tables, except in the following cases:

A child column is NOT NULL REFERENCES … ON UPDATE CASCADE and the parent column allows NULL values
A child column is NOT NULL REFERENCES … ON UPDATE SET NULL
A child column is NOT NULL REFERENCES … ON DELETE SET NULL

I think that the last two rules should be enforced even with foreign_key_checks=0. For the first rule to be enforced, the parent table must exist. A later CREATE TABLE of the parent probably shouldn’t flag an error, but we should test what happens on subsequent DML that would attempt to update the child column to NULL.

On an ALTER TABLE of a parent, I think that we need to flag an error for the first case.

Marko Mäkelä added a comment - 2024-06-20 12:55 As far as I can tell, we should allow the addition or removal of NOT NULL in parent or child tables, except in the following cases: A child column is NOT NULL REFERENCES … ON UPDATE CASCADE and the parent column allows NULL values A child column is NOT NULL REFERENCES … ON UPDATE SET NULL A child column is NOT NULL REFERENCES … ON DELETE SET NULL I think that the last two rules should be enforced even with foreign_key_checks=0 . For the first rule to be enforced, the parent table must exist. A later CREATE TABLE of the parent probably shouldn’t flag an error, but we should test what happens on subsequent DML that would attempt to update the child column to NULL . On an ALTER TABLE of a parent, I think that we need to flag an error for the first case.

Debarun Banerjee added a comment - 2024-07-25 07:05

From thiru's explanation the difference in behaviour is actually related to the underlying algorithm (copy/inplace) being used. The modification of the other type forces COPY algorithm and the error is thrown from this path. The inplace algorithm is doing the right thing. Here is a simpler test to demonstrate the difference.

create table t1 (id int primary key) engine=innodb;

create table t2 (id int primary key, id_parent int references t1(id)) engine=innodb;

MariaDB [test]> alter table t2 modify id_parent int not null, algorithm=copy;

ERROR 1832 (HY000): Cannot change column 'id_parent': used in a foreign key constraint 't2_ibfk_1'

MariaDB [test]> alter table t2 modify id_parent int not null, algorithm=inplace;

Query OK, 0 rows affected (0.746 sec)

Thanks thiru for fixing the issue. I am now reviewing the code changes.

Debarun Banerjee added a comment - 2024-07-25 07:05 From thiru 's explanation the difference in behaviour is actually related to the underlying algorithm (copy/inplace) being used. The modification of the other type forces COPY algorithm and the error is thrown from this path. The inplace algorithm is doing the right thing. Here is a simpler test to demonstrate the difference. create table t1 (id int primary key ) engine=innodb; create table t2 (id int primary key , id_parent int references t1(id)) engine=innodb; MariaDB [test]> alter table t2 modify id_parent int not null , algorithm=copy; ERROR 1832 (HY000): Cannot change column 'id_parent': used in a foreign key constraint 't2_ibfk_1' MariaDB [test]> alter table t2 modify id_parent int not null , algorithm=inplace; Query OK, 0 rows affected (0.746 sec) Thanks thiru for fixing the issue. I am now reviewing the code changes.

MariaDB Server

modification of the column fails to check foreign key constraint

Details

Description

Attachments

Attachments

Issue Links

Activity

People

Dates

Git Integration