| 1 |
--disable_query_log |
| 2 |
call mtr.add_suppression('Error in Log_event::read_log_event()'); |
| 3 |
call mtr.add_suppression("Ignoring ib_.*_0.dblwr - page size doesn't match"); |
| 4 |
--enable_query_log |
| 5 |
|
| 6 |
# setup db and table |
| 7 |
--disable_warnings |
| 8 |
DROP DATABASE IF EXISTS dbredoopt; |
| 9 |
CREATE DATABASE dbredoopt; |
| 10 |
USE dbredoopt; |
| 11 |
--enable_warnings |
| 12 |
CREATE TABLE dbredoopt.t1 |
| 13 |
(col1 INT, col2 INT, c1 LONGBLOB, c2 LONGBLOB, |
| 14 |
PRIMARY KEY(col1), INDEX `idx2` (col2), |
| 15 |
INDEX `idx3` (c1(300),c2(200)) |
| 16 |
); |
| 17 |
delimiter |; |
| 18 |
CREATE PROCEDURE dbredoopt.populate_t1() |
| 19 |
BEGIN |
| 20 |
DECLARE i INT DEFAULT 1; |
| 21 |
while (i <= 500) DO |
| 22 |
INSERT INTO dbredoopt.t1 values (i, i,REPEAT('a',5000), REPEAT('b',5000)); |
| 23 |
SET i = i + 1; |
| 24 |
END WHILE; |
| 25 |
END| |
| 26 |
delimiter ;| |
| 27 |
|
| 28 |
# new user which execute load from different client sessions |
| 29 |
CREATE USER 'redo_wl_user'@'localhost'; |
| 30 |
GRANT ALL PRIVILEGES ON *.* TO 'redo_wl_user'@'localhost' WITH GRANT OPTION; |
| 31 |
|
| 32 |
# Poulate initial data in table |
| 33 |
START TRANSACTION; |
| 34 |
SELECT count(*) FROM dbredoopt.t1; |
| 35 |
call dbredoopt.populate_t1(); |
| 36 |
SELECT count(*) FROM dbredoopt.t1; |
| 37 |
SELECT col1 FROM dbredoopt.t1 LIMIT 10; |
| 38 |
COMMIT; |
| 39 |
|
| 40 |
|
| 41 |
# Following perl snippet generates SQL file with INSERT/UPDATE/DELETE |
| 42 |
# with transactions |
| 43 |
# Files are created per client. |
| 44 |
# Same SQL used in all files but sequence of SQL is shuffled in each file |
| 45 |
perl; |
| 46 |
$files_cnt=$ENV{'client_cnt'}; |
| 47 |
$debug_var="crash_".$ENV{'debug_point'}; |
| 48 |
@load_type_array = ('mixed','high'); |
| 49 |
@log_buffer_size_array = ('1048576','4194304','8388608','16777216','33554432'); |
| 50 |
@log_innodb_log_write_ahead_size_array = ('512','1024','2048','4096','8192','16384'); |
| 51 |
|
| 52 |
$use_transactions = 1; |
| 53 |
$use_transactions = $ENV{'use_transactions'} if (defined($ENV{'use_transactions'})); |
| 54 |
|
| 55 |
while ($files_cnt >= 1) { |
| 56 |
die unless open(FILE, ">$ENV{MYSQLTEST_VARDIR}/tmp/t1"."_input".$files_cnt); |
| 57 |
@lines = (); |
| 58 |
@initial_line = ("START TRANSACTION;\n"); |
| 59 |
@trn_lines = ("COMMIT;START TRANSACTION;\n"); |
| 60 |
@rollback_lines = ("ROLLBACK;START TRANSACTION;\n"); |
| 61 |
for ( $i = 580 ; $i < 1501 ; $i++ ) { |
| 62 |
push(@lines,"INSERT INTO dbredoopt.t1 VALUES (".$i.",".($i*-1).","."REPEAT('c',5000),REPEAT('d',5000));\n" ); |
| 63 |
if ($i%20 == 0) { |
| 64 |
$rand_picked_int = rand_int(1); |
| 65 |
push(@lines,"UPDATE dbredoopt.t1 SET col1=(col1 * -1) , col2= (col2 * -1)". " WHERE ABS(col1)=".$rand_picked_int.";\n"); |
| 66 |
push(@lines,"DELETE FROM dbredoopt.t1 WHERE ABS(col1)=".rand_int(2).";\n"); |
| 67 |
} |
| 68 |
} |
| 69 |
push(@lines,@trn_lines,@trn_lines,@trn_lines,@trn_lines) if ($use_transactions); |
| 70 |
push(@lines,@rollback_lines,@rollback_lines) if ($use_transactions); |
| 71 |
shuffle(\@lines); |
| 72 |
splice @lines, (int rand(400)+100), 0, "set session debug=\"+d,".$debug_var."\";\n"; |
| 73 |
splice @lines, (int rand(400)+100), 0, "set global debug=\"+d,".$debug_var."\";\n"; |
| 74 |
|
| 75 |
# vary innodb_log_write_ahead_size |
| 76 |
print FILE "select \@\@innodb_log_write_ahead_size;\n"; |
| 77 |
print FILE "set global innodb_log_write_ahead_size=".$log_innodb_log_write_ahead_size_array[rand @log_innodb_log_write_ahead_size_array].";\n"; |
| 78 |
print FILE "select \@\@innodb_log_write_ahead_size;\n"; |
| 79 |
|
| 80 |
# innodb-log-buffer-size is global variable |
| 81 |
print FILE "select \@\@innodb_log_buffer_size;\n"; |
| 82 |
print FILE "set global innodb_log_buffer_size=".$log_buffer_size_array[rand @log_buffer_size_array].";\n"; |
| 83 |
print FILE "select \@\@innodb_log_buffer_size;\n"; |
| 84 |
print FILE "SET DEBUG_SYNC= 'now WAIT_FOR go_ahead';\n"; |
| 85 |
print FILE @initial_line if ($use_transactions); |
| 86 |
print FILE @lines; |
| 87 |
rand() < 0.5 ? print FILE "COMMIT;\n" : print FILE "ROLLBACK;\n" if ($use_transactions); |
| 88 |
|
| 89 |
$files_cnt--; |
| 90 |
close(FILE); |
| 91 |
} |
| 92 |
|
| 93 |
sub rand_int { |
| 94 |
return (int rand(1000)+501) if(@_[0]==1) ; |
| 95 |
return (int rand(500)+501) if(@_[0]==2) ; |
| 96 |
} |
| 97 |
sub shuffle { |
| 98 |
my $array = shift; |
| 99 |
my $i; |
| 100 |
for ($i = @$array; --$i; ) { |
| 101 |
my $j = int rand ($i+1); |
| 102 |
next if $i == $j; |
| 103 |
@$array[$i,$j] = @$array[$j,$i]; |
| 104 |
} |
| 105 |
} |
| 106 |
EOF |
| 107 |
|
| 108 |
|
| 109 |
# start mysql clients in parallel |
| 110 |
let connection_cnt = $client_cnt; |
| 111 |
while ($connection_cnt > 0 ) { |
| 112 |
--exec_in_background $MYSQL --force -vvv -hlocalhost --port=$MASTER_MYPORT -uredo_wl_user < $MYSQLTEST_VARDIR/tmp/t1_input$connection_cnt >$MYSQLTEST_VARDIR/tmp/t1_output$connection_cnt 2>&1 |
| 113 |
dec $connection_cnt; |
| 114 |
} |
| 115 |
|
| 116 |
--echo # Check rows which are not modified by parallel load |
| 117 |
SELECT col1 FROM dbredoopt.t1 WHERE col1%50=0 AND (col1 < 501 AND col1 > 0 ); |
| 118 |
|
| 119 |
|
| 120 |
--echo # Wait till all mysql clients are connected |
| 121 |
let $wait_condition= |
| 122 |
SELECT count(*) = $client_cnt FROM INFORMATION_SCHEMA.PROCESSLIST |
| 123 |
WHERE User LIKE "%redo_wl_user%"; |
| 124 |
--source include/wait_condition.inc |
| 125 |
--echo # Singal all mysql clients since each one is waiting for signal go_ahead |
| 126 |
--echo # with 'go_ahead' signal , all client suppose to resume in parallel |
| 127 |
--disable_query_log |
| 128 |
--disable_result_log |
| 129 |
let connection_cnt = $client_cnt; |
| 130 |
while ($connection_cnt > 0 ) { |
| 131 |
let $mysql_errno = 0; |
| 132 |
--error 0,1040,1053,2002,2003,2006,2013,2016,2017 |
| 133 |
SET DEBUG_SYNC= 'now SIGNAL go_ahead'; |
| 134 |
if ($mysql_errno != 0) { |
| 135 |
let connection_cnt = 1; |
| 136 |
} |
| 137 |
dec $connection_cnt; |
| 138 |
} |
| 139 |
# See if server is alive |
| 140 |
# If server is not alive the $mysql_errno will not 0 when 'select 1' is run |
| 141 |
let $mysql_errno = 0; |
| 142 |
--error 0,1040,1053,2002,2003,2006,2013,2016,2017 |
| 143 |
SELECT 1; |
| 144 |
--echo # Wait till all clients are over or server is gone |
| 145 |
#if ($mysql_errno == 0) { |
| 146 |
#let $wait_timeout= 180; |
| 147 |
#let $wait_condition= |
| 148 |
# SELECT count(*) = 0 FROM INFORMATION_SCHEMA.PROCESSLIST |
| 149 |
# WHERE user LIKE "%redo_wl_user%"; |
| 150 |
#--source include/wait_condition.inc |
| 151 |
#} |
| 152 |
|
| 153 |
let $mysql_errno = 0; |
| 154 |
while ($mysql_errno == 0) { |
| 155 |
--error 0,1040,1053,2002,2003,2006,2013,2016,2017 |
| 156 |
SELECT SLEEP(0.1); |
| 157 |
} |
| 158 |
--enable_query_log |
| 159 |
--enable_result_log |
| 160 |
# works |
| 161 |
--source include/start_mysqld.inc |
| 162 |
--echo # Check rows which are not modified by parallel load |
| 163 |
SELECT col1 FROM dbredoopt.t1 WHERE col1%50=0 AND (col1 < 501 AND col1 > 0 ); |
| 164 |
|
| 165 |
--echo # Check rows which are updated by parallel load |
| 166 |
# Insert in parallel load makes sure col2 = col1 * -1 , so that col1+col2=0 |
| 167 |
# and update in parallel is do not violate col1+col2=0 (by doing , col1*-1 & col2*-1) |
| 168 |
SELECT count(*)=0 FROM (SELECT col1,col2 FROM dbredoopt.t1 WHERE col1 NOT BETWEEN 1 AND 501 ) AS A WHERE (A.col1+A.col2) != 0 ; |
| 169 |
|
| 170 |
DROP DATABASE dbredoopt; |
| 171 |
DROP USER 'redo_wl_user'@'localhost'; |
| 172 |
--remove_files_wildcard $MYSQLTEST_VARDIR/tmp/ t1_*put* |