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

--disable_warnings
5
DROP TABLE IF EXISTS t1, t2;
6
drop database if exists mysqltest;
7 8
--enable_warnings

9 10 11 12 13 14 15
connect (con1,localhost,root,,test);
connect (con2,localhost,root,,test);

connection con2;
-- sleep 2
connection con1;

16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31
#
# 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;

32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47
#
# 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;

48 49 50 51 52 53 54 55 56
#
# 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
57
col6 int not null, to_be_deleted int)  ENGINE=ndbcluster;
unknown's avatar
unknown committed
58
--replace_column 6 # 7 # 8 # 10 # 12 # 13 # 14 # 18 #
unknown's avatar
unknown committed
59
show table status;
60
SET SQL_MODE=NO_AUTO_VALUE_ON_ZERO;
unknown's avatar
unknown committed
61 62
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
63
--replace_column 6 # 7 # 8 # 10 # 12 # 13 # 14 # 18 #
unknown's avatar
unknown committed
64
show table status;
65
select * from t1 order by col1;
66 67 68 69 70 71
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
72
--replace_column 6 # 7 # 8 # 10 # 12 # 13 # 14 # 18 #
unknown's avatar
unknown committed
73
show table status;
74 75
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
76
--replace_column 6 # 7 # 8 # 10 # 12 # 13 # 14 # 18 #
unknown's avatar
unknown committed
77
show table status;
78
select * from t1 order by col1;
79 80 81 82 83 84 85 86
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;
87
drop table t1;
88

89 90 91 92 93 94 95 96 97 98 99 100 101

#
# 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);

ALTER TABLE t1 ADD COLUMN c int not null;
102
select * from t1 order by a;
103 104

connection con2;
105
select * from t1 order by a;
106 107 108
alter table t1 drop c;

connection con1;
109
select * from t1 order by a;
110 111 112 113
drop table t1;

connection con2;
--error 1146
114 115 116 117 118 119 120 121 122
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;
123
SET SQL_MODE=NO_AUTO_VALUE_ON_ZERO;
124
alter table  t1 modify column a int not null auto_increment;
125
SET SQL_MODE='';
126 127 128 129 130 131 132 133 134 135 136 137
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;
138 139 140 141
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;
142
drop table t1;
143

unknown's avatar
unknown committed
144 145 146 147 148 149 150 151 152 153 154 155 156
## Test moved to ndb_alter_table_row|stmt respectively as behaviour differs
#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 c = 'two';
#connection server1;
#alter table t1 drop index c;
#connection server2;
#select * from t1 where c = 'two';
#connection server1;
#drop table t1;
157

unknown's avatar
unknown committed
158 159 160
#--disable_warnings
#DROP TABLE IF EXISTS t2;
#--enable_warnings
161

unknown's avatar
unknown committed
162 163 164 165 166 167 168 169 170 171 172 173 174
#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;
175
#to make sure we do a full table scan
unknown's avatar
unknown committed
176 177 178 179
#select count(*) from t2 where a+0 > 0;
#truncate table t2;
#select count(*) from t2;
#drop table t2;
180

unknown's avatar
unknown committed
181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206
## Test moved to ndb_alter_table_row|stmt respectively as behaviour differs
#connection server1;
#create table t3 (a int primary key) engine=ndbcluster;

#connection server2;
#begin;
#insert into t3 values (1);

#connection server1;
#alter table t3 rename t4;

#connection server2;
## with rbr the below will not work as the "alter" event
## explicitly invalidates the dictionary cache.
### This should work as transaction is ongoing...
##delete from t3;
##insert into t3 values (1);
#commit; 

## This should fail as its a new transaction
#--error 1146
#select * from t3;
#select * from t4;
#drop table t4;
#show tables;
#connection server1;
207

