[MDEV-31624] Online ALTER fails due to intermediate DML which is later overridden Created: 2023-07-04  Updated: 2023-07-05

Status: Open
Project: MariaDB Server
Component/s: Data Definition - Alter Table
Affects Version/s: N/A
Fix Version/s: 11.2

Type: Bug Priority: Major
Reporter: Elena Stepanova Assignee: Nikita Malyavin
Resolution: Unresolved Votes: 0
Labels: None

Issue Links:
Problem/Incident
is caused by MDEV-16329 Engine-independent online ALTER TABLE Closed
Relates
relates to MDEV-28808 Test MDEV-16329 (ALTER ONLINE TABLE) ... Stalled

 Description    

--source include/have_debug_sync.inc
 
 
 
create table t (a int);
 
insert into t values (1);
 
set debug_sync= 'alter_table_online_progress WAIT_FOR go';
 
--send
 
  alter table t modify a tinyint, algorithm=copy, lock=none;
 
 
 
--connect (con_dml,localhost,root,,test)
 
 
 
insert into t values (1000);
 
update t set a = 10 where a > 100;
 
select * from t;
 
set debug_sync= 'now signal go';
 
 
 
--connection default
 
--reap
 
show create table t;
 
select * from t;
 
 
 
# Cleanup
 
drop table t;
 
set debug_sync= reset;
 
--disconnect con_dml

bb-11.2-oalter adcf5dfa8d646b9fbd2bb1a7a4f5a3bb8a7e659c

 
select * from t;
 
a
 
1
 
10
 
set debug_sync= 'now signal go';
 
connection default;
 
bug.t                                    [ fail ]
 
        Test ended at 2023-07-04 22:24:16
 
 
 
CURRENT_TEST: bug.t
 
mysqltest: At line 17: query 'reap' failed: ER_WARN_DATA_OUT_OF_RANGE (1264): Out of range value for column 'a' at row 2

So, the DML connection first inserts a value which exceeds the upcoming tinyint, but corrects it right away, and the resulting data set is well within the tinyint range.
However, ALTER still fails. In non-strict mode, it doesn't fail but produces a warning which it shouldn't.

 Comments   
Comment by Elena Stepanova [ 2023-07-05 ]

Another example, without lossy data type conversions:

--source include/have_debug_sync.inc
 
 
 
create table t (a int);
 
insert into t values (1);
 
set debug_sync= 'alter_table_online_progress WAIT_FOR go';
 
--send
 
  alter table t add primary key(a), algorithm=copy, lock=none;
 
 
 
--connect (con_dml,localhost,root,,test)
 
 
 
insert into t values (2),(2);
 
delete from t where a = 2;
 
select * from t;
 
set debug_sync= 'now signal go';
 
 
 
--connection default
 
--reap
 
show create table t;
 
select * from t;
 
 
 
# Cleanup
 
drop table t;
 
set debug_sync= reset;
 
--disconnect con_dml

bb-11.2-oalter adcf5dfa8d646b9fbd2bb1a7a4f5a3bb8a7e659c

 
insert into t values (2),(2);
 
delete from t where a = 2;
 
select * from t;
 
a
 
1
 
set debug_sync= 'now signal go';
 
connection default;
 
bug.t                                    [ fail ]
 
        Test ended at 2023-07-05 11:50:54
 
 
 
CURRENT_TEST: bug.t
 
mysqltest: At line 17: query 'reap' failed: ER_DUP_ENTRY (1062): Duplicate entry '2' for key 'PRIMARY'

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