1 -- source include/have_multi_ndb.inc
2 -- source include/not_embedded.inc
6 DROP TABLE IF EXISTS t1,t2;
8 DROP TABLE IF EXISTS t1;
12 set @old_auto_increment_offset = @@session.auto_increment_offset;
13 set @old_auto_increment_increment = @@session.auto_increment_increment;
14 set @old_ndb_autoincrement_prefetch_sz = @@session.ndb_autoincrement_prefetch_sz;
18 create table t1 (a int not null auto_increment primary key) engine ndb;
20 # Step 1: Verify simple insert
21 insert into t1 values (NULL);
22 select * from t1 order by a;
24 # Step 2: Verify simple update with higher than highest value causes
25 # next insert to use updated_value + 1
26 update t1 set a = 5 where a = 1;
27 insert into t1 values (NULL);
28 select * from t1 order by a;
30 # Step 3: Verify insert that inserts higher than highest value causes
31 # next insert to use inserted_value + 1
32 insert into t1 values (7);
33 insert into t1 values (NULL);
34 select * from t1 order by a;
36 # Step 4: Verify that insert into hole, lower than highest value doesn't
38 insert into t1 values (2);
39 insert into t1 values (NULL);
40 select * from t1 order by a;
42 # Step 5: Verify that update into hole, lower than highest value doesn't
44 update t1 set a = 4 where a = 2;
45 insert into t1 values (NULL);
46 select * from t1 order by a;
48 # Step 6: Verify that delete of highest value doesn't cause the next
49 # insert to reuse this value
50 delete from t1 where a = 10;
51 insert into t1 values (NULL);
52 select * from t1 order by a;
54 # Step 7: Verify that REPLACE has the same effect as INSERT
55 replace t1 values (NULL);
56 select * from t1 order by a;
57 replace t1 values (15);
58 select * from t1 order by a;
59 replace into t1 values (NULL);
60 select * from t1 order by a;
62 # Step 8: Verify that REPLACE has the same effect as UPDATE
63 replace t1 values (15);
64 select * from t1 order by a;
66 # Step 9: Verify that IGNORE doesn't affect auto_increment
67 insert ignore into t1 values (NULL);
68 select * from t1 order by a;
69 insert ignore into t1 values (15), (NULL);
70 select * from t1 order by a;
72 # Step 10: Verify that on duplicate key as UPDATE behaves as an
74 insert into t1 values (15)
75 on duplicate key update a = 20;
76 insert into t1 values (NULL);
77 select * from t1 order by a;
79 # Step 11: Verify that on duplicate key as INSERT behaves as INSERT
80 insert into t1 values (NULL) on duplicate key update a = 30;
81 select * from t1 order by a;
82 insert into t1 values (30) on duplicate key update a = 40;
83 select * from t1 order by a;
85 #Step 12: Vefify INSERT IGNORE (bug#32055)
86 insert ignore into t1 values(600),(NULL),(NULL),(610),(NULL);
87 select * from t1 order by a;
90 #Step 13: Verify auto_increment of unique key
91 create table t1 (a int not null primary key,
92 b int not null unique auto_increment) engine ndb;
93 insert into t1 values (1, NULL);
94 insert into t1 values (3, NULL);
95 update t1 set b = 3 where a = 3;
96 insert into t1 values (4, NULL);
97 select * from t1 order by a;
100 #Step 14: Verify that auto_increment_increment and auto_increment_offset
104 pk INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
106 c INT NOT NULL UNIQUE
110 pk INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
112 c INT NOT NULL UNIQUE
115 SET @@session.auto_increment_increment=10;
116 INSERT INTO t1 (b,c) VALUES (1,0),(2,1),(3,2);
117 INSERT INTO t2 (b,c) VALUES (1,0),(2,1),(3,2);
118 SELECT * FROM t1 ORDER BY pk;
119 SELECT COUNT(t1.pk) FROM t1, t2 WHERE t1.pk = t2.pk AND t1.b = t2.b AND t1.c = t1.c;
122 SET @@session.auto_increment_offset=5;
123 INSERT INTO t1 (b,c) VALUES (1,0),(2,1),(3,2);
124 INSERT INTO t1 (pk,b,c) VALUES (27,4,3),(NULL,5,4),(99,6,5),(NULL,7,6);
125 INSERT INTO t2 (b,c) VALUES (1,0),(2,1),(3,2);
126 INSERT INTO t2 (pk,b,c) VALUES (27,4,3),(NULL,5,4),(99,6,5),(NULL,7,6);
127 SELECT * FROM t1 ORDER BY pk;
128 SELECT COUNT(t1.pk) FROM t1, t2 WHERE t1.pk = t2.pk AND t1.b = t2.b AND t1.c = t1.c;
131 SET @@session.auto_increment_increment=2;
132 INSERT INTO t1 (b,c) VALUES (1,0),(2,1),(3,2);
133 INSERT INTO t2 (b,c) VALUES (1,0),(2,1),(3,2);
134 SELECT * FROM t1 ORDER BY pk;
135 SELECT COUNT(t1.pk) FROM t1, t2 WHERE t1.pk = t2.pk AND t1.b = t2.b AND t1.c = t1.c;
139 pk INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
141 c INT NOT NULL UNIQUE
142 ) ENGINE=NDBCLUSTER AUTO_INCREMENT = 7;
145 pk INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
147 c INT NOT NULL UNIQUE
148 ) ENGINE=MYISAM AUTO_INCREMENT = 7;
150 SET @@session.auto_increment_offset=1;
151 SET @@session.auto_increment_increment=1;
152 INSERT INTO t1 (b,c) VALUES (1,0),(2,1),(3,2);
153 INSERT INTO t2 (b,c) VALUES (1,0),(2,1),(3,2);
154 SELECT * FROM t1 ORDER BY pk;
155 SELECT COUNT(t1.pk) FROM t1, t2 WHERE t1.pk = t2.pk AND t1.b = t2.b AND t1.c = t1.c;
159 pk INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
161 c INT NOT NULL UNIQUE
162 ) ENGINE=NDBCLUSTER AUTO_INCREMENT = 3;
165 pk INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
167 c INT NOT NULL UNIQUE
168 ) ENGINE=MYISAM AUTO_INCREMENT = 3;
170 SET @@session.auto_increment_offset=5;
171 SET @@session.auto_increment_increment=10;
172 INSERT INTO t1 (b,c) VALUES (1,0),(2,1),(3,2);
173 INSERT INTO t2 (b,c) VALUES (1,0),(2,1),(3,2);
174 SELECT * FROM t1 ORDER BY pk;
175 SELECT COUNT(t1.pk) FROM t1, t2 WHERE t1.pk = t2.pk AND t1.b = t2.b AND t1.c = t1.c;
179 pk INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
181 c INT NOT NULL UNIQUE
182 ) ENGINE=NDBCLUSTER AUTO_INCREMENT = 7;
185 pk INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
187 c INT NOT NULL UNIQUE
188 ) ENGINE=MYISAM AUTO_INCREMENT = 7;
190 SET @@session.auto_increment_offset=5;
191 SET @@session.auto_increment_increment=10;
192 INSERT INTO t1 (b,c) VALUES (1,0),(2,1),(3,2);
193 INSERT INTO t2 (b,c) VALUES (1,0),(2,1),(3,2);
194 SELECT * FROM t1 ORDER BY pk;
195 SELECT COUNT(t1.pk) FROM t1, t2 WHERE t1.pk = t2.pk AND t1.b = t2.b AND t1.c = t1.c;
199 pk INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
201 c INT NOT NULL UNIQUE
202 ) ENGINE=NDBCLUSTER AUTO_INCREMENT = 5;
205 pk INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
207 c INT NOT NULL UNIQUE
208 ) ENGINE=MYISAM AUTO_INCREMENT = 5;
210 SET @@session.auto_increment_offset=5;
211 SET @@session.auto_increment_increment=10;
212 INSERT INTO t1 (b,c) VALUES (1,0),(2,1),(3,2);
213 INSERT INTO t2 (b,c) VALUES (1,0),(2,1),(3,2);
214 SELECT * FROM t1 ORDER BY pk;
215 SELECT COUNT(t1.pk) FROM t1, t2 WHERE t1.pk = t2.pk AND t1.b = t2.b AND t1.c = t1.c;
219 pk INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
221 c INT NOT NULL UNIQUE
222 ) ENGINE=NDBCLUSTER AUTO_INCREMENT = 100;
225 pk INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
227 c INT NOT NULL UNIQUE
228 ) ENGINE=MYISAM AUTO_INCREMENT = 100;
230 SET @@session.auto_increment_offset=5;
231 SET @@session.auto_increment_increment=10;
232 INSERT INTO t1 (b,c) VALUES (1,0),(2,1),(3,2);
233 INSERT INTO t2 (b,c) VALUES (1,0),(2,1),(3,2);
234 SELECT * FROM t1 ORDER BY pk;
235 SELECT COUNT(t1.pk) FROM t1, t2 WHERE t1.pk = t2.pk AND t1.b = t2.b AND t1.c = t1.c;
238 #Step 15: Now verify that behaviour on multiple MySQL Servers behave
239 # properly. Start by dropping table and recreating it to start
240 # counters and id caches from zero again.
243 SET @@session.auto_increment_offset=1;
244 SET @@session.auto_increment_increment=1;
245 set ndb_autoincrement_prefetch_sz = 32;
246 drop table if exists t1;
248 SET @@session.auto_increment_offset=1;
249 SET @@session.auto_increment_increment=1;
250 set ndb_autoincrement_prefetch_sz = 32;
254 create table t1 (a int not null auto_increment primary key) engine ndb;
255 # Basic test, ensure that the second server gets a new range.
256 #Generate record with key = 1
257 insert into t1 values (NULL);
259 #Generate record with key = 33
260 insert into t1 values (NULL);
262 select * from t1 order by a;
264 #This insert should not affect the range of the second server
265 insert into t1 values (20);
267 insert into t1 values (NULL);
268 select * from t1 order by a;
271 #This insert should remove cached values but also skip values already
272 #taken by server2, given that there is no method of communicating with
273 #the other server it should also cause a conflict
276 insert into t1 values (35);
277 insert into t1 values (NULL);
280 insert into t1 values (NULL);
281 select * from t1 order by a;
283 insert into t1 values (100);
284 insert into t1 values (NULL);
286 insert into t1 values (NULL);
287 select * from t1 order by a;
289 set auto_increment_offset = @old_auto_increment_offset;
290 set auto_increment_increment = @old_auto_increment_increment;
291 set ndb_autoincrement_prefetch_sz = @old_ndb_autoincrement_prefetch_sz;