mirror of
https://github.com/MariaDB/server.git
synced 2025-02-15 09:55:33 +01:00
5f206259e5
The bug is inherent for row-based replication as well. To reproduce, a virtual (not stored) field of a blob type computed from another field of a different blob type is required. The following happens during an update or delete row event: 1. A row is unpacked. 2. Virtual fields are updated. Field b1 stores the pointer in Field_blob::value and references it in table->record[0]. 3. record[0] is stored to record[1] in Rows_log_event::find_row. 4. A new record is fetched from handler. (e.g. ha_rnd_next) 5. Virtual columns are updated (only non-stored). 6. Field b1 receives new value. Old value is deallocated (Field_blob::val_str). 7. record_compare is called. record[0] and record[1] are compared. 8. record[1] contains a reference to a freed value. record_compare is used in replication to find a matching record for update or delete. Virtual columns that are not stored should be definitely skipped both for correctness, and for this bug fix. STORED virtual columns, on the other hand, may be required and shouldn't be skipped. Stored columns are not affected, since they are not updated after handler's fetch.
376 lines
6.4 KiB
Text
376 lines
6.4 KiB
Text
include/master-slave.inc
|
|
[connection master]
|
|
connection master;
|
|
create table t1(a int primary key);
|
|
insert into t1 values(1);
|
|
insert into t1 values(2);
|
|
insert into t1 values(3);
|
|
insert into t1 values(4);
|
|
connection slave;
|
|
select * from t1 order by a;
|
|
a
|
|
1
|
|
2
|
|
3
|
|
4
|
|
alter table t1 add column z1 int as(a+1) virtual, add column z2 int as (a+2) persistent;
|
|
select * from t1 order by a;
|
|
a z1 z2
|
|
1 2 3
|
|
2 3 4
|
|
3 4 5
|
|
4 5 6
|
|
connection master;
|
|
insert into t1 values(5);
|
|
insert into t1 values(6);
|
|
connection slave;
|
|
select * from t1 order by a;
|
|
a z1 z2
|
|
1 2 3
|
|
2 3 4
|
|
3 4 5
|
|
4 5 6
|
|
5 6 7
|
|
6 7 8
|
|
alter table t1 add column z3 int default(a+2);
|
|
connection master;
|
|
insert into t1 values(7);
|
|
insert into t1 values(8);
|
|
connection slave;
|
|
select * from t1 order by a;
|
|
a z1 z2 z3
|
|
1 2 3 3
|
|
2 3 4 4
|
|
3 4 5 5
|
|
4 5 6 6
|
|
5 6 7 7
|
|
6 7 8 8
|
|
7 8 9 9
|
|
8 9 10 10
|
|
connection master;
|
|
delete from t1 where a > 6;
|
|
#UPDATE query
|
|
update t1 set a = a+10;
|
|
select * from t1 order by a;
|
|
a
|
|
11
|
|
12
|
|
13
|
|
14
|
|
15
|
|
16
|
|
connection slave;
|
|
select * from t1 order by a;
|
|
a z1 z2 z3
|
|
11 12 13 3
|
|
12 13 14 4
|
|
13 14 15 5
|
|
14 15 16 6
|
|
15 16 17 7
|
|
16 17 18 8
|
|
connection master;
|
|
update t1 set a = a-10;
|
|
select * from t1 order by a;
|
|
a
|
|
1
|
|
2
|
|
3
|
|
4
|
|
5
|
|
6
|
|
connection slave;
|
|
select * from t1 order by a;
|
|
a z1 z2 z3
|
|
1 2 3 3
|
|
2 3 4 4
|
|
3 4 5 5
|
|
4 5 6 6
|
|
5 6 7 7
|
|
6 7 8 8
|
|
#DELETE quert
|
|
connection master;
|
|
delete from t1 where a > 2 and a < 4;
|
|
select * from t1 order by a;
|
|
a
|
|
1
|
|
2
|
|
4
|
|
5
|
|
6
|
|
connection slave;
|
|
select * from t1 order by a;
|
|
a z1 z2 z3
|
|
1 2 3 3
|
|
2 3 4 4
|
|
4 5 6 6
|
|
5 6 7 7
|
|
6 7 8 8
|
|
#REPLACE query
|
|
connection master;
|
|
replace into t1 values(1);
|
|
replace into t1 values(3);
|
|
replace into t1 values(1);
|
|
connection slave;
|
|
select * from t1 order by a;
|
|
a z1 z2 z3
|
|
1 2 3 3
|
|
2 3 4 4
|
|
3 4 5 5
|
|
4 5 6 6
|
|
5 6 7 7
|
|
6 7 8 8
|
|
#SELECT query
|
|
connection master;
|
|
select * from t1 where a > 2 and a < 4;
|
|
a
|
|
3
|
|
connection slave;
|
|
select * from t1 where a > 2 and a < 4;
|
|
a z1 z2 z3
|
|
3 4 5 5
|
|
#UPDATE with SELECT query
|
|
connection master;
|
|
update t1 set a = a + 10 where a > 2 and a < 4;
|
|
select * from t1 order by a;
|
|
a
|
|
1
|
|
2
|
|
4
|
|
5
|
|
6
|
|
13
|
|
connection slave;
|
|
select * from t1 order by a;
|
|
a z1 z2 z3
|
|
1 2 3 3
|
|
2 3 4 4
|
|
4 5 6 6
|
|
5 6 7 7
|
|
6 7 8 8
|
|
13 14 15 5
|
|
connection master;
|
|
update t1 set a = a - 10 where a = 13;
|
|
select * from t1 order by a;
|
|
a
|
|
1
|
|
2
|
|
3
|
|
4
|
|
5
|
|
6
|
|
connection slave;
|
|
select * from t1 order by a;
|
|
a z1 z2 z3
|
|
1 2 3 3
|
|
2 3 4 4
|
|
3 4 5 5
|
|
4 5 6 6
|
|
5 6 7 7
|
|
6 7 8 8
|
|
#Break Unique Constraint
|
|
alter table t1 add column z4 int as (a % 6) persistent unique;
|
|
connection master;
|
|
#entering duplicate value for slave persistent column
|
|
insert into t1 values(7);
|
|
select * from t1 order by a;
|
|
a
|
|
1
|
|
2
|
|
3
|
|
4
|
|
5
|
|
6
|
|
7
|
|
connection slave;
|
|
include/wait_for_slave_sql_error.inc [errno=1062]
|
|
select * from t1 order by a;
|
|
a z1 z2 z3 z4
|
|
1 2 3 3 1
|
|
2 3 4 4 2
|
|
3 4 5 5 3
|
|
4 5 6 6 4
|
|
5 6 7 7 5
|
|
6 7 8 8 0
|
|
alter table t1 drop column z4;
|
|
start slave;
|
|
include/wait_for_slave_sql_to_start.inc
|
|
connection master;
|
|
connection slave;
|
|
select * from t1 order by a;
|
|
a z1 z2 z3
|
|
1 2 3 3
|
|
2 3 4 4
|
|
3 4 5 5
|
|
4 5 6 6
|
|
5 6 7 7
|
|
6 7 8 8
|
|
7 8 9 9
|
|
connection master;
|
|
select * from t1 order by a;
|
|
a
|
|
1
|
|
2
|
|
3
|
|
4
|
|
5
|
|
6
|
|
7
|
|
drop table t1;
|
|
connection slave;
|
|
connection master;
|
|
set binlog_row_image=minimal;
|
|
create table t1(a int primary key auto_increment, b int unique);
|
|
insert into t1 values(1, 1);
|
|
insert into t1 values(2, 2);
|
|
insert into t1 values(3, 3);
|
|
insert into t1 values(4, 4);
|
|
insert into t1 values(5, 5);
|
|
connection slave;
|
|
alter table t1 add column d1 int default (b),
|
|
add column z1 int as (b+1) virtual,
|
|
add column z2 int as (b+2) persistent;
|
|
connection master;
|
|
insert into t1 values(6, 6);
|
|
update t1 set a = 11 where a = 1;
|
|
update t1 set b = 12 where b = 2;
|
|
delete from t1 where a = 3;
|
|
delete from t1 where b = 5;
|
|
update t1 set b = 16 where a = 6;
|
|
connection slave;
|
|
select * from t1;
|
|
a b d1 z1 z2
|
|
11 1 1 2 3
|
|
2 12 2 13 14
|
|
4 4 4 5 6
|
|
6 16 6 17 18
|
|
# Cleanup
|
|
connection master;
|
|
drop table t1;
|
|
connection slave;
|
|
connection master;
|
|
set binlog_row_image=minimal;
|
|
#
|
|
# MDEV-29069 ER_KEY_NOT_FOUND upon online autoinc addition and
|
|
# concurrent DELETE
|
|
#
|
|
create or replace table t (a int);
|
|
insert into t values (10),(20),(30);
|
|
connection slave;
|
|
alter table t add pk int auto_increment primary key;
|
|
connection master;
|
|
delete from t where a = 20;
|
|
update t set a = a + 1 where a = 10;
|
|
connection slave;
|
|
select * from t;
|
|
a pk
|
|
11 1
|
|
30 3
|
|
connection master;
|
|
#
|
|
# Add clumsy DEFAULT
|
|
#
|
|
create or replace table t (a int);
|
|
insert into t values (10),(20),(30);
|
|
connection slave;
|
|
alter table t add b int default(RAND() * 20), add key(b),
|
|
algorithm=copy, lock=none;
|
|
connection master;
|
|
delete from t where a = 20;
|
|
update t set a = a + 1 where a = 10;
|
|
connection slave;
|
|
select a from t;
|
|
a
|
|
11
|
|
30
|
|
connection master;
|
|
# CURRENT_TIMESTAMP
|
|
create or replace table t (a int);
|
|
insert into t values (10),(20),(30);
|
|
connection slave;
|
|
alter table t add b timestamp default CURRENT_TIMESTAMP, add key(b);
|
|
connection master;
|
|
delete from t where a = 20;
|
|
update t set a = a + 1 where a = 10;
|
|
connection slave;
|
|
select a from t;
|
|
a
|
|
11
|
|
30
|
|
connection master;
|
|
# CURRENT_TIMESTAMP, mixed key
|
|
create or replace table t (a int);
|
|
insert into t values (10),(20),(30);
|
|
connection slave;
|
|
alter table t add b timestamp default CURRENT_TIMESTAMP, add key(a, b);
|
|
connection master;
|
|
delete from t where a = 20;
|
|
update t set a = a + 1 where a = 10;
|
|
connection slave;
|
|
select a from t;
|
|
a
|
|
11
|
|
30
|
|
connection master;
|
|
# Mixed primary key
|
|
create or replace table t (a int);
|
|
insert into t values (10),(20),(30);
|
|
connection slave;
|
|
alter table t add b int default (1), add primary key(b, a);
|
|
connection master;
|
|
delete from t where a = 20;
|
|
update t set a = a + 1 where a = 10;
|
|
connection slave;
|
|
select a from t;
|
|
a
|
|
11
|
|
30
|
|
connection master;
|
|
#
|
|
# Normal row, could be used as a key
|
|
#
|
|
create or replace table t (a int);
|
|
insert into t values (10),(20),(30);
|
|
connection slave;
|
|
alter table t add b int as (a * 10) unique;
|
|
connection master;
|
|
delete from t where a = 20;
|
|
update t set a = a + 1 where a = 10;
|
|
connection slave;
|
|
select * from t;
|
|
a b
|
|
11 110
|
|
30 300
|
|
connection master;
|
|
#
|
|
# Add key for old row
|
|
#
|
|
create or replace table t (a int);
|
|
insert into t values (10),(20),(30);
|
|
connection slave;
|
|
alter table t add unique(a);
|
|
connection master;
|
|
delete from t where a = 20;
|
|
update t set a = a + 1 where a = 10;
|
|
connection slave;
|
|
select * from t;
|
|
a
|
|
11
|
|
30
|
|
# Cleanup
|
|
connection master;
|
|
connection slave;
|
|
connection master;
|
|
drop table t;
|
|
#
|
|
# MDEV-30985 Replica stops with error on ALTER ONLINE with Geometry Types
|
|
#
|
|
create table t(geo geometrycollection default st_geomfromtext('point(1 1)'));
|
|
insert into t () values (),(),();
|
|
connection slave;
|
|
alter table t add vcol9 point as (geo), add key(vcol9);
|
|
connection master;
|
|
delete from t;
|
|
connection slave;
|
|
connection master;
|
|
drop table t;
|
|
include/rpl_end.inc
|