[MDEV-31008] Instant ALTER allows to violate column check constraint Created: 2023-04-04  Updated: 2023-11-28

Status: Confirmed
Project: MariaDB Server
Component/s: Data Definition - Alter Table, Storage Engine - InnoDB
Affects Version/s: 10.3, 10.4, 10.5, 10.6, 10.7, 10.8, 10.9, 10.10, 10.11, 11.0
Fix Version/s: 10.4, 10.5, 10.6, 10.11, 11.0

Type: Bug Priority: Major
Reporter: Elena Stepanova Assignee: Sergei Golubchik
Resolution: Unresolved Votes: 0
Labels: consistency

Issue Links:
Blocks
blocks MDEV-16356 Allow ALGORITHM=NOCOPY for ADD CONSTR... Open

 Description    

--source include/have_innodb.inc
 
 
 
create table t (a int) engine=InnoDB;
 
insert into t values (0);
 
alter table t add b int default 0 check(b!=a);
 
show create table t;
 
select * from t;
 
 
 
# Cleanup
 
drop table t;

10.4 ff3d4395

 
alter table t add b int default 0 check(b!=a);
 
show create table t;
 
Table	Create Table
 
t	CREATE TABLE `t` (
 
  `a` int(11) DEFAULT NULL,
 
  `b` int(11) DEFAULT 0 CHECK (`b` <> `a`)
 
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
 
select * from t;
 
a	b
 
0	0

An even simpler, but a degenerate test case is

create table t (a int) engine=InnoDB;
 
insert into t values (0);
 
alter table t add b int check(a!=a);

Result is the same.

Not reproducible with algorithm=copy.

 Comments   
Comment by Marko Mäkelä [ 2023-04-05 ]

Part of the problem here is that there no flag exists for adding a CHECK constraint. The only flag that will be set is one for adding the column:

11.1 2b61ff8f2221745f0a96855a0feb0825c426f993

 
0x0000561ffffe56f3 in fill_alter_inplace_info (thd=thd@entry=0x7f5c08002108, 
    table=table@entry=0x7f5c080a2658, varchar=<optimized out>, 
    ha_alter_info=ha_alter_info@entry=0x7f5c2c0a6c20)
 
    at /mariadb/11/sql/sql_table.cc:6832
 
6832	        ha_alter_info->handler_flags|= ALTER_ADD_STORED_BASE_COLUMN;

The same problem occurs also with the following:

create table t (a int) engine=InnoDB;
 
insert into t values (0);
 
alter table t modify a int check(a!=a);

In this case, a different flag (which is ignored by InnoDB) will be set by fill_alter_inplace_info():

6596	  if (alter_info->flags & ALTER_CHANGE_COLUMN)
 
6597	    ha_alter_info->handler_flags|= ALTER_COLUMN_DEFAULT;

I think that by default, adding any CHECK constraints must imply ALGORITHM=COPY. Similar to adding FOREIGN KEY constraints, there could be an override mechanism. Ultimately, in MDEV-16356, we can allow ALGORITHM=NOCOPY for adding such constraints, by implementing a validation pass.

This is something that needs to be fixed outside the storage engine. InnoDB has nothing to do with CHECK constraints. (I assume that it also fails to enforce them related to FOREIGN KEY, similar to triggers: MDEV-12302.)

Generated at Thu Feb 08 10:20:35 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.