Debian Codegraph @

mysql-test/suite/sql_sequence/alter.test

raw
1 --source include/have_sequence.inc
2 --source include/have_innodb.inc
3
4 drop table if exists t1;
5
6 --echo #
7 --echo # Test alter sequence
8 --echo #
9
10 --disable_ps2_protocol
11 CREATE SEQUENCE t1 nocache engine=myisam;
12 select * from t1;
13 select next value for t1;
14 alter sequence t1 start=50;
15 show create sequence t1;
16 select * from t1;
17 select next value for t1;
18
19 alter sequence t1 minvalue=-100;
20 show create sequence t1;
21 select * from t1;
22 --error ER_SEQUENCE_INVALID_DATA
23 alter sequence t1 minvalue=100 start=100;
24 alter sequence t1 minvalue=100 start=100 restart=100;
25 show create sequence t1;
26 select * from t1;
27
28 alter sequence t1 maxvalue=500;
29 show create sequence t1;
30 select * from t1;
31 drop sequence t1;
32
33 CREATE SEQUENCE t1 engine=myisam;
34 alter sequence t1 nocache;
35 show create sequence t1;
36 alter sequence t1 cache=100;
37 flush tables;
38 show create sequence t1;
39 alter sequence t1 nocache;
40 show create sequence t1;
41 flush tables;
42 show create sequence t1;
43 select * from t1;
44 select next value for t1;
45 select next value for t1;
46 select next value for t1;
47 select next_not_cached_value, cycle_count from t1;
48 drop sequence t1;
49
50 CREATE SEQUENCE t1 maxvalue=100 engine=myisam;
51 alter sequence t1 no maxvalue;
52 show create sequence t1;
53 select * from t1;
54 alter sequence t1 cycle;
55 show create sequence t1;
56 alter sequence t1 nocycle;
57 alter sequence t1 start=15 restart minvalue=10 maxvalue=20 cycle;
58 show create sequence t1;
59 select * from t1;
60 select NEXT VALUE for t1 from seq_1_to_10;
61 alter sequence t1 restart with 17 minvalue=10 maxvalue=20 cycle;
62 select NEXT VALUE for t1 from seq_1_to_10;
63 drop sequence t1;
64
65 CREATE SEQUENCE t1 maxvalue=100;
66 alter sequence t1 increment=-2 start with 50 minvalue=-100;
67 show create sequence t1;
68 select * from t1;
69 select NEXT VALUE for t1 from seq_1_to_10;
70 drop sequence t1;
71
72 --echo #
73 --echo # InnoDB (some things work different with InnoDB)
74 --echo
75
76 CREATE SEQUENCE t1 cache 10 engine=innodb;
77 select * from t1;
78 select next value for t1;
79 alter sequence t1 start=100;
80 show create sequence t1;
81 select * from t1;
82 select next value for t1;
83 let $check_innodb_flags =
84 select flag & 12288 is_sequence from information_schema.innodb_sys_tables
85 where name='test/t1';
86 evalp $check_innodb_flags;
87 alter table t1 sequence=0;
88 begin;
89 delete from t1;
90 rollback;
91 evalp $check_innodb_flags;
92 alter table t1 sequence=1;
93 evalp $check_innodb_flags;
94 alter table t1 sequence=0, algorithm=copy;
95 evalp $check_innodb_flags;
96 --error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON
97 alter table t1 sequence=1, algorithm=inplace;
98 alter table t1 sequence=1, algorithm=copy;
99 evalp $check_innodb_flags;
100 --error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON
101 alter table t1 sequence=0, algorithm=inplace;
102 drop sequence t1;
103
104 --echo #
105 --echo # ALTER TABLE
106 --echo #
107
108 CREATE SEQUENCE t1 engine=innodb;
109 select next value for t1;
110 alter table t1 rename t2;
111 select next value for t2;
112 rename table t2 to t1;
113 select next value for t1;
114 alter table t1 comment="foo";
115 show create sequence t1;
116 alter table t1 engine=myisam;
117 show create sequence t1;
118 alter table t1 engine=innodb;
119 show create sequence t1;
120 select * from t1;
121 drop sequence t1;
122
123 #
124 # Some error testing
125 #
126
127 CREATE SEQUENCE t1 engine=myisam;
128 --error ER_SEQUENCE_INVALID_DATA
129 alter sequence t1 minvalue=100;
130 drop sequence t1;
131
132 CREATE SEQUENCE t1 engine=myisam;
133 --error ER_SEQUENCE_INVALID_DATA
134 alter sequence t1 minvalue=25 maxvalue=20;
135 drop sequence t1;
136
137 create table t1 (a int);
138 --error ER_NOT_SEQUENCE
139 alter sequence t1 minvalue=100;
140 drop table t1;
141
142 --echo #
143 --echo # MDEV-32795: ALTER SEQUENCE IF NOT EXISTS non_existing_seq Errors rather than note
144 --echo #
145
146 alter sequence if exists t1 minvalue=100;
147
148 --error ER_UNKNOWN_SEQUENCES
149 alter sequence t1 minvalue=100;
150
151 create sequence t1;
152 --error ER_PARSE_ERROR
153 alter sequence t1;
154 drop sequence t1;
155
156 CREATE SEQUENCE t1 maxvalue=100;
157 alter sequence t1 increment=-2 start with 50;
158 select next value for t1;
159 --error ER_SEQUENCE_RUN_OUT
160 select next value for t1;
161 select * from t1;
162 alter sequence t1 restart;
163 select next value for t1;
164 alter sequence t1 restart with 90;
165 select next value for t1;
166 drop sequence t1;
167
168 #
169 # MDEV-19977 Assertion `(0xFUL & mode) == LOCK_S || (0xFUL & mode) == LOCK_X'
170 # failed in lock_rec_lock
171 #
172
173 CREATE SEQUENCE t1 engine=innodb;
174 --error ER_SEQUENCE_INVALID_TABLE_STRUCTURE
175 ALTER IGNORE TABLE t1 ADD CHECK (start_value < minimum_value);
176 DROP SEQUENCE t1;
177
178 #
179 # MDEV-19320 Sequence gets corrupted and produces ER_KEY_NOT_FOUND (Can't
180 # find record) after ALTER .. ORDER BY
181 #
182
183 CREATE SEQUENCE s;
184 --error ER_SEQUENCE_INVALID_TABLE_STRUCTURE
185 ALTER TABLE s ORDER BY cache_size;
186 SELECT NEXTVAL(s);
187 DROP SEQUENCE s;
188
189 --echo #
190 --echo # MDEV-33169 Alter sequence 2nd ps fails while alter sequence 2nd time (no ps) succeeds
191 --echo #
192 create sequence s;
193 show create sequence s;
194 alter sequence s maxvalue 123;
195 show create sequence s;
196 alter sequence s maxvalue 123;
197 show create sequence s;
198 drop sequence s;
199
200 create sequence s;
201 show create sequence s;
202 prepare stmt from 'alter sequence s maxvalue 123';
203 execute stmt;
204 show create sequence s;
205 execute stmt;
206 show create sequence s;
207 deallocate prepare stmt;
208 drop sequence s;
209
210 create sequence s;
211 show create sequence s;
212 create procedure p() alter sequence s maxvalue 123;
213 call p;
214 show create sequence s;
215 call p;
216 show create sequence s;
217 drop procedure p;
218 drop sequence s;
219
220 --echo #
221 --echo # End of 10.4 tests
222 --echo #
223
224 --echo #
225 --echo # MDEV-32350 Can't selectively restore sequences using innodb tables from
226 --echo # backup
227 --echo #
228
229 --disable_ps_protocol
230
231 create sequence s2 engine=innodb;
232 alter table s2 discard tablespace;
233 --error ER_GET_ERRNO
234 SELECT NEXTVAL(s2);
235 create sequence s1 engine=innodb;
236 select * from s1;
237 flush tables s1 for export;
238 --let $MYSQLD_DATADIR= `select @@datadir`
239 --move_file $MYSQLD_DATADIR/test/s1.cfg $MYSQLD_DATADIR/test/s2.cfg
240 --copy_file $MYSQLD_DATADIR/test/s1.ibd $MYSQLD_DATADIR/test/s2.ibd
241 unlock tables;
242 --error ER_GET_ERRNO
243 select * from s2;
244 --error ER_GET_ERRNO
245 SELECT NEXTVAL(s2);
246 --error ER_GET_ERRNO
247 alter sequence s2 restart;
248 alter table s2 import tablespace;
249 select * from s2;
250 SELECT NEXTVAL(s2);
251 select NEXTVAL(s1);
252 flush table s1,s2;
253 select * from s1;
254 select * from s2;
255 drop sequence s1,s2;
256
257 --enable_ps_protocol
258
259 --echo #
260 --echo # MDEV-35144 CREATE TABLE ... LIKE uses current innodb_compression_default instead of the create value
261 --echo #
262
263 set @@innodb_compression_default= off;
264 create or replace sequence s engine=innodb;
265 set @@innodb_compression_default= on;
266 create or replace table s_import like s;
267
268 show create table s;
269 show create table s_import;
270
271 alter table s_import discard tablespace;
272 flush table s for export;
273 --copy_file $MYSQLD_DATADIR/test/s.ibd $MYSQLD_DATADIR/test/s_import.ibd
274 --copy_file $MYSQLD_DATADIR/test/s.cfg $MYSQLD_DATADIR/test/s_import.cfg
275 UNLOCK TABLES;
276 alter table s_import import tablespace;
277 drop table s,s_import;
278
279 --echo # End of 10.5 tests
280
281 --echo #
282 --echo # MDEV-31607 ER_DUP_KEY in mysql.table_stats upon REANME on sequence
283 --echo #
284
285 CREATE SEQUENCE s1 ENGINE=InnoDB;
286 CREATE SEQUENCE s2 ENGINE=InnoDB;
287 SHOW CREATE SEQUENCE s1;
288 SHOW CREATE SEQUENCE s2;
289 DROP SEQUENCE s2;
290 RENAME TABLE s1 TO s2;
291 DROP SEQUENCE s2;
292
293 --echo #
294 --echo # End of 10.6 tests
295 --echo #
296
297 --echo #
298 --echo # MDEV-36032 Check when doing ALTER TABLE table_name sequence=1 that table can be a sequence
299 --echo #
300
301 ## Too many rows
302 create sequence s;
303 alter table s sequence=0;
304 insert into s values (3,1,9223372036854775806,1,1,1000,0,0);
305 --error ER_INTERNAL_ERROR
306 alter table s sequence=1;
307 drop table s;
308
309 ## Insert a wrong row (min > max)
310 create sequence s;
311 alter table s sequence=0;
312 delete from s;
313 insert into s values (2,500,200,1,1,1000,0,0);
314 select * from s;
315 --error ER_SEQUENCE_INVALID_DATA
316 alter table s sequence=1;
317 check table s;
318 select * from s;
319 check table s;
320 drop table s;
321
322 ## Invalid table structure (already implemented before MDEV-36032)
323 CREATE TABLE `s` (
324 # `next_not_cached_value` bigint(21) NOT NULL,
325 `minimum_value` bigint(21) NOT NULL,
326 `maximum_value` bigint(21) NOT NULL,
327 `start_value` bigint(21) NOT NULL COMMENT 'start value when sequences is created or value if RESTART is used',
328 `increment` bigint(21) NOT NULL COMMENT 'increment value',
329 `cache_size` bigint(21) unsigned NOT NULL,
330 `cycle_option` tinyint(1) unsigned NOT NULL COMMENT '0 if no cycles are allowed, 1 if the sequence should begin a new cycle when maximum_value is passed',
331 `cycle_count` bigint(21) NOT NULL COMMENT 'How many cycles have been done'
332 ) ENGINE=innodb;
333 --error ER_SEQUENCE_INVALID_TABLE_STRUCTURE
334 alter table s sequence=1;
335 drop table s;
336
337 ## Altering a sequence table to a wrong structure is detected (already
338 ## implemented before MDEV-36032)
339 create sequence s;
340 --error ER_SEQUENCE_INVALID_TABLE_STRUCTURE
341 alter table s drop column next_not_cached_value;
342 drop sequence s;
343
344 ## Create a normal table then alter to sequence
345 CREATE TABLE `s1` (
346 `next_not_cached_value` bigint(21) NOT NULL,
347 `minimum_value` bigint(21) NOT NULL,
348 `maximum_value` bigint(21) NOT NULL,
349 `start_value` bigint(21) NOT NULL COMMENT 'start value when sequences is created or value if RESTART is used',
350 `increment` bigint(21) NOT NULL COMMENT 'increment value',
351 `cache_size` bigint(21) unsigned NOT NULL,
352 `cycle_option` tinyint(1) unsigned NOT NULL COMMENT '0 if no cycles are allowed, 1 if the sequence should begin a new cycle when maximum_value is passed',
353 `cycle_count` bigint(21) NOT NULL COMMENT 'How many cycles have been done'
354 ) ENGINE=innodb;
355
356 --error ER_INTERNAL_ERROR
357 alter table s1 sequence=1;
358 # (for coverage) alter a non sequence table with sequence=0
359 alter table s1 sequence=0;
360 insert into s1 values (1,1,9223372036854775806,1,1,1000,0,0);
361 alter table s1 sequence=1;
362
363 alter table s1 sequence=0;
364 insert into s1 values (2,1,9223372036854775806,1,1,1000,0,0);
365 --error ER_INTERNAL_ERROR
366 alter table s1 sequence=1;
367
368 # (for coverage) alter a non sequence table with sequence=0
369 alter table s1 sequence=0;
370 insert into s1 values (3,1,9223372036854775806,1,1,1000,0,0);
371 --error ER_INTERNAL_ERROR
372 alter table s1 sequence=1;
373
374 drop table s1;
375
376 --echo # End of 10.11 tests
377
378 --echo #
379 --echo # MDEV-28152 Features for sequence
380 --echo #
381
382 # truncation in alter sequence
383 create sequence s maxvalue 12345;
384 show create sequence s;
385 alter sequence s maxvalue 123456789012345678901234;
386 show create sequence s;
387 drop sequence s;
388
389 # alter first from a narrower type to a wider type, then maxvalue
390 create sequence s as tinyint;
391 show create sequence s;
392 show create table s;
393 alter sequence s as int;
394 show create sequence s;
395 show create table s;
396 alter sequence s maxvalue 12345;
397 show create sequence s;
398 show create table s;
399 drop sequence s;
400
401 # alter first maxvalue then from a wider type to a narrower type
402 create sequence s;
403 show create sequence s;
404 show create table s;
405 alter sequence s maxvalue 123;
406 show create sequence s;
407 show create table s;
408 alter sequence s as tinyint;
409 show create sequence s;
410 show create table s;
411 drop sequence s;
412
413 # from a wider type to a narrower type with out of range values
414 create sequence s as int;
415 show create sequence s;
416 show create table s;
417 --error ER_WARN_DATA_OUT_OF_RANGE
418 alter sequence s as tinyint;
419 alter sequence s maxvalue 126;
420 alter sequence s as tinyint;
421 drop sequence s;
422
423 # cannot alter both value type and something else yet.
424 create sequence s as tinyint;
425 show create sequence s;
426 show create table s;
427 --error ER_NOT_SUPPORTED_YET
428 alter sequence s as int maxvalue 123;
429 drop sequence s;
430
431 # alter sequence causes discarding of cache values
432 ## alter type then next
433 create sequence s as smallint;
434 select next value for s; # 1
435 alter sequence s as int;
436 select next value for s; # 1001
437 drop sequence s;
438
439 ## alter a tinyint sequence
440 create sequence s as tinyint;
441 select next value for s; # 1
442 alter sequence s cache 100;
443 --error ER_SEQUENCE_RUN_OUT
444 select next value for s;
445 drop sequence s;
446
447 # from signed to unsigned
448 create sequence s as int;
449 show create sequence s;
450 show create table s;
451 alter sequence s as int unsigned;
452 show create sequence s;
453 show create table s;
454 drop sequence s;
455
456 # for each type
457 create sequence s as tinyint;
458 alter sequence s maxvalue 123456789012345678901;
459 show create sequence s;
460 alter sequence s maxvalue 126;
461 show create sequence s;
462 select next value for s;
463 alter sequence s maxvalue 63;
464 show create sequence s;
465 #fixme: should not run out
466 --error ER_SEQUENCE_RUN_OUT
467 select next value for s;
468 drop sequence s;
469
470 create sequence s as tinyint unsigned;
471 alter sequence s maxvalue 123456789012345678901;
472 show create sequence s;
473 alter sequence s maxvalue 254;
474 show create sequence s;
475 select next value for s;
476 alter sequence s maxvalue 120;
477 show create sequence s;
478 #fixme: should not run out
479 --error ER_SEQUENCE_RUN_OUT
480 select next value for s;
481 drop sequence s;
482
483 create sequence s as smallint;
484 alter sequence s maxvalue 123456789012345678901;
485 show create sequence s;
486 alter sequence s maxvalue 32766;
487 show create sequence s;
488 select next value for s;
489 alter sequence s maxvalue 16030;
490 show create sequence s;
491 select next value for s;
492 drop sequence s;
493
494 create sequence s as smallint unsigned;
495 alter sequence s maxvalue 123456789012345678901;
496 show create sequence s;
497 alter sequence s maxvalue 65534;
498 show create sequence s;
499 select next value for s;
500 alter sequence s maxvalue 32000;
501 show create sequence s;
502 select next value for s;
503 drop sequence s;
504
505 create sequence s as mediumint;
506 alter sequence s maxvalue 123456789012345678901;
507 show create sequence s;
508 alter sequence s maxvalue 8388606;
509 show create sequence s;
510 select next value for s;
511 alter sequence s maxvalue 4223212;
512 show create sequence s;
513 select next value for s;
514 drop sequence s;
515
516 create sequence s as mediumint unsigned;
517 alter sequence s maxvalue 123456789012345678901;
518 show create sequence s;
519 alter sequence s maxvalue 16777214;
520 show create sequence s;
521 select next value for s;
522 alter sequence s maxvalue 8389231;
523 show create sequence s;
524 select next value for s;
525 drop sequence s;
526
527 create sequence s as int;
528 alter sequence s maxvalue 123456789012345678901;
529 show create sequence s;
530 alter sequence s maxvalue 2147483646;
531 show create sequence s;
532 select next value for s;
533 alter sequence s maxvalue 1234567890;
534 show create sequence s;
535 select next value for s;
536 drop sequence s;
537
538 create sequence s as int unsigned;
539 alter sequence s maxvalue 123456789012345678901;
540 show create sequence s;
541 alter sequence s maxvalue 4294967294;
542 show create sequence s;
543 select next value for s;
544 alter sequence s maxvalue 2123834923;
545 show create sequence s;
546 select next value for s;
547 drop sequence s;
548
549 create sequence s as bigint;
550 alter sequence s maxvalue 123456789012345678901;
551 show create sequence s;
552 alter sequence s maxvalue 9223372036854775806;
553 show create sequence s;
554 select next value for s;
555 alter sequence s maxvalue 4683883928492758294;
556 show create sequence s;
557 select next value for s;
558 drop sequence s;
559
560 create sequence s as bigint unsigned;
561 alter sequence s maxvalue 123456789012345678901;
562 show create sequence s;
563 alter sequence s maxvalue 18446744073709551614;
564 show create sequence s;
565 select next value for s;
566 alter sequence s maxvalue 9432738420582397432;
567 show create sequence s;
568 select next value for s;
569 drop sequence s;
570
571 --echo #
572 --echo # MDEV-33739 Assertion `0' failed in Type_handler_typelib::max_display_length_for_field
573 --echo #
574
575 CREATE SEQUENCE s1 ;
576 --error ER_SEQUENCE_INVALID_TABLE_STRUCTURE
577 ALTER table s1 CHANGE `next_not_cached_value` next_not_cached_value SET('1','2','3','4','5','6','7','8','9','10');
578 --error ER_SEQUENCE_INVALID_TABLE_STRUCTURE
579 ALTER table s1 CHANGE `next_not_cached_value` next_not_cached_value bool;
580 drop sequence s1;
581
582 --enable_ps2_protocol
583
584 --echo #
585 --echo # End of 11.5 test
586 --echo #