| 1 |
# Test NDB replication with generated columns |
| 2 |
# Part of wl9048 |
| 3 |
# |
| 4 |
|
| 5 |
--source include/have_binlog_format_mixed_or_row.inc |
| 6 |
--source include/have_ndb.inc |
| 7 |
--source suite/ndb_rpl/ndb_master-slave.inc |
| 8 |
|
| 9 |
--echo --- Doing pre test setup and CREATE TABLE --- |
| 10 |
|
| 11 |
connection master; |
| 12 |
|
| 13 |
CREATE TABLE test_gcol_stored |
| 14 |
(id MEDIUMINT NOT NULL PRIMARY KEY, |
| 15 |
gcol_int int GENERATED ALWAYS AS (100 + id) STORED, |
| 16 |
misc int, |
| 17 |
gcol_str varchar(20) GENERATED ALWAYS AS (concat("User ", id)) STORED |
| 18 |
) engine = ndb; |
| 19 |
|
| 20 |
|
| 21 |
CREATE TABLE test_gcol_virtual |
| 22 |
(id MEDIUMINT NOT NULL PRIMARY KEY, |
| 23 |
gcol_int int GENERATED ALWAYS AS (100 + id), |
| 24 |
misc int, |
| 25 |
gcol_str varchar(20) GENERATED ALWAYS AS (concat("User ", id)) |
| 26 |
) engine = ndb; |
| 27 |
|
| 28 |
## test_gcol_virtual_hidden_pk is known not to work |
| 29 |
## |
| 30 |
#CREATE TABLE test_gcol_virtual_hidden_pk |
| 31 |
# (id MEDIUMINT, |
| 32 |
# gcol_int int GENERATED ALWAYS AS (100 + id), |
| 33 |
# misc int, |
| 34 |
# gcol_str varchar(20) GENERATED ALWAYS AS (concat("User ", id)), |
| 35 |
# index(id) |
| 36 |
# ) engine = ndb; |
| 37 |
|
| 38 |
|
| 39 |
--echo --- Show table on slave --- |
| 40 |
sync_slave_with_master; |
| 41 |
SHOW CREATE TABLE test_gcol_stored; |
| 42 |
|
| 43 |
|
| 44 |
--echo --- Insert on master |
| 45 |
connection master; |
| 46 |
insert into test_gcol_stored (id) values (1), (2), (3), (4), (5); |
| 47 |
insert into test_gcol_virtual (id) values (1), (2), (3), (4), (5); |
| 48 |
|
| 49 |
sync_slave_with_master; |
| 50 |
|
| 51 |
select * from test_gcol_stored order by id; |
| 52 |
select * from test_gcol_virtual order by id; |
| 53 |
|
| 54 |
|
| 55 |
--echo --- Update by pk on master |
| 56 |
connection master; |
| 57 |
update test_gcol_stored set misc = 44 where id = 4; |
| 58 |
update test_gcol_virtual set misc = 44 where id = 4; |
| 59 |
|
| 60 |
sync_slave_with_master; |
| 61 |
select * from test_gcol_stored order by id; |
| 62 |
select * from test_gcol_virtual order by id; |
| 63 |
|
| 64 |
|
| 65 |
--echo --- Update by gcol on master |
| 66 |
connection master; |
| 67 |
update test_gcol_stored set misc = 45 where gcol_int = 105; |
| 68 |
update test_gcol_virtual set misc = 45 where gcol_int = 105; |
| 69 |
|
| 70 |
sync_slave_with_master; |
| 71 |
select * from test_gcol_stored order by id; |
| 72 |
select * from test_gcol_virtual order by id; |
| 73 |
|
| 74 |
|
| 75 |
--echo --- Delete by key on master |
| 76 |
connection master; |
| 77 |
delete from test_gcol_stored where id = 1; |
| 78 |
delete from test_gcol_virtual where id = 1; |
| 79 |
|
| 80 |
sync_slave_with_master; |
| 81 |
select * from test_gcol_stored order by id; |
| 82 |
select * from test_gcol_virtual order by id; |
| 83 |
|
| 84 |
|
| 85 |
--echo -- Delete by gcol on master |
| 86 |
connection master; |
| 87 |
delete from test_gcol_stored where gcol_int = 102; |
| 88 |
delete from test_gcol_virtual where gcol_int = 102; |
| 89 |
|
| 90 |
sync_slave_with_master; |
| 91 |
select * from test_gcol_stored order by id; |
| 92 |
select * from test_gcol_virtual order by id; |
| 93 |
|
| 94 |
|
| 95 |
--echo -- Delete by non-key stored column on master |
| 96 |
connection master; |
| 97 |
delete from test_gcol_stored where misc is null; |
| 98 |
delete from test_gcol_virtual where misc is null; |
| 99 |
|
| 100 |
sync_slave_with_master; |
| 101 |
select * from test_gcol_stored order by id; |
| 102 |
select * from test_gcol_virtual order by id; |
| 103 |
|
| 104 |
|
| 105 |
--echo -- Drop test table and clean up |
| 106 |
connection master; |
| 107 |
drop table test_gcol_stored; |
| 108 |
drop table test_gcol_virtual; |
| 109 |
|
| 110 |
--source include/rpl_end.inc |
| 111 |
|