1# The include statement below is a temp one for tests that are yet to 2#be ported to run with InnoDB, 3#but needs to be kept for tests that would need MyISAM in future. 4--source include/force_myisam_default.inc 5 6--source include/have_ndb.inc 7 8--disable_warnings 9DROP TABLE IF EXISTS t1,t2; 10--enable_warnings 11 12CREATE LOGFILE GROUP lg1 13ADD UNDOFILE 'undofile.dat' 14INITIAL_SIZE 16M 15UNDO_BUFFER_SIZE = 1M ENGINE=NDB; 16 17CREATE TABLESPACE ts1 18ADD DATAFILE 'datafile.dat' 19USE LOGFILE GROUP lg1 20INITIAL_SIZE 64M 21ENGINE NDB; 22 23CREATE TABLE t1 (a int unsigned not null, 24 b int unsigned not null, 25 c int unsigned not null, 26 primary key(a,c), 27 unique (b)) 28ENGINE = NDB 29partition by key(a); 30 31CREATE TABLE t2 (a int unsigned not null, 32 b int unsigned not null, 33 c int unsigned not null, 34 primary key(a,b), 35 unique (b)) 36ENGINE = NDB 37STORAGE DISK 38TABLESPACE ts1 39partition by key(a); 40 41CREATE TABLE t3 (a int unsigned not null, 42 b int unsigned not null, 43 c int unsigned not null, 44 primary key(a,b), 45 unique (b)) 46MAX_ROWS=50000000 47ENGINE = NDB; 48 49CREATE TABLE t4 (a int unsigned not null, 50 b int unsigned not null, 51 c int unsigned not null, 52 primary key(a,b), 53 unique (b)) 54ENGINE = NDB; 55 56CREATE TABLE t5 (a int unsigned not null, 57 b int unsigned not null, 58 c int unsigned null, 59 primary key(a,b), 60 unique (b), 61 unique (c)) 62ENGINE = NDB 63partition by key(a) partitions 3; 64 65# Fragment counts for unique index must be equal to main table 66# if main table is hashmap partitioned 67 68let ndb_database=test; 69let ndb_table=t1; 70--source suite/ndb/include/ndb_check_unique_index.inc 71 72let ndb_database=test; 73let ndb_table=t2; 74--source suite/ndb/include/ndb_check_unique_index.inc 75 76let ndb_database=test; 77let ndb_table=t3; 78--source suite/ndb/include/ndb_check_unique_index.inc 79 80let ndb_database=test; 81let ndb_table=t4; 82--source suite/ndb/include/ndb_check_unique_index.inc 83 84let ndb_database=test; 85let ndb_table=t5; 86--source suite/ndb/include/ndb_check_unique_index.inc 87 88# 89 90let $t1_part_count_start = query_get_value(select count(*) as Value from information_schema.partitions where table_schema = 'test' and table_name = 't1', Value, 1); 91 92let $t2_part_count_start = query_get_value(select count(*) as Value from information_schema.partitions where table_schema = 'test' and table_name = 't2', Value, 1); 93 94let $t3_part_count_start = query_get_value(select count(*) as Value from information_schema.partitions where table_schema = 'test' and table_name = 't3', Value, 1); 95 96let $t4_part_count_start = query_get_value(select count(*) as Value from information_schema.partitions where table_schema = 'test' and table_name = 't4', Value, 1); 97 98INSERT INTO t1 VALUES 99(1,1,1),(2,2,2),(3,3,3),(4,4,4),(5,5,5), 100(6,6,6),(7,7,7),(8,8,8),(9,9,9),(10,10,10), 101(11,11,11),(12,12,12),(13,13,13),(14,14,14),(15,15,15), 102(16,16,16),(17,17,17),(18,18,18),(19,19,19),(20,20,20), 103(21,21,21),(22,22,22),(23,23,23),(24,24,24),(25,25,25), 104(26,26,26),(27,27,27),(28,28,28),(29,29,29),(30,30,30), 105(31,31,31),(32,32,32),(33,33,33),(34,34,34),(35,35,35), 106(36,36,36),(37,37,37),(38,38,38),(39,39,39),(40,40,40), 107(41,41,41),(42,42,42),(43,43,43),(44,44,44),(45,45,45), 108(46,46,46),(47,47,47),(48,48,48),(49,49,49),(50,50,50), 109(51,51,51),(52,52,52),(53,53,53),(54,54,54),(55,55,55), 110(56,56,56),(57,57,57),(58,58,58),(59,59,59),(60,60,60), 111(61,61,61),(62,62,62),(63,63,63),(64,64,64),(65,65,65), 112(66,66,66),(67,67,67),(68,68,68),(69,69,69),(70,70,70), 113(71,71,71),(72,72,72),(73,73,73),(74,74,74),(75,75,75), 114(76,76,76),(77,77,77),(78,78,78),(79,79,79),(80,80,80), 115(81,81,81),(82,82,82),(83,83,83),(84,84,84),(85,85,85), 116(86,86,86),(87,87,87),(88,88,88),(89,89,89),(90,90,90), 117(91,91,91),(92,92,92),(93,93,93),(94,94,94),(95,95,95), 118(96,96,96),(97,97,97),(98,98,98),(99,99,99),(100,100,100); 119 120insert into t2 select * from t1; 121insert into t3 select * from t1; 122insert into t4 select * from t1; 123insert into t5 (a,b,c) select a,b,if(a%2,b,NULL) as c from t1; 124 125select count(*) from t1; 126select count(*) from t2; 127select count(*) from t3; 128select count(*) from t4; 129select count(*) from t5; 130--sorted_result 131select * from t1 where a < 20; 132--sorted_result 133select * from t1 where a = 20; 134--sorted_result 135select * from t1 where a = 30; 136--sorted_result 137select * from t1 where a = 40; 138--sorted_result 139select * from t1 where a = 50; 140select * from t1 where b = 20; 141select * from t1 where b = 30; 142select * from t1 where b = 40; 143select * from t1 where b = 50; 144 145--sorted_result 146select * from t2 where a < 20; 147--sorted_result 148select * from t2 where a = 20; 149--sorted_result 150select * from t2 where a = 30; 151--sorted_result 152select * from t2 where a = 40; 153--sorted_result 154select * from t2 where a = 50; 155select * from t2 where b = 20; 156select * from t2 where b = 30; 157select * from t2 where b = 40; 158select * from t2 where b = 50; 159 160--sorted_result 161select * from t3 where a < 20; 162--sorted_result 163select * from t3 where a = 20; 164--sorted_result 165select * from t3 where a = 30; 166--sorted_result 167select * from t3 where a = 40; 168--sorted_result 169select * from t3 where a = 50; 170select * from t3 where b = 20; 171select * from t3 where b = 30; 172select * from t3 where b = 40; 173select * from t3 where b = 50; 174 175--sorted_result 176select * from t4 where a < 20; 177--sorted_result 178select * from t4 where a = 20; 179--sorted_result 180select * from t4 where a = 30; 181--sorted_result 182select * from t4 where a = 40; 183--sorted_result 184select * from t4 where a = 50; 185select * from t4 where b = 20; 186select * from t4 where b = 30; 187select * from t4 where b = 40; 188select * from t4 where b = 50; 189 190alter online table t1 reorganize partition; 191alter online table t2 reorganize partition; 192--echo Cannot use normal reorganize partition on t3 as it has explicit MAX_ROWS set 193--error ER_ALTER_OPERATION_NOT_SUPPORTED 194alter online table t3 reorganize partition; 195show warnings; 196 197alter online table t3 max_rows=50000000; 198alter online table t4 reorganize partition; 199--error ER_ALTER_OPERATION_NOT_SUPPORTED 200alter online table t5 partition by key() partitions 6; 201 202let $t1_part_count_now = query_get_value(select count(*) as Value from information_schema.partitions where table_schema = 'test' and table_name = 't1', Value, 1); 203 204let $t2_part_count_now = query_get_value(select count(*) as Value from information_schema.partitions where table_schema = 'test' and table_name = 't2', Value, 1); 205 206let $t3_part_count_now = query_get_value(select count(*) as Value from information_schema.partitions where table_schema = 'test' and table_name = 't3', Value, 1); 207 208let $t4_part_count_now = query_get_value(select count(*) as Value from information_schema.partitions where table_schema = 'test' and table_name = 't4', Value, 1); 209 210--disable_query_log 211--echo Check partitions added, expect 0 in all cases 212--echo partitions added to t1 213eval select $t1_part_count_now - $t1_part_count_start as t1_added; 214--echo partitions added to t2 215eval select $t2_part_count_now - $t2_part_count_start as t2_added; 216--echo partitions added to t3 217eval select $t3_part_count_now - $t3_part_count_start as t3_added; 218--echo partitions added to t4 219eval select $t4_part_count_now - $t4_part_count_start as t4_added; 220--enable_query_log 221 222alter online table t1 add partition partitions 1; 223alter online table t2 add partition partitions 4; 224alter online table t3 max_rows=100000000; # Expansion of max rows 225--error ER_ALTER_OPERATION_NOT_SUPPORTED 226alter online table t4 max_rows=100000000; # Attempted introduction of max rows - fails 227 228let $t1_part_count_now = query_get_value(select count(*) as Value from information_schema.partitions where table_schema = 'test' and table_name = 't1', Value, 1); 229 230let $t2_part_count_now = query_get_value(select count(*) as Value from information_schema.partitions where table_schema = 'test' and table_name = 't2', Value, 1); 231 232let $t3_part_count_now = query_get_value(select count(*) as Value from information_schema.partitions where table_schema = 'test' and table_name = 't3', Value, 1); 233 234let $t4_part_count_now = query_get_value(select count(*) as Value from information_schema.partitions where table_schema = 'test' and table_name = 't4', Value, 1); 235 236--disable_query_log 237--echo partitions added to t1 (expect 1) 238eval select $t1_part_count_now - $t1_part_count_start as t1_added; 239--echo partitions added to t2 (expect 4) 240eval select $t2_part_count_now - $t2_part_count_start as t2_added; 241--echo partitions added to t3 (expect 2) 242eval select $t3_part_count_now - $t3_part_count_start as t3_added; 243--echo partitions added to t4 (expect 0) 244eval select $t4_part_count_now - $t4_part_count_start as t4_added; 245--enable_query_log 246 247# reorganize partition not support if not default partitioning 248# and after a add partition it's no longer default 249--error ER_REORG_NO_PARAM_ERROR 250alter online table t1 reorganize partition; 251 252# Following will fail as t3 has an explicit MAX_ROWS set 253--error ER_ALTER_OPERATION_NOT_SUPPORTED 254alter online table t3 reorganize partition; 255show warnings; 256 257# t4 reorg will succeed as t4 has no explicit MAX_ROWS 258alter online table t4 reorganize partition; 259 260select count(*) from t1; 261select count(*) from t2; 262--sorted_result 263select * from t1 where a < 20; 264--sorted_result 265select * from t1 where a = 20; 266--sorted_result 267select * from t1 where a = 30; 268--sorted_result 269select * from t1 where a = 40; 270--sorted_result 271select * from t1 where a = 50; 272select * from t1 where b = 20; 273select * from t1 where b = 30; 274select * from t1 where b = 40; 275select * from t1 where b = 50; 276 277--sorted_result 278select * from t2 where a < 20; 279--sorted_result 280select * from t2 where a = 20; 281--sorted_result 282select * from t2 where a = 30; 283--sorted_result 284select * from t2 where a = 40; 285--sorted_result 286select * from t2 where a = 50; 287select * from t2 where b = 20; 288select * from t2 where b = 30; 289select * from t2 where b = 40; 290select * from t2 where b = 50; 291 292--sorted_result 293select * from t3 where a < 20; 294--sorted_result 295select * from t3 where a = 20; 296--sorted_result 297select * from t3 where a = 30; 298--sorted_result 299select * from t3 where a = 40; 300--sorted_result 301select * from t3 where a = 50; 302select * from t3 where b = 20; 303select * from t3 where b = 30; 304select * from t3 where b = 40; 305select * from t3 where b = 50; 306 307--sorted_result 308select * from t4 where a < 20; 309--sorted_result 310select * from t4 where a = 20; 311--sorted_result 312select * from t4 where a = 30; 313--sorted_result 314select * from t4 where a = 40; 315--sorted_result 316select * from t4 where a = 50; 317select * from t4 where b = 20; 318select * from t4 where b = 30; 319select * from t4 where b = 40; 320select * from t4 where b = 50; 321 322let ndb_database=test; 323let ndb_table=t4; 324--source suite/ndb/include/ndb_check_unique_index.inc 325 326drop table t4; 327 328alter online table t1 add partition partitions 2; 329alter online table t2 add partition partitions 1; 330alter online table t3 max_rows=150000000; 331 332let $t1_part_count_now = query_get_value(select count(*) as Value from information_schema.partitions where table_schema = 'test' and table_name = 't1', Value, 1); 333 334let $t2_part_count_now = query_get_value(select count(*) as Value from information_schema.partitions where table_schema = 'test' and table_name = 't2', Value, 1); 335 336let $t3_part_count_now = query_get_value(select count(*) as Value from information_schema.partitions where table_schema = 'test' and table_name = 't3', Value, 1); 337 338--disable_query_log 339--echo partitions added to t1 (expect 3) 340eval select $t1_part_count_now - $t1_part_count_start as t1_added; 341--echo partitions added to t2 (expect 5) 342eval select $t2_part_count_now - $t2_part_count_start as t2_added; 343--echo partitions added to t3 (expect 4) 344eval select $t3_part_count_now - $t3_part_count_start as t3_added; 345--enable_query_log 346 347select count(*) from t1; 348select count(*) from t2; 349--sorted_result 350select * from t1 where a < 20; 351--sorted_result 352select * from t1 where a = 20; 353--sorted_result 354select * from t1 where a = 30; 355--sorted_result 356select * from t1 where a = 40; 357--sorted_result 358select * from t1 where a = 50; 359select * from t1 where b = 20; 360select * from t1 where b = 30; 361select * from t1 where b = 40; 362select * from t1 where b = 50; 363 364--sorted_result 365select * from t2 where a < 20; 366--sorted_result 367select * from t2 where a = 20; 368--sorted_result 369select * from t2 where a = 30; 370--sorted_result 371select * from t2 where a = 40; 372--sorted_result 373select * from t2 where a = 50; 374select * from t2 where b = 20; 375select * from t2 where b = 30; 376select * from t2 where b = 40; 377select * from t2 where b = 50; 378 379--sorted_result 380select * from t3 where a < 20; 381--sorted_result 382select * from t3 where a = 20; 383--sorted_result 384select * from t3 where a = 30; 385--sorted_result 386select * from t3 where a = 40; 387--sorted_result 388select * from t3 where a = 50; 389select * from t3 where b = 20; 390select * from t3 where b = 30; 391select * from t3 where b = 40; 392select * from t3 where b = 50; 393 394# Fragment counts for unique index may now differ from main table 395# since unique index do not reorg 396 397let ndb_database=test; 398let ndb_table=t1; 399--source suite/ndb/include/ndb_check_unique_index.inc 400 401let ndb_database=test; 402let ndb_table=t2; 403--source suite/ndb/include/ndb_check_unique_index.inc 404 405let ndb_database=test; 406let ndb_table=t3; 407--source suite/ndb/include/ndb_check_unique_index.inc 408 409let ndb_database=test; 410let ndb_table=t5; 411--source suite/ndb/include/ndb_check_unique_index.inc 412 413drop table t1,t2,t3,t5; 414alter tablespace ts1 drop datafile 'datafile.dat' engine = ndb; 415drop tablespace ts1 engine = ndb; 416drop logfile group lg1 engine = ndb; 417