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