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