1
--source include/have_ndb.inc
2
--source include/ndb_master-slave.inc
9
# connect to the master and create tabe t1 in gotoslave database
11
CREATE TABLE `t1` ( `nid` int(11) NOT NULL default '0',
12
`nom` char(4) default NULL,
13
`prenom` char(4) default NULL,
15
ENGINE=ndbcluster DEFAULT CHARSET=latin1;
17
INSERT INTO t1 VALUES(1,"XYZ1","ABC1");
18
select * from t1 order by nid;
20
--sync_slave_with_master
21
# connect to slave and ensure data it there.
23
select * from t1 order by nid;
27
INSERT INTO t1 VALUES(1,"XYZ2","ABC2");
28
# Make sure all rows are on the master
29
select * from t1 order by nid;
31
# make sure all rows are on the slave.
32
--sync_slave_with_master
34
# Bug #11087 would have row with nid 2 missing
35
select * from t1 order by nid;
39
insert into t1 values(1,"AA", "AA");
40
insert into t1 values(2,"BB", "BB");
41
insert into t1 values(3,"CC", "CC");
42
insert into t1 values(4,"DD", "DD");
45
# delete+insert = update
46
delete from t1 where nid = 1;
47
insert into t1 values (1,"A2", "A2");
49
# update+delete = delete
50
update t1 set nom="B2" where nid = 2;
51
delete from t1 where nid = 2;
54
update t1 set nom = "D2" where nid = 4;
55
delete from t1 where nid = 4;
56
insert into t1 values (4, "D3", "D3");
57
update t1 set nom = "D4" where nid = 4;
59
# insert+delete = nothing
60
insert into t1 values (5, "EE", "EE");
61
delete from t1 where nid = 5;
64
select * from t1 order by 1;
65
--sync_slave_with_master
67
select * from t1 order by 1;
72
# Test replication of table with no primary key
75
CREATE TABLE `t1` ( `nid` int(11) NOT NULL default '0',
76
`nom` char(4) default NULL,
77
`prenom` char(4) default NULL)
78
ENGINE=ndbcluster DEFAULT CHARSET=latin1;
80
INSERT INTO t1 VALUES(1,"XYZ1","ABC1"),(2,"AAA","BBB"),(3,"CCC","DDD");
81
select * from t1 order by nid;
83
--sync_slave_with_master
84
# connect to slave and ensure data it there.
86
select * from t1 order by nid;
89
delete from t1 where nid = 2;
90
INSERT INTO t1 VALUES(4,"EEE","FFF");
91
# Make sure all rows are on the master
92
select * from t1 order by nid;
94
# make sure all rows are on the slave.
95
--sync_slave_with_master
97
select * from t1 order by nid;
100
UPDATE t1 set nid=nid+1;
101
UPDATE t1 set nom="CCP" where nid = 4;
102
select * from t1 order by nid;
104
# make sure all rows are on the slave.
105
--sync_slave_with_master
107
select * from t1 order by nid;
113
# Bug #27378 update becomes delete on slave
118
`prid` int(10) unsigned NOT NULL,
119
`id_type` enum('IMSI','SIP') NOT NULL,
120
`fkimssub` varchar(50) NOT NULL,
121
`user_id` varchar(20) DEFAULT NULL,
122
`password` varchar(20) DEFAULT NULL,
123
`ptg_nbr` varchar(20) DEFAULT NULL,
124
`old_tmsi` int(10) unsigned DEFAULT NULL,
125
`new_tmsi` int(10) unsigned DEFAULT NULL,
126
`dev_capability` int(10) unsigned DEFAULT NULL,
127
`dev_oid` bigint(20) unsigned DEFAULT NULL,
128
`lac_cell_id` bigint(20) unsigned DEFAULT NULL,
129
`ms_classmark1` int(10) unsigned DEFAULT NULL,
130
`cipher_key` int(10) unsigned DEFAULT NULL,
131
`priid_master` int(10) unsigned DEFAULT NULL,
132
PRIMARY KEY (`prid`),
133
UNIQUE KEY `fkimssub` (`fkimssub`,`ptg_nbr`) USING HASH
134
) ENGINE=ndbcluster DEFAULT CHARSET=latin1;
136
INSERT INTO `t1` VALUES (183342,'IMSI','config3_sub_2Privates_3Publics_imssub_36668','user_id_73336','user_id_73336','73336',NULL,NULL,NULL,123456789,NULL,NULL,NULL,NULL),(47617,'IMSI','config3_sub_2Privates_3Publics_imssub_9523','user_id_19046','user_id_19046','19046',NULL,NULL,NULL,123456789,NULL,NULL,NULL,NULL),(200332,'IMSI','config3_sub_2Privates_3Publics_imssub_40066','user_id_80132','user_id_80132','80132',NULL,NULL,NULL,123456789,NULL,NULL,NULL,NULL),(478882,'IMSI','config3_sub_2Privates_3Publics_imssub_95776','user_id_191552','user_id_191552','191552',NULL,NULL,NULL,123456789,NULL,NULL,NULL,NULL),(490146,'IMSI','config3_sub_2Privates_3Publics_imssub_98029','user_id_196057','user_id_196057','196057',NULL,NULL,NULL,1010,NULL,NULL,NULL,NULL),(499301,'IMSI','config3_sub_2Privates_3Publics_imssub_99860','user_id_199719','user_id_199719','199719',NULL,NULL,NULL,123456789,NULL,NULL,NULL,NULL),(506101,'IMSI','config3_sub_2Privates_3Publics_imssub_101220','user_id_202439','user_id_202439','202439',NULL,NULL,NULL,1010,NULL,NULL,NULL,NULL),(510142,'IMSI','config3_sub_2Privates_3Publics_imssub_102028','user_id_204056','user_id_204056','204056',NULL,NULL,NULL,1010,NULL,NULL,NULL,NULL),(515871,'IMSI','config3_sub_2Privates_3Publics_imssub_103174','user_id_206347','user_id_206347','206347',NULL,NULL,NULL,1010,NULL,NULL,NULL,NULL),(209842,'IMSI','config3_sub_2Privates_3Publics_imssub_41968','user_id_83936','user_id_83936','83936',NULL,NULL,NULL,123456789,NULL,NULL,NULL,NULL),(365902,'IMSI','config3_sub_2Privates_3Publics_imssub_73180','user_id_146360','user_id_146360','146360',NULL,NULL,NULL,1010,NULL,NULL,NULL,NULL),(11892,'IMSI','config3_sub_2Privates_3Publics_imssub_2378','user_id_4756','user_id_4756','4756',NULL,NULL,NULL,123456789,NULL,NULL,NULL,NULL);
138
select count(*) from t1;
140
--sync_slave_with_master
142
select count(*) from t1;
145
update t1 set dev_oid=dev_oid+1;
146
select count(*) from t1;
148
--sync_slave_with_master
150
select count(*) from t1;
155
##################################################################
157
# Check that retries are made on the slave on some temporary errors
164
CREATE TABLE `t1` ( `nid` int(11) NOT NULL default '0',
165
`nom` char(4) default NULL,
166
`prenom` char(4) default NULL,
167
PRIMARY KEY USING HASH (`nid`))
168
ENGINE=ndbcluster DEFAULT CHARSET=latin1;
169
INSERT INTO t1 VALUES(1,"XYZ1","ABC1");
171
# cause a lock on that row on the slave
172
--sync_slave_with_master
174
--echo **** On Slave ****
176
UPDATE t1 SET `nom`="LOCK" WHERE `nid`=1;
178
# set number of retries low so we fail the retries
179
set GLOBAL slave_transaction_retries=1;
181
# now do a change to this row on the master
182
# will deadlock on the slave because of lock above
184
--echo **** On Master ****
185
UPDATE t1 SET `nom`="DEAD" WHERE `nid`=1;
187
--echo **** On Slave ****
188
# Wait for deadlock to be detected.
189
# When detected, the slave will stop, so we just wait for it to stop.
191
source include/wait_for_slave_sql_to_stop.inc;
193
# Replication should have stopped, since max retries were not enough.
194
# verify with show slave status
195
--replace_result $MASTER_MYPORT MASTER_PORT
196
--replace_column 1 <Slave_IO_State> 7 <Read_Master_Log_Pos> 8 <Relay_Log_File> 9 <Relay_Log_Pos> 16 <Replicate_Ignore_Table> 19 <Last_Errno> 20 <Last_Error> 22 <Exec_Master_Log_Pos> 23 <Relay_Log_Space> 33 <Seconds_Behind_Master> 35 <Last_IO_Errno> 36 <Last_IO_Error> 37 <Last_SQL_Errno> 38 <Last_SQL_Error>
197
--query_vertical SHOW SLAVE STATUS;
199
# now set max retries high enough to succeed, and start slave again
200
set GLOBAL slave_transaction_retries=10;
201
source include/start_slave.inc;
202
# Wait for deadlock to be detected and retried.
203
# We want to wait until at least one retry has been made, but before
204
# the slave stops. currently, there is no safe way to do that: we
205
# would need to access the retry counter, but that is not exposed.
206
# Failing that, we just wait sufficiently long that one but not all
207
# retries have been made. See BUG#35183.
210
# commit transaction to release lock on row and let replication succeed
211
select * from t1 order by nid;
214
# verify that the row succeded to be applied on the slave
216
--sync_slave_with_master
218
select * from t1 order by nid;
227
# Slave caches invalid table definition after atlters causes select failure
230
CREATE TABLE t1 (c1 INT KEY) ENGINE=NDB;
232
INSERT INTO t1 VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10);
234
ALTER TABLE t1 ADD c2 INT;
236
--sync_slave_with_master
238
SELECT * FROM t1 ORDER BY c1;
241
ALTER TABLE t1 CHANGE c2 c2 TEXT CHARACTER SET utf8;
242
ALTER TABLE t1 CHANGE c2 c2 BLOB;
244
--sync_slave_with_master
246
# here we would get error 1412 prior to bug
247
SELECT * FROM t1 ORDER BY c1 LIMIT 5;
251
SELECT count(*) FROM t1;
252
INSERT INTO t1 VALUES (101,NULL),(102,NULL),(103,NULL),(104,NULL),(105,NULL),(106,NULL),(107,NULL),(108,NULL),(109,NULL),(1010,NULL);
253
--sync_slave_with_master
255
SELECT count(*) FROM t1;
256
SELECT c1 FROM t1 ORDER BY c1 LIMIT 5;
261
-- source include/master-slave-end.inc