1--source include/have_sequence.inc 2--source include/have_innodb.inc 3 4drop table if exists t1; 5 6--echo # 7--echo # Test alter sequence 8--echo # 9 10CREATE SEQUENCE t1 nocache engine=myisam; 11select * from t1; 12select next value for t1; 13alter sequence t1 start=50; 14show create sequence t1; 15select * from t1; 16select next value for t1; 17 18alter sequence t1 minvalue=-100; 19show create sequence t1; 20select * from t1; 21--error ER_SEQUENCE_INVALID_DATA 22alter sequence t1 minvalue=100 start=100; 23alter sequence t1 minvalue=100 start=100 restart=100; 24show create sequence t1; 25select * from t1; 26 27alter sequence t1 maxvalue=500; 28show create sequence t1; 29select * from t1; 30drop sequence t1; 31 32CREATE SEQUENCE t1 engine=myisam; 33alter sequence t1 nocache; 34show create sequence t1; 35alter sequence t1 cache=100; 36flush tables; 37show create sequence t1; 38alter sequence t1 nocache; 39show create sequence t1; 40flush tables; 41show create sequence t1; 42select * from t1; 43select next value for t1; 44select next value for t1; 45select next value for t1; 46select next_not_cached_value, cycle_count from t1; 47drop sequence t1; 48 49CREATE SEQUENCE t1 maxvalue=100 engine=myisam; 50alter sequence t1 no maxvalue; 51show create sequence t1; 52select * from t1; 53alter sequence t1 cycle; 54show create sequence t1; 55alter sequence t1 nocycle; 56alter sequence t1 start=15 restart minvalue=10 maxvalue=20 cycle; 57show create sequence t1; 58select * from t1; 59select NEXT VALUE for t1 from seq_1_to_10; 60alter sequence t1 restart with 17 minvalue=10 maxvalue=20 cycle; 61select NEXT VALUE for t1 from seq_1_to_10; 62drop sequence t1; 63 64CREATE SEQUENCE t1 maxvalue=100; 65alter sequence t1 increment=-2 start with 50 minvalue=-100; 66show create sequence t1; 67select * from t1; 68select NEXT VALUE for t1 from seq_1_to_10; 69drop sequence t1; 70 71--echo # 72--echo # InnoDB (some things work different with InnoDB) 73--echo 74 75CREATE SEQUENCE t1 cache 10 engine=innodb; 76select * from t1; 77select next value for t1; 78alter sequence t1 start=100; 79show create sequence t1; 80select * from t1; 81select next value for t1; 82drop sequence t1; 83 84--echo # 85--echo # ALTER TABLE 86--echo # 87 88CREATE SEQUENCE t1 engine=innodb; 89select next value for t1; 90alter table t1 rename t2; 91select next value for t2; 92rename table t2 to t1; 93select next value for t1; 94alter table t1 comment="foo"; 95show create sequence t1; 96alter table t1 engine=myisam; 97show create sequence t1; 98alter table t1 engine=innodb; 99show create sequence t1; 100select * from t1; 101drop sequence t1; 102 103# 104# Some error testing 105# 106 107CREATE SEQUENCE t1 engine=myisam; 108--error ER_SEQUENCE_INVALID_DATA 109alter sequence t1 minvalue=100; 110drop sequence t1; 111 112CREATE SEQUENCE t1 engine=myisam; 113--error ER_SEQUENCE_INVALID_DATA 114alter sequence t1 minvalue=25 maxvalue=20; 115drop sequence t1; 116 117create table t1 (a int); 118--error ER_NOT_SEQUENCE 119alter sequence t1 minvalue=100; 120drop table t1; 121 122alter sequence if exists t1 minvalue=100; 123--error ER_NO_SUCH_TABLE 124alter sequence t1 minvalue=100; 125 126create sequence t1; 127--error ER_PARSE_ERROR 128alter sequence t1; 129drop sequence t1; 130 131CREATE SEQUENCE t1 maxvalue=100; 132alter sequence t1 increment=-2 start with 50; 133select next value for t1; 134--error ER_SEQUENCE_RUN_OUT 135select next value for t1; 136select * from t1; 137alter sequence t1 restart; 138select next value for t1; 139alter sequence t1 restart with 90; 140select next value for t1; 141drop sequence t1; 142 143# 144# MDEV-19977 Assertion `(0xFUL & mode) == LOCK_S || (0xFUL & mode) == LOCK_X' 145# failed in lock_rec_lock 146# 147 148CREATE SEQUENCE t1 engine=innodb; 149--error ER_SEQUENCE_INVALID_TABLE_STRUCTURE 150ALTER IGNORE TABLE t1 ADD CHECK (start_value < minimum_value); 151DROP SEQUENCE t1; 152 153# 154# MDEV-19320 Sequence gets corrupted and produces ER_KEY_NOT_FOUND (Can't 155# find record) after ALTER .. ORDER BY 156# 157 158CREATE SEQUENCE s; 159--error ER_SEQUENCE_INVALID_TABLE_STRUCTURE 160ALTER TABLE s ORDER BY cache_size; 161SELECT NEXTVAL(s); 162DROP SEQUENCE s; 163