| 1 |
create table t1 (a bigint auto_increment primary key, |
| 2 |
d int) engine = ndb; |
| 3 |
create table t2 (a int auto_increment primary key, |
| 4 |
d int) engine = ndb; |
| 5 |
insert into t1 values (400,0), (401,1), (402,2), (403,3); |
| 6 |
insert into t2 values (100,0), (101,1), (102,2), (103,3); |
| 7 |
select * from test.t1 order by a; |
| 8 |
a d |
| 9 |
400 0 |
| 10 |
401 1 |
| 11 |
402 2 |
| 12 |
403 3 |
| 13 |
select * from test.t2 order by a; |
| 14 |
a d |
| 15 |
100 0 |
| 16 |
101 1 |
| 17 |
102 2 |
| 18 |
103 3 |
| 19 |
select table_name, auto_increment |
| 20 |
from information_schema.tables where table_schema='test'; |
| 21 |
TABLE_NAME AUTO_INCREMENT |
| 22 |
t1 404 |
| 23 |
t2 104 |
| 24 |
Run backup |
| 25 |
Restore with positive offset of 200 |
| 26 |
truncate test.t1; |
| 27 |
truncate test.t2; |
| 28 |
Expect that next autoincrement value |
| 29 |
has moved forward accordingly |
| 30 |
insert into t1 (d) values (4); |
| 31 |
insert into t2 (d) values (4); |
| 32 |
select * from test.t1 order by a; |
| 33 |
a d |
| 34 |
600 0 |
| 35 |
601 1 |
| 36 |
602 2 |
| 37 |
603 3 |
| 38 |
604 4 |
| 39 |
select * from test.t2 order by a; |
| 40 |
a d |
| 41 |
300 0 |
| 42 |
301 1 |
| 43 |
302 2 |
| 44 |
303 3 |
| 45 |
304 4 |
| 46 |
show create table t1; |
| 47 |
Table Create Table |
| 48 |
t1 CREATE TABLE `t1` ( |
| 49 |
`a` bigint NOT NULL AUTO_INCREMENT, |
| 50 |
`d` int DEFAULT NULL, |
| 51 |
PRIMARY KEY (`a`) |
| 52 |
) ENGINE=ndbcluster AUTO_INCREMENT=605 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
| 53 |
show create table t2; |
| 54 |
Table Create Table |
| 55 |
t2 CREATE TABLE `t2` ( |
| 56 |
`a` int NOT NULL AUTO_INCREMENT, |
| 57 |
`d` int DEFAULT NULL, |
| 58 |
PRIMARY KEY (`a`) |
| 59 |
) ENGINE=ndbcluster AUTO_INCREMENT=305 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
| 60 |
Restore with negative offset of 50 |
| 61 |
truncate test.t1; |
| 62 |
truncate test.t2; |
| 63 |
Expect that next autoincrement value has *not* |
| 64 |
moved back |
| 65 |
insert into t1 (d) values (4); |
| 66 |
insert into t2 (d) values (4); |
| 67 |
select * from test.t1 order by a; |
| 68 |
a d |
| 69 |
350 0 |
| 70 |
351 1 |
| 71 |
352 2 |
| 72 |
353 3 |
| 73 |
404 4 |
| 74 |
select * from test.t2 order by a; |
| 75 |
a d |
| 76 |
50 0 |
| 77 |
51 1 |
| 78 |
52 2 |
| 79 |
53 3 |
| 80 |
104 4 |
| 81 |
show create table t1; |
| 82 |
Table Create Table |
| 83 |
t1 CREATE TABLE `t1` ( |
| 84 |
`a` bigint NOT NULL AUTO_INCREMENT, |
| 85 |
`d` int DEFAULT NULL, |
| 86 |
PRIMARY KEY (`a`) |
| 87 |
) ENGINE=ndbcluster AUTO_INCREMENT=405 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
| 88 |
show create table t2; |
| 89 |
Table Create Table |
| 90 |
t2 CREATE TABLE `t2` ( |
| 91 |
`a` int NOT NULL AUTO_INCREMENT, |
| 92 |
`d` int DEFAULT NULL, |
| 93 |
PRIMARY KEY (`a`) |
| 94 |
) ENGINE=ndbcluster AUTO_INCREMENT=105 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
| 95 |
Restore with negative offset of 200 |
| 96 |
truncate test.t1; |
| 97 |
truncate test.t2; |
| 98 |
Expect that next autoincrement value has *not* |
| 99 |
moved back |
| 100 |
insert into t1 (d) values (4); |
| 101 |
insert into t2 (d) values (4); |
| 102 |
select * from test.t1 order by a; |
| 103 |
a d |
| 104 |
200 0 |
| 105 |
201 1 |
| 106 |
202 2 |
| 107 |
203 3 |
| 108 |
404 4 |
| 109 |
select * from test.t2 order by a; |
| 110 |
a d |
| 111 |
-100 0 |
| 112 |
-99 1 |
| 113 |
-98 2 |
| 114 |
-97 3 |
| 115 |
104 4 |
| 116 |
show create table t1; |
| 117 |
Table Create Table |
| 118 |
t1 CREATE TABLE `t1` ( |
| 119 |
`a` bigint NOT NULL AUTO_INCREMENT, |
| 120 |
`d` int DEFAULT NULL, |
| 121 |
PRIMARY KEY (`a`) |
| 122 |
) ENGINE=ndbcluster AUTO_INCREMENT=405 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
| 123 |
show create table t2; |
| 124 |
Table Create Table |
| 125 |
t2 CREATE TABLE `t2` ( |
| 126 |
`a` int NOT NULL AUTO_INCREMENT, |
| 127 |
`d` int DEFAULT NULL, |
| 128 |
PRIMARY KEY (`a`) |
| 129 |
) ENGINE=ndbcluster AUTO_INCREMENT=105 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
| 130 |
drop table test.t1; |
| 131 |
drop table test.t2; |