alter.test 4 KB
Newer Older
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20
--source include/have_sequence.inc
--source include/have_innodb.inc

drop table if exists t1;

--echo #
--echo # Test alter sequence
--echo #

CREATE SEQUENCE t1 nocache engine=myisam;
select * from t1;
select next value for t1;
alter sequence t1 start=50;
show create sequence t1;
select * from t1;
select next value for t1;

alter sequence t1 minvalue=-100;
show create sequence t1;
select * from t1;
21
--error ER_SEQUENCE_INVALID_DATA
22
alter sequence t1 minvalue=100 start=100;
23
alter sequence t1 minvalue=100 start=100 restart=100;
24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45
show create sequence t1;
select * from t1;

alter sequence t1 maxvalue=500;
show create sequence t1;
select * from t1;
drop sequence t1;

CREATE SEQUENCE t1 engine=myisam;
alter sequence t1 nocache;
show create sequence t1;
alter sequence t1 cache=100;
flush tables;
show create sequence t1;
alter sequence t1 nocache;
show create sequence t1;
flush tables;
show create sequence t1;
select * from t1;
select next value for t1;
select next value for t1;
select next value for t1;
46
select next_not_cached_value, cycle_count from t1;
47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141
drop sequence t1;

CREATE SEQUENCE t1 maxvalue=100 engine=myisam;
alter sequence t1 no maxvalue;
show create sequence t1;
select * from t1;
alter sequence t1 cycle;
show create sequence t1;
alter sequence t1 nocycle;
alter sequence t1 start=15 restart minvalue=10 maxvalue=20 cycle;
show create sequence t1;
select * from t1;
select NEXT VALUE for t1 from seq_1_to_10;
alter sequence t1 restart with 17 minvalue=10 maxvalue=20 cycle;
select NEXT VALUE for t1 from seq_1_to_10;
drop sequence t1;

CREATE SEQUENCE t1 maxvalue=100;
alter sequence t1 increment=-2 start with 50 minvalue=-100;
show create sequence t1;
select * from t1;
select NEXT VALUE for t1 from seq_1_to_10;
drop sequence t1;

--echo #
--echo # InnoDB (some things work different with InnoDB)
--echo

CREATE SEQUENCE t1 cache 10 engine=innodb;
select * from t1;
select next value for t1;
alter sequence t1 start=100;
show create sequence t1;
select * from t1;
select next value for t1;
drop sequence t1;

--echo #
--echo # ALTER TABLE
--echo #

CREATE SEQUENCE t1 engine=innodb;
select next value for t1;
alter table t1 rename t2;
select next value for t2;
rename table t2 to t1;
select next value for t1;
alter table t1 comment="foo";
show create sequence t1;
alter table t1 engine=myisam;
show create sequence t1;
alter table t1 engine=innodb;
show create sequence t1;
select * from t1;
drop sequence t1;

#
# Some error testing
#

CREATE SEQUENCE t1 engine=myisam;
--error ER_SEQUENCE_INVALID_DATA
alter sequence t1 minvalue=100;
drop sequence t1;

CREATE SEQUENCE t1 engine=myisam;
--error ER_SEQUENCE_INVALID_DATA
alter sequence t1 minvalue=25 maxvalue=20;
drop sequence t1;

create table t1 (a int);
--error ER_NOT_SEQUENCE
alter sequence t1 minvalue=100;
drop table t1;

alter sequence if exists t1 minvalue=100;
--error ER_NO_SUCH_TABLE
alter sequence t1 minvalue=100;

create sequence t1;
--error ER_PARSE_ERROR
alter sequence t1;
drop sequence t1;

CREATE SEQUENCE t1 maxvalue=100;
alter sequence t1 increment=-2 start with 50;
select next value for t1;
--error ER_SEQUENCE_RUN_OUT
select next value for t1;
select * from t1;
alter sequence t1 restart;
select next value for t1;
alter sequence t1 restart with 90;
select next value for t1;
drop sequence t1;
142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175

--echo
--echo GRANT
--echo

create database s_db;
create sequence s_db.s1;
grant select on s_db.s1 to normal_1@'%' identified by 'pass';

connect(m_normal_1, localhost, normal_1, pass, s_db);
select * from s1;
--error ER_TABLEACCESS_DENIED_ERROR
select nextval(s1);
show create sequence s1;
--error ER_TABLEACCESS_DENIED_ERROR
alter sequence s1 restart 50;
connection default;
grant insert on s_db.s1 to normal_1@'%';
connection m_normal_1;
select nextval(s1);
--error ER_TABLEACCESS_DENIED_ERROR
alter sequence s1 restart 50;
connection default;
grant alter on s_db.s1 to normal_1@'%';
connection m_normal_1;
alter sequence s1 restart 50;
select nextval(s1);
--error ER_TABLEACCESS_DENIED_ERROR
drop sequence s1;

connection default;
disconnect m_normal_1;
drop database s_db;
drop user normal_1@'%';