1# 2# wl#7277: InnoDB: Bulk Load for Create Index 3# 4 5# Not supported in embedded 6-- source include/not_embedded.inc 7 8# This test case needs to crash the server. Needs a debug server. 9-- source include/have_debug.inc 10 11# Don't test this under valgrind, memory leaks will occur. 12-- source include/not_valgrind.inc 13 14# Avoid CrashReporter popup on Mac 15-- source include/not_crashrep.inc 16 17-- source include/have_innodb.inc 18 19# Create Insert Procedure 20DELIMITER |; 21CREATE PROCEDURE populate_t1() 22BEGIN 23 DECLARE i int DEFAULT 1; 24 25 START TRANSACTION; 26 WHILE (i <= 10000) DO 27 INSERT INTO t1 VALUES (i, i, CONCAT('a', i)); 28 SET i = i + 1; 29 END WHILE; 30 COMMIT; 31END| 32DELIMITER ;| 33 34# Test scenarios: 35# 1. Test restart; 36# 2. Test crash recovery. 37 38# Test Restart 39if ($row_format != 'COMPRESSED') 40{ 41 eval CREATE TABLE t1( 42 class INT, 43 id INT, 44 title VARCHAR(100) 45 ) ENGINE=InnoDB ROW_FORMAT=$row_format; 46} 47 48if ($row_format == 'COMPRESSED') 49{ 50 SET GLOBAL innodb_file_per_table=1; 51 52 eval CREATE TABLE t1( 53 class INT, 54 id INT, 55 title VARCHAR(100) 56 ) ENGINE=InnoDB ROW_FORMAT=$row_format KEY_BLOCK_SIZE=4; 57} 58 59-- disable_query_log 60CALL populate_t1(); 61-- enable_query_log 62 63SELECT COUNT(*) FROM t1; 64 65CREATE INDEX idx_title ON t1(title); 66 67--source include/restart_mysqld.inc 68 69CHECK TABLE t1; 70 71SELECT * FROM t1 WHERE title = 'a10'; 72 73SELECT * FROM t1 WHERE title = 'a5000'; 74 75SELECT * FROM t1 WHERE title = 'a10000'; 76 77SELECT * FROM t1 WHERE title = 'a10010'; 78 79DROP TABLE t1; 80 81-- echo # Test Blob 82 83if ($row_format != 'COMPRESSED') { 84 eval CREATE TABLE t1( 85 a INT PRIMARY KEY, 86 b TEXT, 87 c TEXT) ENGINE=InnoDB ROW_FORMAT=$row_format; 88} 89 90if ($row_format == 'COMPRESSED') { 91 SET GLOBAL innodb_file_per_table=1; 92 93 eval CREATE TABLE t1( 94 a INT PRIMARY KEY, 95 b TEXT, 96 c TEXT) ENGINE=InnoDB ROW_FORMAT=$row_format KEY_BLOCK_SIZE=4; 97} 98 99INSERT INTO t1 VALUES 100 (1, REPEAT('a',10000), 'a'), 101 (2, REPEAT('b',20000), 'b'), 102 (3, REPEAT('c',40000), 'c'), 103 (4, REPEAT('d',60000), 'd'); 104 105SELECT CHAR_LENGTH(b) FROM t1; 106 107ALTER TABLE t1 DROP COLUMN c; 108 109--source include/restart_mysqld.inc 110 111CHECK TABLE t1; 112 113SELECT CHAR_LENGTH(b) FROM t1; 114 115DROP TABLE t1; 116 117# Test Crash Recovery 118 119if ($row_format != 'COMPRESSED') 120{ 121 eval CREATE TABLE t1( 122 class INT, 123 id INT, 124 title VARCHAR(100) 125 ) ENGINE=InnoDB ROW_FORMAT=$row_format; 126} 127 128if ($row_format == 'COMPRESSED') 129{ 130 SET GLOBAL innodb_file_per_table=1; 131 132 eval CREATE TABLE t1( 133 class INT, 134 id INT, 135 title VARCHAR(100) 136 ) ENGINE=InnoDB ROW_FORMAT=$row_format KEY_BLOCK_SIZE=4; 137} 138 139-- disable_query_log 140CALL populate_t1(); 141-- enable_query_log 142 143SET SESSION debug="+d,crash_commit_before"; 144 145# Write file to make mysql-test-run.pl start up the server again 146--exec echo "restart" > $MYSQLTEST_VARDIR/tmp/mysqld.1.expect 147 148--error 2013 149CREATE INDEX idx_title ON t1(title); 150 151--enable_reconnect 152--source include/wait_until_connected_again.inc 153--disable_reconnect 154 155SELECT COUNT(*) FROM t1; 156 157CHECK TABLE t1; 158 159EXPLAIN SELECT * FROM t1 WHERE title = 'a10'; 160 161SELECT * FROM t1 WHERE title = 'a10'; 162 163SELECT * FROM t1 WHERE title = 'a5000'; 164 165SELECT * FROM t1 WHERE title = 'a10000'; 166 167SELECT * FROM t1 WHERE title = 'a10010'; 168 169DROP TABLE t1; 170 171-- echo # Test Blob 172 173if ($row_format != 'COMPRESSED') { 174 eval CREATE TABLE t1( 175 a INT PRIMARY KEY, 176 b TEXT, 177 c TEXT) ENGINE=InnoDB ROW_FORMAT=$row_format; 178} 179 180if ($row_format == 'COMPRESSED') { 181 SET GLOBAL innodb_file_per_table=1; 182 183 eval CREATE TABLE t1( 184 a INT PRIMARY KEY, 185 b TEXT, 186 c TEXT) ENGINE=InnoDB ROW_FORMAT=$row_format KEY_BLOCK_SIZE=4; 187} 188 189INSERT INTO t1 VALUES 190 (1, REPEAT('a',10000), 'a'), 191 (2, REPEAT('b',20000), 'b'), 192 (3, REPEAT('c',40000), 'c'), 193 (4, REPEAT('d',60000), 'd'); 194 195SELECT CHAR_LENGTH(b) FROM t1; 196 197SET SESSION debug="+d,crash_commit_before"; 198 199# Write file to make mysql-test-run.pl start up the server again 200--exec echo "restart" > $MYSQLTEST_VARDIR/tmp/mysqld.1.expect 201 202--error 2013 203ALTER TABLE t1 DROP COLUMN c; 204 205--enable_reconnect 206--source include/wait_until_connected_again.inc 207--disable_reconnect 208 209CHECK TABLE t1; 210 211SELECT CHAR_LENGTH(b) FROM t1; 212 213DROP TABLE t1; 214 215# Restore global variables 216if ($row_format == 'COMPRESSED') 217{ 218 SET GLOBAL innodb_file_per_table=default; 219} 220 221DROP PROCEDURE populate_t1; 222