unknown's avatar
unknown committed
208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 283 284 285 286 287 288 289 290 291 292 293 294 295 296 297 298 299 300 301 302 303 304 305 306 307 308 309 310 311 312 313 314 315 316 317 318 319 320 321 322 323 324 325 326
create table t1 (
ai bigint auto_increment,
c001 int(11) not null,
c002 int(11) not null,
c003 int(11) not null,
c004 int(11) not null,
c005 int(11) not null,
c006 int(11) not null,
c007 int(11) not null,
c008 int(11) not null,
c009 int(11) not null,
c010 int(11) not null,
c011 int(11) not null,
c012 int(11) not null,
c013 int(11) not null,
c014 int(11) not null,
c015 int(11) not null,
c016 int(11) not null,
c017 int(11) not null,
c018 int(11) not null,
c019 int(11) not null,
c020 int(11) not null,
c021 int(11) not null,
c022 int(11) not null,
c023 int(11) not null,
c024 int(11) not null,
c025 int(11) not null,
c026 int(11) not null,
c027 int(11) not null,
c028 int(11) not null,
c029 int(11) not null,
c030 int(11) not null,
c031 int(11) not null,
c032 int(11) not null,
c033 int(11) not null,
c034 int(11) not null,
c035 int(11) not null,
c036 int(11) not null,
c037 int(11) not null,
c038 int(11) not null,
c039 int(11) not null,
c040 int(11) not null,
c041 int(11) not null,
c042 int(11) not null,
c043 int(11) not null,
c044 int(11) not null,
c045 int(11) not null,
c046 int(11) not null,
c047 int(11) not null,
c048 int(11) not null,
c049 int(11) not null,
c050 int(11) not null,
c051 int(11) not null,
c052 int(11) not null,
c053 int(11) not null,
c054 int(11) not null,
c055 int(11) not null,
c056 int(11) not null,
c057 int(11) not null,
c058 int(11) not null,
c059 int(11) not null,
c060 int(11) not null,
c061 int(11) not null,
c062 int(11) not null,
c063 int(11) not null,
c064 int(11) not null,
c065 int(11) not null,
c066 int(11) not null,
c067 int(11) not null,
c068 int(11) not null,
c069 int(11) not null,
c070 int(11) not null,
c071 int(11) not null,
c072 int(11) not null,
c073 int(11) not null,
c074 int(11) not null,
c075 int(11) not null,
c076 int(11) not null,
c077 int(11) not null,
c078 int(11) not null,
c079 int(11) not null,
c080 int(11) not null,
c081 int(11) not null,
c082 int(11) not null,
c083 int(11) not null,
c084 int(11) not null,
c085 int(11) not null,
c086 int(11) not null,
c087 int(11) not null,
c088 int(11) not null,
c089 int(11) not null,
c090 int(11) not null,
c091 int(11) not null,
c092 int(11) not null,
c093 int(11) not null,
c094 int(11) not null,
c095 int(11) not null,
c096 int(11) not null,
c097 int(11) not null,
c098 int(11) not null,
c099 int(11) not null,
c100 int(11) not null,
c101 int(11) not null,
c102 int(11) not null,
c103 int(11) not null,
c104 int(11) not null,
c105 int(11) not null,
c106 int(11) not null,
c107 int(11) not null,
c108 int(11) not null,
c109 int(11) not null,
primary key (ai),
unique key tx1 (c002, c003, c004, c005)) engine=ndb;

create index tx2 
on t1 (c010, c011, c012, c013);

drop table t1;

327
# End of 4.1 tests
328

329 330 331 332 333 334 335 336 337 338 339 340 341 342 343 344 345 346 347 348 349 350 351 352 353 354 355 356 357 358 359 360 361 362 363 364
# On-line alter table


CREATE TABLE t1 (
  auto int(5) unsigned NOT NULL auto_increment,
  string char(10),
  vstring varchar(10),
  bin binary(2),
  vbin varbinary(7),
  tiny tinyint(4) DEFAULT '0' NOT NULL ,
  short smallint(6) DEFAULT '1' NOT NULL ,
  medium mediumint(8) DEFAULT '0' NOT NULL,
  long_int int(11) DEFAULT '0' NOT NULL,
  longlong bigint(13) DEFAULT '0' NOT NULL,
  real_float float(13,1) DEFAULT 0.0 NOT NULL,
  real_double double(16,4),
  real_decimal decimal(16,4),
  utiny tinyint(3) unsigned DEFAULT '0' NOT NULL,
  ushort smallint(5) unsigned zerofill DEFAULT '00000' NOT NULL,
  umedium mediumint(8) unsigned DEFAULT '0' NOT NULL,
  ulong int(11) unsigned DEFAULT '0' NOT NULL,
  ulonglong bigint(13) unsigned DEFAULT '0' NOT NULL,
  bits bit(3),
  options enum('zero','one','two','three','four') not null,
  flags set('zero','one','two','three','four') not null,
  date_field date,
  year_field year,
  time_field time,
  date_time datetime,
  time_stamp timestamp,
  PRIMARY KEY (auto)
) engine=ndb;
                                                                                                   
CREATE TEMPORARY TABLE ndb_show_tables (id INT, type VARCHAR(20), state VARCHAR(20), logging VARCHAR(20), _database VARCHAR(255), _schema VARCHAR(20), name VARCHAR(255));

--disable_warnings
unknown's avatar
unknown committed
365 366
let $MYSQLD_DATADIR= `select @@datadir`;
--exec $NDB_TOOLS_DIR/ndb_show_tables --p > $MYSQLD_DATADIR/test/tmp.dat
367 368 369
LOAD DATA INFILE 'tmp.dat' INTO TABLE ndb_show_tables;
--enable_warnings

