partition_sessions.test 11.1 KB
Newer Older
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 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

#--------------------------------------------------
# Initialize system_3 test variables 
#--------------------------------------------------

--source suite/system_3/include/system_3_init.inc

let $NUM_VAL=`SELECT @NUM_VAL`;
let $LOAD_LINES=`SELECT @LOAD_LINES`;
let $LOG_UPPER=`SELECT @LOG_UPPER`;
let $LOG_LOWER=`SELECT @LOG_LOWER`;
#let $ENG1=`SELECT @ENG1`;
let $ENG2=`SELECT @ENG2`;
let $ENG_LOG=`SELECT @ENG_LOG`;
let $CLIENT_HOST=`SELECT @CLIENT_HOST`;
let $ENG=innodb;
let $ENG1=innodb;
#---------------------------------------------------------
# Column list with definition for all tables to be checked
#---------------------------------------------------------

let $column_list= f1 int,
f2 char (15),
f3 decimal (5,3),
f4 datetime;

let $col_access_list = f1,f2,f3,f4 ;
let $col_new_list    = new.f1,new.f2,new.f3 new.f4 ;

#---------------------------------------------------
# Setting the parameters to use during testing
#---------------------------------------------------
# Set number of variations of the f1 variable (used to segment the rows
# being updated/deleted by a user at a time. The higher the number, the
# more smaller segments used with each query.
--replace_result $NUM_VAL NUM_VAL
eval set @f1_nums=$NUM_VAL;

# The following sets the number controls the size of the log table.
# Once a size of '@threshold' is reached, the first rows are removed
# sunch that the table is down to '@shrink_to' lines
--replace_result $LOG_LOWER LOG_LOWER
eval set @shrink_to=$LOG_LOWER;
--replace_result $LOG_UPPER LOG_UPPER
eval set @threshold=$LOG_UPPER;

#---------------------------------------------------
# Creating the database tables and loading the data
#---------------------------------------------------

--disable_warnings
drop database if exists systest1;
--enable_warnings

create database systest1;

--disable_abort_on_error
--replace_result $CLIENT_HOST CLIENT_HOST
eval create user systuser@'$CLIENT_HOST';
--enable_abort_on_error
--replace_result $CLIENT_HOST CLIENT_HOST
eval set password for systuser@'$CLIENT_HOST' = password('systpass');
--replace_result $CLIENT_HOST CLIENT_HOST
eval grant ALL on systest1.* to systuser@'$CLIENT_HOST';
use systest1;
--replace_result $MASTER_MYPORT MASTER_MYPORT $MASTER_MYSOCK MASTER_MYSOCK
connect (systuser,localhost,systuser,systpass,systest1,$MASTER_MYPORT,$MASTER_MYSOCK);

create table tb1_master (
   f1 int,
   f2 char(15),
   f3 decimal (5,3),
   f4 datetime
);

#--replace_result $ENG_LOG ENG_LOG
eval create table tb1_logs (
   i1 int NOT NULL auto_increment, primary key (i1),
   dt1 datetime NOT NULL,
   entry_dsc char(100),
   f4 int
) engine=$ENG_LOG
;
#PARTITION BY HASH (i1) PARTITIONS 8;

if ($debug)
{
SHOW CREATE TABLE tb1_logs;
}

#--replace_result $ENG_LOG ENG_LOG
eval create table ddl_logs (
   i1 int NOT NULL auto_increment, primary key (i1),
   dt1 datetime NOT NULL,
   entry_dsc char(100),
   errno int
) engine=$ENG_LOG;
#PARTITION BY HASH (i1) PARTITIONS 8;

if ($debug)
{
SHOW CREATE TABLE tb1_logs;
}
create table test_stat (
   dt1 datetime,
   table_name char(20),
   row_count int,
   start_row int,
   end_row int
);

#----------------------------------------------------------------------
# tb3_eng1: key partitioning
#----------------------------------------------------------------------

116
#--replace_result $ENG1 ENG1
117 118 119 120 121
eval create table tb3_eng1 (
   i1 int NOT NULL auto_increment, primary key (i1),
   $column_list
) engine=$ENG1
PARTITION BY KEY (i1) PARTITIONS 4
122 123 124 125
(PARTITION part1,
PARTITION part2,
PARTITION part3,
PARTITION part4);
126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 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 176 177 178 179 180 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 207 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 327 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 365 366 367 368 369 370 371 372 373 374 375 376 377 378 379 380 381 382 383 384 385 386 387 388 389 390 391

#--replace_result $MYSQL_TEST_DIR MYSQL_TEST_DIR
eval load data local infile '$MYSQL_TEST_DIR/suite/system_3/data/tb1.txt'
     into table tb3_eng1 ($col_access_list);

