Debian Codegraph @

mysql-test/suite/innodb/t/autoinc_persist.test

raw
1 --source include/no_valgrind_without_big.inc
2 # Skipping the test when binlog_format=STATEMENT due to unsafe statements:
3 # unsafe auto-increment column.
4 --source include/not_binlog_format_statement.inc
5
6 --echo # This test case is introduced to test the persisted autoinc, basic
7 --echo # autoinc features are not the main part of this one.
8
9 --disable_query_log
10 CALL mtr.add_suppression("\\[ERROR\\].*MLOG_FILE_RENAME: Invalid to file name");
11 --enable_query_log
12
13 --echo # Pre-create several tables
14
15 CREATE TABLE t1(a TINYINT AUTO_INCREMENT KEY) ENGINE = InnoDB;
16 INSERT INTO t1 VALUES(0), (0), (0), (0), (-1), (-10), (0),
17 (20), (30), (31);
18 SELECT * FROM t1;
19
20 CREATE TABLE t2(a TINYINT UNSIGNED AUTO_INCREMENT KEY) ENGINE = InnoDB;
21 --error ER_WARN_DATA_OUT_OF_RANGE
22 INSERT INTO t2 VALUES(-5);
23 INSERT INTO t2 VALUES(0), (0), (0), (0), (8), (10), (0),
24 (20), (30), (31);
25 SELECT * FROM t2;
26
27 CREATE TABLE t3(a SMALLINT AUTO_INCREMENT KEY) ENGINE = InnoDB;
28 INSERT INTO t3 VALUES(0), (0), (0), (0), (-1), (-10), (0),
29 (20), (30), (31), (1024), (4096);
30 SELECT * FROM t3;
31
32 CREATE TABLE t4(a SMALLINT UNSIGNED AUTO_INCREMENT KEY) ENGINE = InnoDB;
33 --error ER_WARN_DATA_OUT_OF_RANGE
34 INSERT INTO t4 VALUES(-5);
35 INSERT INTO t4 VALUES(0), (0), (0), (0), (8), (10), (0),
36 (20), (30), (31), (1024), (4096);
37 SELECT * FROM t4;
38
39 CREATE TABLE t5(a MEDIUMINT AUTO_INCREMENT KEY) ENGINE = InnoDB;
40 INSERT INTO t5 VALUES(0), (0), (0), (0), (-1), (-10), (0),
41 (20), (30), (31), (1000000), (1000005);
42 SELECT * FROM t5;
43
44 CREATE TABLE t6(a MEDIUMINT UNSIGNED AUTO_INCREMENT KEY) ENGINE = InnoDB;
45 --error ER_WARN_DATA_OUT_OF_RANGE
46 INSERT INTO t6 VALUES(-5);
47 INSERT INTO t6 VALUES(0), (0), (0), (0), (8), (10), (0),
48 (20), (30), (31), (1000000), (1000005);
49 SELECT * FROM t6;
50
51 CREATE TABLE t7(a INT AUTO_INCREMENT KEY) ENGINE = InnoDB;
52 INSERT INTO t7 VALUES(0), (0), (0), (0), (-1), (-10), (0),
53 (20), (30), (31), (100000000), (100000008);
54 SELECT * FROM t7;
55
56 CREATE TABLE t8(a INT UNSIGNED AUTO_INCREMENT KEY) ENGINE = InnoDB;
57 --error ER_WARN_DATA_OUT_OF_RANGE
58 INSERT INTO t8 VALUES(-5);
59 INSERT INTO t8 VALUES(0), (0), (0), (0), (8), (10), (0),
60 (20), (30), (31), (100000000), (100000008);
61 SELECT * FROM t8;
62
63 CREATE TABLE t9(a BIGINT AUTO_INCREMENT KEY) ENGINE = InnoDB;
64 INSERT INTO t9 VALUES(0), (0), (0), (0), (-1), (-10), (0),
65 (20), (30), (31), (100000000000), (100000000006);
66 SELECT * FROM t9;
67
68 CREATE TABLE t10(a BIGINT UNSIGNED AUTO_INCREMENT KEY) ENGINE = InnoDB;
69 --error ER_WARN_DATA_OUT_OF_RANGE
70 INSERT INTO t10 VALUES(-5);
71 INSERT INTO t10 VALUES(0), (0), (0), (0), (8), (10), (0),
72 (20), (30), (31), (100000000000), (100000000006);
73 SELECT * FROM t10;
74
75 CREATE TABLE t11(a FLOAT AUTO_INCREMENT KEY) ENGINE = InnoDB;
76 INSERT INTO t11 VALUES(0), (0), (0), (0), (-1), (-10), (0),
77 (20), (30), (31);
78 SELECT * FROM t11;
79
80 # Since autoinc counter is persisted by redo logs, we don't want to
81 # lose them on kill and restart, so to make the result after restart stable.
82 set global innodb_flush_log_at_trx_commit=1;
83
84 CREATE TABLE t12(a DOUBLE AUTO_INCREMENT KEY) ENGINE = InnoDB;
85 INSERT INTO t12 VALUES(0), (0), (0), (0), (-1), (-10), (0),
86 (20), (30), (31);
87 SELECT * FROM t12;
88
89 CREATE TABLE t13(a INT AUTO_INCREMENT PRIMARY KEY) ENGINE = InnoDB,
90 AUTO_INCREMENT = 1234;
91
92 --echo # Scenario 1: Normal restart, to test if the counters are persisted
93 --source include/restart_mysqld.inc
94
95 --echo # We expect these results should be equal to above SELECTs
96 SELECT * FROM t1;
97 SELECT * FROM t2;
98 SELECT * FROM t3;
99 SELECT * FROM t4;
100 SELECT * FROM t5;
101 SELECT * FROM t6;
102 SELECT * FROM t7;
103 SELECT * FROM t8;
104 SELECT * FROM t9;
105 SELECT * FROM t10;
106 SELECT * FROM t11;
107 SELECT * FROM t12;
108
109 SELECT * FROM t13;
110 SHOW CREATE TABLE t13;
111 INSERT INTO t13 VALUES(0);
112 SELECT a AS `Expect 1234` FROM t13;
113
114 --echo # Scenario 2: Delete some values, to test the counters should not be the
115 --echo # one which is the largest in current table
116
117 set global innodb_flush_log_at_trx_commit=1;
118
119 DELETE FROM t1 WHERE a > 30;
120 SELECT MAX(a) AS `Expect 30` FROM t1;
121 DELETE FROM t3 WHERE a > 2000;
122 SELECT MAX(a) AS `Expect 2000` FROM t3;
123 DELETE FROM t5 WHERE a > 1000000;
124 SELECT MAX(a) AS `Expect 1000000` FROM t5;
125 DELETE FROM t7 WHERE a > 100000000;
126 SELECT MAX(a) AS `Expect 100000000` FROM t7;
127 DELETE FROM t9 WHERE a > 100000000000;
128 SELECT MAX(a) AS `Expect 100000000000` FROM t9;
129
130 --source include/restart_mysqld.inc
131
132 INSERT INTO t1 VALUES(0), (0);
133 SELECT MAX(a) AS `Expect 33` FROM t1;
134 INSERT INTO t3 VALUES(0), (0);
135 SELECT MAX(a) AS `Expect 4098` FROM t3;
136 INSERT INTO t5 VALUES(0), (0);
137 SELECT MAX(a) AS `Expect 1000007` FROM t5;
138 INSERT INTO t7 VALUES(0), (0);
139 SELECT MAX(a) AS `Expect 100000010` FROM t7;
140 INSERT INTO t9 VALUES(0), (0);
141 SELECT MAX(a) AS `Expect 100000000008` FROM t9;
142
143 --echo # Scenario 3: Insert some bigger counters, the next counter should start
144 --echo # from there
145
146 INSERT INTO t1 VALUES(40), (0);
147 INSERT INTO t1 VALUES(42), (0);
148 SELECT a AS `Expect 43, 42` FROM t1 ORDER BY a DESC LIMIT 4;
149 INSERT INTO t3 VALUES(5000), (0);
150 INSERT INTO t3 VALUES(5010), (0);
151 SELECT a AS `Expect 5011, 5010` FROM t3 ORDER BY a DESC LIMIT 4;
152 INSERT INTO t5 VALUES(1000010), (0);
153 INSERT INTO t5 VALUES(1000020), (0);
154 SELECT a AS `Expect 1000021, 1000020` FROM t5 ORDER BY a DESC LIMIT 4;
155 INSERT INTO t7 VALUES(100000020), (0);
156 INSERT INTO t7 VALUES(100000030), (0);
157 SELECT a AS `Expect 100000031, 100000030` FROM t7 ORDER BY a DESC LIMIT 4;
158 INSERT INTO t9 VALUES(100000000010), (0);
159 INSERT INTO t9 VALUES(100000000020), (0);
160 SELECT a AS `Expect 100000000021, 100000000020` FROM t9 ORDER BY a DESC LIMIT 4;
161
162 --echo # Scenario 4: Update some values, to test the counters should be updated
163 --echo # to the bigger value, but not smaller value.
164
165 INSERT INTO t1 VALUES(50), (55);
166 # Updating to bigger value will update the auto-increment counter
167 UPDATE t1 SET a = 105 WHERE a = 5;
168 # Updating to smaller value will not update the counter
169 UPDATE t1 SET a = 100 WHERE a = 55;
170 --echo # This should insert 102, 106, 107, and make next counter 109.
171 INSERT INTO t1 VALUES(102), (0), (0);
172 SELECT a AS `Expect 107, 106` FROM t1 ORDER BY a DESC LIMIT 2;
173 DELETE FROM t1 WHERE a > 105;
174 INSERT INTO t1 VALUES(0);
175 SELECT MAX(a) AS `Expect 109` FROM t1;
176
177 --echo # Test the same things on t3, t5, t7, t9, to test if DDTableBuffer would
178 --echo # be updated accordingly
179
180 INSERT INTO t3 VALUES(60), (65);
181 # Updating to bigger value will update the auto-increment counter
182 UPDATE t3 SET a = 6005 WHERE a = 5;
183 # Updating to smaller value will not update the counter
184 UPDATE t3 SET a = 6000 WHERE a = 60;
185 --echo # This should insert 6002, 6006, 6007, and make next counter 6009.
186 INSERT INTO t3 VALUES(6002), (0), (0);
187 SELECT a AS `Expect 6007, 6006` FROM t3 ORDER BY a DESC LIMIT 2;
188 DELETE FROM t3 WHERE a > 6005;
189 INSERT INTO t3 VALUES(0);
190 SELECT MAX(a) AS `Expect 6009` FROM t3;
191
192 INSERT INTO t5 VALUES(100), (200);
193 # Updating to bigger value will update the auto-increment counter
194 UPDATE t5 SET a = 1000105 WHERE a = 5;
195 # Updating to smaller value will not update the counter
196 UPDATE t5 SET a = 1000100 WHERE a = 100;
197 --echo # This should insert 1000102, 1000106, 1000107, and make next counter
198 --echo # 1000109.
199 INSERT INTO t5 VALUES(1000102), (0), (0);
200 SELECT a AS `Expect 1000107, 1000106` FROM t5 ORDER BY a DESC LIMIT 2;
201 DELETE FROM t5 WHERE a > 1000105;
202 INSERT INTO t5 VALUES(0);
203 SELECT MAX(a) AS `Expect 1000109` FROM t5;
204
205 INSERT INTO t7 VALUES(100), (200);
206 # Updating to bigger value will update the auto-increment counter
207 UPDATE t7 SET a = 100000105 WHERE a = 5;
208 # Updating to smaller value will not update the counter
209 UPDATE t7 SET a = 100000100 WHERE a = 100;
210 --echo # This should insert 100000102, 1100000106, 100000107, and make next
211 --echo # counter 100000109.
212 INSERT INTO t7 VALUES(100000102), (0), (0);
213 SELECT a AS `Expect 100000107, 100000106` FROM t7 ORDER BY a DESC LIMIT 2;
214 DELETE FROM t7 WHERE a > 100000105;
215 INSERT INTO t7 VALUES(0);
216 SELECT MAX(a) AS `Expect 100000109` FROM t7;
217
218 set global innodb_flush_log_at_trx_commit=1;
219
220 INSERT INTO t9 VALUES(100), (200);
221 # Updating to bigger value will update the auto-increment counter
222 UPDATE t9 SET a = 100000000105 WHERE a = 5;
223 # Updating to smaller value will not update the counter
224 UPDATE t9 SET a = 100000000100 WHERE a = 100;
225 --echo # This should insert 100000000102, 100000000106, 100000000107, and make
226 --echo # next counter 100000000109.
227 INSERT INTO t9 VALUES(100000000102), (0), (0);
228 SELECT a AS `Expect 100000000107, 100000000106` FROM t9 ORDER BY a DESC LIMIT 2;
229 DELETE FROM t9 WHERE a > 100000000105;
230 INSERT INTO t9 VALUES(0);
231 SELECT MAX(a) AS `Expect 100000000109` FROM t9;
232
233 --source include/restart_mysqld.inc
234
235 INSERT INTO t1 VALUES(0), (0);
236 SELECT a AS `Expect 110, 111` FROM t1 ORDER BY a DESC LIMIT 2;
237
238 INSERT INTO t3 VALUES(0), (0);
239 SELECT a AS `Expect 6010, 6011` FROM t3 ORDER BY a DESC LIMIT 2;
240
241 INSERT INTO t5 VALUES(0), (0);
242 SELECT a AS `Expect 1100111, 1100110` FROM t5 ORDER BY a DESC LIMIT 2;
243
244 INSERT INTO t7 VALUES(0), (0);
245 SELECT a AS `Expect 100000111, 100000110` FROM t7 ORDER BY a DESC LIMIT 2;
246
247 INSERT INTO t9 VALUES(0), (0);
248 SELECT a AS `Expect 100000000111, 100000000110` FROM t9 ORDER BY a DESC LIMIT 2;
249
250 --echo # Scenario 5: Test kill the server
251
252 INSERT INTO t1 VALUES(125);
253 DELETE FROM t1 WHERE a = 125;
254
255 INSERT INTO t3 VALUES(6100);
256 DELETE FROM t3 WHERE a = 6100;
257
258 INSERT INTO t5 VALUES(1100200);
259 DELETE FROM t5 WHERE a = 1100200;
260
261 INSERT INTO t7 VALUES(100000200);
262 DELETE FROM t7 WHERE a = 100000200;
263
264 set global innodb_flush_log_at_trx_commit=1;
265
266 INSERT INTO t9 VALUES(100000000200);
267 DELETE FROM t9 WHERE a = 100000000200;
268
269 --source include/kill_and_restart_mysqld.inc
270
271 INSERT INTO t1 VALUES(0);
272 SELECT a AS `Expect 126` FROM t1 ORDER BY a DESC LIMIT 1;
273
274 INSERT INTO t3 VALUES(0);
275 SELECT a AS `Expect 6101` FROM t3 ORDER BY a DESC LIMIT 1;
276
277 INSERT INTO t5 VALUES(0);
278 SELECT a AS `Expect 1100201` FROM t5 ORDER BY a DESC LIMIT 1;
279
280 INSERT INTO t7 VALUES(0);
281 SELECT a AS `Expect 100000201` FROM t7 ORDER BY a DESC LIMIT 1;
282
283 INSERT INTO t9 VALUES(0);
284 SELECT a AS `Expect 100000000201` FROM t9 ORDER BY a DESC LIMIT 1;
285
286 --echo # Scenario 6: Test truncate will reset the counters to 0
287
288 TRUNCATE TABLE t1;
289 TRUNCATE TABLE t3;
290 TRUNCATE TABLE t5;
291 TRUNCATE TABLE t7;
292 TRUNCATE TABLE t9;
293
294 INSERT INTO t1 VALUES(0), (0);
295 SELECT * FROM t1;
296
297 INSERT INTO t3 VALUES(0), (0);
298 SELECT * FROM t3;
299
300 INSERT INTO t5 VALUES(0), (0);
301 SELECT * FROM t5;
302
303 INSERT INTO t7 VALUES(0), (0);
304 SELECT * FROM t7;
305
306 INSERT INTO t9 VALUES(0), (0);
307 SELECT * FROM t9;
308
309 set global innodb_flush_log_at_trx_commit=1;
310
311 TRUNCATE TABLE t1;
312 TRUNCATE TABLE t3;
313 TRUNCATE TABLE t5;
314 TRUNCATE TABLE t7;
315 TRUNCATE TABLE t9;
316
317 --source include/kill_and_restart_mysqld.inc
318
319 INSERT INTO t1 VALUES(0), (0);
320 SELECT * FROM t1;
321
322 INSERT INTO t3 VALUES(0), (0);
323 SELECT * FROM t3;
324
325 INSERT INTO t5 VALUES(0), (0);
326 SELECT * FROM t5;
327
328 INSERT INTO t7 VALUES(0), (0);
329 SELECT * FROM t7;
330
331 INSERT INTO t9 VALUES(0), (0);
332 SELECT * FROM t9;
333
334 --echo # Scenario 7: Test explicit rename table won't change the counter
335
336 set global innodb_flush_log_at_trx_commit=1;
337
338 RENAME TABLE t9 to t19;
339 INSERT INTO t19 VALUES(0), (0);
340 SELECT * FROM t19;
341 DELETE FROM t19 WHERE a = 4;
342
343 --source include/kill_and_restart_mysqld.inc
344
345 RENAME TABLE t19 to t9;
346 INSERT INTO t9 VALUES(0), (0);
347 SELECT * FROM t9;
348
349 TRUNCATE TABLE t9;
350
351 INSERT INTO t9 VALUES(0), (0);
352 SELECT * FROM t9;
353
354 --echo # Scenario 8: Test ALTER TABLE operations
355
356 INSERT INTO t3 VALUES(0), (0), (100), (200), (1000);
357 SELECT * FROM t3;
358 DELETE FROM t3 WHERE a > 300;
359 SELECT MAX(a) AS `Expect 200` FROM t3;
360 --echo # This will not change the counter to 150, but to 201, which is the next
361 --echo # of current max counter in the table
362 ALTER TABLE t3 AUTO_INCREMENT = 150;
363 SHOW CREATE TABLE t3;
364 INSERT INTO t3 VALUES(0);
365 SELECT MAX(a) AS `Expect 201` FROM t3;
366 --echo # This will change the counter to 500, which is bigger than any counter
367 --echo # in the table
368 ALTER TABLE t3 AUTO_INCREMENT = 500;
369 SHOW CREATE TABLE t3;
370 INSERT INTO t3 VALUES(0);
371 SELECT MAX(a) AS `Expect 500` FROM t3;
372
373 TRUNCATE TABLE t3;
374 ALTER TABLE t3 AUTO_INCREMENT = 100;
375 SHOW CREATE TABLE t3;
376 INSERT INTO t3 VALUES(0), (0);
377 SELECT * FROM t3;
378
379 INSERT INTO t3 VALUES(150), (180);
380 UPDATE t3 SET a = 200 WHERE a = 150;
381 INSERT INTO t3 VALUES(220);
382 --echo # This still fails to set to 120, but just 221
383 ALTER TABLE t3 AUTO_INCREMENT = 120;
384 SHOW CREATE TABLE t3;
385 INSERT INTO t3 VALUES(0);
386 SELECT MAX(a) AS `Expect 221` FROM t3;
387
388 DELETE FROM t3 WHERE a > 120;
389
390 ALTER TABLE t3 AUTO_INCREMENT = 120;
391 SHOW CREATE TABLE t3;
392
393 --source include/kill_and_restart_mysqld.inc
394
395 INSERT INTO t3 VALUES(0);
396 SELECT MAX(a) AS `Expect 120` FROM t3;
397
398 set global innodb_flush_log_at_trx_commit=1;
399
400 INSERT INTO t3 VALUES(0), (0), (200), (210);
401
402 --echo # Test the different algorithms in ALTER TABLE
403
404 --let $template = t3
405 --let $algorithm = INPLACE
406 --let $table = t_inplace
407 --source suite/innodb/include/autoinc_persist_alter.inc
408 --let $algorithm = COPY
409 --let $table = t_copy
410 --source suite/innodb/include/autoinc_persist_alter.inc
411
412 --echo # Scenario 9: Test the sql_mode = NO_AUTO_VALUE_ON_ZERO
413
414 CREATE TABLE t30 (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, b INT, key(b)) ENGINE = InnoDB;
415
416 set SQL_MODE = NO_AUTO_VALUE_ON_ZERO;
417
418 INSERT INTO t30 VALUES(NULL, 1), (200, 2), (0, 3);
419 INSERT INTO t30(b) VALUES(4), (5), (6), (7);
420 SELECT * FROM t30 ORDER BY b;
421 ALTER TABLE t30 MODIFY b MEDIUMINT;
422 SELECT * FROM t30 ORDER BY b;
423
424 set global innodb_flush_log_at_trx_commit=1;
425
426 CREATE TABLE t31 (a INT) ENGINE = InnoDB;
427 INSERT INTO t31 VALUES(1), (2);
428 ALTER TABLE t31 ADD b INT AUTO_INCREMENT PRIMARY KEY;
429 INSERT INTO t31 VALUES(3, 0), (4, NULL), (5, NULL);
430 --error ER_DUP_ENTRY
431 INSERT INTO t31 VALUES(6, 0);
432 SELECT * FROM t31;
433
434 --source include/kill_and_restart_mysqld.inc
435
436 --echo # This will not insert 0
437 INSERT INTO t31(a) VALUES(6), (0);
438 SELECT * FROM t31;
439 DROP TABLE t31;
440
441 set SQL_MODE = NO_AUTO_VALUE_ON_ZERO;
442
443 DELETE FROM t30 WHERE a = 0;
444 UPDATE t30 set a = 0 where b = 5;
445 SELECT * FROM t30 ORDER BY b;
446 DELETE FROM t30 WHERE a = 0;
447
448 UPDATE t30 SET a = NULL WHERE b = 6;
449 UPDATE t30 SET a = 300 WHERE b = 7;
450
451 SELECT * FROM t30 ORDER BY b;
452
453 SET SQL_MODE = 0;
454
455 --echo # Scenario 10: Rollback would not rollback the counter
456 CREATE TABLE t32 (
457 a BIGINT NOT NULL PRIMARY KEY AUTO_INCREMENT) ENGINE=InnoDB;
458
459 INSERT INTO t32 VALUES(0), (0);
460
461 set global innodb_flush_log_at_trx_commit=1;
462
463 START TRANSACTION;
464 INSERT INTO t32 VALUES(0), (0);
465 SELECT MAX(a) AS `Expect 4` FROM t32;
466 DELETE FROM t32 WHERE a >= 2;
467 ROLLBACK;
468
469 --source include/kill_and_restart_mysqld.inc
470
471 SELECT MAX(a) AS `Expect 2` FROM t32;
472 INSERT INTO t32 VALUES(0), (0);
473 SELECT MAX(a) AS `Expect 6` FROM t32;
474
475 --echo # Scenario 11: Test duplicate primary key/secondary key will not stop
476 --echo # increasing the counter
477
478 CREATE TABLE t33 (
479 a BIGINT NOT NULL PRIMARY KEY,
480 b BIGINT NOT NULL AUTO_INCREMENT,
481 KEY(b)) ENGINE = InnoDB;
482
483 INSERT INTO t33 VALUES(1, NULL);
484 INSERT INTO t33 VALUES(2, NULL);
485 --error ER_DUP_ENTRY
486 INSERT INTO t33 VALUES(2, NULL);
487
488 INSERT INTO t33 VALUES(3, NULL);
489 SELECT MAX(b) AS `Expect 4` FROM t33;
490
491 TRUNCATE TABLE t33;
492
493 INSERT INTO t33 VALUES(1, NULL);
494 INSERT INTO t33 VALUES(2, NULL);
495
496 --echo # To make sure the redo logs for autoinc counter must have been flushed
497 --echo # after commit, we start the transaction manually and do some other
498 --echo # operations to generate some redo logs. Or else the redo logs in the
499 --echo # single INSERT statement would not be flushed since the INSERT would fail
500
501 set global innodb_flush_log_at_trx_commit=1;
502
503 START TRANSACTION;
504 UPDATE t33 SET a = 10 WHERE a = 1;
505 --error ER_DUP_ENTRY
506 INSERT INTO t33 VALUES(2, NULL);
507 COMMIT;
508
509 --source include/kill_and_restart_mysqld.inc
510
511 INSERT INTO t33 VALUES(3, NULL);
512 SELECT MAX(b) AS `Expect 4` FROM t33;
513
514 --echo # Scenario 12: This should work with virtual column. We should make sure
515 --echo # that the column position of autoinc counter is calculated correctly
516
517 CREATE TABLE v1 (
518 a INT NOT NULL,
519 d INT NOT NULL,
520 b BLOB NOT NULL,
521 c TEXT,
522 vadcol INT AS (a + length(d)) STORED,
523 cbcol CHAR(2) AS (substr(b, 2, 2)) VIRTUAL,
524 vbidxcol CHAR(3) AS (substr(b, 1, 3)) VIRTUAL,
525 pkey INT NOT NULL AUTO_INCREMENT,
526 leader INT NOT NULL,
527 leaderpoint POINT NOT NULL,
528 PRIMARY KEY(pkey)) ENGINE = InnoDB;
529
530 INSERT INTO v1(a, d, b, c, pkey, leader, leaderpoint) VALUES
531 (1, 2, 'abcdefg', 'qwertyu', 20, 5, ST_PointFromText('POINT(1 2)'));
532 INSERT INTO v1(a, d, b, c, leader, leaderpoint) VALUES
533 (1, 2, 'abcdefg', 'qwertyu', 5, ST_PointFromText('POINT(1 2)'));
534 SELECT pkey, a, d, b, c FROM v1;
535
536 CREATE TABLE v2 (
537 a INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
538 c CHAR(5) NOT NULL,
539 vaccol INT AS (length(c) * 2) VIRTUAL) ENGINE = InnoDB;
540
541 INSERT INTO v2(a, c) VALUES(5, 'ABC');
542 SELECT * FROM v2;
543
544 CREATE TABLE v3 (
545 a INT NOT NULL,
546 b INT NOT NULL,
547 c INT NOT NULL,
548 d VARCHAR(100) NOT NULL,
549 vacol INT AS (a + b) VIRTUAL,
550 vadcol CHAR(3) AS (substr(d, 1, 3)) STORED,
551 pkey INT NOT NULL AUTO_INCREMENT,
552 e CHAR(10) NOT NULL,
553 vbecol CHAR(2) AS (substr(e, 2, 1)) VIRTUAL,
554 g POINT NOT NULL,
555 PRIMARY KEY(pkey)) ENGINE = InnoDB;
556
557 INSERT INTO v3(a, b, c, d, pkey, e, g) VALUES
558 (1, 2, 3, 'This is a test', 10, 'MySQL', ST_PointFromText('POINT(10 20)'));
559 SELECT a, pkey, d FROM v3;
560
561 ALTER TABLE v3 DROP COLUMN c;
562
563 INSERT INTO v3(a, b, d, pkey, e, g) VALUES
564 (1, 2, 'This is a test', 0, 'MySQL', ST_PointFromText('POINT(10 20)'));
565 SELECT a, pkey, d FROM v3;
566
567 DROP TABLE v1, v2, v3;
568
569 DROP TABLE t1, t2, t3, t4, t5, t6, t7, t8, t9, t10, t11, t12, t13, t30, t32, t33;
570
571 CREATE TABLE t1 (
572 a INT NOT NULL,
573 d VARCHAR(15) NOT NULL,
574 b VARCHAR(198) NOT NULL,
575 c CHAR(187),
576 vadcol INT AS (a + length(d)) STORED,
577 vbcol CHAR(2) AS (substr(b, 2, 2)) VIRTUAL,
578 vbidxcol CHAR(3) AS (substr(b, 1, 3)) VIRTUAL,
579 INDEX(d),
580 INDEX(a),
581 INDEX(vbidxcol),
582 INDEX(a,vbidxcol),
583 INDEX(vbidxcol,d)) ENGINE=InnoDB;
584
585 INSERT INTO t1(a, d, b, c) VALUES (1, 'database', 'storage', 'engine');
586
587 ALTER TABLE t1 DROP COLUMN vbcol, ADD COLUMN nc07226 BIGINT AUTO_INCREMENT NOT NULL, ADD UNIQUE KEY auto_nc07226(nc07226), ADD COLUMN nc05019 BOOL NOT NULL FIRST, LOCK=EXCLUSIVE;
588
589 UPDATE t1 SET b = repeat('dbms', 3);
590
591 INSERT INTO t1(a, d, b, c, nc07226, nc05019) VALUES (2, 'database', 'storage', 'engine', 0, false);
592
593 UPDATE t1 SET c = repeat('engine', 3);
594
595 SELECT * FROM t1;
596
597 DROP TABLE t1;
598
599 # Check if the AUTOINC can be kept after a no-op ALTER TABLE
600 CREATE TABLE t1 (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, b INT) ENGINE = InnoDB;
601 INSERT INTO t1 VALUES(0, 1), (2, 4), (3, 6);
602 ALTER TABLE t1 CHANGE COLUMN b b INTEGER, ALGORITHM DEFAULT;
603 SHOW CREATE TABLE t1;
604 ALTER TABLE t1 AUTO_INCREMENT = 20, CHANGE COLUMN b b INT, ALGORITHM DEFAULT;
605 SHOW CREATE TABLE t1;
606 INSERT INTO t1 VALUES(NULL, 10);
607 SELECT MAX(a) FROM t1;
608 ALTER TABLE t1 AUTO_INCREMENT = 10, CHANGE COLUMN b b INTEGER, ALGORITHM DEFAULT;
609 SHOW CREATE TABLE t1;
610 INSERT INTO t1 VALUES(NULL, 12);
611 SELECT MAX(a) FROM t1;
612 DROP TABLE t1;