| 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 |
CREATE TABLE tmyisam (a int) ENGINE = MYISAM; |
| 7 |
CREATE TABLE tinnodb (a int) ENGINE = INNODB; |
| 8 |
CREATE TABLE tndb (a int) ENGINE = NDB; |
| 9 |
SHOW CREATE TABLE tmyisam; |
| 10 |
Table Create Table |
| 11 |
tmyisam CREATE TABLE `tmyisam` ( |
| 12 |
`a` int DEFAULT NULL |
| 13 |
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
| 14 |
SHOW CREATE TABLE tinnodb; |
| 15 |
Table Create Table |
| 16 |
tinnodb CREATE TABLE `tinnodb` ( |
| 17 |
`a` int DEFAULT NULL |
| 18 |
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
| 19 |
SHOW CREATE TABLE tndb; |
| 20 |
Table Create Table |
| 21 |
tndb CREATE TABLE `tndb` ( |
| 22 |
`a` int DEFAULT NULL |
| 23 |
) ENGINE=ndbcluster DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
| 24 |
[on master] |
| 25 |
==== Single-engine transactions ==== |
| 26 |
---- autocommitted ---- |
| 27 |
SET AUTOCOMMIT = 1; |
| 28 |
INSERT INTO tmyisam VALUES (0); |
| 29 |
INSERT INTO tinnodb VALUES (1); |
| 30 |
INSERT INTO tndb VALUES (2); |
| 31 |
---- committed with BEGIN ---- |
| 32 |
BEGIN; |
| 33 |
INSERT INTO tmyisam VALUES (3); |
| 34 |
INSERT INTO tmyisam VALUES (4); |
| 35 |
COMMIT; |
| 36 |
BEGIN; |
| 37 |
INSERT INTO tinnodb VALUES (5); |
| 38 |
INSERT INTO tinnodb VALUES (6); |
| 39 |
COMMIT; |
| 40 |
BEGIN; |
| 41 |
INSERT INTO tndb VALUES (7); |
| 42 |
INSERT INTO tndb VALUES (8); |
| 43 |
COMMIT; |
| 44 |
---- rolled back with BEGIN ---- |
| 45 |
BEGIN; |
| 46 |
INSERT INTO tmyisam VALUES (9); |
| 47 |
INSERT INTO tmyisam VALUES (10); |
| 48 |
ROLLBACK; |
| 49 |
Warnings: |
| 50 |
Warning 1196 Some non-transactional changed tables couldn't be rolled back |
| 51 |
BEGIN; |
| 52 |
INSERT INTO tinnodb VALUES (11); |
| 53 |
INSERT INTO tinnodb VALUES (12); |
| 54 |
ROLLBACK; |
| 55 |
BEGIN; |
| 56 |
INSERT INTO tndb VALUES (13); |
| 57 |
INSERT INTO tndb VALUES (14); |
| 58 |
ROLLBACK; |
| 59 |
---- committed with AUTOCOMMIT = 0 ---- |
| 60 |
SET AUTOCOMMIT = 0; |
| 61 |
INSERT INTO tmyisam VALUES (15); |
| 62 |
INSERT INTO tmyisam VALUES (16); |
| 63 |
COMMIT; |
| 64 |
INSERT INTO tinnodb VALUES (17); |
| 65 |
INSERT INTO tinnodb VALUES (18); |
| 66 |
COMMIT; |
| 67 |
INSERT INTO tndb VALUES (19); |
| 68 |
INSERT INTO tndb VALUES (20); |
| 69 |
COMMIT; |
| 70 |
---- rolled back with AUTOCOMMIT = 0 ---- |
| 71 |
INSERT INTO tmyisam VALUES (21); |
| 72 |
INSERT INTO tmyisam VALUES (22); |
| 73 |
ROLLBACK; |
| 74 |
Warnings: |
| 75 |
Warning 1196 Some non-transactional changed tables couldn't be rolled back |
| 76 |
INSERT INTO tinnodb VALUES (23); |
| 77 |
INSERT INTO tinnodb VALUES (24); |
| 78 |
ROLLBACK; |
| 79 |
INSERT INTO tndb VALUES (25); |
| 80 |
INSERT INTO tndb VALUES (26); |
| 81 |
ROLLBACK; |
| 82 |
SET AUTOCOMMIT = 1; |
| 83 |
==== MyISAM + InnoDB ==== |
| 84 |
---- committed with BEGIN ---- |
| 85 |
BEGIN; |
| 86 |
INSERT INTO tmyisam VALUES (27); |
| 87 |
INSERT INTO tinnodb VALUES (28); |
| 88 |
COMMIT; |
| 89 |
BEGIN; |
| 90 |
INSERT INTO tinnodb VALUES (29); |
| 91 |
INSERT INTO tmyisam VALUES (30); |
| 92 |
COMMIT; |
| 93 |
---- rolled back with BEGIN ---- |
| 94 |
BEGIN; |
| 95 |
INSERT INTO tmyisam VALUES (31); |
| 96 |
INSERT INTO tinnodb VALUES (32); |
| 97 |
ROLLBACK; |
| 98 |
Warnings: |
| 99 |
Warning 1196 Some non-transactional changed tables couldn't be rolled back |
| 100 |
BEGIN; |
| 101 |
INSERT INTO tinnodb VALUES (33); |
| 102 |
INSERT INTO tmyisam VALUES (34); |
| 103 |
ROLLBACK; |
| 104 |
Warnings: |
| 105 |
Warning 1196 Some non-transactional changed tables couldn't be rolled back |
| 106 |
---- committed with AUTOCOMMIT = 0 ---- |
| 107 |
SET AUTOCOMMIT = 0; |
| 108 |
INSERT INTO tmyisam VALUES (35); |
| 109 |
INSERT INTO tinnodb VALUES (36); |
| 110 |
COMMIT; |
| 111 |
INSERT INTO tinnodb VALUES (37); |
| 112 |
INSERT INTO tmyisam VALUES (38); |
| 113 |
COMMIT; |
| 114 |
---- rolled back with AUTOCOMMIT = 0 ---- |
| 115 |
INSERT INTO tmyisam VALUES (39); |
| 116 |
INSERT INTO tinnodb VALUES (40); |
| 117 |
ROLLBACK; |
| 118 |
Warnings: |
| 119 |
Warning 1196 Some non-transactional changed tables couldn't be rolled back |
| 120 |
INSERT INTO tinnodb VALUES (41); |
| 121 |
INSERT INTO tmyisam VALUES (42); |
| 122 |
ROLLBACK; |
| 123 |
Warnings: |
| 124 |
Warning 1196 Some non-transactional changed tables couldn't be rolled back |
| 125 |
SET AUTOCOMMIT = 1; |
| 126 |
==== MyISAM + NDB ==== |
| 127 |
---- committed with BEGIN---- |
| 128 |
BEGIN; |
| 129 |
INSERT INTO tmyisam VALUES (43); |
| 130 |
INSERT INTO tndb VALUES (44); |
| 131 |
COMMIT; |
| 132 |
BEGIN; |
| 133 |
INSERT INTO tndb VALUES (45); |
| 134 |
INSERT INTO tmyisam VALUES (46); |
| 135 |
COMMIT; |
| 136 |
---- rolled back with BEGIN ---- |
| 137 |
BEGIN; |
| 138 |
INSERT INTO tmyisam VALUES (47); |
| 139 |
INSERT INTO tndb VALUES (48); |
| 140 |
ROLLBACK; |
| 141 |
Warnings: |
| 142 |
Warning 1196 Some non-transactional changed tables couldn't be rolled back |
| 143 |
BEGIN; |
| 144 |
INSERT INTO tndb VALUES (49); |
| 145 |
INSERT INTO tmyisam VALUES (50); |
| 146 |
ROLLBACK; |
| 147 |
Warnings: |
| 148 |
Warning 1196 Some non-transactional changed tables couldn't be rolled back |
| 149 |
---- committed with AUTOCOMMIT = 0 ---- |
| 150 |
SET AUTOCOMMIT = 0; |
| 151 |
INSERT INTO tmyisam VALUES (51); |
| 152 |
INSERT INTO tndb VALUES (52); |
| 153 |
COMMIT; |
| 154 |
INSERT INTO tndb VALUES (53); |
| 155 |
INSERT INTO tmyisam VALUES (54); |
| 156 |
COMMIT; |
| 157 |
---- rolled back with AUTOCOMMIT = 0 ---- |
| 158 |
INSERT INTO tmyisam VALUES (55); |
| 159 |
INSERT INTO tndb VALUES (56); |
| 160 |
ROLLBACK; |
| 161 |
Warnings: |
| 162 |
Warning 1196 Some non-transactional changed tables couldn't be rolled back |
| 163 |
INSERT INTO tndb VALUES (57); |
| 164 |
INSERT INTO tmyisam VALUES (58); |
| 165 |
ROLLBACK; |
| 166 |
Warnings: |
| 167 |
Warning 1196 Some non-transactional changed tables couldn't be rolled back |
| 168 |
SET AUTOCOMMIT = 1; |
| 169 |
==== InnoDB + NDB ==== |
| 170 |
---- committed with BEGIN ---- |
| 171 |
BEGIN; |
| 172 |
INSERT INTO tinnodb VALUES (59); |
| 173 |
INSERT INTO tndb VALUES (60); |
| 174 |
COMMIT; |
| 175 |
BEGIN; |
| 176 |
INSERT INTO tndb VALUES (61); |
| 177 |
INSERT INTO tinnodb VALUES (62); |
| 178 |
COMMIT; |
| 179 |
---- rolled back with BEGIN ---- |
| 180 |
BEGIN; |
| 181 |
INSERT INTO tinnodb VALUES (63); |
| 182 |
INSERT INTO tndb VALUES (64); |
| 183 |
ROLLBACK; |
| 184 |
BEGIN; |
| 185 |
INSERT INTO tndb VALUES (65); |
| 186 |
INSERT INTO tinnodb VALUES (66); |
| 187 |
ROLLBACK; |
| 188 |
---- committed with AUTOCOMMIT = 0 ---- |
| 189 |
SET AUTOCOMMIT = 0; |
| 190 |
INSERT INTO tinnodb VALUES (67); |
| 191 |
INSERT INTO tndb VALUES (68); |
| 192 |
COMMIT; |
| 193 |
INSERT INTO tndb VALUES (69); |
| 194 |
INSERT INTO tinnodb VALUES (70); |
| 195 |
COMMIT; |
| 196 |
---- rolled back with AUTOCOMMIT = 0 ---- |
| 197 |
INSERT INTO tinnodb VALUES (71); |
| 198 |
INSERT INTO tndb VALUES (72); |
| 199 |
ROLLBACK; |
| 200 |
INSERT INTO tndb VALUES (73); |
| 201 |
INSERT INTO tinnodb VALUES (74); |
| 202 |
ROLLBACK; |
| 203 |
SET AUTOCOMMIT = 1; |
| 204 |
==== MyISAM + InnoDB + NDB ==== |
| 205 |
---- committed with BEGIN ---- |
| 206 |
BEGIN; |
| 207 |
INSERT INTO tmyisam VALUES (75); |
| 208 |
INSERT INTO tinnodb VALUES (76); |
| 209 |
INSERT INTO tndb VALUES (77); |
| 210 |
COMMIT; |
| 211 |
BEGIN; |
| 212 |
INSERT INTO tmyisam VALUES (78); |
| 213 |
INSERT INTO tndb VALUES (79); |
| 214 |
INSERT INTO tinnodb VALUES (80); |
| 215 |
COMMIT; |
| 216 |
BEGIN; |
| 217 |
INSERT INTO tinnodb VALUES (81); |
| 218 |
INSERT INTO tmyisam VALUES (82); |
| 219 |
INSERT INTO tndb VALUES (83); |
| 220 |
COMMIT; |
| 221 |
BEGIN; |
| 222 |
INSERT INTO tinnodb VALUES (84); |
| 223 |
INSERT INTO tndb VALUES (85); |
| 224 |
INSERT INTO tmyisam VALUES (86); |
| 225 |
COMMIT; |
| 226 |
BEGIN; |
| 227 |
INSERT INTO tndb VALUES (87); |
| 228 |
INSERT INTO tmyisam VALUES (88); |
| 229 |
INSERT INTO tinnodb VALUES (89); |
| 230 |
COMMIT; |
| 231 |
BEGIN; |
| 232 |
INSERT INTO tndb VALUES (90); |
| 233 |
INSERT INTO tinnodb VALUES (91); |
| 234 |
INSERT INTO tmyisam VALUES (92); |
| 235 |
COMMIT; |
| 236 |
---- rolled back with BEGIN ---- |
| 237 |
BEGIN; |
| 238 |
INSERT INTO tmyisam VALUES (93); |
| 239 |
INSERT INTO tinnodb VALUES (94); |
| 240 |
INSERT INTO tndb VALUES (95); |
| 241 |
ROLLBACK; |
| 242 |
Warnings: |
| 243 |
Warning 1196 Some non-transactional changed tables couldn't be rolled back |
| 244 |
BEGIN; |
| 245 |
INSERT INTO tmyisam VALUES (96); |
| 246 |
INSERT INTO tndb VALUES (97); |
| 247 |
INSERT INTO tinnodb VALUES (98); |
| 248 |
ROLLBACK; |
| 249 |
Warnings: |
| 250 |
Warning 1196 Some non-transactional changed tables couldn't be rolled back |
| 251 |
BEGIN; |
| 252 |
INSERT INTO tinnodb VALUES (99); |
| 253 |
INSERT INTO tmyisam VALUES (100); |
| 254 |
INSERT INTO tndb VALUES (101); |
| 255 |
ROLLBACK; |
| 256 |
Warnings: |
| 257 |
Warning 1196 Some non-transactional changed tables couldn't be rolled back |
| 258 |
BEGIN; |
| 259 |
INSERT INTO tinnodb VALUES (102); |
| 260 |
INSERT INTO tndb VALUES (103); |
| 261 |
INSERT INTO tmyisam VALUES (104); |
| 262 |
ROLLBACK; |
| 263 |
Warnings: |
| 264 |
Warning 1196 Some non-transactional changed tables couldn't be rolled back |
| 265 |
BEGIN; |
| 266 |
INSERT INTO tndb VALUES (105); |
| 267 |
INSERT INTO tmyisam VALUES (106); |
| 268 |
INSERT INTO tinnodb VALUES (107); |
| 269 |
ROLLBACK; |
| 270 |
Warnings: |
| 271 |
Warning 1196 Some non-transactional changed tables couldn't be rolled back |
| 272 |
BEGIN; |
| 273 |
INSERT INTO tndb VALUES (108); |
| 274 |
INSERT INTO tinnodb VALUES (109); |
| 275 |
INSERT INTO tmyisam VALUES (110); |
| 276 |
ROLLBACK; |
| 277 |
Warnings: |
| 278 |
Warning 1196 Some non-transactional changed tables couldn't be rolled back |
| 279 |
---- committed with AUTOCOMMIT = 0 ---- |
| 280 |
SET AUTOCOMMIT = 0; |
| 281 |
INSERT INTO tmyisam VALUES (111); |
| 282 |
INSERT INTO tinnodb VALUES (112); |
| 283 |
INSERT INTO tndb VALUES (113); |
| 284 |
COMMIT; |
| 285 |
INSERT INTO tmyisam VALUES (114); |
| 286 |
INSERT INTO tndb VALUES (115); |
| 287 |
INSERT INTO tinnodb VALUES (116); |
| 288 |
COMMIT; |
| 289 |
INSERT INTO tinnodb VALUES (117); |
| 290 |
INSERT INTO tmyisam VALUES (118); |
| 291 |
INSERT INTO tndb VALUES (119); |
| 292 |
COMMIT; |
| 293 |
INSERT INTO tinnodb VALUES (120); |
| 294 |
INSERT INTO tndb VALUES (121); |
| 295 |
INSERT INTO tmyisam VALUES (122); |
| 296 |
COMMIT; |
| 297 |
INSERT INTO tndb VALUES (123); |
| 298 |
INSERT INTO tmyisam VALUES (124); |
| 299 |
INSERT INTO tinnodb VALUES (125); |
| 300 |
COMMIT; |
| 301 |
INSERT INTO tndb VALUES (126); |
| 302 |
INSERT INTO tinnodb VALUES (127); |
| 303 |
INSERT INTO tmyisam VALUES (128); |
| 304 |
COMMIT; |
| 305 |
---- rolled back with AUTOCOMMIT = 0 ---- |
| 306 |
INSERT INTO tmyisam VALUES (129); |
| 307 |
INSERT INTO tinnodb VALUES (130); |
| 308 |
INSERT INTO tndb VALUES (131); |
| 309 |
ROLLBACK; |
| 310 |
Warnings: |
| 311 |
Warning 1196 Some non-transactional changed tables couldn't be rolled back |
| 312 |
INSERT INTO tmyisam VALUES (132); |
| 313 |
INSERT INTO tndb VALUES (133); |
| 314 |
INSERT INTO tinnodb VALUES (134); |
| 315 |
ROLLBACK; |
| 316 |
Warnings: |
| 317 |
Warning 1196 Some non-transactional changed tables couldn't be rolled back |
| 318 |
INSERT INTO tinnodb VALUES (135); |
| 319 |
INSERT INTO tmyisam VALUES (136); |
| 320 |
INSERT INTO tndb VALUES (137); |
| 321 |
ROLLBACK; |
| 322 |
Warnings: |
| 323 |
Warning 1196 Some non-transactional changed tables couldn't be rolled back |
| 324 |
INSERT INTO tinnodb VALUES (138); |
| 325 |
INSERT INTO tndb VALUES (139); |
| 326 |
INSERT INTO tmyisam VALUES (140); |
| 327 |
ROLLBACK; |
| 328 |
Warnings: |
| 329 |
Warning 1196 Some non-transactional changed tables couldn't be rolled back |
| 330 |
INSERT INTO tndb VALUES (141); |
| 331 |
INSERT INTO tmyisam VALUES (142); |
| 332 |
INSERT INTO tinnodb VALUES (143); |
| 333 |
ROLLBACK; |
| 334 |
Warnings: |
| 335 |
Warning 1196 Some non-transactional changed tables couldn't be rolled back |
| 336 |
INSERT INTO tndb VALUES (144); |
| 337 |
INSERT INTO tinnodb VALUES (145); |
| 338 |
INSERT INTO tmyisam VALUES (146); |
| 339 |
ROLLBACK; |
| 340 |
Warnings: |
| 341 |
Warning 1196 Some non-transactional changed tables couldn't be rolled back |
| 342 |
SET AUTOCOMMIT = 1; |
| 343 |
---- Mixed statements Innodb ---- |
| 344 |
BEGIN; |
| 345 |
INSERT INTO tndb VALUES (147); |
| 346 |
INSERT INTO tinnodb SELECT * FROM tndb ORDER BY a DESC LIMIT 1; |
| 347 |
COMMIT; |
| 348 |
INSERT INTO tndb VALUES (148); |
| 349 |
BEGIN; |
| 350 |
INSERT INTO tinnodb SELECT * FROM tndb ORDER BY a DESC LIMIT 1; |
| 351 |
INSERT INTO tndb VALUES (149); |
| 352 |
COMMIT; |
| 353 |
BEGIN; |
| 354 |
INSERT INTO tndb VALUES (150); |
| 355 |
INSERT INTO tmyisam SELECT * FROM tndb ORDER BY a DESC LIMIT 1; |
| 356 |
COMMIT; |
| 357 |
INSERT INTO tndb VALUES (151); |
| 358 |
BEGIN; |
| 359 |
INSERT INTO tmyisam SELECT * FROM tndb ORDER BY a DESC LIMIT 1; |
| 360 |
INSERT INTO tndb VALUES (152); |
| 361 |
COMMIT; |
| 362 |
==== Verify the result ==== |
| 363 |
SELECT * FROM tmyisam ORDER BY a; |
| 364 |
a |
| 365 |
0 |
| 366 |
3 |
| 367 |
4 |
| 368 |
9 |
| 369 |
10 |
| 370 |
15 |
| 371 |
16 |
| 372 |
21 |
| 373 |
22 |
| 374 |
27 |
| 375 |
30 |
| 376 |
31 |
| 377 |
34 |
| 378 |
35 |
| 379 |
38 |
| 380 |
39 |
| 381 |
42 |
| 382 |
43 |
| 383 |
46 |
| 384 |
47 |
| 385 |
50 |
| 386 |
51 |
| 387 |
54 |
| 388 |
55 |
| 389 |
58 |
| 390 |
75 |
| 391 |
78 |
| 392 |
82 |
| 393 |
86 |
| 394 |
88 |
| 395 |
92 |
| 396 |
93 |
| 397 |
96 |
| 398 |
100 |
| 399 |
104 |
| 400 |
106 |
| 401 |
110 |
| 402 |
111 |
| 403 |
114 |
| 404 |
118 |
| 405 |
122 |
| 406 |
124 |
| 407 |
128 |
| 408 |
129 |
| 409 |
132 |
| 410 |
136 |
| 411 |
140 |
| 412 |
142 |
| 413 |
146 |
| 414 |
150 |
| 415 |
151 |
| 416 |
SELECT * FROM tinnodb ORDER BY a; |
| 417 |
a |
| 418 |
1 |
| 419 |
5 |
| 420 |
6 |
| 421 |
17 |
| 422 |
18 |
| 423 |
28 |
| 424 |
29 |
| 425 |
36 |
| 426 |
37 |
| 427 |
59 |
| 428 |
62 |
| 429 |
67 |
| 430 |
70 |
| 431 |
76 |
| 432 |
80 |
| 433 |
81 |
| 434 |
84 |
| 435 |
89 |
| 436 |
91 |
| 437 |
112 |
| 438 |
116 |
| 439 |
117 |
| 440 |
120 |
| 441 |
125 |
| 442 |
127 |
| 443 |
147 |
| 444 |
148 |
| 445 |
SELECT * FROM tndb ORDER BY a; |
| 446 |
a |
| 447 |
2 |
| 448 |
7 |
| 449 |
8 |
| 450 |
19 |
| 451 |
20 |
| 452 |
44 |
| 453 |
45 |
| 454 |
52 |
| 455 |
53 |
| 456 |
60 |
| 457 |
61 |
| 458 |
68 |
| 459 |
69 |
| 460 |
77 |
| 461 |
79 |
| 462 |
83 |
| 463 |
85 |
| 464 |
87 |
| 465 |
90 |
| 466 |
113 |
| 467 |
115 |
| 468 |
119 |
| 469 |
121 |
| 470 |
123 |
| 471 |
126 |
| 472 |
147 |
| 473 |
148 |
| 474 |
149 |
| 475 |
150 |
| 476 |
151 |
| 477 |
152 |
| 478 |
[on slave] |
| 479 |
include/diff_tables.inc [master:tmyisam, slave:tmyisam] |
| 480 |
include/diff_tables.inc [master:tinnodb, slave:tinnodb] |
| 481 |
include/diff_tables.inc [master:tndb, slave:tndb] |
| 482 |
==== Clean up ==== |
| 483 |
[on master] |
| 484 |
DROP TABLE tmyisam, tinnodb, tndb; |
| 485 |
[on slave] |
| 486 |
include/rpl_end.inc |