if ($WITH_TRIGGERS)
{
delimiter //;

Create trigger tb3_eng1_ins after insert on tb3_eng1 for each row
BEGIN
   insert into tb1_logs (dt1, entry_dsc, f4)
   values (now(), concat('Insert row ', new.f1,' ',
           new.f2, ' ', new.f3, ' (tb3_eng1)'), new.f1);
END//

Create trigger tb3_eng1_upd after update on tb3_eng1 for each row
BEGIN
   insert into tb1_logs (dt1, entry_dsc, f4)
   values (now(), concat('Update row ', old.f1,' ', old.f2, '->',
           new.f2, ' ', old.f3, '->', new.f3, ' (tb3_eng1)'), new.f1);
END//

Create trigger tb3_eng1_del after delete on tb3_eng1 for each row
BEGIN
   insert into tb1_logs (dt1, entry_dsc, f4)
   values (now(), concat('Delete row ', old.f1,' ', old.f2, ' ',
           old.f3, ' (tb3_eng1)'), old.f1);
END//

delimiter ;//
}
delimiter //;

# This functions returns a random integer number 
# between zero and 'num'
#-----------------------------------------------
create function int_rand(num int) returns int
BEGIN
   return round(num*rand()+0.5); 
END//

# This function returns a string in the length 'len' of 
# random letters (ascii range of 65-122)
#------------------------------------------------------
create function str_rand (len int) returns char(12)
BEGIN
  declare tmp_letter char(1);
  declare tmp_word char(12);
  declare word_str char(12) default '';
  wl_loop: WHILE len DO
     set tmp_letter=char(round(57*rand()+65)); 
     set tmp_word=concat(word_str,tmp_letter);
     set word_str=tmp_word;
     set len=len-1;
  END WHILE wl_loop;
  return word_str;
END//


# This procedure scans 'tb1_master' table for rows where f1='num_pr'
# and for each row inserts a row in 'tb3_eng1'
#------------------------------------------------------------------
eval create procedure ins_tb3_eng1 (num_pr int, str_pr char(15))
BEGIN
  declare done int default 0;
  declare v3 decimal(5,3);
  declare cur1 cursor for 
     select f3 from tb1_master where f1=num_pr;
  declare continue handler for sqlstate '01000' set done = 1;
  declare continue handler for sqlstate '02000' set done = 1;
  open cur1;
  fetch cur1 into v3;
  wl_loop: WHILE NOT done DO
     insert into tb3_eng1 ($col_access_list) values
        (int_rand(@f1_nums), concat('I:',str_pr,'-',num_pr), v3, now());
     fetch cur1 into v3;
  END WHILE wl_loop;
  close cur1;
END//


# This procedure does selects from the 'tb1_logs' and inserts the 
# count into the table
#------------------------------------------------------------------
create procedure slct_tb1_logs ()
BEGIN
  declare done int default 0;
  declare v4 int;
  declare v_count int default 0;
  declare str_val char(15) default ELT(int_rand(3), 
     'Insert', 'Update', 'Delete');
  declare cur1 cursor for 
     select f4 from tb1_logs where entry_dsc like concat('%',str_val,'%'); 
  declare continue handler for sqlstate '01000' set done = 1;
  declare continue handler for sqlstate '02000' set done = 1;
  open cur1;
  fetch cur1 into v4;
  wl_loop: WHILE NOT done DO
     set v_count=v_count+1;
     fetch cur1 into v4;
  END WHILE wl_loop;
  close cur1;
  insert into tb1_logs (dt1, entry_dsc, f4)
     values (now(), concat('Number of \'', str_val, '\' rows is: ', 
             v_count, ' (tb1_log)'),0);
END//

delimiter ;//

--disable_abort_on_error
insert into systest1.tb3_eng1 values (NULL,50,'init_val',12.345,'2005-01-01 00:00:00');
insert into systest1.tb3_eng1 values (NULL,70,'init_val',12.345,'2005-01-01 00:00:00');
--enable_abort_on_error

connection default;0.
--disable_abort_on_error
--replace_result $CLIENT_HOST CLIENT_HOST
eval create user syst1user@'$CLIENT_HOST';
--enable_abort_on_error
--replace_result $CLIENT_HOST CLIENT_HOST
eval set password for syst1user@'$CLIENT_HOST' = password('systpass');
--replace_result $CLIENT_HOST CLIENT_HOST
eval grant ALL on systest1.* to syst1user@'$CLIENT_HOST';
use systest1;
--replace_result $MASTER_MYPORT MASTER_MYPORT $MASTER_MYSOCK MASTER_MYSOCK
connect (syst1user,localhost,syst1user,systpass,systest1,$MASTER_MYPORT,$MASTER_MYSOCK);

