Merge lp:~sergei.glushchenko/mysql-server/expand_fast_index_creation into lp:mysql-server/5.1
- expand_fast_index_creation
- Merge into 5.1
Proposed by
Sergei Glushchenko
Status: | Needs review |
---|---|
Proposed branch: | lp:~sergei.glushchenko/mysql-server/expand_fast_index_creation |
Merge into: | lp:mysql-server/5.1 |
Diff against target: |
1508 lines (+1160/-2) 20 files modified
client/client_priv.h (+1/-0) client/mysqldump.c (+223/-0) mysql-test/r/percona_innodb_expand_fast_index_creation.result (+67/-0) mysql-test/r/percona_mysqldump_innodb_optimize_keys.result (+367/-0) mysql-test/suite/innodb_plugin/r/percona_bug_999147.result (+8/-0) mysql-test/suite/innodb_plugin/t/percona_bug_999147-master.opt (+1/-0) mysql-test/suite/innodb_plugin/t/percona_bug_999147.test (+29/-0) mysql-test/t/percona_innodb_expand_fast_index_creation.test (+46/-0) mysql-test/t/percona_mysqldump_innodb_optimize_keys.test (+187/-0) sql/mysqld.cc (+9/-1) sql/set_var.cc (+4/-0) sql/sql_class.h (+2/-0) sql/sql_lex.cc (+4/-0) sql/sql_lex.h (+8/-0) sql/sql_table.cc (+171/-0) storage/innodb_plugin/handler/ha_innodb.cc (+15/-0) storage/innodb_plugin/handler/handler0alter.cc (+2/-0) storage/innodb_plugin/include/ha_prototypes.h (+11/-0) storage/innodb_plugin/row/row0merge.c (+4/-0) storage/innodb_plugin/row/row0mysql.c (+1/-1) |
To merge this branch: | bzr merge lp:~sergei.glushchenko/mysql-server/expand_fast_index_creation |
Related bugs: |
Reviewer | Review Type | Date Requested | Status |
---|---|---|---|
Alexey Kopytov (community) | Approve | ||
Review via email: mp+113247@code.launchpad.net |
Commit message
Description of the change
Patch expand_
Fix for bug #999147.
To post a comment you must log in.
Unmerged revisions
- 3733. By Sergei Glushchenko
-
port expand_
fast_index_ creation patch
Preview Diff
[H/L] Next/Prev Comment, [J/K] Next/Prev File, [N/P] Next/Prev Hunk
1 | === modified file 'client/client_priv.h' |
2 | --- client/client_priv.h 2011-07-03 15:47:37 +0000 |
3 | +++ client/client_priv.h 2012-07-03 16:14:26 +0000 |
4 | @@ -94,5 +94,6 @@ |
5 | OPT_WRITE_BINLOG, OPT_DUMP_DATE, |
6 | OPT_FIRST_SLAVE, |
7 | OPT_ALL, |
8 | + OPT_INNODB_OPTIMIZE_KEYS, |
9 | OPT_MAX_CLIENT_OPTION |
10 | }; |
11 | |
12 | === modified file 'client/mysqldump.c' |
13 | --- client/mysqldump.c 2012-05-07 11:16:44 +0000 |
14 | +++ client/mysqldump.c 2012-07-03 16:14:26 +0000 |
15 | @@ -47,6 +47,7 @@ |
16 | #include <m_ctype.h> |
17 | #include <hash.h> |
18 | #include <stdarg.h> |
19 | +#include <my_list.h> |
20 | |
21 | #include "client_priv.h" |
22 | #include "mysql.h" |
23 | @@ -148,6 +149,8 @@ |
24 | #endif |
25 | static uint opt_protocol= 0; |
26 | |
27 | +static my_bool opt_innodb_optimize_keys= FALSE; |
28 | + |
29 | /* |
30 | Dynamic_string wrapper functions. In this file use these |
31 | wrappers, they will terminate the process if there is |
32 | @@ -193,6 +196,8 @@ |
33 | |
34 | HASH ignore_table; |
35 | |
36 | +LIST *skipped_keys_list; |
37 | + |
38 | static struct my_option my_long_options[] = |
39 | { |
40 | {"all", OPT_ALL, "Deprecated. Use --create-options instead.", |
41 | @@ -339,6 +344,11 @@ |
42 | "be specified with both database and table names, e.g., " |
43 | "--ignore-table=database.table.", |
44 | 0, 0, 0, GET_STR, REQUIRED_ARG, 0, 0, 0, 0, 0, 0}, |
45 | + {"innodb-optimize-keys", OPT_INNODB_OPTIMIZE_KEYS, |
46 | + "Use InnoDB fast index creation by creating secondary indexes after " |
47 | + "dumping the data.", |
48 | + &opt_innodb_optimize_keys, &opt_innodb_optimize_keys, 0, GET_BOOL, NO_ARG, |
49 | + 0, 0, 0, 0, 0, 0}, |
50 | {"insert-ignore", OPT_INSERT_IGNORE, "Insert rows with INSERT IGNORE.", |
51 | &opt_ignore, &opt_ignore, 0, GET_BOOL, NO_ARG, 0, 0, 0, 0, |
52 | 0, 0}, |
53 | @@ -2433,6 +2443,190 @@ |
54 | } |
55 | |
56 | /* |
57 | + Parse the specified key definition string and check if the key indexes |
58 | + any of the columns from ignored_columns. |
59 | +*/ |
60 | +static my_bool contains_ignored_column(HASH *ignored_columns, char *keydef) |
61 | +{ |
62 | + char *leftp, *rightp; |
63 | + |
64 | + if ((leftp = strchr(keydef, '(')) && |
65 | + (rightp = strchr(leftp, ')')) && |
66 | + rightp > leftp + 3 && /* (`...`) */ |
67 | + leftp[1] == '`' && |
68 | + rightp[-1] == '`' && |
69 | + my_hash_search(ignored_columns, (uchar *) leftp + 2, rightp - leftp - 3)) |
70 | + return TRUE; |
71 | + |
72 | + return FALSE; |
73 | +} |
74 | + |
75 | + |
76 | +/* |
77 | + Remove secondary/foreign key definitions from a given SHOW CREATE TABLE string |
78 | + and store them into a temporary list to be used later. |
79 | + |
80 | + SYNOPSIS |
81 | + skip_secondary_keys() |
82 | + create_str SHOW CREATE TABLE output |
83 | + has_pk TRUE, if the table has PRIMARY KEY |
84 | + (or UNIQUE key on non-nullable columns) |
85 | + |
86 | + |
87 | + DESCRIPTION |
88 | + |
89 | + Stores all lines starting with "KEY" or "UNIQUE KEY" |
90 | + into skipped_keys_list and removes them from the input string. |
91 | + Ignoring FOREIGN KEYS constraints when creating the table is ok, because |
92 | + mysqldump sets foreign_key_checks to 0 anyway. |
93 | +*/ |
94 | + |
95 | +static void skip_secondary_keys(char *create_str, my_bool has_pk) |
96 | +{ |
97 | + char *ptr, *strend; |
98 | + char *last_comma = NULL; |
99 | + HASH ignored_columns; |
100 | + my_bool pk_processed= FALSE; |
101 | + |
102 | + if (hash_init(&ignored_columns, charset_info, 16, 0, 0, |
103 | + (hash_get_key) get_table_key, |
104 | + (hash_free_key) free_table_ent, 0)) |
105 | + exit(EX_EOM); |
106 | + |
107 | + strend= create_str + strlen(create_str); |
108 | + |
109 | + ptr= create_str; |
110 | + while (*ptr) |
111 | + { |
112 | + char *tmp, *orig_ptr, c; |
113 | + my_bool is_unique= FALSE; |
114 | + |
115 | + orig_ptr= ptr; |
116 | + /* Skip leading whitespace */ |
117 | + while (*ptr && my_isspace(charset_info, *ptr)) |
118 | + ptr++; |
119 | + |
120 | + /* Read the next line */ |
121 | + for (tmp= ptr; *tmp != '\n' && *tmp != '\0'; tmp++); |
122 | + |
123 | + c= *tmp; |
124 | + *tmp= '\0'; /* so strstr() only processes the current line */ |
125 | + |
126 | + /* Is it a secondary index definition? */ |
127 | + if (c == '\n' && |
128 | + (((is_unique= !strncmp(ptr, "UNIQUE KEY ", sizeof("UNIQUE KEY ")-1)) && |
129 | + (pk_processed || !has_pk)) || |
130 | + !strncmp(ptr, "KEY ", sizeof("KEY ") - 1)) && |
131 | + !contains_ignored_column(&ignored_columns, ptr)) |
132 | + { |
133 | + char *data, *end= tmp - 1; |
134 | + |
135 | + /* Remove the trailing comma */ |
136 | + if (*end == ',') |
137 | + end--; |
138 | + data= my_strndup(ptr, end - ptr + 1, MYF(MY_FAE)); |
139 | + skipped_keys_list= list_cons(data, skipped_keys_list); |
140 | + |
141 | + memmove(orig_ptr, tmp + 1, strend - tmp); |
142 | + ptr= orig_ptr; |
143 | + strend-= tmp + 1 - ptr; |
144 | + |
145 | + /* Remove the comma on the previos line */ |
146 | + if (last_comma != NULL) |
147 | + { |
148 | + *last_comma= ' '; |
149 | + } |
150 | + } |
151 | + else |
152 | + { |
153 | + char *end; |
154 | + |
155 | + if (last_comma != NULL && *ptr != ')') |
156 | + { |
157 | + /* |
158 | + It's not the last line of CREATE TABLE, so we have skipped a key |
159 | + definition. We have to restore the last removed comma. |
160 | + */ |
161 | + *last_comma= ','; |
162 | + } |
163 | + |
164 | + if ((has_pk && is_unique && !pk_processed) || |
165 | + !strncmp(ptr, "PRIMARY KEY ", sizeof("PRIMARY KEY ") - 1)) |
166 | + pk_processed= TRUE; |
167 | + |
168 | + if (strstr(ptr, "AUTO_INCREMENT") && *ptr == '`') |
169 | + { |
170 | + /* |
171 | + If a secondary key is defined on this column later, |
172 | + it cannot be skipped, as CREATE TABLE would fail on import. |
173 | + */ |
174 | + for (end= ptr + 1; *end != '`' && *end != '\0'; end++); |
175 | + if (*end == '`' && end > ptr + 1 && |
176 | + my_hash_insert(&ignored_columns, |
177 | + (uchar *) my_strndup(ptr + 1, |
178 | + end - ptr - 1, MYF(0)))) |
179 | + { |
180 | + exit(EX_EOM); |
181 | + } |
182 | + } |
183 | + |
184 | + *tmp= c; |
185 | + |
186 | + if (tmp[-1] == ',') |
187 | + last_comma= tmp - 1; |
188 | + ptr= (*tmp == '\0') ? tmp : tmp + 1; |
189 | + } |
190 | + } |
191 | + |
192 | + my_hash_free(&ignored_columns); |
193 | +} |
194 | + |
195 | +/* |
196 | + Check if the table has a primary key defined either explicitly or |
197 | + implicitly (i.e. a unique key on non-nullable columns). |
198 | + |
199 | + SYNOPSIS |
200 | + my_bool has_primary_key(const char *table_name) |
201 | + |
202 | + table_name quoted table name |
203 | + |
204 | + RETURNS TRUE if the table has a primary key |
205 | + |
206 | + DESCRIPTION |
207 | +*/ |
208 | + |
209 | +static my_bool has_primary_key(const char *table_name) |
210 | +{ |
211 | + MYSQL_RES *res= NULL; |
212 | + MYSQL_ROW row; |
213 | + char query_buff[QUERY_LENGTH]; |
214 | + my_bool has_pk= TRUE; |
215 | + |
216 | + my_snprintf(query_buff, sizeof(query_buff), |
217 | + "SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE " |
218 | + "TABLE_SCHEMA=DATABASE() AND TABLE_NAME='%s' AND " |
219 | + "COLUMN_KEY='PRI'", table_name); |
220 | + if (mysql_query(mysql, query_buff) || !(res= mysql_store_result(mysql)) || |
221 | + !(row= mysql_fetch_row(res))) |
222 | + { |
223 | + fprintf(stderr, "Warning: Couldn't determine if table %s has a " |
224 | + "primary key (%s). " |
225 | + "--innodb-optimize-keys may work inefficiently.\n", |
226 | + table_name, mysql_error(mysql)); |
227 | + goto cleanup; |
228 | + } |
229 | + |
230 | + has_pk= atoi(row[0]) > 0; |
231 | + |
232 | +cleanup: |
233 | + if (res) |
234 | + mysql_free_result(res); |
235 | + |
236 | + return has_pk; |
237 | +} |
238 | + |
239 | + |
240 | +/* |
241 | get_table_structure -- retrievs database structure, prints out corresponding |
242 | CREATE statement and fills out insert_pat if the table is the type we will |
243 | be dumping. |
244 | @@ -2470,6 +2664,7 @@ |
245 | my_bool is_log_table; |
246 | MYSQL_RES *result; |
247 | MYSQL_ROW row; |
248 | + my_bool has_pk= FALSE; |
249 | DBUG_ENTER("get_table_structure"); |
250 | DBUG_PRINT("enter", ("db: %s table: %s", db, table)); |
251 | |
252 | @@ -2511,6 +2706,9 @@ |
253 | result_table= quote_name(table, table_buff, 1); |
254 | opt_quoted_table= quote_name(table, table_buff2, 0); |
255 | |
256 | + if (opt_innodb_optimize_keys && !strcmp(table_type, "InnoDB")) |
257 | + has_pk= has_primary_key(table); |
258 | + |
259 | if (opt_order_by_primary) |
260 | order_by= primary_key_fields(result_table); |
261 | |
262 | @@ -2672,6 +2870,10 @@ |
263 | is_log_table= general_log_or_slow_log_tables(db, table); |
264 | if (is_log_table) |
265 | row[1]+= 13; /* strlen("CREATE TABLE ")= 13 */ |
266 | + |
267 | + if (opt_innodb_optimize_keys && !strcmp(table_type, "InnoDB")) |
268 | + skip_secondary_keys(row[1], has_pk); |
269 | + |
270 | if (opt_compatible_mode & 3) |
271 | { |
272 | fprintf(sql_file, |
273 | @@ -3788,6 +3990,27 @@ |
274 | goto err; |
275 | } |
276 | |
277 | + /* Perform delayed secondary index creation for --innodb-optimize-keys */ |
278 | + if (skipped_keys_list) |
279 | + { |
280 | + uint keys; |
281 | + skipped_keys_list= list_reverse(skipped_keys_list); |
282 | + fprintf(md_result_file, "ALTER TABLE %s ", opt_quoted_table); |
283 | + for (keys= list_length(skipped_keys_list); keys > 0; keys--) |
284 | + { |
285 | + LIST *node= skipped_keys_list; |
286 | + char *def= node->data; |
287 | + |
288 | + fprintf(md_result_file, "ADD %s%s", def, (keys > 1) ? ", " : ";\n"); |
289 | + |
290 | + skipped_keys_list= list_delete(skipped_keys_list, node); |
291 | + my_free(def, MYF(0)); |
292 | + my_free(node, MYF(0)); |
293 | + } |
294 | + |
295 | + DBUG_ASSERT(skipped_keys_list == NULL); |
296 | + } |
297 | + |
298 | /* Moved enable keys to before unlock per bug 15977 */ |
299 | if (opt_disable_keys) |
300 | { |
301 | |
302 | === added file 'mysql-test/r/percona_innodb_expand_fast_index_creation.result' |
303 | --- mysql-test/r/percona_innodb_expand_fast_index_creation.result 1970-01-01 00:00:00 +0000 |
304 | +++ mysql-test/r/percona_innodb_expand_fast_index_creation.result 2012-07-03 16:14:26 +0000 |
305 | @@ -0,0 +1,67 @@ |
306 | +SELECT @@expand_fast_index_creation; |
307 | +@@expand_fast_index_creation |
308 | +0 |
309 | +CREATE TABLE t1( |
310 | +id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, |
311 | +a CHAR(1) NOT NULL, |
312 | +b CHAR(36) NOT NULL) ENGINE=InnoDB; |
313 | +INSERT INTO t1(a,b) VALUES ('a','b'); |
314 | +INSERT INTO t1(a,b) SELECT a,b FROM t1; |
315 | +INSERT INTO t1(a,b) SELECT a,b FROM t1; |
316 | +INSERT INTO t1(a,b) SELECT a,b FROM t1; |
317 | +INSERT INTO t1(a,b) SELECT a,b FROM t1; |
318 | +ALTER TABLE t1 ADD KEY (a); |
319 | +affected rows: 0 |
320 | +info: Records: 0 Duplicates: 0 Warnings: 0 |
321 | +EXPLAIN SELECT COUNT(*) FROM t1, t1 t2 WHERE t1.a = t2.a AND t1.b = t2.b; |
322 | +id 1 |
323 | +select_type SIMPLE |
324 | +table t1 |
325 | +type ALL |
326 | +possible_keys a |
327 | +key NULL |
328 | +key_len NULL |
329 | +ref NULL |
330 | +rows 16 |
331 | +Extra |
332 | +id 1 |
333 | +select_type SIMPLE |
334 | +table t2 |
335 | +type ref |
336 | +possible_keys a |
337 | +key a |
338 | +key_len 1 |
339 | +ref test.t1.a |
340 | +rows 1 |
341 | +Extra Using where |
342 | +ALTER TABLE t1 DROP KEY a; |
343 | +SET expand_fast_index_creation = 1; |
344 | +SELECT @@expand_fast_index_creation; |
345 | +@@expand_fast_index_creation |
346 | +1 |
347 | +ALTER TABLE t1 ADD KEY (a); |
348 | +affected rows: 0 |
349 | +info: Records: 0 Duplicates: 0 Warnings: 0 |
350 | +EXPLAIN SELECT COUNT(*) FROM t1, t1 t2 WHERE t1.a = t2.a AND t1.b = t2.b; |
351 | +id 1 |
352 | +select_type SIMPLE |
353 | +table t1 |
354 | +type ALL |
355 | +possible_keys a |
356 | +key NULL |
357 | +key_len NULL |
358 | +ref NULL |
359 | +rows 16 |
360 | +Extra |
361 | +id 1 |
362 | +select_type SIMPLE |
363 | +table t2 |
364 | +type ALL |
365 | +possible_keys a |
366 | +key NULL |
367 | +key_len NULL |
368 | +ref NULL |
369 | +rows 16 |
370 | +Extra Using where; Using join buffer |
371 | +SET expand_fast_index_creation = 0; |
372 | +DROP TABLE t1; |
373 | |
374 | === added file 'mysql-test/r/percona_mysqldump_innodb_optimize_keys.result' |
375 | --- mysql-test/r/percona_mysqldump_innodb_optimize_keys.result 1970-01-01 00:00:00 +0000 |
376 | +++ mysql-test/r/percona_mysqldump_innodb_optimize_keys.result 2012-07-03 16:14:26 +0000 |
377 | @@ -0,0 +1,367 @@ |
378 | +# |
379 | +# Test the --innodb-optimize-keys option. |
380 | +# |
381 | +CREATE TABLE t1 (a INT NOT NULL PRIMARY KEY, b INT, KEY(b)) ENGINE=MyISAM; |
382 | +###################################### |
383 | + |
384 | +/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; |
385 | +/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; |
386 | +/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */; |
387 | +/*!40101 SET NAMES utf8 */; |
388 | +/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */; |
389 | +/*!40103 SET TIME_ZONE='+00:00' */; |
390 | +/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */; |
391 | +/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */; |
392 | +/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */; |
393 | +/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */; |
394 | +DROP TABLE IF EXISTS `t1`; |
395 | +/*!40101 SET @saved_cs_client = @@character_set_client */; |
396 | +/*!40101 SET character_set_client = utf8 */; |
397 | +CREATE TABLE `t1` ( |
398 | + `a` int(11) NOT NULL, |
399 | + `b` int(11) DEFAULT NULL, |
400 | + PRIMARY KEY (`a`), |
401 | + KEY `b` (`b`) |
402 | +) ENGINE=MyISAM DEFAULT CHARSET=latin1; |
403 | +/*!40101 SET character_set_client = @saved_cs_client */; |
404 | + |
405 | +LOCK TABLES `t1` WRITE; |
406 | +/*!40000 ALTER TABLE `t1` DISABLE KEYS */; |
407 | +/*!40000 ALTER TABLE `t1` ENABLE KEYS */; |
408 | +UNLOCK TABLES; |
409 | +/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */; |
410 | + |
411 | +/*!40101 SET SQL_MODE=@OLD_SQL_MODE */; |
412 | +/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */; |
413 | +/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */; |
414 | +/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */; |
415 | +/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */; |
416 | +/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */; |
417 | +/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */; |
418 | + |
419 | +###################################### |
420 | +DROP TABLE t1; |
421 | +CREATE TABLE t2 (a INT PRIMARY KEY) ENGINE=InnoDB; |
422 | +INSERT INTO t2 VALUES (0), (1), (2); |
423 | +CREATE TABLE t1 ( |
424 | +id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, |
425 | +a INT, b VARCHAR(255), c DECIMAL(10,3), |
426 | +KEY (b), |
427 | +UNIQUE KEY uniq(c,a), |
428 | +FOREIGN KEY (a) REFERENCES t2(a) ON DELETE CASCADE |
429 | +) ENGINE=InnoDB; |
430 | +INSERT INTO t1(a,b,c) VALUES (0, "0", 0.0), (1, "1", 1.1), (2, "2", 2.2); |
431 | +###################################### |
432 | + |
433 | +/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; |
434 | +/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; |
435 | +/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */; |
436 | +/*!40101 SET NAMES utf8 */; |
437 | +/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */; |
438 | +/*!40103 SET TIME_ZONE='+00:00' */; |
439 | +/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */; |
440 | +/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */; |
441 | +/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */; |
442 | +/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */; |
443 | +DROP TABLE IF EXISTS `t1`; |
444 | +/*!40101 SET @saved_cs_client = @@character_set_client */; |
445 | +/*!40101 SET character_set_client = utf8 */; |
446 | +CREATE TABLE `t1` ( |
447 | + `id` int(11) NOT NULL AUTO_INCREMENT, |
448 | + `a` int(11) DEFAULT NULL, |
449 | + `b` varchar(255) DEFAULT NULL, |
450 | + `c` decimal(10,3) DEFAULT NULL, |
451 | + PRIMARY KEY (`id`), |
452 | + CONSTRAINT `t1_ibfk_1` FOREIGN KEY (`a`) REFERENCES `t2` (`a`) ON DELETE CASCADE |
453 | +) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1; |
454 | +/*!40101 SET character_set_client = @saved_cs_client */; |
455 | + |
456 | +LOCK TABLES `t1` WRITE; |
457 | +/*!40000 ALTER TABLE `t1` DISABLE KEYS */; |
458 | +INSERT INTO `t1` VALUES (1,0,'0','0.000'),(2,1,'1','1.100'),(3,2,'2','2.200'); |
459 | +ALTER TABLE `t1` ADD UNIQUE KEY `uniq` (`c`,`a`), ADD KEY `b` (`b`), ADD KEY `a` (`a`); |
460 | +/*!40000 ALTER TABLE `t1` ENABLE KEYS */; |
461 | +UNLOCK TABLES; |
462 | +DROP TABLE IF EXISTS `t2`; |
463 | +/*!40101 SET @saved_cs_client = @@character_set_client */; |
464 | +/*!40101 SET character_set_client = utf8 */; |
465 | +CREATE TABLE `t2` ( |
466 | + `a` int(11) NOT NULL, |
467 | + PRIMARY KEY (`a`) |
468 | +) ENGINE=InnoDB DEFAULT CHARSET=latin1; |
469 | +/*!40101 SET character_set_client = @saved_cs_client */; |
470 | + |
471 | +LOCK TABLES `t2` WRITE; |
472 | +/*!40000 ALTER TABLE `t2` DISABLE KEYS */; |
473 | +INSERT INTO `t2` VALUES (0),(1),(2); |
474 | +/*!40000 ALTER TABLE `t2` ENABLE KEYS */; |
475 | +UNLOCK TABLES; |
476 | +/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */; |
477 | + |
478 | +/*!40101 SET SQL_MODE=@OLD_SQL_MODE */; |
479 | +/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */; |
480 | +/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */; |
481 | +/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */; |
482 | +/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */; |
483 | +/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */; |
484 | +/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */; |
485 | + |
486 | +###################################### |
487 | +DROP TABLE t1, t2; |
488 | +CREATE TABLE t1 ( |
489 | +id INT NOT NULL AUTO_INCREMENT, |
490 | +KEY (id) |
491 | +) ENGINE=InnoDB; |
492 | +CREATE TABLE t2 ( |
493 | +id INT NOT NULL AUTO_INCREMENT, |
494 | +UNIQUE KEY (id) |
495 | +) ENGINE=InnoDB; |
496 | +INSERT INTO t1 VALUES (), (), (); |
497 | +INSERT INTO t2 VALUES (), (), (); |
498 | +###################################### |
499 | + |
500 | +/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; |
501 | +/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; |
502 | +/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */; |
503 | +/*!40101 SET NAMES utf8 */; |
504 | +/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */; |
505 | +/*!40103 SET TIME_ZONE='+00:00' */; |
506 | +/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */; |
507 | +/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */; |
508 | +/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */; |
509 | +/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */; |
510 | +DROP TABLE IF EXISTS `t1`; |
511 | +/*!40101 SET @saved_cs_client = @@character_set_client */; |
512 | +/*!40101 SET character_set_client = utf8 */; |
513 | +CREATE TABLE `t1` ( |
514 | + `id` int(11) NOT NULL AUTO_INCREMENT, |
515 | + KEY `id` (`id`) |
516 | +) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1; |
517 | +/*!40101 SET character_set_client = @saved_cs_client */; |
518 | + |
519 | +LOCK TABLES `t1` WRITE; |
520 | +/*!40000 ALTER TABLE `t1` DISABLE KEYS */; |
521 | +INSERT INTO `t1` VALUES (1),(2),(3); |
522 | +/*!40000 ALTER TABLE `t1` ENABLE KEYS */; |
523 | +UNLOCK TABLES; |
524 | +DROP TABLE IF EXISTS `t2`; |
525 | +/*!40101 SET @saved_cs_client = @@character_set_client */; |
526 | +/*!40101 SET character_set_client = utf8 */; |
527 | +CREATE TABLE `t2` ( |
528 | + `id` int(11) NOT NULL AUTO_INCREMENT, |
529 | + UNIQUE KEY `id` (`id`) |
530 | +) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1; |
531 | +/*!40101 SET character_set_client = @saved_cs_client */; |
532 | + |
533 | +LOCK TABLES `t2` WRITE; |
534 | +/*!40000 ALTER TABLE `t2` DISABLE KEYS */; |
535 | +INSERT INTO `t2` VALUES (1),(2),(3); |
536 | +/*!40000 ALTER TABLE `t2` ENABLE KEYS */; |
537 | +UNLOCK TABLES; |
538 | +/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */; |
539 | + |
540 | +/*!40101 SET SQL_MODE=@OLD_SQL_MODE */; |
541 | +/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */; |
542 | +/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */; |
543 | +/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */; |
544 | +/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */; |
545 | +/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */; |
546 | +/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */; |
547 | + |
548 | +###################################### |
549 | +DROP TABLE t1, t2; |
550 | +CREATE TABLE t1 ( |
551 | +a INT NOT NULL, |
552 | +UNIQUE KEY (a)) ENGINE=InnoDB; |
553 | +CREATE TABLE t2 ( |
554 | +a INT NOT NULL, |
555 | +b INT NOT NULL, |
556 | +UNIQUE KEY (a,b)) ENGINE=InnoDB; |
557 | +CREATE TABLE t3 ( |
558 | +a INT, |
559 | +b INT, |
560 | +UNIQUE KEY (a,b)) ENGINE=InnoDB; |
561 | +CREATE TABLE t4 ( |
562 | +a INT NOT NULL, |
563 | +b INT NOT NULL, |
564 | +PRIMARY KEY (a,b), |
565 | +UNIQUE KEY(b)) ENGINE=InnoDB; |
566 | +SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE |
567 | +TABLE_SCHEMA=DATABASE() AND |
568 | +TABLE_NAME='t1' AND |
569 | +COLUMN_KEY='PRI'; |
570 | +COUNT(*) |
571 | +1 |
572 | +SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE |
573 | +TABLE_SCHEMA=DATABASE() AND |
574 | +TABLE_NAME='t2' AND |
575 | +COLUMN_KEY='PRI'; |
576 | +COUNT(*) |
577 | +2 |
578 | +SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE |
579 | +TABLE_SCHEMA=DATABASE() AND |
580 | +TABLE_NAME='t3' AND |
581 | +COLUMN_KEY='PRI'; |
582 | +COUNT(*) |
583 | +0 |
584 | +SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE |
585 | +TABLE_SCHEMA=DATABASE() AND |
586 | +TABLE_NAME='t4' AND |
587 | +COLUMN_KEY='PRI'; |
588 | +COUNT(*) |
589 | +2 |
590 | +INSERT INTO t1 VALUES (1), (2), (3); |
591 | +INSERT INTO t2 VALUES (1,1), (2,2), (3,3); |
592 | +INSERT INTO t3 SELECT * FROM t2; |
593 | +INSERT INTO t4 SELECT * FROM t2; |
594 | +###################################### |
595 | + |
596 | +/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; |
597 | +/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; |
598 | +/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */; |
599 | +/*!40101 SET NAMES utf8 */; |
600 | +/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */; |
601 | +/*!40103 SET TIME_ZONE='+00:00' */; |
602 | +/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */; |
603 | +/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */; |
604 | +/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */; |
605 | +/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */; |
606 | +DROP TABLE IF EXISTS `t1`; |
607 | +/*!40101 SET @saved_cs_client = @@character_set_client */; |
608 | +/*!40101 SET character_set_client = utf8 */; |
609 | +CREATE TABLE `t1` ( |
610 | + `a` int(11) NOT NULL, |
611 | + UNIQUE KEY `a` (`a`) |
612 | +) ENGINE=InnoDB DEFAULT CHARSET=latin1; |
613 | +/*!40101 SET character_set_client = @saved_cs_client */; |
614 | + |
615 | +LOCK TABLES `t1` WRITE; |
616 | +/*!40000 ALTER TABLE `t1` DISABLE KEYS */; |
617 | +INSERT INTO `t1` VALUES (1),(2),(3); |
618 | +/*!40000 ALTER TABLE `t1` ENABLE KEYS */; |
619 | +UNLOCK TABLES; |
620 | +DROP TABLE IF EXISTS `t2`; |
621 | +/*!40101 SET @saved_cs_client = @@character_set_client */; |
622 | +/*!40101 SET character_set_client = utf8 */; |
623 | +CREATE TABLE `t2` ( |
624 | + `a` int(11) NOT NULL, |
625 | + `b` int(11) NOT NULL, |
626 | + UNIQUE KEY `a` (`a`,`b`) |
627 | +) ENGINE=InnoDB DEFAULT CHARSET=latin1; |
628 | +/*!40101 SET character_set_client = @saved_cs_client */; |
629 | + |
630 | +LOCK TABLES `t2` WRITE; |
631 | +/*!40000 ALTER TABLE `t2` DISABLE KEYS */; |
632 | +INSERT INTO `t2` VALUES (1,1),(2,2),(3,3); |
633 | +/*!40000 ALTER TABLE `t2` ENABLE KEYS */; |
634 | +UNLOCK TABLES; |
635 | +DROP TABLE IF EXISTS `t3`; |
636 | +/*!40101 SET @saved_cs_client = @@character_set_client */; |
637 | +/*!40101 SET character_set_client = utf8 */; |
638 | +CREATE TABLE `t3` ( |
639 | + `a` int(11) DEFAULT NULL, |
640 | + `b` int(11) DEFAULT NULL |
641 | +) ENGINE=InnoDB DEFAULT CHARSET=latin1; |
642 | +/*!40101 SET character_set_client = @saved_cs_client */; |
643 | + |
644 | +LOCK TABLES `t3` WRITE; |
645 | +/*!40000 ALTER TABLE `t3` DISABLE KEYS */; |
646 | +INSERT INTO `t3` VALUES (1,1),(2,2),(3,3); |
647 | +ALTER TABLE `t3` ADD UNIQUE KEY `a` (`a`,`b`); |
648 | +/*!40000 ALTER TABLE `t3` ENABLE KEYS */; |
649 | +UNLOCK TABLES; |
650 | +DROP TABLE IF EXISTS `t4`; |
651 | +/*!40101 SET @saved_cs_client = @@character_set_client */; |
652 | +/*!40101 SET character_set_client = utf8 */; |
653 | +CREATE TABLE `t4` ( |
654 | + `a` int(11) NOT NULL, |
655 | + `b` int(11) NOT NULL, |
656 | + PRIMARY KEY (`a`,`b`) |
657 | +) ENGINE=InnoDB DEFAULT CHARSET=latin1; |
658 | +/*!40101 SET character_set_client = @saved_cs_client */; |
659 | + |
660 | +LOCK TABLES `t4` WRITE; |
661 | +/*!40000 ALTER TABLE `t4` DISABLE KEYS */; |
662 | +INSERT INTO `t4` VALUES (1,1),(2,2),(3,3); |
663 | +ALTER TABLE `t4` ADD UNIQUE KEY `b` (`b`); |
664 | +/*!40000 ALTER TABLE `t4` ENABLE KEYS */; |
665 | +UNLOCK TABLES; |
666 | +/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */; |
667 | + |
668 | +/*!40101 SET SQL_MODE=@OLD_SQL_MODE */; |
669 | +/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */; |
670 | +/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */; |
671 | +/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */; |
672 | +/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */; |
673 | +/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */; |
674 | +/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */; |
675 | + |
676 | +###################################### |
677 | +DROP TABLE t1, t2, t3, t4; |
678 | +CREATE TABLE t1 ( |
679 | +id INT NOT NULL PRIMARY KEY |
680 | +) ENGINE=InnoDB; |
681 | +CREATE TABLE t2 ( |
682 | +id INT NOT NULL AUTO_INCREMENT, |
683 | +a INT NOT NULL, |
684 | +PRIMARY KEY (id), |
685 | +KEY (a), |
686 | +FOREIGN KEY (a) REFERENCES t2 (id) |
687 | +) ENGINE=InnoDB; |
688 | +INSERT INTO t1 VALUES (1), (2), (3); |
689 | +INSERT INTO t2 VALUES (1, 1), (2, 2), (3, 3); |
690 | +###################################### |
691 | + |
692 | +/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; |
693 | +/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; |
694 | +/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */; |
695 | +/*!40101 SET NAMES utf8 */; |
696 | +/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */; |
697 | +/*!40103 SET TIME_ZONE='+00:00' */; |
698 | +/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */; |
699 | +/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */; |
700 | +/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */; |
701 | +/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */; |
702 | +DROP TABLE IF EXISTS `t1`; |
703 | +/*!40101 SET @saved_cs_client = @@character_set_client */; |
704 | +/*!40101 SET character_set_client = utf8 */; |
705 | +CREATE TABLE `t1` ( |
706 | + `id` int(11) NOT NULL, |
707 | + PRIMARY KEY (`id`) |
708 | +) ENGINE=InnoDB DEFAULT CHARSET=latin1; |
709 | +/*!40101 SET character_set_client = @saved_cs_client */; |
710 | + |
711 | +LOCK TABLES `t1` WRITE; |
712 | +/*!40000 ALTER TABLE `t1` DISABLE KEYS */; |
713 | +INSERT INTO `t1` VALUES (1),(2),(3); |
714 | +/*!40000 ALTER TABLE `t1` ENABLE KEYS */; |
715 | +UNLOCK TABLES; |
716 | +DROP TABLE IF EXISTS `t2`; |
717 | +/*!40101 SET @saved_cs_client = @@character_set_client */; |
718 | +/*!40101 SET character_set_client = utf8 */; |
719 | +CREATE TABLE `t2` ( |
720 | + `id` int(11) NOT NULL AUTO_INCREMENT, |
721 | + `a` int(11) NOT NULL, |
722 | + PRIMARY KEY (`id`), |
723 | + CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`a`) REFERENCES `t2` (`id`) |
724 | +) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1; |
725 | +/*!40101 SET character_set_client = @saved_cs_client */; |
726 | + |
727 | +LOCK TABLES `t2` WRITE; |
728 | +/*!40000 ALTER TABLE `t2` DISABLE KEYS */; |
729 | +INSERT INTO `t2` VALUES (1,1),(2,2),(3,3); |
730 | +ALTER TABLE `t2` ADD KEY `a` (`a`); |
731 | +/*!40000 ALTER TABLE `t2` ENABLE KEYS */; |
732 | +UNLOCK TABLES; |
733 | +/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */; |
734 | + |
735 | +/*!40101 SET SQL_MODE=@OLD_SQL_MODE */; |
736 | +/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */; |
737 | +/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */; |
738 | +/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */; |
739 | +/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */; |
740 | +/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */; |
741 | +/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */; |
742 | + |
743 | +###################################### |
744 | +DROP TABLE t1, t2; |
745 | |
746 | === added file 'mysql-test/suite/innodb_plugin/r/percona_bug_999147.result' |
747 | --- mysql-test/suite/innodb_plugin/r/percona_bug_999147.result 1970-01-01 00:00:00 +0000 |
748 | +++ mysql-test/suite/innodb_plugin/r/percona_bug_999147.result 2012-07-03 16:14:26 +0000 |
749 | @@ -0,0 +1,8 @@ |
750 | +DROP TABLE IF EXISTS t1; |
751 | +SET SESSION expand_fast_index_creation=ON; |
752 | +CREATE TEMPORARY TABLE t1 (a INT, b INT, INDEX(a)) ENGINE=InnoDB; |
753 | +SET debug="+d,crash_innodb_add_index_after"; |
754 | +ALTER TABLE t1 ADD INDEX (b); |
755 | +ERROR HY000: Lost connection to MySQL server during query |
756 | +SHOW TABLES; |
757 | +Tables_in_test |
758 | |
759 | === added file 'mysql-test/suite/innodb_plugin/t/percona_bug_999147-master.opt' |
760 | --- mysql-test/suite/innodb_plugin/t/percona_bug_999147-master.opt 1970-01-01 00:00:00 +0000 |
761 | +++ mysql-test/suite/innodb_plugin/t/percona_bug_999147-master.opt 2012-07-03 16:14:26 +0000 |
762 | @@ -0,0 +1,1 @@ |
763 | +--skip-stack-trace --skip-core-file --innodb-file-per-table=1 |
764 | |
765 | === added file 'mysql-test/suite/innodb_plugin/t/percona_bug_999147.test' |
766 | --- mysql-test/suite/innodb_plugin/t/percona_bug_999147.test 1970-01-01 00:00:00 +0000 |
767 | +++ mysql-test/suite/innodb_plugin/t/percona_bug_999147.test 2012-07-03 16:14:26 +0000 |
768 | @@ -0,0 +1,29 @@ |
769 | +# Test for Percona Server bug 999147 (A crash that leaves behind an |
770 | +# InnoDB temporary table with indexes results in an unbootable server) |
771 | +# https://bugs.launchpad.net/percona-server/+bug/999147 |
772 | + |
773 | +-- source include/not_embedded.inc |
774 | +-- source include/not_valgrind.inc |
775 | +-- source include/not_crashrep.inc |
776 | +-- source include/have_debug.inc |
777 | +-- source include/have_innodb_plugin.inc |
778 | + |
779 | +--disable_warnings |
780 | +DROP TABLE IF EXISTS t1; |
781 | +--enable_warnings |
782 | + |
783 | +SET SESSION expand_fast_index_creation=ON; |
784 | + |
785 | +CREATE TEMPORARY TABLE t1 (a INT, b INT, INDEX(a)) ENGINE=InnoDB; |
786 | + |
787 | +--exec echo "restart" > $MYSQLTEST_VARDIR/tmp/mysqld.1.expect |
788 | + |
789 | +SET debug="+d,crash_innodb_add_index_after"; |
790 | +--error 2013 |
791 | +ALTER TABLE t1 ADD INDEX (b); |
792 | + |
793 | +--enable_reconnect |
794 | + |
795 | +--source include/wait_until_connected_again.inc |
796 | + |
797 | +SHOW TABLES; |
798 | |
799 | === added file 'mysql-test/t/percona_innodb_expand_fast_index_creation.test' |
800 | --- mysql-test/t/percona_innodb_expand_fast_index_creation.test 1970-01-01 00:00:00 +0000 |
801 | +++ mysql-test/t/percona_innodb_expand_fast_index_creation.test 2012-07-03 16:14:26 +0000 |
802 | @@ -0,0 +1,46 @@ |
803 | +--source include/have_innodb_plugin.inc |
804 | + |
805 | +SELECT @@expand_fast_index_creation; |
806 | + |
807 | +######################################################################## |
808 | +# Bug #857590: Fast index creation does not update index statistics |
809 | +######################################################################## |
810 | + |
811 | +CREATE TABLE t1( |
812 | + id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, |
813 | + a CHAR(1) NOT NULL, |
814 | + b CHAR(36) NOT NULL) ENGINE=InnoDB; |
815 | + |
816 | +INSERT INTO t1(a,b) VALUES ('a','b'); |
817 | +INSERT INTO t1(a,b) SELECT a,b FROM t1; |
818 | +INSERT INTO t1(a,b) SELECT a,b FROM t1; |
819 | +INSERT INTO t1(a,b) SELECT a,b FROM t1; |
820 | +INSERT INTO t1(a,b) SELECT a,b FROM t1; |
821 | + |
822 | +# Check that fast index creation is used |
823 | +--enable_info |
824 | +ALTER TABLE t1 ADD KEY (a); |
825 | +--disable_info |
826 | + |
827 | +# The default (wrong) plan due to bogus statistics |
828 | +--vertical_results |
829 | +EXPLAIN SELECT COUNT(*) FROM t1, t1 t2 WHERE t1.a = t2.a AND t1.b = t2.b; |
830 | +--horizontal_results |
831 | + |
832 | +ALTER TABLE t1 DROP KEY a; |
833 | + |
834 | +SET expand_fast_index_creation = 1; |
835 | +SELECT @@expand_fast_index_creation; |
836 | + |
837 | +# Check that stats are updated with the option enabled |
838 | + |
839 | +--enable_info |
840 | +ALTER TABLE t1 ADD KEY (a); |
841 | +--disable_info |
842 | +--vertical_results |
843 | +EXPLAIN SELECT COUNT(*) FROM t1, t1 t2 WHERE t1.a = t2.a AND t1.b = t2.b; |
844 | +--horizontal_results |
845 | + |
846 | +SET expand_fast_index_creation = 0; |
847 | + |
848 | +DROP TABLE t1; |
849 | |
850 | === added file 'mysql-test/t/percona_mysqldump_innodb_optimize_keys.test' |
851 | --- mysql-test/t/percona_mysqldump_innodb_optimize_keys.test 1970-01-01 00:00:00 +0000 |
852 | +++ mysql-test/t/percona_mysqldump_innodb_optimize_keys.test 2012-07-03 16:14:26 +0000 |
853 | @@ -0,0 +1,187 @@ |
854 | +# Embedded server doesn't support external clients |
855 | +--source include/not_embedded.inc |
856 | + |
857 | +# Fast index creation is only available in InnoDB plugin |
858 | +--source include/have_innodb_plugin.inc |
859 | + |
860 | +# Save the initial number of concurrent sessions |
861 | +--source include/count_sessions.inc |
862 | + |
863 | +--echo # |
864 | +--echo # Test the --innodb-optimize-keys option. |
865 | +--echo # |
866 | + |
867 | +--let $file=$MYSQLTEST_VARDIR/tmp/t1.sql |
868 | + |
869 | +# First test that the option has no effect on non-InnoDB tables |
870 | + |
871 | +CREATE TABLE t1 (a INT NOT NULL PRIMARY KEY, b INT, KEY(b)) ENGINE=MyISAM; |
872 | + |
873 | +--exec $MYSQL_DUMP --skip-comments --innodb-optimize-keys test t1 >$file |
874 | + |
875 | +--echo ###################################### |
876 | +--cat_file $file |
877 | +--echo ###################################### |
878 | + |
879 | +--remove_file $file |
880 | + |
881 | +DROP TABLE t1; |
882 | + |
883 | +# Check that for InnoDB tables secondary keys are created after the data is |
884 | +# dumped but foreign ones are left in CREATE TABLE |
885 | + |
886 | +CREATE TABLE t2 (a INT PRIMARY KEY) ENGINE=InnoDB; |
887 | +INSERT INTO t2 VALUES (0), (1), (2); |
888 | + |
889 | +CREATE TABLE t1 ( |
890 | + id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, |
891 | + a INT, b VARCHAR(255), c DECIMAL(10,3), |
892 | + KEY (b), |
893 | + UNIQUE KEY uniq(c,a), |
894 | + FOREIGN KEY (a) REFERENCES t2(a) ON DELETE CASCADE |
895 | +) ENGINE=InnoDB; |
896 | + |
897 | +INSERT INTO t1(a,b,c) VALUES (0, "0", 0.0), (1, "1", 1.1), (2, "2", 2.2); |
898 | + |
899 | +--exec $MYSQL_DUMP --skip-comments --innodb-optimize-keys test t1 t2 >$file |
900 | + |
901 | +--echo ###################################### |
902 | +--cat_file $file |
903 | +--echo ###################################### |
904 | + |
905 | +# Check that the resulting dump can be imported back |
906 | + |
907 | +--exec $MYSQL test < $file |
908 | + |
909 | +--remove_file $file |
910 | + |
911 | +DROP TABLE t1, t2; |
912 | + |
913 | +######################################################################## |
914 | +# Bug #812179: AUTO_INCREMENT columns must be skipped by the |
915 | +# --innodb-optimize-keys optimization in mysqldump |
916 | +######################################################################## |
917 | + |
918 | +CREATE TABLE t1 ( |
919 | + id INT NOT NULL AUTO_INCREMENT, |
920 | + KEY (id) |
921 | +) ENGINE=InnoDB; |
922 | + |
923 | +CREATE TABLE t2 ( |
924 | + id INT NOT NULL AUTO_INCREMENT, |
925 | + UNIQUE KEY (id) |
926 | +) ENGINE=InnoDB; |
927 | + |
928 | +INSERT INTO t1 VALUES (), (), (); |
929 | +INSERT INTO t2 VALUES (), (), (); |
930 | + |
931 | +--exec $MYSQL_DUMP --skip-comments --innodb-optimize-keys test t1 t2 >$file |
932 | + |
933 | +--echo ###################################### |
934 | +--cat_file $file |
935 | +--echo ###################################### |
936 | + |
937 | +# Check that the resulting dump can be imported back |
938 | + |
939 | +--exec $MYSQL test < $file |
940 | + |
941 | +--remove_file $file |
942 | + |
943 | +DROP TABLE t1, t2; |
944 | + |
945 | +######################################################################## |
946 | +# Bug #851674: --innodb-optimize-keys does not work correctly with table |
947 | +# without PRIMARY KEY |
948 | +######################################################################## |
949 | + |
950 | +CREATE TABLE t1 ( |
951 | + a INT NOT NULL, |
952 | + UNIQUE KEY (a)) ENGINE=InnoDB; |
953 | + |
954 | +CREATE TABLE t2 ( |
955 | + a INT NOT NULL, |
956 | + b INT NOT NULL, |
957 | + UNIQUE KEY (a,b)) ENGINE=InnoDB; |
958 | + |
959 | +CREATE TABLE t3 ( |
960 | + a INT, |
961 | + b INT, |
962 | + UNIQUE KEY (a,b)) ENGINE=InnoDB; |
963 | + |
964 | +CREATE TABLE t4 ( |
965 | + a INT NOT NULL, |
966 | + b INT NOT NULL, |
967 | + PRIMARY KEY (a,b), |
968 | + UNIQUE KEY(b)) ENGINE=InnoDB; |
969 | + |
970 | +SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE |
971 | + TABLE_SCHEMA=DATABASE() AND |
972 | + TABLE_NAME='t1' AND |
973 | + COLUMN_KEY='PRI'; |
974 | +SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE |
975 | + TABLE_SCHEMA=DATABASE() AND |
976 | + TABLE_NAME='t2' AND |
977 | + COLUMN_KEY='PRI'; |
978 | +SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE |
979 | + TABLE_SCHEMA=DATABASE() AND |
980 | + TABLE_NAME='t3' AND |
981 | + COLUMN_KEY='PRI'; |
982 | +SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE |
983 | + TABLE_SCHEMA=DATABASE() AND |
984 | + TABLE_NAME='t4' AND |
985 | + COLUMN_KEY='PRI'; |
986 | + |
987 | +INSERT INTO t1 VALUES (1), (2), (3); |
988 | +INSERT INTO t2 VALUES (1,1), (2,2), (3,3); |
989 | +INSERT INTO t3 SELECT * FROM t2; |
990 | +INSERT INTO t4 SELECT * FROM t2; |
991 | + |
992 | +--exec $MYSQL_DUMP --skip-comments --innodb-optimize-keys test t1 t2 t3 t4 >$file |
993 | + |
994 | +--echo ###################################### |
995 | +--cat_file $file |
996 | +--echo ###################################### |
997 | + |
998 | +# Check that the resulting dump can be imported back |
999 | + |
1000 | +--exec $MYSQL test < $file |
1001 | + |
1002 | +--remove_file $file |
1003 | + |
1004 | +DROP TABLE t1, t2, t3, t4; |
1005 | + |
1006 | +######################################################################## |
1007 | +# Bug #859078: --innodb-optimize-keys should ignore foreign keys |
1008 | +######################################################################## |
1009 | + |
1010 | +CREATE TABLE t1 ( |
1011 | + id INT NOT NULL PRIMARY KEY |
1012 | +) ENGINE=InnoDB; |
1013 | + |
1014 | +CREATE TABLE t2 ( |
1015 | + id INT NOT NULL AUTO_INCREMENT, |
1016 | + a INT NOT NULL, |
1017 | + PRIMARY KEY (id), |
1018 | + KEY (a), |
1019 | + FOREIGN KEY (a) REFERENCES t2 (id) |
1020 | +) ENGINE=InnoDB; |
1021 | + |
1022 | +INSERT INTO t1 VALUES (1), (2), (3); |
1023 | +INSERT INTO t2 VALUES (1, 1), (2, 2), (3, 3); |
1024 | + |
1025 | +--exec $MYSQL_DUMP --skip-comments --innodb-optimize-keys test t1 t2 >$file |
1026 | + |
1027 | +--echo ###################################### |
1028 | +--cat_file $file |
1029 | +--echo ###################################### |
1030 | + |
1031 | +# Check that the resulting dump can be imported back |
1032 | + |
1033 | +--exec $MYSQL test < $file |
1034 | + |
1035 | +--remove_file $file |
1036 | + |
1037 | +DROP TABLE t1, t2; |
1038 | + |
1039 | +# Wait till we reached the initial number of concurrent sessions |
1040 | +--source include/wait_until_count_sessions.inc |
1041 | |
1042 | === modified file 'sql/mysqld.cc' |
1043 | --- sql/mysqld.cc 2012-03-08 15:16:53 +0000 |
1044 | +++ sql/mysqld.cc 2012-07-03 16:14:26 +0000 |
1045 | @@ -5667,7 +5667,8 @@ |
1046 | OPT_IGNORE_BUILTIN_INNODB, |
1047 | OPT_BINLOG_DIRECT_NON_TRANS_UPDATE, |
1048 | OPT_DEFAULT_CHARACTER_SET_OLD, |
1049 | - OPT_MAX_LONG_DATA_SIZE |
1050 | + OPT_MAX_LONG_DATA_SIZE, |
1051 | + OPT_EXPAND_FAST_INDEX_CREATION |
1052 | }; |
1053 | |
1054 | |
1055 | @@ -5896,6 +5897,13 @@ |
1056 | each time the SQL thread starts.", |
1057 | &opt_init_slave, &opt_init_slave, 0, GET_STR_ALLOC, |
1058 | REQUIRED_ARG, 0, 0, 0, 0, 0, 0}, |
1059 | + {"expand-fast-index-creation", OPT_EXPAND_FAST_INDEX_CREATION, |
1060 | + "Enable/disable improvements to the InnoDB fast index creation functionality. " |
1061 | + "Has no effect when fast index creation is disabled with the " |
1062 | + "fast-index-creation option", |
1063 | + &global_system_variables.expand_fast_index_creation, |
1064 | + &max_system_variables.expand_fast_index_creation, |
1065 | + 0, GET_BOOL, NO_ARG, 0, 0, 0, 0, 0, NULL}, |
1066 | {"language", 'L', |
1067 | "Client error messages in given language. May be given as a full path.", |
1068 | &language_ptr, &language_ptr, 0, GET_STR, REQUIRED_ARG, |
1069 | |
1070 | === modified file 'sql/set_var.cc' |
1071 | --- sql/set_var.cc 2011-07-03 15:47:37 +0000 |
1072 | +++ sql/set_var.cc 2012-07-03 16:14:26 +0000 |
1073 | @@ -921,6 +921,10 @@ |
1074 | SHOW_LONGLONG, |
1075 | get_myisam_mmap_size); |
1076 | |
1077 | +static sys_var_thd_bool |
1078 | +sys_expand_fast_index_creation(&vars, |
1079 | + "expand_fast_index_creation", &SV::expand_fast_index_creation); |
1080 | + |
1081 | |
1082 | bool sys_var::check(THD *thd, set_var *var) |
1083 | { |
1084 | |
1085 | === modified file 'sql/sql_class.h' |
1086 | --- sql/sql_class.h 2012-04-20 16:41:20 +0000 |
1087 | +++ sql/sql_class.h 2012-07-03 16:14:26 +0000 |
1088 | @@ -404,6 +404,8 @@ |
1089 | DATE_TIME_FORMAT *datetime_format; |
1090 | DATE_TIME_FORMAT *time_format; |
1091 | my_bool sysdate_is_now; |
1092 | + |
1093 | + my_bool expand_fast_index_creation; |
1094 | }; |
1095 | |
1096 | |
1097 | |
1098 | === modified file 'sql/sql_lex.cc' |
1099 | --- sql/sql_lex.cc 2012-04-18 11:14:05 +0000 |
1100 | +++ sql/sql_lex.cc 2012-07-03 16:14:26 +0000 |
1101 | @@ -1498,6 +1498,9 @@ |
1102 | alter_list(rhs.alter_list, mem_root), |
1103 | key_list(rhs.key_list, mem_root), |
1104 | create_list(rhs.create_list, mem_root), |
1105 | + delayed_key_list(rhs.delayed_key_list, mem_root), |
1106 | + delayed_key_info(rhs.delayed_key_info), |
1107 | + delayed_key_count(rhs.delayed_key_count), |
1108 | flags(rhs.flags), |
1109 | keys_onoff(rhs.keys_onoff), |
1110 | tablespace_op(rhs.tablespace_op), |
1111 | @@ -1520,6 +1523,7 @@ |
1112 | list_copy_and_replace_each_value(alter_list, mem_root); |
1113 | list_copy_and_replace_each_value(key_list, mem_root); |
1114 | list_copy_and_replace_each_value(create_list, mem_root); |
1115 | + list_copy_and_replace_each_value(delayed_key_list, mem_root); |
1116 | /* partition_names are not deeply copied currently */ |
1117 | } |
1118 | |
1119 | |
1120 | === modified file 'sql/sql_lex.h' |
1121 | --- sql/sql_lex.h 2012-04-18 11:14:05 +0000 |
1122 | +++ sql/sql_lex.h 2012-07-03 16:14:26 +0000 |
1123 | @@ -911,6 +911,9 @@ |
1124 | List<Alter_column> alter_list; |
1125 | List<Key> key_list; |
1126 | List<Create_field> create_list; |
1127 | + List<Key> delayed_key_list; |
1128 | + KEY *delayed_key_info; |
1129 | + uint delayed_key_count; |
1130 | uint flags; |
1131 | enum enum_enable_or_disable keys_onoff; |
1132 | enum tablespace_op_type tablespace_op; |
1133 | @@ -922,6 +925,8 @@ |
1134 | |
1135 | |
1136 | Alter_info() : |
1137 | + delayed_key_info(NULL), |
1138 | + delayed_key_count(0), |
1139 | flags(0), |
1140 | keys_onoff(LEAVE_AS_IS), |
1141 | tablespace_op(NO_TABLESPACE_OP), |
1142 | @@ -937,6 +942,9 @@ |
1143 | alter_list.empty(); |
1144 | key_list.empty(); |
1145 | create_list.empty(); |
1146 | + delayed_key_list.empty(); |
1147 | + delayed_key_info= NULL; |
1148 | + delayed_key_count= 0; |
1149 | flags= 0; |
1150 | keys_onoff= LEAVE_AS_IS; |
1151 | tablespace_op= NO_TABLESPACE_OP; |
1152 | |
1153 | === modified file 'sql/sql_table.cc' |
1154 | --- sql/sql_table.cc 2012-02-15 16:21:38 +0000 |
1155 | +++ sql/sql_table.cc 2012-07-03 16:14:26 +0000 |
1156 | @@ -3016,6 +3016,14 @@ |
1157 | if (!*key_info_buffer || ! key_part_info) |
1158 | DBUG_RETURN(TRUE); // Out of memory |
1159 | |
1160 | + List_iterator<Key> delayed_key_iterator(alter_info->delayed_key_list); |
1161 | + alter_info->delayed_key_count= 0; |
1162 | + if (alter_info->delayed_key_list.elements > 0) |
1163 | + { |
1164 | + alter_info->delayed_key_info= (KEY *) sql_calloc(sizeof(KEY) * |
1165 | + (*key_count)); |
1166 | + } |
1167 | + |
1168 | key_iterator.rewind(); |
1169 | key_number=0; |
1170 | for (; (key=key_iterator++) ; key_number++) |
1171 | @@ -3394,6 +3402,23 @@ |
1172 | my_error(ER_TOO_LONG_KEY,MYF(0),max_key_length); |
1173 | DBUG_RETURN(TRUE); |
1174 | } |
1175 | + |
1176 | + if (alter_info->delayed_key_list.elements > 0) |
1177 | + { |
1178 | + Key *delayed_key; |
1179 | + |
1180 | + delayed_key_iterator.rewind(); |
1181 | + while ((delayed_key= delayed_key_iterator++)) |
1182 | + { |
1183 | + if (delayed_key == key) |
1184 | + { |
1185 | + alter_info->delayed_key_info[alter_info->delayed_key_count++]= |
1186 | + *key_info; |
1187 | + break; |
1188 | + } |
1189 | + } |
1190 | + } |
1191 | + |
1192 | key_info++; |
1193 | } |
1194 | if (!unique_key && !primary_key && |
1195 | @@ -6099,6 +6124,10 @@ |
1196 | List<Create_field> new_create_list; |
1197 | /* New key definitions are added here */ |
1198 | List<Key> new_key_list; |
1199 | + /* List with secondary keys which should be created after copying the data */ |
1200 | + List<Key> delayed_key_list; |
1201 | + /* Foreign key list returned by handler::get_foreign_key_list() */ |
1202 | + List<FOREIGN_KEY_INFO> f_key_list; |
1203 | List_iterator<Alter_drop> drop_it(alter_info->drop_list); |
1204 | List_iterator<Create_field> def_it(alter_info->create_list); |
1205 | List_iterator<Alter_column> alter_it(alter_info->alter_list); |
1206 | @@ -6111,6 +6140,7 @@ |
1207 | uint used_fields= create_info->used_fields; |
1208 | KEY *key_info=table->key_info; |
1209 | bool rc= TRUE; |
1210 | + bool skip_secondary; |
1211 | |
1212 | DBUG_ENTER("mysql_prepare_alter_table"); |
1213 | |
1214 | @@ -6305,8 +6335,26 @@ |
1215 | /* |
1216 | Collect all keys which isn't in drop list. Add only those |
1217 | for which some fields exists. |
1218 | + |
1219 | + We also store secondary keys in delayed_key_list to make use of |
1220 | + the InnoDB fast index creation. The following conditions must be |
1221 | + met: |
1222 | + |
1223 | + - fast_index_creation is enabled for the current session |
1224 | + - expand_fast_index_creation is enabled for the current session; |
1225 | + - we are going to create an InnoDB table (this is checked later when the |
1226 | + target engine is known); |
1227 | + - the key most be a non-UNIQUE one; |
1228 | + - there are no foreign keys. This can be optimized later to exclude only |
1229 | + those keys which are a part of foreign key constraints. Currently we |
1230 | + simply disable this optimization for all keys if there are any foreign |
1231 | + key constraints in the table. |
1232 | */ |
1233 | |
1234 | + skip_secondary= thd->variables.expand_fast_index_creation && |
1235 | + !table->file->get_foreign_key_list(thd, &f_key_list) && |
1236 | + f_key_list.elements == 0; |
1237 | + |
1238 | for (uint i=0 ; i < table->s->keys ; i++,key_info++) |
1239 | { |
1240 | char *key_name= key_info->name; |
1241 | @@ -6408,6 +6456,8 @@ |
1242 | test(key_info->flags & HA_GENERATED_KEY), |
1243 | key_parts); |
1244 | new_key_list.push_back(key); |
1245 | + if (skip_secondary && key_type == Key::MULTIPLE) |
1246 | + delayed_key_list.push_back(key); |
1247 | } |
1248 | } |
1249 | { |
1250 | @@ -6415,7 +6465,21 @@ |
1251 | while ((key=key_it++)) // Add new keys |
1252 | { |
1253 | if (key->type != Key::FOREIGN_KEY) |
1254 | + { |
1255 | new_key_list.push_back(key); |
1256 | + if (skip_secondary && key->type == Key::MULTIPLE) |
1257 | + delayed_key_list.push_back(key); |
1258 | + } |
1259 | + else if (skip_secondary) |
1260 | + { |
1261 | + /* |
1262 | + We are adding a foreign key so disable the secondary keys |
1263 | + optimization. |
1264 | + */ |
1265 | + skip_secondary= FALSE; |
1266 | + delayed_key_list.empty(); |
1267 | + } |
1268 | + |
1269 | if (key->name && |
1270 | !my_strcasecmp(system_charset_info,key->name,primary_key_name)) |
1271 | { |
1272 | @@ -6464,12 +6528,100 @@ |
1273 | rc= FALSE; |
1274 | alter_info->create_list.swap(new_create_list); |
1275 | alter_info->key_list.swap(new_key_list); |
1276 | + alter_info->delayed_key_list.swap(delayed_key_list); |
1277 | err: |
1278 | DBUG_RETURN(rc); |
1279 | } |
1280 | |
1281 | |
1282 | /* |
1283 | + Temporarily remove secondary keys previously stored in |
1284 | + alter_info->delayed_key_info. |
1285 | +*/ |
1286 | +static int |
1287 | +remove_secondary_keys(THD *thd, TABLE *table, Alter_info *alter_info) |
1288 | +{ |
1289 | + uint *key_numbers; |
1290 | + uint key_counter= 0; |
1291 | + uint i; |
1292 | + int error; |
1293 | + DBUG_ENTER("remove_secondary_keys"); |
1294 | + DBUG_ASSERT(alter_info->delayed_key_count > 0); |
1295 | + |
1296 | + key_numbers= (uint *) thd->alloc(sizeof(uint) * |
1297 | + alter_info->delayed_key_count); |
1298 | + for (i= 0; i < alter_info->delayed_key_count; i++) |
1299 | + { |
1300 | + KEY *key= alter_info->delayed_key_info + i; |
1301 | + uint j; |
1302 | + |
1303 | + for (j= 0; j < table->s->keys; j++) |
1304 | + { |
1305 | + if (!strcmp(table->key_info[j].name, key->name)) |
1306 | + { |
1307 | + key_numbers[key_counter++]= j; |
1308 | + break; |
1309 | + } |
1310 | + } |
1311 | + } |
1312 | + |
1313 | + DBUG_ASSERT(key_counter == alter_info->delayed_key_count); |
1314 | + |
1315 | + if ((error= table->file->prepare_drop_index(table, key_numbers, |
1316 | + key_counter)) || |
1317 | + (error= table->file->final_drop_index(table))) |
1318 | + { |
1319 | + table->file->print_error(error, MYF(0)); |
1320 | + } |
1321 | + |
1322 | + DBUG_RETURN(error); |
1323 | +} |
1324 | + |
1325 | +/* |
1326 | + Restore secondary keys previously removed in remove_secondary_keys. |
1327 | +*/ |
1328 | + |
1329 | +static int |
1330 | +restore_secondary_keys(THD *thd, TABLE *table, Alter_info *alter_info) |
1331 | +{ |
1332 | + uint i; |
1333 | + int error; |
1334 | + DBUG_ENTER("restore_secondary_keys"); |
1335 | + DBUG_ASSERT(alter_info->delayed_key_count > 0); |
1336 | + |
1337 | + thd_proc_info(thd, "restoring secondary keys"); |
1338 | + |
1339 | + /* Fix the key parts */ |
1340 | + for (i= 0; i < alter_info->delayed_key_count; i++) |
1341 | + { |
1342 | + KEY *key = alter_info->delayed_key_info + i; |
1343 | + KEY_PART_INFO *key_part; |
1344 | + KEY_PART_INFO *part_end; |
1345 | + |
1346 | + part_end= key->key_part + key->key_parts; |
1347 | + for (key_part= key->key_part; key_part < part_end; key_part++) |
1348 | + key_part->field= table->field[key_part->fieldnr]; |
1349 | + } |
1350 | + |
1351 | + if ((error= table->file->add_index(table, alter_info->delayed_key_info, |
1352 | + alter_info->delayed_key_count))) |
1353 | + { |
1354 | + /* |
1355 | + Exchange the key_info for the error message. If we exchange |
1356 | + key number by key name in the message later, we need correct info. |
1357 | + */ |
1358 | + KEY *save_key_info= table->key_info; |
1359 | + table->key_info= alter_info->delayed_key_info; |
1360 | + table->file->print_error(error, MYF(0)); |
1361 | + table->key_info= save_key_info; |
1362 | + |
1363 | + DBUG_RETURN(error); |
1364 | + } |
1365 | + |
1366 | + DBUG_RETURN(0); |
1367 | +} |
1368 | + |
1369 | +/* |
1370 | Alter table |
1371 | |
1372 | SYNOPSIS |
1373 | @@ -7305,15 +7457,34 @@ |
1374 | */ |
1375 | if (new_table && !(new_table->file->ha_table_flags() & HA_NO_COPY_ON_ALTER)) |
1376 | { |
1377 | + /* |
1378 | + Check if we can temporarily remove secondary indexes from the table |
1379 | + before copying the data and recreate them later to utilize InnoDB fast |
1380 | + index creation. |
1381 | + TODO: is there a better way to check for InnoDB? |
1382 | + */ |
1383 | + bool optimize_keys= (alter_info->delayed_key_count > 0) && |
1384 | + !my_strcasecmp(system_charset_info, |
1385 | + new_table->file->table_type(), "InnoDB"); |
1386 | /* We don't want update TIMESTAMP fields during ALTER TABLE. */ |
1387 | new_table->timestamp_field_type= TIMESTAMP_NO_AUTO_SET; |
1388 | new_table->next_number_field=new_table->found_next_number_field; |
1389 | + |
1390 | thd_proc_info(thd, "copy to tmp table"); |
1391 | + |
1392 | + if (optimize_keys) |
1393 | + { |
1394 | + /* ignore the error */ |
1395 | + error= remove_secondary_keys(thd, new_table, alter_info); |
1396 | + } |
1397 | + |
1398 | error= copy_data_between_tables(table, new_table, |
1399 | alter_info->create_list, ignore, |
1400 | order_num, order, &copied, &deleted, |
1401 | alter_info->keys_onoff, |
1402 | alter_info->error_if_not_empty); |
1403 | + if (!error && optimize_keys) |
1404 | + error= restore_secondary_keys(thd, new_table, alter_info); |
1405 | } |
1406 | else |
1407 | { |
1408 | |
1409 | === modified file 'storage/innodb_plugin/handler/ha_innodb.cc' |
1410 | --- storage/innodb_plugin/handler/ha_innodb.cc 2012-05-08 05:19:14 +0000 |
1411 | +++ storage/innodb_plugin/handler/ha_innodb.cc 2012-07-03 16:14:26 +0000 |
1412 | @@ -43,6 +43,8 @@ |
1413 | #pragma implementation // gcc: Class implementation |
1414 | #endif |
1415 | |
1416 | +#define MYSQL_SERVER |
1417 | + |
1418 | #include <mysql_priv.h> |
1419 | |
1420 | #include <m_ctype.h> |
1421 | @@ -682,6 +684,19 @@ |
1422 | } |
1423 | |
1424 | /******************************************************************//** |
1425 | +Returns true if expand_fast_index_creation is enabled for the current |
1426 | +session. |
1427 | +@return the value of the server's expand_fast_index_creation variable */ |
1428 | +extern "C" |
1429 | +ibool |
1430 | +thd_expand_fast_index_creation( |
1431 | +/*================================*/ |
1432 | + void* thd) |
1433 | +{ |
1434 | + return((ibool) (((THD*) thd)->variables.expand_fast_index_creation)); |
1435 | +} |
1436 | + |
1437 | +/******************************************************************//** |
1438 | Returns true if the thread supports XA, |
1439 | global value of innodb_supports_xa if thd is NULL. |
1440 | @return true if thd has XA support */ |
1441 | |
1442 | === modified file 'storage/innodb_plugin/handler/handler0alter.cc' |
1443 | --- storage/innodb_plugin/handler/handler0alter.cc 2012-02-28 12:00:00 +0000 |
1444 | +++ storage/innodb_plugin/handler/handler0alter.cc 2012-07-03 16:14:26 +0000 |
1445 | @@ -813,6 +813,8 @@ |
1446 | innodb_table, indexed_table, |
1447 | index, num_of_idx, table); |
1448 | |
1449 | + DBUG_EXECUTE_IF("crash_innodb_add_index_after", DBUG_SUICIDE();); |
1450 | + |
1451 | error_handling: |
1452 | /* After an error, remove all those index definitions from the |
1453 | dictionary which were defined. */ |
1454 | |
1455 | === modified file 'storage/innodb_plugin/include/ha_prototypes.h' |
1456 | --- storage/innodb_plugin/include/ha_prototypes.h 2010-05-14 13:08:15 +0000 |
1457 | +++ storage/innodb_plugin/include/ha_prototypes.h 2012-07-03 16:14:26 +0000 |
1458 | @@ -268,4 +268,15 @@ |
1459 | void* thd); /*!< in: thread handle (THD*), or NULL to query |
1460 | the global innodb_lock_wait_timeout */ |
1461 | |
1462 | +/******************************************************************//** |
1463 | +Returns true if innodb_expand_fast_index_creation is enabled for the current |
1464 | +session. |
1465 | +@return the value of the server's innodb_expand_fast_index_creation variable */ |
1466 | + |
1467 | +ibool |
1468 | +thd_expand_fast_index_creation( |
1469 | +/*==================*/ |
1470 | + void* thd); /*!< in: thread handle (THD*) */ |
1471 | + |
1472 | + |
1473 | #endif |
1474 | |
1475 | === modified file 'storage/innodb_plugin/row/row0merge.c' |
1476 | --- storage/innodb_plugin/row/row0merge.c 2012-02-10 03:09:12 +0000 |
1477 | +++ storage/innodb_plugin/row/row0merge.c 2012-07-03 16:14:26 +0000 |
1478 | @@ -56,6 +56,7 @@ |
1479 | #include "log0log.h" |
1480 | #include "ut0sort.h" |
1481 | #include "handler0alter.h" |
1482 | +#include "ha_prototypes.h" |
1483 | |
1484 | #ifdef UNIV_DEBUG |
1485 | /** Set these in order ot enable debug printout. */ |
1486 | @@ -2640,6 +2641,9 @@ |
1487 | } |
1488 | } |
1489 | |
1490 | + if (trx->mysql_thd && thd_expand_fast_index_creation(trx->mysql_thd)) |
1491 | + dict_update_statistics(new_table, FALSE); |
1492 | + |
1493 | func_exit: |
1494 | close(tmpfd); |
1495 | |
1496 | |
1497 | === modified file 'storage/innodb_plugin/row/row0mysql.c' |
1498 | --- storage/innodb_plugin/row/row0mysql.c 2012-03-08 15:16:53 +0000 |
1499 | +++ storage/innodb_plugin/row/row0mysql.c 2012-07-03 16:14:26 +0000 |
1500 | @@ -3512,7 +3512,7 @@ |
1501 | btr_pcur_store_position(&pcur, &mtr); |
1502 | btr_pcur_commit_specify_mtr(&pcur, &mtr); |
1503 | |
1504 | - table = dict_load_table(table_name); |
1505 | + table = dict_table_get_low(table_name); |
1506 | |
1507 | if (table) { |
1508 | row_drop_table_for_mysql(table_name, trx, FALSE); |