1drop table if exists t1; 2Warnings: 3Note 1051 Unknown table 'test.t1' 4# 5# Test alter sequence 6# 7CREATE SEQUENCE t1 nocache engine=myisam; 8select * from t1; 9next_not_cached_value minimum_value maximum_value start_value increment cache_size cycle_option cycle_count 101 1 9223372036854775806 1 1 0 0 0 11select next value for t1; 12next value for t1 131 14alter sequence t1 start=50; 15show create sequence t1; 16Table Create Table 17t1 CREATE SEQUENCE `t1` start with 50 minvalue 1 maxvalue 9223372036854775806 increment by 1 nocache nocycle ENGINE=MyISAM 18select * from t1; 19next_not_cached_value minimum_value maximum_value start_value increment cache_size cycle_option cycle_count 202 1 9223372036854775806 50 1 0 0 0 21select next value for t1; 22next value for t1 232 24alter sequence t1 minvalue=-100; 25show create sequence t1; 26Table Create Table 27t1 CREATE SEQUENCE `t1` start with 50 minvalue -100 maxvalue 9223372036854775806 increment by 1 nocache nocycle ENGINE=MyISAM 28select * from t1; 29next_not_cached_value minimum_value maximum_value start_value increment cache_size cycle_option cycle_count 303 -100 9223372036854775806 50 1 0 0 0 31alter sequence t1 minvalue=100 start=100; 32ERROR HY000: Sequence 'test.t1' values are conflicting 33alter sequence t1 minvalue=100 start=100 restart=100; 34show create sequence t1; 35Table Create Table 36t1 CREATE SEQUENCE `t1` start with 100 minvalue 100 maxvalue 9223372036854775806 increment by 1 nocache nocycle ENGINE=MyISAM 37select * from t1; 38next_not_cached_value minimum_value maximum_value start_value increment cache_size cycle_option cycle_count 39100 100 9223372036854775806 100 1 0 0 0 40alter sequence t1 maxvalue=500; 41show create sequence t1; 42Table Create Table 43t1 CREATE SEQUENCE `t1` start with 100 minvalue 100 maxvalue 500 increment by 1 nocache nocycle ENGINE=MyISAM 44select * from t1; 45next_not_cached_value minimum_value maximum_value start_value increment cache_size cycle_option cycle_count 46100 100 500 100 1 0 0 0 47drop sequence t1; 48CREATE SEQUENCE t1 engine=myisam; 49alter sequence t1 nocache; 50show create sequence t1; 51Table Create Table 52t1 CREATE SEQUENCE `t1` start with 1 minvalue 1 maxvalue 9223372036854775806 increment by 1 nocache nocycle ENGINE=MyISAM 53alter sequence t1 cache=100; 54flush tables; 55show create sequence t1; 56Table Create Table 57t1 CREATE SEQUENCE `t1` start with 1 minvalue 1 maxvalue 9223372036854775806 increment by 1 cache 100 nocycle ENGINE=MyISAM 58alter sequence t1 nocache; 59show create sequence t1; 60Table Create Table 61t1 CREATE SEQUENCE `t1` start with 1 minvalue 1 maxvalue 9223372036854775806 increment by 1 nocache nocycle ENGINE=MyISAM 62flush tables; 63show create sequence t1; 64Table Create Table 65t1 CREATE SEQUENCE `t1` start with 1 minvalue 1 maxvalue 9223372036854775806 increment by 1 nocache nocycle ENGINE=MyISAM 66select * from t1; 67next_not_cached_value minimum_value maximum_value start_value increment cache_size cycle_option cycle_count 681 1 9223372036854775806 1 1 0 0 0 69select next value for t1; 70next value for t1 711 72select next value for t1; 73next value for t1 742 75select next value for t1; 76next value for t1 773 78select next_not_cached_value, cycle_count from t1; 79next_not_cached_value cycle_count 804 0 81drop sequence t1; 82CREATE SEQUENCE t1 maxvalue=100 engine=myisam; 83alter sequence t1 no maxvalue; 84show create sequence t1; 85Table Create Table 86t1 CREATE SEQUENCE `t1` start with 1 minvalue 1 maxvalue 9223372036854775806 increment by 1 cache 1000 nocycle ENGINE=MyISAM 87select * from t1; 88next_not_cached_value minimum_value maximum_value start_value increment cache_size cycle_option cycle_count 891 1 9223372036854775806 1 1 1000 0 0 90alter sequence t1 cycle; 91show create sequence t1; 92Table Create Table 93t1 CREATE SEQUENCE `t1` start with 1 minvalue 1 maxvalue 9223372036854775806 increment by 1 cache 1000 cycle ENGINE=MyISAM 94alter sequence t1 nocycle; 95alter sequence t1 start=15 restart minvalue=10 maxvalue=20 cycle; 96show create sequence t1; 97Table Create Table 98t1 CREATE SEQUENCE `t1` start with 15 minvalue 10 maxvalue 20 increment by 1 cache 1000 cycle ENGINE=MyISAM 99select * from t1; 100next_not_cached_value minimum_value maximum_value start_value increment cache_size cycle_option cycle_count 10115 10 20 15 1 1000 1 0 102select NEXT VALUE for t1 from seq_1_to_10; 103NEXT VALUE for t1 10415 10516 10617 10718 10819 10920 11010 11111 11212 11313 114alter sequence t1 restart with 17 minvalue=10 maxvalue=20 cycle; 115select NEXT VALUE for t1 from seq_1_to_10; 116NEXT VALUE for t1 11717 11818 11919 12020 12110 12211 12312 12413 12514 12615 127drop sequence t1; 128CREATE SEQUENCE t1 maxvalue=100; 129alter sequence t1 increment=-2 start with 50 minvalue=-100; 130show create sequence t1; 131Table Create Table 132t1 CREATE SEQUENCE `t1` start with 50 minvalue -100 maxvalue 100 increment by -2 cache 1000 nocycle ENGINE=MyISAM 133select * from t1; 134next_not_cached_value minimum_value maximum_value start_value increment cache_size cycle_option cycle_count 1351 -100 100 50 -2 1000 0 0 136select NEXT VALUE for t1 from seq_1_to_10; 137NEXT VALUE for t1 1381 139-1 140-3 141-5 142-7 143-9 144-11 145-13 146-15 147-17 148drop sequence t1; 149# 150# InnoDB (some things work different with InnoDB) 151 152CREATE SEQUENCE t1 cache 10 engine=innodb; 153select * from t1; 154next_not_cached_value minimum_value maximum_value start_value increment cache_size cycle_option cycle_count 1551 1 9223372036854775806 1 1 10 0 0 156select next value for t1; 157next value for t1 1581 159alter sequence t1 start=100; 160show create sequence t1; 161Table Create Table 162t1 CREATE SEQUENCE `t1` start with 100 minvalue 1 maxvalue 9223372036854775806 increment by 1 cache 10 nocycle ENGINE=InnoDB 163select * from t1; 164next_not_cached_value minimum_value maximum_value start_value increment cache_size cycle_option cycle_count 16511 1 9223372036854775806 100 1 10 0 0 166select next value for t1; 167next value for t1 16811 169drop sequence t1; 170# 171# ALTER TABLE 172# 173CREATE SEQUENCE t1 engine=innodb; 174select next value for t1; 175next value for t1 1761 177alter table t1 rename t2; 178select next value for t2; 179next value for t2 1801001 181rename table t2 to t1; 182select next value for t1; 183next value for t1 1842001 185alter table t1 comment="foo"; 186show create sequence t1; 187Table Create Table 188t1 CREATE SEQUENCE `t1` start with 1 minvalue 1 maxvalue 9223372036854775806 increment by 1 cache 1000 nocycle ENGINE=InnoDB COMMENT='foo' 189alter table t1 engine=myisam; 190show create sequence t1; 191Table Create Table 192t1 CREATE SEQUENCE `t1` start with 1 minvalue 1 maxvalue 9223372036854775806 increment by 1 cache 1000 nocycle ENGINE=MyISAM COMMENT='foo' 193alter table t1 engine=innodb; 194show create sequence t1; 195Table Create Table 196t1 CREATE SEQUENCE `t1` start with 1 minvalue 1 maxvalue 9223372036854775806 increment by 1 cache 1000 nocycle ENGINE=InnoDB COMMENT='foo' 197select * from t1; 198next_not_cached_value minimum_value maximum_value start_value increment cache_size cycle_option cycle_count 1993001 1 9223372036854775806 1 1 1000 0 0 200drop sequence t1; 201CREATE SEQUENCE t1 engine=myisam; 202alter sequence t1 minvalue=100; 203ERROR HY000: Sequence 'test.t1' values are conflicting 204drop sequence t1; 205CREATE SEQUENCE t1 engine=myisam; 206alter sequence t1 minvalue=25 maxvalue=20; 207ERROR HY000: Sequence 'test.t1' values are conflicting 208drop sequence t1; 209create table t1 (a int); 210alter sequence t1 minvalue=100; 211ERROR 42S02: 'test.t1' is not a SEQUENCE 212drop table t1; 213alter sequence if exists t1 minvalue=100; 214Warnings: 215Note 4091 Unknown SEQUENCE: 'test.t1' 216alter sequence t1 minvalue=100; 217ERROR 42S02: Table 'test.t1' doesn't exist 218create sequence t1; 219alter sequence t1; 220ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '' at line 1 221drop sequence t1; 222CREATE SEQUENCE t1 maxvalue=100; 223alter sequence t1 increment=-2 start with 50; 224select next value for t1; 225next value for t1 2261 227select next value for t1; 228ERROR HY000: Sequence 'test.t1' has run out 229select * from t1; 230next_not_cached_value minimum_value maximum_value start_value increment cache_size cycle_option cycle_count 2310 1 100 50 -2 1000 0 0 232alter sequence t1 restart; 233select next value for t1; 234next value for t1 23550 236alter sequence t1 restart with 90; 237select next value for t1; 238next value for t1 23990 240drop sequence t1; 241CREATE SEQUENCE t1 engine=innodb; 242ALTER IGNORE TABLE t1 ADD CHECK (start_value < minimum_value); 243ERROR HY000: Sequence 'test.t1' table structure is invalid (Sequence tables cannot have any constraints) 244DROP SEQUENCE t1; 245CREATE SEQUENCE s; 246ALTER TABLE s ORDER BY cache_size; 247ERROR HY000: Sequence 'test.s' table structure is invalid (ORDER BY) 248SELECT NEXTVAL(s); 249NEXTVAL(s) 2501 251DROP SEQUENCE s; 252