--source suite/system_3/include/system_3_init.inc
use systest1;
let $NUM_VAL=`SELECT @NUM_VAL`;
eval SET @f1_nums=$NUM_VAL;
SET @tmp_num=int_rand(@f1_nums);
SET @tmp_word=str_rand(4);

# DEBUG select @tmp_num, @tmp_word;

# Insert rows replacing the deleted rows using a strored procedure
# that reads the rows from a master table
CALL ins_tb3_eng1 (@tmp_num, @tmp_word);

connection syst1user;
--source suite/system_3/include/system_3_init.inc
use systest1;
let $NUM_VAL=`SELECT @NUM_VAL`;
eval SET @f1_nums=$NUM_VAL;
SET @tmp_num=int_rand(@f1_nums);
SET @tmp_word=str_rand(4);

# DEBUG select @tmp_num, @tmp_word;

# Insert rows replacing the deleted rows using a strored procedure
# that reads the rows from a master table
CALL ins_tb3_eng1 (@tmp_num, @tmp_word);

connection systuser;
--source suite/system_3/include/system_3_init.inc
use systest1;
call slct_tb1_logs();

connection syst1user;
--source suite/system_3/include/system_3_init.inc
use systest1;
let $NUM_VAL=`SELECT @NUM_VAL`;
eval set @f1_nums=$NUM_VAL;
set @tmp_num=int_rand(@f1_nums);
set @tmp_word=str_rand(4);

select @tmp_num, @tmp_word;

# Update all rows in the table where f1 is one less the random number
update tb3_eng1  
   set f2=concat('U:',@tmp_word,'-',@tmp_num), f3=f3+1 
   where f1=@tmp_num-1;

connection systuser;
--source suite/system_3/include/system_3_init.inc
use systest1;
let $NUM_VAL=`SELECT @NUM_VAL`;
eval set @f1_nums=$NUM_VAL;
set @tmp_num=int_rand(@f1_nums);
set @tmp_word=str_rand(4);

select @tmp_num, @tmp_word;

# Update all rows in the table where f1 is one less the random number
update tb3_eng1  
   set f2=concat('U:',@tmp_word,'-',@tmp_num), f3=f3+1 
   where f1=@tmp_num-1;

connection syst1user;
--source suite/system_3/include/system_3_init.inc
use systest1;
call slct_tb1_logs();

connection systuser;
--source suite/system_3/include/system_3_init.inc
use systest1;
let $NUM_VAL=`SELECT @NUM_VAL`;
eval set @f1_nums=$NUM_VAL;
set @tmp_num=int_rand(@f1_nums);
set @tmp_word=str_rand(4);

select @tmp_num, @tmp_word;

# Update all rows in the table where f1 is one less the random number
update tb3_eng1  
   set f2=concat('U:',@tmp_word,'-',@tmp_num), f3=f3+1 
   where f1=@tmp_num-1;


connection syst1user;
--source suite/system_3/include/system_3_init.inc
use systest1;
#--replace_result $NUM_VAL <NUM_VAL>
let $NUM_VAL=`SELECT @NUM_VAL`;
eval set @f1_nums=$NUM_VAL;
set @tmp_num=int_rand(@f1_nums);
select @tmp_num;

# DEBUG select @tmp_num, @tmp_word;

# Delete all rows from the table where f1 is equal to the above number
delete from tb3_eng1 where f1=@tmp_num;

connection systuser;
--source suite/system_3/include/system_3_init.inc
use systest1;
select * from tb3_eng1 where f1>40;


connection syst1user;
--source suite/system_3/include/system_3_init.inc
use systest1;
let $NUM_VAL=`SELECT @NUM_VAL`;
eval set @f1_nums=$NUM_VAL;
set @tmp_num=int_rand(@f1_nums);
select @tmp_num;

# DEBUG select @tmp_num, @tmp_word;

# Delete all rows from the table where f1 is equal to the above number
delete from tb3_eng1 where f1=@tmp_num;

connection systuser;
--source suite/system_3/include/system_3_init.inc
use systest1;
select * from tb3_eng1 where f1>40;

connection syst1user;
--source suite/system_3/include/system_3_init.inc
use systest1;
let $NUM_VAL=`SELECT @NUM_VAL`;
eval set @f1_nums=$NUM_VAL;
set @tmp_num=int_rand(@f1_nums);
select @tmp_num;

select @tmp_num, @tmp_word;

# Delete all rows from the table where f1 is equal to the above number
delete from tb3_eng1 where f1=@tmp_num;

connection systuser;
--source suite/system_3/include/system_3_init.inc
use systest1;
select * from tb3_eng1 where f1>40;