370
# Ndb doesn't support renaming attributes on-line
371 372 373 374
set @t1_id = (select id from ndb_show_tables where name like '%t1%');
truncate ndb_show_tables;

alter table t1 change tiny new_tiny tinyint(4) DEFAULT '0' NOT NULL;
375
--disable_warnings
unknown's avatar
unknown committed
376
--exec $NDB_TOOLS_DIR/ndb_show_tables --p > $MYSQLD_DATADIR/test/tmp.dat
377 378 379 380 381 382 383 384
LOAD DATA INFILE 'tmp.dat' INTO TABLE ndb_show_tables;
--enable_warnings

select 'no_copy' from ndb_show_tables where id = @t1_id and name like '%t1%';

set @t1_id = (select id from ndb_show_tables where name like '%t1%');
truncate ndb_show_tables;

385
create index i1 on t1(medium);
386
alter table t1 add index i2(new_tiny);
387 388 389
drop index i1 on t1;

--disable_warnings
unknown's avatar
unknown committed
390
--exec $NDB_TOOLS_DIR/ndb_show_tables --p > $MYSQLD_DATADIR/test/tmp.dat
391
LOAD DATA INFILE 'tmp.dat' INTO TABLE ndb_show_tables;
unknown's avatar
unknown committed
392
--exec rm $MYSQLD_DATADIR/test/tmp.dat || true
393 394 395 396 397
--enable_warnings

select 'no_copy' from ndb_show_tables where id = @t1_id and name like '%t1%';

DROP TABLE t1, ndb_show_tables;
398 399 400 401 402 403 404 405 406 407 408 409 410 411 412

# simple test that auto incr is not lost at rename or alter
create table t1 (a int primary key auto_increment, b int) engine=ndb;
insert into t1 (b) values (101),(102),(103);
select * from t1 where a = 3;
alter table t1 rename t2;
insert into t2 (b) values (201),(202),(203);
select * from t2 where a = 6;
alter table t2 add c int;
insert into t2 (b) values (301),(302),(303);
select * from t2 where a = 9;
alter table t2 rename t1;
insert into t1 (b) values (401),(402),(403);
select * from t1 where a = 12;
drop table t1;
unknown's avatar
unknown committed
413 414

# some other ALTER combinations
415 416 417 418
# Check add/drop primary key (not supported on-line)
create table t1(a int not null) engine=ndb;
--exec $NDB_TOOLS_DIR/ndb_desc --no-defaults -d test t1 | grep PRIMARY
insert into t1 values (1),(2),(3);
unknown's avatar
unknown committed
419
alter table t1 add primary key (a);
420 421 422
--exec $NDB_TOOLS_DIR/ndb_desc --no-defaults -d test t1 | grep PRIMARY
update t1 set a = 17 where a = 1;
select * from t1 order by a;
unknown's avatar
unknown committed
423
alter table t1 drop primary key;
424 425 426 427 428 429 430 431 432 433 434 435 436 437 438 439 440
--exec $NDB_TOOLS_DIR/ndb_desc --no-defaults -d test t1 | grep PRIMARY
update t1 set a = 1 where a = 17;
select * from t1 order by a;
drop table t1;

# bug#31233 mysql_alter_table() fails to drop UNIQUE KEY
create table t1(a int not null) engine=ndb;
--exec $NDB_TOOLS_DIR/ndb_desc --no-defaults -d test t1 | grep PRIMARY
insert into t1 values (1),(2),(3);
create unique index pk on t1(a);
--exec $NDB_TOOLS_DIR/ndb_desc --no-defaults -d test t1 | grep PRIMARY
update t1 set a = 17 where a = 1;
select * from t1 order by a;
alter table t1 drop index pk;
--exec $NDB_TOOLS_DIR/ndb_desc --no-defaults -d test t1 | grep PRIMARY
update t1 set a = 1 where a = 17;
select * from t1 order by a;
unknown's avatar
unknown committed
441 442 443 444 445 446 447 448 449 450 451 452
drop table t1;

# alter .. alter
create table t1 (a int not null primary key, b int not null default 0, c varchar(254)) engine=ndb;
show create table t1;
alter table t1 alter b set default 1;
show create table t1;
drop table t1;

# alter .. order by
create table t1 (a int not null, b int not null) engine=ndb;
insert into t1 values (1, 300), (2, 200), (3, 100);
unknown's avatar
unknown committed
453
select * from t1 order by a;
unknown's avatar
unknown committed
454
alter table t1 order by b;
unknown's avatar
unknown committed
455
select * from t1 order by b;
unknown's avatar
unknown committed
456 457 458
drop table t1;

--echo End of 5.1 tests