rpl_ndb_basic.test 8.6 KB
Newer Older
unknown's avatar
unknown committed
1 2
--source include/have_ndb.inc
--source include/have_binlog_format_row.inc
3
--source include/ndb_master-slave.inc
unknown's avatar
unknown committed
4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37



#
# Bug #11087
#
# connect to the master and create tabe t1 in gotoslave database
--connection master
CREATE TABLE `t1` ( `nid` int(11) NOT NULL default '0',
 	            `nom` char(4) default NULL,
  		    `prenom` char(4) default NULL,
		    PRIMARY KEY  (`nid`)) 
    ENGINE=ndbcluster DEFAULT CHARSET=latin1;

INSERT INTO t1 VALUES(1,"XYZ1","ABC1");
select * from t1 order by nid;

--sync_slave_with_master
# connect to slave and ensure data it there.
--connection slave
select * from t1 order by nid;

--connection master
delete from t1;
INSERT INTO t1 VALUES(1,"XYZ2","ABC2");
# Make sure all rows are on the master
select * from t1 order by nid;

# make sure all rows are on the slave.
--sync_slave_with_master
--connection slave
# Bug #11087 would have row with nid 2 missing
select * from t1 order by nid;

unknown's avatar
unknown committed
38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68
--connection master
delete from t1;
insert into t1 values(1,"AA", "AA");
insert into t1 values(2,"BB", "BB");
insert into t1 values(3,"CC", "CC");
insert into t1 values(4,"DD", "DD");

begin;
# delete+insert = update
delete from t1 where nid = 1;
insert into t1 values (1,"A2", "A2");

# update+delete = delete
update t1 set nom="B2" where nid = 2;
delete from t1 where nid = 2;

# multi-update
update t1 set nom = "D2" where nid = 4;
delete from t1 where nid = 4;
insert into t1 values (4, "D3", "D3");
update t1 set nom = "D4" where nid = 4;

# insert+delete = nothing
insert into t1 values (5, "EE", "EE");
delete from t1 where nid = 5;

commit;
select * from t1 order by 1;
--sync_slave_with_master
--connection slave
select * from t1 order by 1;
unknown's avatar
unknown committed
69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112
--connection master
DROP table t1;

#
# Test replication of table with no primary key 
#
--connection master
CREATE TABLE `t1` ( `nid` int(11) NOT NULL default '0',
 	            `nom` char(4) default NULL,
  		    `prenom` char(4) default NULL)
    ENGINE=ndbcluster DEFAULT CHARSET=latin1;

INSERT INTO t1 VALUES(1,"XYZ1","ABC1"),(2,"AAA","BBB"),(3,"CCC","DDD");
select * from t1 order by nid;

--sync_slave_with_master
# connect to slave and ensure data it there.
--connection slave
select * from t1 order by nid;

--connection master
delete from t1 where nid = 2;
INSERT INTO t1 VALUES(4,"EEE","FFF");
# Make sure all rows are on the master
select * from t1 order by nid;

# make sure all rows are on the slave.
--sync_slave_with_master
--connection slave
select * from t1 order by nid;

--connection master
UPDATE t1 set nid=nid+1;
UPDATE t1 set nom="CCP" where nid = 4;
select * from t1 order by nid;

# make sure all rows are on the slave.
--sync_slave_with_master
--connection slave
select * from t1 order by nid;

--connection master
DROP table t1;

113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155
#
# Bug #27378 update becomes delete on slave
#

--connection master
CREATE TABLE `t1` (
  `prid` int(10) unsigned NOT NULL,
  `id_type` enum('IMSI','SIP') NOT NULL,
  `fkimssub` varchar(50) NOT NULL,
  `user_id` varchar(20) DEFAULT NULL,
  `password` varchar(20) DEFAULT NULL,
  `ptg_nbr` varchar(20) DEFAULT NULL,
  `old_tmsi` int(10) unsigned DEFAULT NULL,
  `new_tmsi` int(10) unsigned DEFAULT NULL,
  `dev_capability` int(10) unsigned DEFAULT NULL,
  `dev_oid` bigint(20) unsigned DEFAULT NULL,
  `lac_cell_id` bigint(20) unsigned DEFAULT NULL,
  `ms_classmark1` int(10) unsigned DEFAULT NULL,
  `cipher_key` int(10) unsigned DEFAULT NULL,
  `priid_master` int(10) unsigned DEFAULT NULL,
  PRIMARY KEY (`prid`),
  UNIQUE KEY `fkimssub` (`fkimssub`,`ptg_nbr`) USING HASH
) ENGINE=ndbcluster DEFAULT CHARSET=latin1;

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);

