ndb_alter_table.test 4.25 KB
Newer Older
1
-- source include/have_ndb.inc
2
-- source include/have_multi_ndb.inc
unknown's avatar
unknown committed
3
-- source include/not_embedded.inc
4 5 6

--disable_warnings
DROP TABLE IF EXISTS t1;
7
drop database if exists mysqltest;
8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25
--enable_warnings

#
# Basic test to show that the ALTER TABLE
#  is working
#
CREATE TABLE t1 (
  a INT NOT NULL,
  b INT NOT NULL
) ENGINE=ndbcluster;

INSERT INTO t1 VALUES (9410,9412);
  
ALTER TABLE t1 ADD COLUMN c int not null;
SELECT * FROM t1;

DROP TABLE t1;

26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41
#
# Verfify changing table names between databases
#
CREATE DATABASE mysqltest;
USE mysqltest;
CREATE TABLE t1 (
  a INT NOT NULL,
  b INT NOT NULL
) ENGINE=ndbcluster;
RENAME TABLE t1 TO test.t1;
SHOW TABLES;
DROP DATABASE mysqltest;
USE test;
SHOW TABLES;
DROP TABLE t1;

42 43 44 45 46 47 48 49 50
#
# More advanced test
#
create table t1 (
col1 int not null auto_increment primary key,
col2 varchar(30) not null,
col3 varchar (20) not null,
col4 varchar(4) not null,
col5 enum('PENDING', 'ACTIVE', 'DISABLED') not null,
unknown's avatar
unknown committed
51
col6 int not null, to_be_deleted int)  ENGINE=ndbcluster;
unknown's avatar
unknown committed
52
show table status;
53
SET SQL_MODE=NO_AUTO_VALUE_ON_ZERO;
unknown's avatar
unknown committed
54 55
insert into t1 values
(0,4,3,5,"PENDING",1,7),(NULL,4,3,5,"PENDING",1,7),(31,4,3,5,"PENDING",1,7), (7,4,3,5,"PENDING",1,7), (NULL,4,3,5,"PENDING",1,7), (100,4,3,5,"PENDING",1,7), (99,4,3,5,"PENDING",1,7), (8,4,3,5,"PENDING",1,7), (NULL,4,3,5,"PENDING",1,7);
unknown's avatar
unknown committed
56
show table status;
57
select * from t1 order by col1;
58 59 60 61 62 63
alter table t1
add column col4_5 varchar(20) not null after col4,
add column col7 varchar(30) not null after col5,
add column col8 datetime not null, drop column to_be_deleted,
change column col2 fourth varchar(30) not null after col3,
modify column col6 int not null first;
unknown's avatar
unknown committed
64
show table status;
65 66
select * from t1 order by col1;
insert into t1 values (2, NULL,4,3,5,99,"PENDING","EXTRA",'2004-01-01 00:00:00');
unknown's avatar
unknown committed
67
show table status;
68
select * from t1 order by col1;
69 70 71 72 73 74 75 76
delete from t1;
insert into t1 values (0,0,4,3,5,99,"PENDING","EXTRA",'2004-01-01 00:00:00');
SET SQL_MODE='';
insert into t1 values (1,0,4,3,5,99,"PENDING","EXTRA",'2004-01-01 00:00:00');
select * from t1 order by col1;
alter table t1 drop column col4_5;
insert into t1 values (2,0,4,3,5,"PENDING","EXTRA",'2004-01-01 00:00:00');
select * from t1 order by col1;
77
drop table t1;
78

79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95

#
# Check that invalidating dictionary cache works
#

CREATE TABLE t1 (
  a INT NOT NULL,
  b INT NOT NULL
) ENGINE=ndbcluster;

INSERT INTO t1 VALUES (9410,9412);

connect (con1,localhost,,,test);
connect (con2,localhost,,,test);

connection con1;
ALTER TABLE t1 ADD COLUMN c int not null;
96
select * from t1 order by a;
97 98

connection con2;
99
select * from t1 order by a;
100 101 102
alter table t1 drop c;

connection con1;
103
select * from t1 order by a;
104 105 106 107
drop table t1;

connection con2;
--error 1146
108 109 110 111 112 113 114 115 116
select * from t1 order by a;

CREATE TABLE t1 (
  a INT NOT NULL PRIMARY KEY,
  b INT NOT NULL
) ENGINE=ndbcluster;

INSERT INTO t1 VALUES (0,1),(17,18);
select * from t1 order by a;
117
SET SQL_MODE=NO_AUTO_VALUE_ON_ZERO;
118
alter table  t1 modify column a int not null auto_increment;
119
SET SQL_MODE='';
120 121 122 123 124 125 126 127 128 129 130 131
select * from t1 order by a;
INSERT INTO t1 VALUES (0,19),(20,21);
select * from t1 order by a;
drop table t1;

CREATE TABLE t1 (
  a INT NOT NULL PRIMARY KEY,
  b INT NOT NULL
) ENGINE=ndbcluster;

INSERT INTO t1 VALUES (0,1),(17,18);
select * from t1 order by a;
132 133 134 135
alter table  t1 add c int not null unique auto_increment;
select c from t1 order by c;
INSERT INTO t1 VALUES (1,2,0),(18,19,4),(20,21,0);
select c from t1 order by c;
136
drop table t1;
137

138 139 140 141 142 143 144 145 146
create table t1 ( a int primary key, b varchar(10), c varchar(10), index (b) )
engine=ndb;
insert into t1 values (1,'one','one'), (2,'two','two'), (3,'three','three');
create index c on t1(c); 
connection server2;
select * from t1 where b = 'two';
connection server1;
alter table t1 drop index c;
connection server2;
147
--error 1105
148 149 150 151 152
select * from t1 where b = 'two';
select * from t1 where b = 'two';
connection server1;
drop table t1;

unknown's avatar
unknown committed
153 154 155
#--disable_warnings
#DROP TABLE IF EXISTS t2;
#--enable_warnings
156

unknown's avatar
unknown committed
157 158 159 160 161 162 163 164 165 166 167 168 169
#create table t2 (a int NOT NULL PRIMARY KEY) engine=myisam;
#let $1=12001;
#disable_query_log;
#while ($1)
#{
# eval insert into t2 values($1);
# dec $1;
#}
#enable_query_log;
#alter table t2 engine=ndbcluster;
#alter table t2 add c int;
#--error 1297
#delete from t2;
170
#to make sure we do a full table scan
unknown's avatar
unknown committed
171 172 173 174
#select count(*) from t2 where a+0 > 0;
#truncate table t2;
#select count(*) from t2;
#drop table t2;