Debian Codegraph @

mysql-test/suite/ndb_rpl/t/ndb_rpl_basic.test

raw
1 --source include/have_ndb.inc
2 #error message differs slightly with statement based replication
3 --source include/have_binlog_format_mixed_or_row.inc
4 --source suite/ndb_rpl/ndb_master-slave.inc
5
6 -- disable_query_log
7 call mtr.add_suppression("Got error 146 - 'Lock timed out; Retry transaction' during COMMIT");
8 -- enable_query_log
9
10 #
11 # Bug #11087
12 #
13 # connect to the master and create tabe t1 in gotoslave database
14 --connection master
15 CREATE TABLE `t1` (
16 `nid` int NOT NULL default 0,
17 `nom` char(4) default NULL,
18 `prenom` char(4) default NULL,
19 PRIMARY KEY (`nid`)) ENGINE=ndbcluster DEFAULT CHARSET=latin1;
20
21 INSERT INTO t1 VALUES(1,"XYZ1","ABC1");
22 select * from t1 order by nid;
23
24 --sync_slave_with_master
25 # connect to slave and ensure data it there.
26 --connection slave
27 call mtr.add_suppression(".*worker thread retried transaction 1 time.*");
28 call mtr.add_suppression(".*coordinator and worker threads are stopped.*");
29 select * from t1 order by nid;
30
31 --connection master
32 delete from t1;
33 INSERT INTO t1 VALUES(1,"XYZ2","ABC2");
34 # Make sure all rows are on the master
35 select * from t1 order by nid;
36
37 # make sure all rows are on the slave.
38 --sync_slave_with_master
39 --connection slave
40 # Bug #11087 would have row with nid 2 missing
41 select * from t1 order by nid;
42
43 --connection master
44 delete from t1;
45 insert into t1 values(1,"AA", "AA");
46 insert into t1 values(2,"BB", "BB");
47 insert into t1 values(3,"CC", "CC");
48 insert into t1 values(4,"DD", "DD");
49
50 begin;
51 # delete+insert = update
52 delete from t1 where nid = 1;
53 insert into t1 values (1,"A2", "A2");
54
55 # update+delete = delete
56 update t1 set nom="B2" where nid = 2;
57 delete from t1 where nid = 2;
58
59 # multi-update
60 update t1 set nom = "D2" where nid = 4;
61 delete from t1 where nid = 4;
62 insert into t1 values (4, "D3", "D3");
63 update t1 set nom = "D4" where nid = 4;
64
65 # insert+delete = nothing
66 insert into t1 values (5, "EE", "EE");
67 delete from t1 where nid = 5;
68
69 commit;
70 select * from t1 order by 1;
71 --sync_slave_with_master
72 --connection slave
73 select * from t1 order by 1;
74 --connection master
75 DROP table t1;
76
77 #
78 # Test replication of table with no primary key
79 #
80 --connection master
81 CREATE TABLE `t1` (
82 `nid` int NOT NULL default '0',
83 `nom` char(4) default NULL,
84 `prenom` char(4) default NULL
85 ) ENGINE=ndbcluster DEFAULT CHARSET=latin1;
86
87 INSERT INTO t1 VALUES(1,"XYZ1","ABC1"),(2,"AAA","BBB"),(3,"CCC","DDD");
88 select * from t1 order by nid;
89
90 --sync_slave_with_master
91 # connect to slave and ensure data it there.
92 --connection slave
93 select * from t1 order by nid;
94
95 --connection master
96 delete from t1 where nid = 2;
97 INSERT INTO t1 VALUES(4,"EEE","FFF");
98 # Make sure all rows are on the master
99 select * from t1 order by nid;
100
101 # make sure all rows are on the slave.
102 --sync_slave_with_master
103 --connection slave
104 select * from t1 order by nid;
105
106 --connection master
107 UPDATE t1 set nid=nid+1;
108 UPDATE t1 set nom="CCP" where nid = 4;
109 select * from t1 order by nid;
110
111 # make sure all rows are on the slave.
112 --sync_slave_with_master
113
114 --connection slave
115 select * from t1 order by nid;
116
117 --connection master
118 DROP table t1;
119
120 #
121 # Bug #27378 update becomes delete on slave
122 #
123
124 --connection master
125 CREATE TABLE `t1` (
126 `prid` int unsigned NOT NULL,
127 `id_type` enum('IMSI','SIP') NOT NULL,
128 `fkimssub` varchar(50) NOT NULL,
129 `user_id` varchar(20) DEFAULT NULL,
130 `password` varchar(20) DEFAULT NULL,
131 `ptg_nbr` varchar(20) DEFAULT NULL,
132 `old_tmsi` int unsigned DEFAULT NULL,
133 `new_tmsi` int unsigned DEFAULT NULL,
134 `dev_capability` int unsigned DEFAULT NULL,
135 `dev_oid` bigint unsigned DEFAULT NULL,
136 `lac_cell_id` bigint unsigned DEFAULT NULL,
137 `ms_classmark1` int unsigned DEFAULT NULL,
138 `cipher_key` int unsigned DEFAULT NULL,
139 `priid_master` int unsigned DEFAULT NULL,
140 PRIMARY KEY (`prid`),
141 UNIQUE KEY `fkimssub` (`fkimssub`,`ptg_nbr`) USING HASH
142 ) ENGINE=ndbcluster DEFAULT CHARSET=latin1;
143
144 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);
145
146 select count(*) from t1;
147
148 --sync_slave_with_master
149 --connection slave
150 select count(*) from t1;
151
152 --connection master
153 update t1 set dev_oid=dev_oid+1;
154 select count(*) from t1;
155
156 --sync_slave_with_master
157 --connection slave
158 select count(*) from t1;
159
160 --connection master
161 DROP table t1;
162
163 ##################################################################
164 #
165 # Check that retries are made on the slave on some temporary errors
166 #
167
168 #
169 # 1. Deadlock
170 #
171 --connection master
172 CREATE TABLE `t1` ( `nid` int(11) NOT NULL default '0',
173 `nom` char(4) default NULL,
174 `prenom` char(4) default NULL,
175 PRIMARY KEY USING HASH (`nid`))
176 ENGINE=ndbcluster DEFAULT CHARSET=latin1;
177 INSERT INTO t1 VALUES(1,"XYZ1","ABC1");
178
179 # cause a lock on that row on the slave
180 --sync_slave_with_master
181 --connection slave
182 --echo **** On Slave ****
183 BEGIN;
184 UPDATE t1 SET `nom`="LOCK" WHERE `nid`=1;
185
186 # set number of retries low so we fail the retries
187 set GLOBAL replica_transaction_retries=1;
188
189 # now do a change to this row on the master
190 # will deadlock on the slave because of lock above
191 --connection master
192 --echo **** On Master ****
193 UPDATE t1 SET `nom`="DEAD" WHERE `nid`=1;
194
195 # Wait for deadlock to be detected.
196 # When detected, the slave will stop, so we just wait for it to stop.
197 # Use different connection to slave to avoid implicit commit
198 # caused by START SLAVE
199 --connection slave1
200 --echo **** On Slave ****
201
202 # Replication should have stopped, since max retries were not enough.
203 # verify with show slave status
204 # 1205 = ER_LOCK_WAIT_TIMEOUT
205 --let $slave_sql_errno= 1205
206 --let $show_slave_sql_error= 0
207 --source include/wait_for_slave_sql_error.inc
208
209 # now set max retries high enough to succeed, and start slave again
210 set GLOBAL replica_transaction_retries=10;
211 source include/start_slave.inc;
212 # Wait for deadlock to be detected and retried.
213 # We want to wait until at least one retry has been made, but before
214 # the slave stops. currently, there is no safe way to do that: we
215 # would need to access the retry counter, but that is not exposed.
216 # Failing that, we just wait sufficiently long that one but not all
217 # retries have been made. See BUG#35183.
218 sleep 5;
219
220 # Switch back to the slave connection with the lock and commit the
221 # transaction to release lock on row and let replication succeed
222 --connection slave
223 select * from t1 order by nid;
224 COMMIT;
225
226 # verify that the row succeded to be applied on the slave
227 --connection master
228 --sync_slave_with_master
229 --connection slave
230 select * from t1 order by nid;
231
232 # cleanup
233 --connection master
234 DROP TABLE t1;
235
236
237 #
238 # BUG#18094
239 # Slave caches invalid table definition after atlters causes select failure
240 #
241 --connection master
242 CREATE TABLE t1 (c1 INT KEY) ENGINE=NDB;
243
244 INSERT INTO t1 VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10);
245
246 ALTER TABLE t1 ADD c2 INT;
247
248 --sync_slave_with_master
249 connection slave;
250 SELECT * FROM t1 ORDER BY c1;
251
252 connection master;
253
254 INSERT INTO t1 VALUES (11,11),(12,12),(13,13),(14,14);
255
256 --sync_slave_with_master
257 connection slave;
258 SELECT * FROM t1 ORDER BY c1;
259
260 connection master;
261 ALTER TABLE t1 CHANGE c2 c2 TEXT CHARACTER SET utf8mb4;
262 ALTER TABLE t1 CHANGE c2 c2 BLOB;
263
264 --sync_slave_with_master
265 connection slave;
266 # here we would get error 1412 prior to bug
267 SELECT * FROM t1 ORDER BY c1 LIMIT 5;
268
269 --connection master
270 TRUNCATE t1;
271 SELECT count(*) FROM t1;
272 INSERT INTO t1 VALUES (101,NULL),(102,NULL),(103,NULL),(104,NULL),(105,NULL),(106,NULL),(107,NULL),(108,NULL),(109,NULL),(1010,NULL);
273 --sync_slave_with_master
274 connection slave;
275 SELECT count(*) FROM t1;
276 SELECT c1 FROM t1 ORDER BY c1 LIMIT 5;
277
278 # cleanup
279 --connection master
280 DROP TABLE t1;
281
282 # bug#35208
283 # NOTE: should use ndb_log_updated_only
284 create table t1 (a int primary key,
285 b00 int,b01 int,b02 int,b03 int,b04 int,b05 int,b06 int,b07 int,
286 b08 int,b09 int,b10 int,b11 int,b12 int,b13 int,b14 int,b15 int,
287 b16 int,b17 int,b18 int,b19 int,b20 int,b21 int,b22 int,b23 int,
288 b24 int,b25 int,b26 int,b27 int,b28 int,b29 int,b30 int,b31 int,
289 b32 int,b33 int,b34 int,b35 int,b36 int,b37 int,b38 int,b39 int,
290 b40 int,b41 int,b42 int,b43 int,b44 int,b45 int,b46 int,b47 int,
291 b48 int,b49 int,b50 int,b51 int,b52 int,b53 int,b54 int,b55 int,
292 b56 int,b57 int,b58 int,b59 int,b60 int,b61 int,b62 int,b63 int,
293 b64 int,b65 int,b66 int,b67 int,b68 int,b69 int,b70 int) engine=ndb;
294
295 insert into t1 (a,b00) values (1,1);
296 --sync_slave_with_master
297 --connection slave
298 update t1 set b00 = 2 where a = 1;
299 --connection master
300 update t1 set b66 = 1 where a = 1;
301 --sync_slave_with_master
302 # after bug fix result *should* be different on master/slave
303 --connection master
304 select b00 from t1;
305 --connection slave
306 select b00 from t1;
307
308 --connection master
309 DROP TABLE t1;
310
311 #
312 # test the limit of no of attributes in one table
313 # - use while loop to build dynamic sql string
314 #
315 let $i=499;
316 let $separator=;
317 let $sql=create table t1 (;
318 while ($i)
319 {
320 let $sql=$sql$separator c$i int;
321 let $separator=,;
322 dec $i;
323 }
324 let $sql=$sql, c500 varchar(10000);
325 let $sql=$sql, primary key using hash(c1)) engine=ndb charset=latin1;
326 let $sql=$sql partition by key(c1);
327 eval $sql; # eval the sql and create the table
328
329 set @v10 = '0123456789';
330 set @v100 = concat(@v10,@v10,@v10,@v10,@v10,@v10,@v10,@v10,@v10,@v10);
331 set @v1000 =concat(@v100,@v100,@v100,@v100,@v100,@v100,@v100,@v100,@v100,@v100);
332 set @v10000 = concat(@v1000,@v1000,@v1000,@v1000,@v1000,@v1000,@v1000,@v1000,@v1000,@v1000);
333 set @v29000 = concat(@v10000,@v10000,@v1000,@v1000,@v1000,@v1000,@v1000,@v1000,@v1000,@v1000,@v1000);
334
335 insert into t1 (c1,c500) values (1,@v10000), (2,@v10000), (3,@v10000);
336 alter table t1 algorithm=copy, modify c1 int auto_increment;
337 alter table t1 algorithm=inplace, add column c501 bit(1) column_format DYNAMIC;
338 --error ER_GET_ERRMSG
339 alter table t1 algorithm=inplace, add column c502 varchar(18001);
340 show warnings;
341 --sync_slave_with_master
342 --connection slave
343 select length(c500) from t1 order by 1;
344 --connection master
345 delete from t1;
346 drop table t1;
347 --sync_slave_with_master
348 --connection master
349
350 create table t1 (a int primary key, b varchar(29000)) engine = ndb charset=latin1;
351 insert into t1 values (1,@v29000), (2,@v29000);
352 --sync_slave_with_master
353 --connection slave
354 select length(b) from t1 order by 1;
355 --connection master
356 drop table t1;
357 --sync_slave_with_master
358 --connection master
359
360 --source include/rpl_end.inc