[MDEV-31624] Online ALTER fails due to intermediate DML which is later overridden - Jira

Details

Type: Bug
Status: Open (View Workflow)
Priority: Major
Resolution: Unresolved
Affects Version/s: N/A
Fix Version/s: 11.2(EOL)
Component/s: Data Definition - Alter Table
Labels:
None

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.

Attachments

Issue Links

is caused by

MDEV-16329 Engine-independent online ALTER TABLE

Closed

relates to

MDEV-28808 Test MDEV-16329 (ALTER ONLINE TABLE) - Core server part

Closed

Activity

Ascending order - Click to sort in descending order

Elena Stepanova created issue - 2023-07-04 19:16

Elena Stepanova made changes - 2023-07-04 19:16

Field	Original Value	New Value
Link		This issue is caused by ~~MDEV-16329~~ [ ~~MDEV-16329~~ ]

Elena Stepanova made changes - 2023-07-04 19:24

Description

{code:sql}
--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;

--connect (con_dml,localhost,root,,test)

insert into t values (1000);
update t set a = 10 where a > 100;
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
{code}

{code:sql|title=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:12:56

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
{code}

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.

{code:sql}
--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
{code}

{code:sql|title=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:12:56

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
{code}

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.

Elena Stepanova made changes - 2023-07-04 19:25

Description

{code:sql}
--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
{code}

{code:sql|title=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:12:56

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
{code}

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.

{code:sql}
--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
{code}

{code:sql|title=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
{code}

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.

Elena Stepanova made changes - 2023-07-04 19:25

Link

This issue relates to ~~MDEV-28808~~ [ ~~MDEV-28808~~ ]

Elena Stepanova added a comment - 2023-07-05 08:52

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'

Elena Stepanova added a comment - 2023-07-05 08:52 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'

Elena Stepanova made changes - 2023-07-05 19:09

Priority

Critical [ 2 ]

Major [ 3 ]

People

Assignee:: Nikita Malyavin

Reporter:: Elena Stepanova

Votes:: 0 Vote for this issue

Watchers:: 2 Start watching this issue

Dates

Created:: 2023-07-04 19:16

Updated:: 2023-07-05 19:09

Git Integration

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

MariaDB Server

Details

Description

Attachments

Issue Links

Activity

People

Dates

Git Integration