select count(*) from t1;

--sync_slave_with_master
--connection slave
select count(*) from t1;

--connection master
update t1 set dev_oid=dev_oid+1;
select count(*) from t1;

--sync_slave_with_master
--connection slave
select count(*) from t1;

--connection master
DROP table t1;

unknown's avatar
unknown committed
156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174
##################################################################
#
# Check that retries are made on the slave on some temporary errors
#

#
# 1. Deadlock
#
--connection master
CREATE TABLE `t1` ( `nid` int(11) NOT NULL default '0',
 	            `nom` char(4) default NULL,
  		    `prenom` char(4) default NULL,
		    PRIMARY KEY USING HASH (`nid`)) 
    ENGINE=ndbcluster DEFAULT CHARSET=latin1;
INSERT INTO t1 VALUES(1,"XYZ1","ABC1");

# cause a lock on that row on the slave
--sync_slave_with_master
--connection slave
175
--echo **** On Slave ****
unknown's avatar
unknown committed
176 177 178 179 180 181 182 183 184
BEGIN;
UPDATE t1 SET `nom`="LOCK" WHERE `nid`=1;

# set number of retries low so we fail the retries
set GLOBAL slave_transaction_retries=1;

# now do a change to this row on the master
# will deadlock on the slave because of lock above
--connection master
185
--echo **** On Master ****
unknown's avatar
unknown committed
186 187 188 189 190 191 192 193 194 195 196 197
UPDATE t1 SET `nom`="DEAD" WHERE `nid`=1;

# wait for deadlock to be detected
# sleep longer than dead lock detection timeout in config
# we do this 2 times, once with few retries to verify that we 
# get a failure with the set sleep, and once with the _same_
# sleep, but with more retries to get it to succeed
--sleep 5

# replication should have stopped, since max retries where not enough
# verify with show slave status
--connection slave
198
--echo **** On Slave ****
unknown's avatar
unknown committed
199
--replace_result $MASTER_MYPORT MASTER_PORT
200
--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>
201
--query_vertical SHOW SLAVE STATUS;
unknown's avatar
unknown committed
202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219

# now set max retries high enough to succeed, and start slave again
set GLOBAL slave_transaction_retries=10;
START SLAVE;
# wait for deadlock to be detected and retried
# should be the same sleep as above for test to be valid
--sleep 5

# commit transaction to release lock on row and let replication succeed
select * from t1 order by nid;
COMMIT;

# verify that the row succeded to be applied on the slave
--connection master
--sync_slave_with_master
--connection slave
select * from t1 order by nid;

220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248
# cleanup
--connection master
DROP TABLE t1;


#
# BUG#18094
# Slave caches invalid table definition after atlters causes select failure
#
--connection master
CREATE TABLE t1 (c1 INT KEY) ENGINE=NDB;

INSERT INTO t1 VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10);

ALTER TABLE t1 ADD c2 INT;

--sync_slave_with_master
connection slave;
SELECT * FROM t1 ORDER BY c1;

connection master;
ALTER TABLE t1 CHANGE c2 c2 TEXT CHARACTER SET utf8;
ALTER TABLE t1 CHANGE c2 c2 BLOB;

--sync_slave_with_master
connection slave;
# here we would get error 1412 prior to bug
SELECT * FROM t1 ORDER BY c1 LIMIT 5;

unknown's avatar
unknown committed
249 250 251 252 253 254 255 256
--connection master
TRUNCATE t1;
SELECT count(*) FROM t1;
INSERT INTO t1 VALUES (101,NULL),(102,NULL),(103,NULL),(104,NULL),(105,NULL),(106,NULL),(107,NULL),(108,NULL),(109,NULL),(1010,NULL);
--sync_slave_with_master
connection slave;
SELECT count(*) FROM t1;
SELECT c1 FROM t1 ORDER BY c1 LIMIT 5;
257

unknown's avatar
unknown committed
258 259 260
# cleanup
--connection master
DROP TABLE t1;
261
-- source include/master-slave-end.inc