| 1 |
include/master-slave.inc |
| 2 |
Warnings: |
| 3 |
Note #### Sending passwords in plain text without SSL/TLS is extremely insecure. |
| 4 |
Note #### Storing MySQL user name or password information in the connection metadata repository is not secure and is therefore not recommended. Please consider using the USER and PASSWORD connection options for START REPLICA; see the 'START REPLICA Syntax' in the MySQL Manual for more information. |
| 5 |
[connection master] |
| 6 |
SET @save_binlog_transaction_dependency_tracking = @@GLOBAL.binlog_transaction_dependency_tracking; |
| 7 |
Warnings: |
| 8 |
Warning 1287 '@@binlog_transaction_dependency_tracking' is deprecated and will be removed in a future release. |
| 9 |
|
| 10 |
SET GLOBAL binlog_transaction_dependency_tracking = WRITESET; |
| 11 |
Warnings: |
| 12 |
Warning 1287 '@@binlog_transaction_dependency_tracking' is deprecated and will be removed in a future release. |
| 13 |
######## 1. Test referential action RESTRICT with WRITESET. ######## |
| 14 |
SELECT @@binlog_transaction_dependency_tracking; |
| 15 |
@@binlog_transaction_dependency_tracking |
| 16 |
WRITESET |
| 17 |
Warnings: |
| 18 |
Warning 1287 '@@binlog_transaction_dependency_tracking' is deprecated and will be removed in a future release. |
| 19 |
CREATE TABLE parent1 (a INT NOT NULL PRIMARY KEY, b VARCHAR(50), c INT, UNIQUE(c)) engine = NDB; |
| 20 |
CREATE TABLE parent2 (a INT NOT NULL PRIMARY KEY, b VARCHAR(50)) engine = NDB; |
| 21 |
CREATE TABLE t1 (a INT PRIMARY KEY) engine = NDB; |
| 22 |
CREATE TABLE child1 ( |
| 23 |
a INT PRIMARY KEY, |
| 24 |
b INT, |
| 25 |
FOREIGN KEY(b) REFERENCES parent1 (c) ON UPDATE RESTRICT |
| 26 |
) engine = NDB; |
| 27 |
CREATE TABLE child2 ( |
| 28 |
a INT PRIMARY KEY, |
| 29 |
b INT, |
| 30 |
FOREIGN KEY(b) REFERENCES parent2 (a) ON DELETE RESTRICT |
| 31 |
) engine = NDB; |
| 32 |
INSERT INTO parent1 VALUES (1, 1, 1), (2, 2, 2); |
| 33 |
INSERT INTO parent2 VALUES (1, 1), (2, 2); |
| 34 |
FLUSH LOGS; |
| 35 |
# Case 2.1, 2.2, 2.3 |
| 36 |
INSERT INTO child1 VALUES (1, 1); |
| 37 |
# Wait for ndb_binlog thread... |
| 38 |
INSERT INTO child2 VALUES (1, 1); |
| 39 |
# Wait for ndb_binlog thread... |
| 40 |
UPDATE child1 SET a = 2; |
| 41 |
# Wait for ndb_binlog thread... |
| 42 |
UPDATE child2 SET a = 2; |
| 43 |
# Wait for ndb_binlog thread... |
| 44 |
DELETE FROM child1; |
| 45 |
# Wait for ndb_binlog thread... |
| 46 |
DELETE FROM child2; |
| 47 |
# Wait for ndb_binlog thread... |
| 48 |
# Case 1.1, 1.2, 1.3 |
| 49 |
INSERT INTO t1 VALUES (1); |
| 50 |
# Wait for ndb_binlog thread... |
| 51 |
INSERT INTO parent1 VALUES (3, 3, 3); |
| 52 |
# Wait for ndb_binlog thread... |
| 53 |
INSERT INTO t1 VALUES (2); |
| 54 |
# Wait for ndb_binlog thread... |
| 55 |
# Case 1.4, 1.5 |
| 56 |
BEGIN; |
| 57 |
INSERT INTO t1 VALUES (3); |
| 58 |
INSERT INTO child1 VALUES (1, 1); |
| 59 |
BEGIN; |
| 60 |
INSERT INTO parent1 VALUEs (4, 4, 4); |
| 61 |
INSERT INTO child2 VALUES (1, 1); |
| 62 |
BEGIN; |
| 63 |
INSERT INTO t1 VALUES (4); |
| 64 |
COMMIT; |
| 65 |
# Wait for ndb_binlog thread... |
| 66 |
COMMIT; |
| 67 |
# Wait for ndb_binlog thread... |
| 68 |
COMMIT; |
| 69 |
# Wait for ndb_binlog thread... |
| 70 |
INSERT INTO t1 VALUES (5); |
| 71 |
# Wait for ndb_binlog thread... |
| 72 |
UPDATE parent1 SET c=5 WHERE a=1; |
| 73 |
ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`child1`, CONSTRAINT `child1_fk_1` FOREIGN KEY (`b`) REFERENCES `parent1` (`c`) ON DELETE NO ACTION ON UPDATE RESTRICT) |
| 74 |
# Wait for ndb_binlog thread... |
| 75 |
DELETE FROM parent2 WHERE a=1; |
| 76 |
ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`child2`, CONSTRAINT `child2_fk_1` FOREIGN KEY (`b`) REFERENCES `parent2` (`a`) ON DELETE RESTRICT ON UPDATE NO ACTION) |
| 77 |
# Wait for ndb_binlog thread... |
| 78 |
# Drop table and flush logs to force binlog to rotate |
| 79 |
DROP TABLE child2, parent2, child1, parent1, t1; |
| 80 |
FLUSH LOGS; |
| 81 |
# Processing binlog... |
| 82 |
include/include/assert_logical_timestamps.inc [0 1;1 2;1 3;2 4;3 5;4 6;1 7;7 8;8 9;8 10;10 11;11 12;11 13;13 14] |
| 83 |
# Verify that replication is correct |
| 84 |
include/sync_slave_sql_with_master.inc |
| 85 |
|
| 86 |
SET GLOBAL binlog_transaction_dependency_tracking = WRITESET_SESSION; |
| 87 |
Warnings: |
| 88 |
Warning 1287 '@@binlog_transaction_dependency_tracking' is deprecated and will be removed in a future release. |
| 89 |
######## 2. Test referential action RESTRICT with WRITESET_SESSION. ######## |
| 90 |
SELECT @@binlog_transaction_dependency_tracking; |
| 91 |
@@binlog_transaction_dependency_tracking |
| 92 |
WRITESET_SESSION |
| 93 |
Warnings: |
| 94 |
Warning 1287 '@@binlog_transaction_dependency_tracking' is deprecated and will be removed in a future release. |
| 95 |
CREATE TABLE parent1 (a INT NOT NULL PRIMARY KEY, b VARCHAR(50), c INT, UNIQUE(c)) engine = NDB; |
| 96 |
CREATE TABLE parent2 (a INT NOT NULL PRIMARY KEY, b VARCHAR(50)) engine = NDB; |
| 97 |
CREATE TABLE t1 (a INT PRIMARY KEY) engine = NDB; |
| 98 |
CREATE TABLE child1 ( |
| 99 |
a INT PRIMARY KEY, |
| 100 |
b INT, |
| 101 |
FOREIGN KEY(b) REFERENCES parent1 (c) ON UPDATE RESTRICT |
| 102 |
) engine = NDB; |
| 103 |
CREATE TABLE child2 ( |
| 104 |
a INT PRIMARY KEY, |
| 105 |
b INT, |
| 106 |
FOREIGN KEY(b) REFERENCES parent2 (a) ON DELETE RESTRICT |
| 107 |
) engine = NDB; |
| 108 |
INSERT INTO parent1 VALUES (1, 1, 1), (2, 2, 2); |
| 109 |
INSERT INTO parent2 VALUES (1, 1), (2, 2); |
| 110 |
FLUSH LOGS; |
| 111 |
# Case 2.1, 2.2, 2.3 |
| 112 |
INSERT INTO child1 VALUES (1, 1); |
| 113 |
# Wait for ndb_binlog thread... |
| 114 |
INSERT INTO child2 VALUES (1, 1); |
| 115 |
# Wait for ndb_binlog thread... |
| 116 |
UPDATE child1 SET a = 2; |
| 117 |
# Wait for ndb_binlog thread... |
| 118 |
UPDATE child2 SET a = 2; |
| 119 |
# Wait for ndb_binlog thread... |
| 120 |
DELETE FROM child1; |
| 121 |
# Wait for ndb_binlog thread... |
| 122 |
DELETE FROM child2; |
| 123 |
# Wait for ndb_binlog thread... |
| 124 |
# Case 1.1, 1.2, 1.3 |
| 125 |
INSERT INTO t1 VALUES (1); |
| 126 |
# Wait for ndb_binlog thread... |
| 127 |
INSERT INTO parent1 VALUES (3, 3, 3); |
| 128 |
# Wait for ndb_binlog thread... |
| 129 |
INSERT INTO t1 VALUES (2); |
| 130 |
# Wait for ndb_binlog thread... |
| 131 |
# Case 1.4, 1.5 |
| 132 |
BEGIN; |
| 133 |
INSERT INTO t1 VALUES (3); |
| 134 |
INSERT INTO child1 VALUES (1, 1); |
| 135 |
BEGIN; |
| 136 |
INSERT INTO parent1 VALUEs (4, 4, 4); |
| 137 |
INSERT INTO child2 VALUES (1, 1); |
| 138 |
BEGIN; |
| 139 |
INSERT INTO t1 VALUES (4); |
| 140 |
COMMIT; |
| 141 |
# Wait for ndb_binlog thread... |
| 142 |
COMMIT; |
| 143 |
# Wait for ndb_binlog thread... |
| 144 |
COMMIT; |
| 145 |
# Wait for ndb_binlog thread... |
| 146 |
INSERT INTO t1 VALUES (5); |
| 147 |
# Wait for ndb_binlog thread... |
| 148 |
UPDATE parent1 SET c=5 WHERE a=1; |
| 149 |
ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`child1`, CONSTRAINT `child1_fk_1` FOREIGN KEY (`b`) REFERENCES `parent1` (`c`) ON DELETE NO ACTION ON UPDATE RESTRICT) |
| 150 |
# Wait for ndb_binlog thread... |
| 151 |
DELETE FROM parent2 WHERE a=1; |
| 152 |
ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`child2`, CONSTRAINT `child2_fk_1` FOREIGN KEY (`b`) REFERENCES `parent2` (`a`) ON DELETE RESTRICT ON UPDATE NO ACTION) |
| 153 |
# Wait for ndb_binlog thread... |
| 154 |
# Drop table and flush logs to force binlog to rotate |
| 155 |
DROP TABLE child2, parent2, child1, parent1, t1; |
| 156 |
FLUSH LOGS; |
| 157 |
# Processing binlog... |
| 158 |
include/include/assert_logical_timestamps.inc [0 1;1 2;2 3;3 4;4 5;5 6;6 7;7 8;8 9;9 10;10 11;11 12;12 13;13 14] |
| 159 |
# Verify that replication is correct |
| 160 |
include/sync_slave_sql_with_master.inc |
| 161 |
|
| 162 |
SET GLOBAL binlog_transaction_dependency_tracking = WRITESET; |
| 163 |
Warnings: |
| 164 |
Warning 1287 '@@binlog_transaction_dependency_tracking' is deprecated and will be removed in a future release. |
| 165 |
######## 3. Test referential action NO ACTION with WRITESET. ######## |
| 166 |
SELECT @@binlog_transaction_dependency_tracking; |
| 167 |
@@binlog_transaction_dependency_tracking |
| 168 |
WRITESET |
| 169 |
Warnings: |
| 170 |
Warning 1287 '@@binlog_transaction_dependency_tracking' is deprecated and will be removed in a future release. |
| 171 |
CREATE TABLE parent1 (a INT NOT NULL PRIMARY KEY, b VARCHAR(50), c INT, UNIQUE(c)) engine = NDB; |
| 172 |
CREATE TABLE parent2 (a INT NOT NULL PRIMARY KEY, b VARCHAR(50)) engine = NDB; |
| 173 |
CREATE TABLE t1 (a INT PRIMARY KEY) engine = NDB; |
| 174 |
CREATE TABLE child1 ( |
| 175 |
a INT PRIMARY KEY, |
| 176 |
b INT, |
| 177 |
FOREIGN KEY(b) REFERENCES parent1 (c) ON UPDATE NO ACTION |
| 178 |
) engine = NDB; |
| 179 |
CREATE TABLE child2 ( |
| 180 |
a INT PRIMARY KEY, |
| 181 |
b INT, |
| 182 |
FOREIGN KEY(b) REFERENCES parent2 (a) ON DELETE NO ACTION |
| 183 |
) engine = NDB; |
| 184 |
INSERT INTO parent1 VALUES (1, 1, 1), (2, 2, 2); |
| 185 |
INSERT INTO parent2 VALUES (1, 1), (2, 2); |
| 186 |
FLUSH LOGS; |
| 187 |
# Case 2.1, 2.2, 2.3 |
| 188 |
INSERT INTO child1 VALUES (1, 1); |
| 189 |
# Wait for ndb_binlog thread... |
| 190 |
INSERT INTO child2 VALUES (1, 1); |
| 191 |
# Wait for ndb_binlog thread... |
| 192 |
UPDATE child1 SET a = 2; |
| 193 |
# Wait for ndb_binlog thread... |
| 194 |
UPDATE child2 SET a = 2; |
| 195 |
# Wait for ndb_binlog thread... |
| 196 |
DELETE FROM child1; |
| 197 |
# Wait for ndb_binlog thread... |
| 198 |
DELETE FROM child2; |
| 199 |
# Wait for ndb_binlog thread... |
| 200 |
# Case 1.1, 1.2, 1.3 |
| 201 |
INSERT INTO t1 VALUES (1); |
| 202 |
# Wait for ndb_binlog thread... |
| 203 |
INSERT INTO parent1 VALUES (3, 3, 3); |
| 204 |
# Wait for ndb_binlog thread... |
| 205 |
INSERT INTO t1 VALUES (2); |
| 206 |
# Wait for ndb_binlog thread... |
| 207 |
# Case 1.4, 1.5 |
| 208 |
BEGIN; |
| 209 |
INSERT INTO t1 VALUES (3); |
| 210 |
INSERT INTO child1 VALUES (1, 1); |
| 211 |
BEGIN; |
| 212 |
INSERT INTO parent1 VALUEs (4, 4, 4); |
| 213 |
INSERT INTO child2 VALUES (1, 1); |
| 214 |
BEGIN; |
| 215 |
INSERT INTO t1 VALUES (4); |
| 216 |
COMMIT; |
| 217 |
# Wait for ndb_binlog thread... |
| 218 |
COMMIT; |
| 219 |
# Wait for ndb_binlog thread... |
| 220 |
COMMIT; |
| 221 |
# Wait for ndb_binlog thread... |
| 222 |
INSERT INTO t1 VALUES (5); |
| 223 |
# Wait for ndb_binlog thread... |
| 224 |
UPDATE parent1 SET c=5 WHERE a=1; |
| 225 |
ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`child1`, CONSTRAINT `child1_fk_1` FOREIGN KEY (`b`) REFERENCES `parent1` (`c`) ON DELETE NO ACTION ON UPDATE NO ACTION) |
| 226 |
# Wait for ndb_binlog thread... |
| 227 |
DELETE FROM parent2 WHERE a=1; |
| 228 |
ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`child2`, CONSTRAINT `child2_fk_1` FOREIGN KEY (`b`) REFERENCES `parent2` (`a`) ON DELETE NO ACTION ON UPDATE NO ACTION) |
| 229 |
# Wait for ndb_binlog thread... |
| 230 |
# Drop table and flush logs to force binlog to rotate |
| 231 |
DROP TABLE child2, parent2, child1, parent1, t1; |
| 232 |
FLUSH LOGS; |
| 233 |
# Processing binlog... |
| 234 |
include/include/assert_logical_timestamps.inc [0 1;1 2;1 3;2 4;3 5;4 6;1 7;7 8;8 9;8 10;10 11;11 12;11 13;13 14] |
| 235 |
# Verify that replication is correct |
| 236 |
include/sync_slave_sql_with_master.inc |
| 237 |
|
| 238 |
SET GLOBAL binlog_transaction_dependency_tracking = WRITESET_SESSION; |
| 239 |
Warnings: |
| 240 |
Warning 1287 '@@binlog_transaction_dependency_tracking' is deprecated and will be removed in a future release. |
| 241 |
######## 4. Test referential action NO ACTION with WRITESET_SESSION. ######## |
| 242 |
SELECT @@binlog_transaction_dependency_tracking; |
| 243 |
@@binlog_transaction_dependency_tracking |
| 244 |
WRITESET_SESSION |
| 245 |
Warnings: |
| 246 |
Warning 1287 '@@binlog_transaction_dependency_tracking' is deprecated and will be removed in a future release. |
| 247 |
CREATE TABLE parent1 (a INT NOT NULL PRIMARY KEY, b VARCHAR(50), c INT, UNIQUE(c)) engine = NDB; |
| 248 |
CREATE TABLE parent2 (a INT NOT NULL PRIMARY KEY, b VARCHAR(50)) engine = NDB; |
| 249 |
CREATE TABLE t1 (a INT PRIMARY KEY) engine = NDB; |
| 250 |
CREATE TABLE child1 ( |
| 251 |
a INT PRIMARY KEY, |
| 252 |
b INT, |
| 253 |
FOREIGN KEY(b) REFERENCES parent1 (c) ON UPDATE NO ACTION |
| 254 |
) engine = NDB; |
| 255 |
CREATE TABLE child2 ( |
| 256 |
a INT PRIMARY KEY, |
| 257 |
b INT, |
| 258 |
FOREIGN KEY(b) REFERENCES parent2 (a) ON DELETE NO ACTION |
| 259 |
) engine = NDB; |
| 260 |
INSERT INTO parent1 VALUES (1, 1, 1), (2, 2, 2); |
| 261 |
INSERT INTO parent2 VALUES (1, 1), (2, 2); |
| 262 |
FLUSH LOGS; |
| 263 |
# Case 2.1, 2.2, 2.3 |
| 264 |
INSERT INTO child1 VALUES (1, 1); |
| 265 |
# Wait for ndb_binlog thread... |
| 266 |
INSERT INTO child2 VALUES (1, 1); |
| 267 |
# Wait for ndb_binlog thread... |
| 268 |
UPDATE child1 SET a = 2; |
| 269 |
# Wait for ndb_binlog thread... |
| 270 |
UPDATE child2 SET a = 2; |
| 271 |
# Wait for ndb_binlog thread... |
| 272 |
DELETE FROM child1; |
| 273 |
# Wait for ndb_binlog thread... |
| 274 |
DELETE FROM child2; |
| 275 |
# Wait for ndb_binlog thread... |
| 276 |
# Case 1.1, 1.2, 1.3 |
| 277 |
INSERT INTO t1 VALUES (1); |
| 278 |
# Wait for ndb_binlog thread... |
| 279 |
INSERT INTO parent1 VALUES (3, 3, 3); |
| 280 |
# Wait for ndb_binlog thread... |
| 281 |
INSERT INTO t1 VALUES (2); |
| 282 |
# Wait for ndb_binlog thread... |
| 283 |
# Case 1.4, 1.5 |
| 284 |
BEGIN; |
| 285 |
INSERT INTO t1 VALUES (3); |
| 286 |
INSERT INTO child1 VALUES (1, 1); |
| 287 |
BEGIN; |
| 288 |
INSERT INTO parent1 VALUEs (4, 4, 4); |
| 289 |
INSERT INTO child2 VALUES (1, 1); |
| 290 |
BEGIN; |
| 291 |
INSERT INTO t1 VALUES (4); |
| 292 |
COMMIT; |
| 293 |
# Wait for ndb_binlog thread... |
| 294 |
COMMIT; |
| 295 |
# Wait for ndb_binlog thread... |
| 296 |
COMMIT; |
| 297 |
# Wait for ndb_binlog thread... |
| 298 |
INSERT INTO t1 VALUES (5); |
| 299 |
# Wait for ndb_binlog thread... |
| 300 |
UPDATE parent1 SET c=5 WHERE a=1; |
| 301 |
ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`child1`, CONSTRAINT `child1_fk_1` FOREIGN KEY (`b`) REFERENCES `parent1` (`c`) ON DELETE NO ACTION ON UPDATE NO ACTION) |
| 302 |
# Wait for ndb_binlog thread... |
| 303 |
DELETE FROM parent2 WHERE a=1; |
| 304 |
ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`child2`, CONSTRAINT `child2_fk_1` FOREIGN KEY (`b`) REFERENCES `parent2` (`a`) ON DELETE NO ACTION ON UPDATE NO ACTION) |
| 305 |
# Wait for ndb_binlog thread... |
| 306 |
# Drop table and flush logs to force binlog to rotate |
| 307 |
DROP TABLE child2, parent2, child1, parent1, t1; |
| 308 |
FLUSH LOGS; |
| 309 |
# Processing binlog... |
| 310 |
include/include/assert_logical_timestamps.inc [0 1;1 2;2 3;3 4;4 5;5 6;6 7;7 8;8 9;9 10;10 11;11 12;12 13;13 14] |
| 311 |
# Verify that replication is correct |
| 312 |
include/sync_slave_sql_with_master.inc |
| 313 |
|
| 314 |
SET GLOBAL binlog_transaction_dependency_tracking = WRITESET; |
| 315 |
Warnings: |
| 316 |
Warning 1287 '@@binlog_transaction_dependency_tracking' is deprecated and will be removed in a future release. |
| 317 |
######## 5. Test referential action CASCADE with WRITESET. ######## |
| 318 |
SELECT @@binlog_transaction_dependency_tracking; |
| 319 |
@@binlog_transaction_dependency_tracking |
| 320 |
WRITESET |
| 321 |
Warnings: |
| 322 |
Warning 1287 '@@binlog_transaction_dependency_tracking' is deprecated and will be removed in a future release. |
| 323 |
CREATE TABLE parent1 (a INT NOT NULL PRIMARY KEY, b VARCHAR(50), c INT, UNIQUE(c)) engine = NDB; |
| 324 |
CREATE TABLE parent2 (a INT NOT NULL PRIMARY KEY, b VARCHAR(50)) engine = NDB; |
| 325 |
CREATE TABLE t1 (a INT PRIMARY KEY) engine = NDB; |
| 326 |
CREATE TABLE child1 ( |
| 327 |
a INT PRIMARY KEY, |
| 328 |
b INT, |
| 329 |
FOREIGN KEY(b) REFERENCES parent1 (c) ON UPDATE CASCADE |
| 330 |
) engine = NDB; |
| 331 |
CREATE TABLE child2 ( |
| 332 |
a INT PRIMARY KEY, |
| 333 |
b INT, |
| 334 |
FOREIGN KEY(b) REFERENCES parent2 (a) ON DELETE CASCADE |
| 335 |
) engine = NDB; |
| 336 |
INSERT INTO parent1 VALUES (1, 1, 1), (2, 2, 2); |
| 337 |
INSERT INTO parent2 VALUES (1, 1), (2, 2); |
| 338 |
FLUSH LOGS; |
| 339 |
# Case 2.1, 2.2, 2.3 |
| 340 |
INSERT INTO child1 VALUES (1, 1); |
| 341 |
# Wait for ndb_binlog thread... |
| 342 |
INSERT INTO child2 VALUES (1, 1); |
| 343 |
# Wait for ndb_binlog thread... |
| 344 |
UPDATE child1 SET a = 2; |
| 345 |
# Wait for ndb_binlog thread... |
| 346 |
UPDATE child2 SET a = 2; |
| 347 |
# Wait for ndb_binlog thread... |
| 348 |
DELETE FROM child1; |
| 349 |
# Wait for ndb_binlog thread... |
| 350 |
DELETE FROM child2; |
| 351 |
# Wait for ndb_binlog thread... |
| 352 |
# Case 1.1, 1.2, 1.3 |
| 353 |
INSERT INTO t1 VALUES (1); |
| 354 |
# Wait for ndb_binlog thread... |
| 355 |
INSERT INTO parent1 VALUES (3, 3, 3); |
| 356 |
# Wait for ndb_binlog thread... |
| 357 |
INSERT INTO t1 VALUES (2); |
| 358 |
# Wait for ndb_binlog thread... |
| 359 |
# Case 1.4, 1.5 |
| 360 |
BEGIN; |
| 361 |
INSERT INTO t1 VALUES (3); |
| 362 |
INSERT INTO child1 VALUES (1, 1); |
| 363 |
BEGIN; |
| 364 |
INSERT INTO parent1 VALUEs (4, 4, 4); |
| 365 |
INSERT INTO child2 VALUES (1, 1); |
| 366 |
BEGIN; |
| 367 |
INSERT INTO t1 VALUES (4); |
| 368 |
COMMIT; |
| 369 |
# Wait for ndb_binlog thread... |
| 370 |
COMMIT; |
| 371 |
# Wait for ndb_binlog thread... |
| 372 |
COMMIT; |
| 373 |
# Wait for ndb_binlog thread... |
| 374 |
INSERT INTO t1 VALUES (5); |
| 375 |
# Wait for ndb_binlog thread... |
| 376 |
UPDATE parent1 SET c=5 WHERE a=1; |
| 377 |
# Wait for ndb_binlog thread... |
| 378 |
DELETE FROM parent2 WHERE a=1; |
| 379 |
# Wait for ndb_binlog thread... |
| 380 |
# Drop table and flush logs to force binlog to rotate |
| 381 |
DROP TABLE child2, parent2, child1, parent1, t1; |
| 382 |
FLUSH LOGS; |
| 383 |
# Processing binlog... |
| 384 |
include/include/assert_logical_timestamps.inc [0 1;1 2;1 3;2 4;3 5;4 6;1 7;7 8;8 9;8 10;10 11;11 12;11 13;13 14;14 15;15 16] |
| 385 |
# Verify that replication is correct |
| 386 |
include/sync_slave_sql_with_master.inc |
| 387 |
|
| 388 |
SET GLOBAL binlog_transaction_dependency_tracking = WRITESET_SESSION; |
| 389 |
Warnings: |
| 390 |
Warning 1287 '@@binlog_transaction_dependency_tracking' is deprecated and will be removed in a future release. |
| 391 |
######## 6. Test referential action CASCADE with WRITESET_SESSION. ######## |
| 392 |
SELECT @@binlog_transaction_dependency_tracking; |
| 393 |
@@binlog_transaction_dependency_tracking |
| 394 |
WRITESET_SESSION |
| 395 |
Warnings: |
| 396 |
Warning 1287 '@@binlog_transaction_dependency_tracking' is deprecated and will be removed in a future release. |
| 397 |
CREATE TABLE parent1 (a INT NOT NULL PRIMARY KEY, b VARCHAR(50), c INT, UNIQUE(c)) engine = NDB; |
| 398 |
CREATE TABLE parent2 (a INT NOT NULL PRIMARY KEY, b VARCHAR(50)) engine = NDB; |
| 399 |
CREATE TABLE t1 (a INT PRIMARY KEY) engine = NDB; |
| 400 |
CREATE TABLE child1 ( |
| 401 |
a INT PRIMARY KEY, |
| 402 |
b INT, |
| 403 |
FOREIGN KEY(b) REFERENCES parent1 (c) ON UPDATE CASCADE |
| 404 |
) engine = NDB; |
| 405 |
CREATE TABLE child2 ( |
| 406 |
a INT PRIMARY KEY, |
| 407 |
b INT, |
| 408 |
FOREIGN KEY(b) REFERENCES parent2 (a) ON DELETE CASCADE |
| 409 |
) engine = NDB; |
| 410 |
INSERT INTO parent1 VALUES (1, 1, 1), (2, 2, 2); |
| 411 |
INSERT INTO parent2 VALUES (1, 1), (2, 2); |
| 412 |
FLUSH LOGS; |
| 413 |
# Case 2.1, 2.2, 2.3 |
| 414 |
INSERT INTO child1 VALUES (1, 1); |
| 415 |
# Wait for ndb_binlog thread... |
| 416 |
INSERT INTO child2 VALUES (1, 1); |
| 417 |
# Wait for ndb_binlog thread... |
| 418 |
UPDATE child1 SET a = 2; |
| 419 |
# Wait for ndb_binlog thread... |
| 420 |
UPDATE child2 SET a = 2; |
| 421 |
# Wait for ndb_binlog thread... |
| 422 |
DELETE FROM child1; |
| 423 |
# Wait for ndb_binlog thread... |
| 424 |
DELETE FROM child2; |
| 425 |
# Wait for ndb_binlog thread... |
| 426 |
# Case 1.1, 1.2, 1.3 |
| 427 |
INSERT INTO t1 VALUES (1); |
| 428 |
# Wait for ndb_binlog thread... |
| 429 |
INSERT INTO parent1 VALUES (3, 3, 3); |
| 430 |
# Wait for ndb_binlog thread... |
| 431 |
INSERT INTO t1 VALUES (2); |
| 432 |
# Wait for ndb_binlog thread... |
| 433 |
# Case 1.4, 1.5 |
| 434 |
BEGIN; |
| 435 |
INSERT INTO t1 VALUES (3); |
| 436 |
INSERT INTO child1 VALUES (1, 1); |
| 437 |
BEGIN; |
| 438 |
INSERT INTO parent1 VALUEs (4, 4, 4); |
| 439 |
INSERT INTO child2 VALUES (1, 1); |
| 440 |
BEGIN; |
| 441 |
INSERT INTO t1 VALUES (4); |
| 442 |
COMMIT; |
| 443 |
# Wait for ndb_binlog thread... |
| 444 |
COMMIT; |
| 445 |
# Wait for ndb_binlog thread... |
| 446 |
COMMIT; |
| 447 |
# Wait for ndb_binlog thread... |
| 448 |
INSERT INTO t1 VALUES (5); |
| 449 |
# Wait for ndb_binlog thread... |
| 450 |
UPDATE parent1 SET c=5 WHERE a=1; |
| 451 |
# Wait for ndb_binlog thread... |
| 452 |
DELETE FROM parent2 WHERE a=1; |
| 453 |
# Wait for ndb_binlog thread... |
| 454 |
# Drop table and flush logs to force binlog to rotate |
| 455 |
DROP TABLE child2, parent2, child1, parent1, t1; |
| 456 |
FLUSH LOGS; |
| 457 |
# Processing binlog... |
| 458 |
include/include/assert_logical_timestamps.inc [0 1;1 2;2 3;3 4;4 5;5 6;6 7;7 8;8 9;9 10;10 11;11 12;12 13;13 14;14 15;15 16] |
| 459 |
# Verify that replication is correct |
| 460 |
include/sync_slave_sql_with_master.inc |
| 461 |
|
| 462 |
SET GLOBAL binlog_transaction_dependency_tracking = WRITESET; |
| 463 |
Warnings: |
| 464 |
Warning 1287 '@@binlog_transaction_dependency_tracking' is deprecated and will be removed in a future release. |
| 465 |
######## 7. Test referential action SET NULL with WRITESET. ######## |
| 466 |
SELECT @@binlog_transaction_dependency_tracking; |
| 467 |
@@binlog_transaction_dependency_tracking |
| 468 |
WRITESET |
| 469 |
Warnings: |
| 470 |
Warning 1287 '@@binlog_transaction_dependency_tracking' is deprecated and will be removed in a future release. |
| 471 |
CREATE TABLE parent1 (a INT NOT NULL PRIMARY KEY, b VARCHAR(50), c INT, UNIQUE(c)) engine = NDB; |
| 472 |
CREATE TABLE parent2 (a INT NOT NULL PRIMARY KEY, b VARCHAR(50)) engine = NDB; |
| 473 |
CREATE TABLE t1 (a INT PRIMARY KEY) engine = NDB; |
| 474 |
CREATE TABLE child1 ( |
| 475 |
a INT PRIMARY KEY, |
| 476 |
b INT, |
| 477 |
FOREIGN KEY(b) REFERENCES parent1 (c) ON UPDATE SET NULL |
| 478 |
) engine = NDB; |
| 479 |
CREATE TABLE child2 ( |
| 480 |
a INT PRIMARY KEY, |
| 481 |
b INT, |
| 482 |
FOREIGN KEY(b) REFERENCES parent2 (a) ON DELETE SET NULL |
| 483 |
) engine = NDB; |
| 484 |
INSERT INTO parent1 VALUES (1, 1, 1), (2, 2, 2); |
| 485 |
INSERT INTO parent2 VALUES (1, 1), (2, 2); |
| 486 |
FLUSH LOGS; |
| 487 |
# Case 2.1, 2.2, 2.3 |
| 488 |
INSERT INTO child1 VALUES (1, 1); |
| 489 |
# Wait for ndb_binlog thread... |
| 490 |
INSERT INTO child2 VALUES (1, 1); |
| 491 |
# Wait for ndb_binlog thread... |
| 492 |
UPDATE child1 SET a = 2; |
| 493 |
# Wait for ndb_binlog thread... |
| 494 |
UPDATE child2 SET a = 2; |
| 495 |
# Wait for ndb_binlog thread... |
| 496 |
DELETE FROM child1; |
| 497 |
# Wait for ndb_binlog thread... |
| 498 |
DELETE FROM child2; |
| 499 |
# Wait for ndb_binlog thread... |
| 500 |
# Case 1.1, 1.2, 1.3 |
| 501 |
INSERT INTO t1 VALUES (1); |
| 502 |
# Wait for ndb_binlog thread... |
| 503 |
INSERT INTO parent1 VALUES (3, 3, 3); |
| 504 |
# Wait for ndb_binlog thread... |
| 505 |
INSERT INTO t1 VALUES (2); |
| 506 |
# Wait for ndb_binlog thread... |
| 507 |
# Case 1.4, 1.5 |
| 508 |
BEGIN; |
| 509 |
INSERT INTO t1 VALUES (3); |
| 510 |
INSERT INTO child1 VALUES (1, 1); |
| 511 |
BEGIN; |
| 512 |
INSERT INTO parent1 VALUEs (4, 4, 4); |
| 513 |
INSERT INTO child2 VALUES (1, 1); |
| 514 |
BEGIN; |
| 515 |
INSERT INTO t1 VALUES (4); |
| 516 |
COMMIT; |
| 517 |
# Wait for ndb_binlog thread... |
| 518 |
COMMIT; |
| 519 |
# Wait for ndb_binlog thread... |
| 520 |
COMMIT; |
| 521 |
# Wait for ndb_binlog thread... |
| 522 |
INSERT INTO t1 VALUES (5); |
| 523 |
# Wait for ndb_binlog thread... |
| 524 |
UPDATE parent1 SET c=5 WHERE a=1; |
| 525 |
# Wait for ndb_binlog thread... |
| 526 |
DELETE FROM parent2 WHERE a=1; |
| 527 |
# Wait for ndb_binlog thread... |
| 528 |
# Drop table and flush logs to force binlog to rotate |
| 529 |
DROP TABLE child2, parent2, child1, parent1, t1; |
| 530 |
FLUSH LOGS; |
| 531 |
# Processing binlog... |
| 532 |
include/include/assert_logical_timestamps.inc [0 1;1 2;1 3;2 4;3 5;4 6;1 7;7 8;8 9;8 10;10 11;11 12;11 13;13 14;14 15;15 16] |
| 533 |
# Verify that replication is correct |
| 534 |
include/sync_slave_sql_with_master.inc |
| 535 |
|
| 536 |
SET GLOBAL binlog_transaction_dependency_tracking = WRITESET_SESSION; |
| 537 |
Warnings: |
| 538 |
Warning 1287 '@@binlog_transaction_dependency_tracking' is deprecated and will be removed in a future release. |
| 539 |
######## 8. Test referential action SET NULL with WRITESET_SESSION. ######## |
| 540 |
SELECT @@binlog_transaction_dependency_tracking; |
| 541 |
@@binlog_transaction_dependency_tracking |
| 542 |
WRITESET_SESSION |
| 543 |
Warnings: |
| 544 |
Warning 1287 '@@binlog_transaction_dependency_tracking' is deprecated and will be removed in a future release. |
| 545 |
CREATE TABLE parent1 (a INT NOT NULL PRIMARY KEY, b VARCHAR(50), c INT, UNIQUE(c)) engine = NDB; |
| 546 |
CREATE TABLE parent2 (a INT NOT NULL PRIMARY KEY, b VARCHAR(50)) engine = NDB; |
| 547 |
CREATE TABLE t1 (a INT PRIMARY KEY) engine = NDB; |
| 548 |
CREATE TABLE child1 ( |
| 549 |
a INT PRIMARY KEY, |
| 550 |
b INT, |
| 551 |
FOREIGN KEY(b) REFERENCES parent1 (c) ON UPDATE SET NULL |
| 552 |
) engine = NDB; |
| 553 |
CREATE TABLE child2 ( |
| 554 |
a INT PRIMARY KEY, |
| 555 |
b INT, |
| 556 |
FOREIGN KEY(b) REFERENCES parent2 (a) ON DELETE SET NULL |
| 557 |
) engine = NDB; |
| 558 |
INSERT INTO parent1 VALUES (1, 1, 1), (2, 2, 2); |
| 559 |
INSERT INTO parent2 VALUES (1, 1), (2, 2); |
| 560 |
FLUSH LOGS; |
| 561 |
# Case 2.1, 2.2, 2.3 |
| 562 |
INSERT INTO child1 VALUES (1, 1); |
| 563 |
# Wait for ndb_binlog thread... |
| 564 |
INSERT INTO child2 VALUES (1, 1); |
| 565 |
# Wait for ndb_binlog thread... |
| 566 |
UPDATE child1 SET a = 2; |
| 567 |
# Wait for ndb_binlog thread... |
| 568 |
UPDATE child2 SET a = 2; |
| 569 |
# Wait for ndb_binlog thread... |
| 570 |
DELETE FROM child1; |
| 571 |
# Wait for ndb_binlog thread... |
| 572 |
DELETE FROM child2; |
| 573 |
# Wait for ndb_binlog thread... |
| 574 |
# Case 1.1, 1.2, 1.3 |
| 575 |
INSERT INTO t1 VALUES (1); |
| 576 |
# Wait for ndb_binlog thread... |
| 577 |
INSERT INTO parent1 VALUES (3, 3, 3); |
| 578 |
# Wait for ndb_binlog thread... |
| 579 |
INSERT INTO t1 VALUES (2); |
| 580 |
# Wait for ndb_binlog thread... |
| 581 |
# Case 1.4, 1.5 |
| 582 |
BEGIN; |
| 583 |
INSERT INTO t1 VALUES (3); |
| 584 |
INSERT INTO child1 VALUES (1, 1); |
| 585 |
BEGIN; |
| 586 |
INSERT INTO parent1 VALUEs (4, 4, 4); |
| 587 |
INSERT INTO child2 VALUES (1, 1); |
| 588 |
BEGIN; |
| 589 |
INSERT INTO t1 VALUES (4); |
| 590 |
COMMIT; |
| 591 |
# Wait for ndb_binlog thread... |
| 592 |
COMMIT; |
| 593 |
# Wait for ndb_binlog thread... |
| 594 |
COMMIT; |
| 595 |
# Wait for ndb_binlog thread... |
| 596 |
INSERT INTO t1 VALUES (5); |
| 597 |
# Wait for ndb_binlog thread... |
| 598 |
UPDATE parent1 SET c=5 WHERE a=1; |
| 599 |
# Wait for ndb_binlog thread... |
| 600 |
DELETE FROM parent2 WHERE a=1; |
| 601 |
# Wait for ndb_binlog thread... |
| 602 |
# Drop table and flush logs to force binlog to rotate |
| 603 |
DROP TABLE child2, parent2, child1, parent1, t1; |
| 604 |
FLUSH LOGS; |
| 605 |
# Processing binlog... |
| 606 |
include/include/assert_logical_timestamps.inc [0 1;1 2;2 3;3 4;4 5;5 6;6 7;7 8;8 9;9 10;10 11;11 12;12 13;13 14;14 15;15 16] |
| 607 |
# Verify that replication is correct |
| 608 |
include/sync_slave_sql_with_master.inc |
| 609 |
|
| 610 |
# Clean up |
| 611 |
SET @@GLOBAL.binlog_transaction_dependency_tracking= @save_binlog_transaction_dependency_tracking; |
| 612 |
Warnings: |
| 613 |
Warning 1287 '@@binlog_transaction_dependency_tracking' is deprecated and will be removed in a future release. |
| 614 |
include/rpl_end.inc |