Merge lp:~sergei.glushchenko/mysql-server/56-expand_fast_index_creation into lp:mysql-server/5.6
- 56-expand_fast_index_creation
- Merge into 5.6
Status: | Needs review |
---|---|
Proposed branch: | lp:~sergei.glushchenko/mysql-server/56-expand_fast_index_creation |
Merge into: | lp:mysql-server/5.6 |
Diff against target: |
1860 lines (+1471/-2) (has conflicts) 22 files modified
client/client_priv.h (+1/-0) client/mysqldump.c (+325/-0) mysql-test/r/mysqld--help-notwin.result (+5/-0) mysql-test/r/mysqld--help-win.result (+5/-0) mysql-test/r/percona_expand_fast_index_creation.result (+76/-0) mysql-test/r/percona_mysqldump_innodb_optimize_keys.result (+443/-0) mysql-test/suite/innodb/r/percona_bug_999147.result (+17/-0) mysql-test/suite/innodb/t/percona_bug_999147-master.opt (+1/-0) mysql-test/suite/innodb/t/percona_bug_999147.test (+29/-0) mysql-test/suite/sys_vars/r/expand_fast_index_creation_basic.result (+6/-0) mysql-test/suite/sys_vars/t/expand_fast_index_creation_basic.test (+2/-0) mysql-test/t/percona_expand_fast_index_creation.test (+70/-0) mysql-test/t/percona_mysqldump_innodb_optimize_keys.test (+227/-0) sql/handler.cc (+4/-2) sql/sql_alter.cc (+4/-0) sql/sql_alter.h (+6/-0) sql/sql_class.h (+1/-0) sql/sql_table.cc (+215/-0) sql/sys_vars.cc (+8/-0) storage/innobase/handler/ha_innodb.cc (+14/-0) storage/innobase/handler/handler0alter.cc (+2/-0) storage/innobase/include/ha_prototypes.h (+10/-0) Text conflict in client/mysqldump.c |
To merge this branch: | bzr merge lp:~sergei.glushchenko/mysql-server/56-expand_fast_index_creation |
Related bugs: |
Reviewer | Review Type | Date Requested | Status |
---|---|---|---|
Alexey Kopytov | Pending | ||
Review via email: mp+132554@code.launchpad.net |
Commit message
Description of the change
- 3924. By Sergei Glushchenko
-
Testcase added
- 3925. By Sergei Glushchenko
-
fix 'C' linkage
- 3926. By Sergei Glushchenko
-
fix for bugs #1039536, #1081003 been merged
- 3927. By Sergei Glushchenko
-
Test case improved.
Drop and create keys on temporary table is performed as following.
Create Alter_info which describe table changes. As fields of the
table not changed, populate clreate_list with field definitions
using field==orig_field. Create Alter_inplace_info describing
alter operations to be performed. Fill index_drop_buffer or
index_add_buffer respectively. Set ADD_INDEX/DROP_INDEX handler flag.
Check that operation could be performed online by calling
check_if_supported_ inplace_ alter. Perform alter operation by calling
ha_prepare_inplace_ alter_table, ha_inplace_ alter_table,
ha_commit_inplace_ alter_table. - 3928. By Sergei Glushchenko
-
error is not used variable
- 3929. By Sergei Glushchenko
-
Added comment for change in ha_commit_
inplace_ alter_table - 3930. By Sergei Glushchenko
-
Coding guidelines and proper error handling.
- 3931. By Sergei Glushchenko
-
Stronger assertion. Check that mdl_ticket == 0 only for temporary table
Unmerged revisions
- 3931. By Sergei Glushchenko
-
Stronger assertion. Check that mdl_ticket == 0 only for temporary table
- 3930. By Sergei Glushchenko
-
Coding guidelines and proper error handling.
- 3929. By Sergei Glushchenko
-
Added comment for change in ha_commit_
inplace_ alter_table - 3928. By Sergei Glushchenko
-
error is not used variable
- 3927. By Sergei Glushchenko
-
Test case improved.
Drop and create keys on temporary table is performed as following.
Create Alter_info which describe table changes. As fields of the
table not changed, populate clreate_list with field definitions
using field==orig_field. Create Alter_inplace_info describing
alter operations to be performed. Fill index_drop_buffer or
index_add_buffer respectively. Set ADD_INDEX/DROP_INDEX handler flag.
Check that operation could be performed online by calling
check_if_supported_ inplace_ alter. Perform alter operation by calling
ha_prepare_inplace_ alter_table, ha_inplace_ alter_table,
ha_commit_inplace_ alter_table. - 3926. By Sergei Glushchenko
-
fix for bugs #1039536, #1081003 been merged
- 3925. By Sergei Glushchenko
-
fix 'C' linkage
- 3924. By Sergei Glushchenko
-
Testcase added
- 3923. By Sergei Glushchenko
-
some more cleanup
- 3922. By Sergei Glushchenko
-
Workaround for locking
Preview Diff
1 | === modified file 'client/client_priv.h' |
2 | --- client/client_priv.h 2012-11-07 15:22:57 +0000 |
3 | +++ client/client_priv.h 2012-12-14 14:59:00 +0000 |
4 | @@ -99,6 +99,7 @@ |
5 | OPT_CONFIG_ALL, |
6 | OPT_SERVER_PUBLIC_KEY, |
7 | OPT_ENABLE_CLEARTEXT_PLUGIN, |
8 | + OPT_INNODB_OPTIMIZE_KEYS, |
9 | OPT_MAX_CLIENT_OPTION |
10 | }; |
11 | |
12 | |
13 | === modified file 'client/mysqldump.c' |
14 | --- client/mysqldump.c 2012-11-09 11:23:45 +0000 |
15 | +++ client/mysqldump.c 2012-12-14 14:59:00 +0000 |
16 | @@ -47,6 +47,7 @@ |
17 | #include <m_ctype.h> |
18 | #include <hash.h> |
19 | #include <stdarg.h> |
20 | +#include <my_list.h> |
21 | |
22 | #include "client_priv.h" |
23 | #include "my_default.h" |
24 | @@ -84,6 +85,13 @@ |
25 | #define IGNORE_DATA 0x01 /* don't dump data for this table */ |
26 | #define IGNORE_INSERT_DELAYED 0x02 /* table doesn't support INSERT DELAYED */ |
27 | |
28 | +typedef enum { |
29 | + KEY_TYPE_NONE, |
30 | + KEY_TYPE_PRIMARY, |
31 | + KEY_TYPE_UNIQUE, |
32 | + KEY_TYPE_NON_UNIQUE |
33 | +} key_type_t; |
34 | + |
35 | /* general_log or slow_log tables under mysql database */ |
36 | static inline my_bool general_log_or_slow_log_tables(const char *db, |
37 | const char *table) |
38 | @@ -166,6 +174,8 @@ |
39 | static uint opt_protocol= 0; |
40 | static char *opt_plugin_dir= 0, *opt_default_auth= 0; |
41 | |
42 | +static my_bool opt_innodb_optimize_keys= FALSE; |
43 | + |
44 | /* |
45 | Dynamic_string wrapper functions. In this file use these |
46 | wrappers, they will terminate the process if there is |
47 | @@ -211,6 +221,8 @@ |
48 | |
49 | HASH ignore_table; |
50 | |
51 | +LIST *skipped_keys_list; |
52 | + |
53 | static struct my_option my_long_options[] = |
54 | { |
55 | {"all-databases", 'A', |
56 | @@ -380,6 +392,11 @@ |
57 | "in dump produced with --dump-slave.", &opt_include_master_host_port, |
58 | &opt_include_master_host_port, 0, GET_BOOL, NO_ARG, |
59 | 0, 0, 0, 0, 0, 0}, |
60 | + {"innodb-optimize-keys", OPT_INNODB_OPTIMIZE_KEYS, |
61 | + "Use InnoDB fast index creation by creating secondary indexes after " |
62 | + "dumping the data.", |
63 | + &opt_innodb_optimize_keys, &opt_innodb_optimize_keys, 0, GET_BOOL, NO_ARG, |
64 | + 0, 0, 0, 0, 0, 0}, |
65 | {"insert-ignore", OPT_INSERT_IGNORE, "Insert rows with INSERT IGNORE.", |
66 | &opt_ignore, &opt_ignore, 0, GET_BOOL, NO_ARG, 0, 0, 0, 0, |
67 | 0, 0}, |
68 | @@ -2471,6 +2488,256 @@ |
69 | } |
70 | |
71 | /* |
72 | + Parse the specified key definition string and check if the key contains an |
73 | + AUTO_INCREMENT column as the first key part. We only check for the first key |
74 | + part, because unlike MyISAM, InnoDB does not allow the AUTO_INCREMENT column |
75 | + as a secondary key column, i.e. the AUTO_INCREMENT column would not be |
76 | + considered indexed for such key specification. |
77 | +*/ |
78 | +static my_bool contains_autoinc_column(const char *autoinc_column, |
79 | + const char *keydef, |
80 | + key_type_t type) |
81 | +{ |
82 | + char *from, *to; |
83 | + uint idnum; |
84 | + |
85 | + DBUG_ASSERT(type != KEY_TYPE_NONE); |
86 | + |
87 | + if (autoinc_column == NULL || !(from= strchr(keydef, '`'))) |
88 | + return FALSE; |
89 | + |
90 | + to= from; |
91 | + idnum= 0; |
92 | + |
93 | + while ((to= strchr(to + 1, '`'))) |
94 | + { |
95 | + /* |
96 | + Double backticks represent a backtick in identifier, rather than a quote |
97 | + character. |
98 | + */ |
99 | + if (to[1] == '`') |
100 | + { |
101 | + to++; |
102 | + continue; |
103 | + } |
104 | + |
105 | + if (to <= from + 1) |
106 | + break; /* Broken key definition */ |
107 | + |
108 | + idnum++; |
109 | + |
110 | + /* |
111 | + Skip the check if it's the first identifier and we are processing a |
112 | + secondary key. |
113 | + */ |
114 | + if ((type == KEY_TYPE_PRIMARY || idnum != 1) && |
115 | + !strncmp(autoinc_column, from + 1, to - from - 1)) |
116 | + return TRUE; |
117 | + |
118 | + /* |
119 | + Check only the first (for PRIMARY KEY) or the second (for secondary keys) |
120 | + quoted identifier. |
121 | + */ |
122 | + if ((idnum == 1 + test(type != KEY_TYPE_PRIMARY)) || |
123 | + !(from= strchr(to + 1, '`'))) |
124 | + break; |
125 | + |
126 | + to= from; |
127 | + } |
128 | + |
129 | + return FALSE; |
130 | +} |
131 | + |
132 | + |
133 | +/* |
134 | + Remove secondary/foreign key definitions from a given SHOW CREATE TABLE string |
135 | + and store them into a temporary list to be used later. |
136 | + |
137 | + SYNOPSIS |
138 | + skip_secondary_keys() |
139 | + create_str SHOW CREATE TABLE output |
140 | + has_pk TRUE, if the table has PRIMARY KEY |
141 | + (or UNIQUE key on non-nullable columns) |
142 | + |
143 | + |
144 | + DESCRIPTION |
145 | + |
146 | + Stores all lines starting with "KEY" or "UNIQUE KEY" |
147 | + into skipped_keys_list and removes them from the input string. |
148 | + Ignoring FOREIGN KEYS constraints when creating the table is ok, because |
149 | + mysqldump sets foreign_key_checks to 0 anyway. |
150 | +*/ |
151 | + |
152 | +static void skip_secondary_keys(char *create_str, my_bool has_pk) |
153 | +{ |
154 | + char *ptr, *strend; |
155 | + char *last_comma= NULL; |
156 | + my_bool pk_processed= FALSE; |
157 | + char *autoinc_column= NULL; |
158 | + my_bool has_autoinc= FALSE; |
159 | + key_type_t type; |
160 | + |
161 | + strend= create_str + strlen(create_str); |
162 | + |
163 | + ptr= create_str; |
164 | + while (*ptr) |
165 | + { |
166 | + char *tmp, *orig_ptr, c; |
167 | + |
168 | + orig_ptr= ptr; |
169 | + /* Skip leading whitespace */ |
170 | + while (*ptr && my_isspace(charset_info, *ptr)) |
171 | + ptr++; |
172 | + |
173 | + /* Read the next line */ |
174 | + for (tmp= ptr; *tmp != '\n' && *tmp != '\0'; tmp++); |
175 | + |
176 | + c= *tmp; |
177 | + *tmp= '\0'; /* so strstr() only processes the current line */ |
178 | + |
179 | + if (!strncmp(ptr, "UNIQUE KEY ", sizeof("UNIQUE KEY ") - 1)) |
180 | + type= KEY_TYPE_UNIQUE; |
181 | + else if (!strncmp(ptr, "KEY ", sizeof("KEY ") - 1)) |
182 | + type= KEY_TYPE_NON_UNIQUE; |
183 | + else if (!strncmp(ptr, "PRIMARY KEY ", sizeof("PRIMARY KEY ") - 1)) |
184 | + type= KEY_TYPE_PRIMARY; |
185 | + else |
186 | + type= KEY_TYPE_NONE; |
187 | + |
188 | + has_autoinc= (type != KEY_TYPE_NONE) ? |
189 | + contains_autoinc_column(autoinc_column, ptr, type) : FALSE; |
190 | + |
191 | + /* Is it a secondary index definition? */ |
192 | + if (c == '\n' && |
193 | + ((type == KEY_TYPE_UNIQUE && (pk_processed || !has_pk)) || |
194 | + type == KEY_TYPE_NON_UNIQUE) && !has_autoinc) |
195 | + { |
196 | + char *data, *end= tmp - 1; |
197 | + |
198 | + /* Remove the trailing comma */ |
199 | + if (*end == ',') |
200 | + end--; |
201 | + data= my_strndup(ptr, end - ptr + 1, MYF(MY_FAE)); |
202 | + skipped_keys_list= list_cons(data, skipped_keys_list); |
203 | + |
204 | + memmove(orig_ptr, tmp + 1, strend - tmp); |
205 | + ptr= orig_ptr; |
206 | + strend-= tmp + 1 - ptr; |
207 | + |
208 | + /* Remove the comma on the previos line */ |
209 | + if (last_comma != NULL) |
210 | + { |
211 | + *last_comma= ' '; |
212 | + } |
213 | + } |
214 | + else |
215 | + { |
216 | + char *end; |
217 | + |
218 | + if (last_comma != NULL && *ptr != ')') |
219 | + { |
220 | + /* |
221 | + It's not the last line of CREATE TABLE, so we have skipped a key |
222 | + definition. We have to restore the last removed comma. |
223 | + */ |
224 | + *last_comma= ','; |
225 | + } |
226 | + |
227 | + /* |
228 | + If we are skipping a key which indexes an AUTO_INCREMENT column, it is |
229 | + safe to optimize all subsequent keys, i.e. we should not be checking for |
230 | + that column anymore. |
231 | + */ |
232 | + if (type != KEY_TYPE_NONE && has_autoinc) |
233 | + { |
234 | + DBUG_ASSERT(autoinc_column != NULL); |
235 | + |
236 | + my_free(autoinc_column); |
237 | + autoinc_column= NULL; |
238 | + } |
239 | + |
240 | + if ((has_pk && type == KEY_TYPE_UNIQUE && !pk_processed) || |
241 | + type == KEY_TYPE_PRIMARY) |
242 | + pk_processed= TRUE; |
243 | + |
244 | + if (strstr(ptr, "AUTO_INCREMENT") && *ptr == '`') |
245 | + { |
246 | + /* |
247 | + The first secondary key defined on this column later cannot be |
248 | + skipped, as CREATE TABLE would fail on import. Unless there is a |
249 | + PRIMARY KEY and it indexes that column. |
250 | + */ |
251 | + for (end= ptr + 1; |
252 | + /* Skip double backticks as they are a part of identifier */ |
253 | + *end != '\0' && (*end != '`' || end[1] == '`'); |
254 | + end++) |
255 | + /* empty */; |
256 | + |
257 | + if (*end == '`' && end > ptr + 1) |
258 | + { |
259 | + DBUG_ASSERT(autoinc_column == NULL); |
260 | + |
261 | + autoinc_column= my_strndup(ptr + 1, end - ptr - 1, MYF(MY_FAE)); |
262 | + } |
263 | + } |
264 | + |
265 | + *tmp= c; |
266 | + |
267 | + if (tmp[-1] == ',') |
268 | + last_comma= tmp - 1; |
269 | + ptr= (*tmp == '\0') ? tmp : tmp + 1; |
270 | + } |
271 | + } |
272 | + |
273 | + my_free(autoinc_column); |
274 | +} |
275 | + |
276 | +/* |
277 | + Check if the table has a primary key defined either explicitly or |
278 | + implicitly (i.e. a unique key on non-nullable columns). |
279 | + |
280 | + SYNOPSIS |
281 | + my_bool has_primary_key(const char *table_name) |
282 | + |
283 | + table_name quoted table name |
284 | + |
285 | + RETURNS TRUE if the table has a primary key |
286 | + |
287 | + DESCRIPTION |
288 | +*/ |
289 | + |
290 | +static my_bool has_primary_key(const char *table_name) |
291 | +{ |
292 | + MYSQL_RES *res= NULL; |
293 | + MYSQL_ROW row; |
294 | + char query_buff[QUERY_LENGTH]; |
295 | + my_bool has_pk= TRUE; |
296 | + |
297 | + my_snprintf(query_buff, sizeof(query_buff), |
298 | + "SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE " |
299 | + "TABLE_SCHEMA=DATABASE() AND TABLE_NAME='%s' AND " |
300 | + "COLUMN_KEY='PRI'", table_name); |
301 | + if (mysql_query(mysql, query_buff) || !(res= mysql_store_result(mysql)) || |
302 | + !(row= mysql_fetch_row(res))) |
303 | + { |
304 | + fprintf(stderr, "Warning: Couldn't determine if table %s has a " |
305 | + "primary key (%s). " |
306 | + "--innodb-optimize-keys may work inefficiently.\n", |
307 | + table_name, mysql_error(mysql)); |
308 | + goto cleanup; |
309 | + } |
310 | + |
311 | + has_pk= atoi(row[0]) > 0; |
312 | + |
313 | +cleanup: |
314 | + if (res) |
315 | + mysql_free_result(res); |
316 | + |
317 | + return has_pk; |
318 | +} |
319 | + |
320 | + |
321 | +/* |
322 | get_table_structure -- retrievs database structure, prints out corresponding |
323 | CREATE statement and fills out insert_pat if the table is the type we will |
324 | be dumping. |
325 | @@ -2508,6 +2775,7 @@ |
326 | my_bool is_log_table; |
327 | MYSQL_RES *result; |
328 | MYSQL_ROW row; |
329 | + my_bool has_pk= FALSE; |
330 | DBUG_ENTER("get_table_structure"); |
331 | DBUG_PRINT("enter", ("db: %s table: %s", db, table)); |
332 | |
333 | @@ -2549,6 +2817,9 @@ |
334 | result_table= quote_name(table, table_buff, 1); |
335 | opt_quoted_table= quote_name(table, table_buff2, 0); |
336 | |
337 | + if (opt_innodb_optimize_keys && !strcmp(table_type, "InnoDB")) |
338 | + has_pk= has_primary_key(table); |
339 | + |
340 | if (opt_order_by_primary) |
341 | order_by= primary_key_fields(result_table); |
342 | |
343 | @@ -2728,6 +2999,9 @@ |
344 | |
345 | row= mysql_fetch_row(result); |
346 | |
347 | + if (opt_innodb_optimize_keys && !strcmp(table_type, "InnoDB")) |
348 | + skip_secondary_keys(row[1], has_pk); |
349 | + |
350 | is_log_table= general_log_or_slow_log_tables(db, table); |
351 | if (is_log_table) |
352 | row[1]+= 13; /* strlen("CREATE TABLE ")= 13 */ |
353 | @@ -3379,6 +3653,36 @@ |
354 | } |
355 | |
356 | |
357 | + |
358 | +/* |
359 | + Perform delayed secondary index creation for --innodb-optimize-keys. |
360 | +*/ |
361 | + |
362 | +static void restore_secondary_keys(char *table) |
363 | +{ |
364 | + if (skipped_keys_list) |
365 | + { |
366 | + uint keys; |
367 | + skipped_keys_list= list_reverse(skipped_keys_list); |
368 | + fprintf(md_result_file, "ALTER TABLE %s ", table); |
369 | + for (keys= list_length(skipped_keys_list); keys > 0; keys--) |
370 | + { |
371 | + LIST *node= skipped_keys_list; |
372 | + char *def= node->data; |
373 | + |
374 | + fprintf(md_result_file, "ADD %s%s", def, (keys > 1) ? ", " : ";\n"); |
375 | + |
376 | + skipped_keys_list= list_delete(skipped_keys_list, node); |
377 | + my_free(def); |
378 | + my_free(node); |
379 | + } |
380 | + |
381 | + DBUG_ASSERT(skipped_keys_list == NULL); |
382 | + } |
383 | +} |
384 | + |
385 | + |
386 | + |
387 | /* |
388 | |
389 | SYNOPSIS |
390 | @@ -3422,11 +3726,15 @@ |
391 | if (strcmp(table_type, "VIEW") == 0) |
392 | DBUG_VOID_RETURN; |
393 | |
394 | + result_table= quote_name(table,table_buff, 1); |
395 | + opt_quoted_table= quote_name(table, table_buff2, 0); |
396 | + |
397 | /* Check --no-data flag */ |
398 | if (opt_no_data) |
399 | { |
400 | verbose_msg("-- Skipping dump data for table '%s', --no-data was used\n", |
401 | table); |
402 | + restore_secondary_keys(opt_quoted_table); |
403 | DBUG_VOID_RETURN; |
404 | } |
405 | |
406 | @@ -3451,9 +3759,24 @@ |
407 | DBUG_VOID_RETURN; |
408 | } |
409 | |
410 | +<<<<<<< TREE |
411 | result_table= quote_name(table,table_buff, 1); |
412 | opt_quoted_table= quote_name(table, table_buff2, 0); |
413 | |
414 | +======= |
415 | + /* |
416 | + Check --skip-events flag: it is not enough to skip creation of events |
417 | + discarding SHOW CREATE EVENT statements generation. The myslq.event |
418 | + table data should be skipped too. |
419 | + */ |
420 | + if (!opt_events && !my_strcasecmp(&my_charset_latin1, db, "mysql") && |
421 | + !my_strcasecmp(&my_charset_latin1, table, "event")) |
422 | + { |
423 | + verbose_msg("-- Skipping data table mysql.event, --skip-events was used\n"); |
424 | + DBUG_VOID_RETURN; |
425 | + } |
426 | + |
427 | +>>>>>>> MERGE-SOURCE |
428 | verbose_msg("-- Sending SELECT query...\n"); |
429 | |
430 | init_dynamic_string_checked(&query_string, "", 1024, 1024); |
431 | @@ -3842,6 +4165,8 @@ |
432 | goto err; |
433 | } |
434 | |
435 | + restore_secondary_keys(opt_quoted_table); |
436 | + |
437 | /* Moved enable keys to before unlock per bug 15977 */ |
438 | if (opt_disable_keys) |
439 | { |
440 | |
441 | === modified file 'mysql-test/r/mysqld--help-notwin.result' |
442 | --- mysql-test/r/mysqld--help-notwin.result 2012-10-30 07:59:01 +0000 |
443 | +++ mysql-test/r/mysqld--help-notwin.result 2012-12-14 14:59:00 +0000 |
444 | @@ -191,6 +191,10 @@ |
445 | and DISABLED (keep the event scheduler completely |
446 | deactivated, it cannot be activated run-time) |
447 | -T, --exit-info[=#] Used for debugging. Use at your own risk. |
448 | + --expand-fast-index-creation |
449 | + Enable/disable improvements to the InnoDB fast index |
450 | + creation functionality. Has no effect when fast index |
451 | + creation is disabled with the fast-index-creation option |
452 | --expire-logs-days=# |
453 | If non-zero, binary logs will be purged after |
454 | expire_logs_days days; possible purges happen at startup |
455 | @@ -1013,6 +1017,7 @@ |
456 | enforce-gtid-consistency FALSE |
457 | eq-range-index-dive-limit 10 |
458 | event-scheduler OFF |
459 | +expand-fast-index-creation FALSE |
460 | expire-logs-days 0 |
461 | explicit-defaults-for-timestamp FALSE |
462 | external-locking FALSE |
463 | |
464 | === modified file 'mysql-test/r/mysqld--help-win.result' |
465 | --- mysql-test/r/mysqld--help-win.result 2012-10-30 07:59:01 +0000 |
466 | +++ mysql-test/r/mysqld--help-win.result 2012-12-14 14:59:00 +0000 |
467 | @@ -191,6 +191,10 @@ |
468 | and DISABLED (keep the event scheduler completely |
469 | deactivated, it cannot be activated run-time) |
470 | -T, --exit-info[=#] Used for debugging. Use at your own risk. |
471 | + --expand-fast-index-creation |
472 | + Enable/disable improvements to InnoDB fast index creation |
473 | + functionality. Has no effect when fast index creation is |
474 | + disabled with the fast-index-creation option |
475 | --expire-logs-days=# |
476 | If non-zero, binary logs will be purged after |
477 | expire_logs_days days; possible purges happen at startup |
478 | @@ -1021,6 +1025,7 @@ |
479 | enforce-gtid-consistency FALSE |
480 | eq-range-index-dive-limit 10 |
481 | event-scheduler OFF |
482 | +expand-fast-index-creation FALSE |
483 | expire-logs-days 0 |
484 | explicit-defaults-for-timestamp FALSE |
485 | external-locking FALSE |
486 | |
487 | === added file 'mysql-test/r/percona_expand_fast_index_creation.result' |
488 | --- mysql-test/r/percona_expand_fast_index_creation.result 1970-01-01 00:00:00 +0000 |
489 | +++ mysql-test/r/percona_expand_fast_index_creation.result 2012-12-14 14:59:00 +0000 |
490 | @@ -0,0 +1,76 @@ |
491 | +SET expand_fast_index_creation=ON; |
492 | +CREATE TABLE idx (a INT PRIMARY KEY); |
493 | +CREATE TABLE t (a INT PRIMARY KEY AUTO_INCREMENT, b FLOAT, c VARCHAR(15), d VARCHAR(17), z VARCHAR(1)) ENGINE=InnoDB; |
494 | +INSERT INTO t (b) VALUES (RAND()); |
495 | +INSERT INTO t (b) SELECT RAND() FROM t; |
496 | +INSERT INTO t (b) SELECT RAND() FROM t; |
497 | +INSERT INTO t (b) SELECT RAND() FROM t; |
498 | +INSERT INTO t (b) SELECT RAND() FROM t; |
499 | +INSERT INTO t (b) SELECT RAND() FROM t; |
500 | +INSERT INTO t (b) SELECT RAND() FROM t; |
501 | +INSERT INTO t (b) SELECT RAND() FROM t; |
502 | +ALTER TABLE t ADD KEY k1(b), ADD KEY k2(a,b), ADD KEY k3(a,b,d), ADD UNIQUE KEY k4(a,c,d); |
503 | +INSERT INTO idx (a) SELECT i.INDEX_ID FROM |
504 | +INFORMATION_SCHEMA.INNODB_SYS_INDEXES i, |
505 | +INFORMATION_SCHEMA.INNODB_SYS_TABLES t |
506 | +WHERE |
507 | +t.NAME = 'test/t' AND i.TABLE_ID = t.TABLE_ID; |
508 | +SELECT i.NAME, i.TYPE, i.N_FIELDS, f.NAME, f.POS FROM |
509 | +INFORMATION_SCHEMA.INNODB_SYS_FIELDS f, |
510 | +INFORMATION_SCHEMA.INNODB_SYS_INDEXES i, |
511 | +INFORMATION_SCHEMA.INNODB_SYS_TABLES t |
512 | +WHERE |
513 | +t.NAME = 'test/t' AND i.TABLE_ID = t.TABLE_ID AND |
514 | +f.INDEX_ID = i.INDEX_ID |
515 | +ORDER BY |
516 | +i.NAME, f.NAME; |
517 | +NAME TYPE N_FIELDS NAME POS |
518 | +k1 0 1 b 0 |
519 | +k2 0 2 a 0 |
520 | +k2 0 2 b 1 |
521 | +k3 0 3 a 0 |
522 | +k3 0 3 b 1 |
523 | +k3 0 3 d 2 |
524 | +k4 2 3 a 0 |
525 | +k4 2 3 c 1 |
526 | +k4 2 3 d 2 |
527 | +PRIMARY 3 1 a 0 |
528 | +SET profiling=1; |
529 | +ALTER TABLE t MODIFY COLUMN z VARCHAR(2); |
530 | +SET profiling=0; |
531 | +SELECT COUNT(*) FROM INFORMATION_SCHEMA.PROFILING |
532 | +WHERE STATE = 'restoring secondary keys' AND |
533 | +QUERY_ID = (SELECT MAX(QUERY_ID) FROM INFORMATION_SCHEMA.PROFILING); |
534 | +COUNT(*) |
535 | +1 |
536 | +INSERT INTO idx (a) SELECT i.INDEX_ID FROM |
537 | +INFORMATION_SCHEMA.INNODB_SYS_INDEXES i, |
538 | +INFORMATION_SCHEMA.INNODB_SYS_TABLES t |
539 | +WHERE |
540 | +t.NAME = 'test/t' AND i.TABLE_ID = t.TABLE_ID; |
541 | +SET profiling=1; |
542 | +SELECT i.NAME, i.TYPE, i.N_FIELDS, f.NAME, f.POS FROM |
543 | +INFORMATION_SCHEMA.INNODB_SYS_FIELDS f, |
544 | +INFORMATION_SCHEMA.INNODB_SYS_INDEXES i, |
545 | +INFORMATION_SCHEMA.INNODB_SYS_TABLES t |
546 | +WHERE |
547 | +t.NAME = 'test/t' AND i.TABLE_ID = t.TABLE_ID AND |
548 | +f.INDEX_ID = i.INDEX_ID |
549 | +ORDER BY |
550 | +i.NAME, f.NAME; |
551 | +NAME TYPE N_FIELDS NAME POS |
552 | +k1 0 1 b 0 |
553 | +k2 0 2 a 0 |
554 | +k2 0 2 b 1 |
555 | +k3 0 3 a 0 |
556 | +k3 0 3 b 1 |
557 | +k3 0 3 d 2 |
558 | +k4 2 3 a 0 |
559 | +k4 2 3 c 1 |
560 | +k4 2 3 d 2 |
561 | +PRIMARY 3 1 a 0 |
562 | +SELECT COUNT(DISTINCT a) FROM idx; |
563 | +COUNT(DISTINCT a) |
564 | +10 |
565 | +DROP TABLE idx; |
566 | +DROP TABLE t; |
567 | |
568 | === added file 'mysql-test/r/percona_mysqldump_innodb_optimize_keys.result' |
569 | --- mysql-test/r/percona_mysqldump_innodb_optimize_keys.result 1970-01-01 00:00:00 +0000 |
570 | +++ mysql-test/r/percona_mysqldump_innodb_optimize_keys.result 2012-12-14 14:59:00 +0000 |
571 | @@ -0,0 +1,443 @@ |
572 | +# |
573 | +# Test the --innodb-optimize-keys option. |
574 | +# |
575 | +CREATE TABLE t1 (a INT NOT NULL PRIMARY KEY, b INT, KEY(b)) ENGINE=MyISAM; |
576 | +###################################### |
577 | + |
578 | +/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; |
579 | +/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; |
580 | +/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */; |
581 | +/*!40101 SET NAMES utf8 */; |
582 | +/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */; |
583 | +/*!40103 SET TIME_ZONE='+00:00' */; |
584 | +/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */; |
585 | +/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */; |
586 | +/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */; |
587 | +/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */; |
588 | +DROP TABLE IF EXISTS `t1`; |
589 | +/*!40101 SET @saved_cs_client = @@character_set_client */; |
590 | +/*!40101 SET character_set_client = utf8 */; |
591 | +CREATE TABLE `t1` ( |
592 | + `a` int(11) NOT NULL, |
593 | + `b` int(11) DEFAULT NULL, |
594 | + PRIMARY KEY (`a`), |
595 | + KEY `b` (`b`) |
596 | +) ENGINE=MyISAM DEFAULT CHARSET=latin1; |
597 | +/*!40101 SET character_set_client = @saved_cs_client */; |
598 | + |
599 | +LOCK TABLES `t1` WRITE; |
600 | +/*!40000 ALTER TABLE `t1` DISABLE KEYS */; |
601 | +/*!40000 ALTER TABLE `t1` ENABLE KEYS */; |
602 | +UNLOCK TABLES; |
603 | +/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */; |
604 | + |
605 | +/*!40101 SET SQL_MODE=@OLD_SQL_MODE */; |
606 | +/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */; |
607 | +/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */; |
608 | +/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */; |
609 | +/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */; |
610 | +/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */; |
611 | +/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */; |
612 | + |
613 | +###################################### |
614 | +DROP TABLE t1; |
615 | +CREATE TABLE t2 (a INT PRIMARY KEY) ENGINE=InnoDB; |
616 | +INSERT INTO t2 VALUES (0), (1), (2); |
617 | +CREATE TABLE t1 ( |
618 | +id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, |
619 | +a INT, b VARCHAR(255), c DECIMAL(10,3), |
620 | +KEY (b), |
621 | +UNIQUE KEY uniq(c,a), |
622 | +FOREIGN KEY (a) REFERENCES t2(a) ON DELETE CASCADE |
623 | +) ENGINE=InnoDB; |
624 | +INSERT INTO t1(a,b,c) VALUES (0, "0", 0.0), (1, "1", 1.1), (2, "2", 2.2); |
625 | +###################################### |
626 | + |
627 | +/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; |
628 | +/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; |
629 | +/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */; |
630 | +/*!40101 SET NAMES utf8 */; |
631 | +/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */; |
632 | +/*!40103 SET TIME_ZONE='+00:00' */; |
633 | +/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */; |
634 | +/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */; |
635 | +/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */; |
636 | +/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */; |
637 | +DROP TABLE IF EXISTS `t1`; |
638 | +/*!40101 SET @saved_cs_client = @@character_set_client */; |
639 | +/*!40101 SET character_set_client = utf8 */; |
640 | +CREATE TABLE `t1` ( |
641 | + `id` int(11) NOT NULL AUTO_INCREMENT, |
642 | + `a` int(11) DEFAULT NULL, |
643 | + `b` varchar(255) DEFAULT NULL, |
644 | + `c` decimal(10,3) DEFAULT NULL, |
645 | + PRIMARY KEY (`id`), |
646 | + CONSTRAINT `t1_ibfk_1` FOREIGN KEY (`a`) REFERENCES `t2` (`a`) ON DELETE CASCADE |
647 | +) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1; |
648 | +/*!40101 SET character_set_client = @saved_cs_client */; |
649 | + |
650 | +LOCK TABLES `t1` WRITE; |
651 | +/*!40000 ALTER TABLE `t1` DISABLE KEYS */; |
652 | +INSERT INTO `t1` VALUES (1,0,'0',0.000),(2,1,'1',1.100),(3,2,'2',2.200); |
653 | +ALTER TABLE `t1` ADD UNIQUE KEY `uniq` (`c`,`a`), ADD KEY `b` (`b`), ADD KEY `a` (`a`); |
654 | +/*!40000 ALTER TABLE `t1` ENABLE KEYS */; |
655 | +UNLOCK TABLES; |
656 | +DROP TABLE IF EXISTS `t2`; |
657 | +/*!40101 SET @saved_cs_client = @@character_set_client */; |
658 | +/*!40101 SET character_set_client = utf8 */; |
659 | +CREATE TABLE `t2` ( |
660 | + `a` int(11) NOT NULL, |
661 | + PRIMARY KEY (`a`) |
662 | +) ENGINE=InnoDB DEFAULT CHARSET=latin1; |
663 | +/*!40101 SET character_set_client = @saved_cs_client */; |
664 | + |
665 | +LOCK TABLES `t2` WRITE; |
666 | +/*!40000 ALTER TABLE `t2` DISABLE KEYS */; |
667 | +INSERT INTO `t2` VALUES (0),(1),(2); |
668 | +/*!40000 ALTER TABLE `t2` ENABLE KEYS */; |
669 | +UNLOCK TABLES; |
670 | +/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */; |
671 | + |
672 | +/*!40101 SET SQL_MODE=@OLD_SQL_MODE */; |
673 | +/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */; |
674 | +/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */; |
675 | +/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */; |
676 | +/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */; |
677 | +/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */; |
678 | +/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */; |
679 | + |
680 | +###################################### |
681 | +DROP TABLE t1, t2; |
682 | +CREATE TABLE t1 ( |
683 | +id INT NOT NULL AUTO_INCREMENT, |
684 | +KEY (id) |
685 | +) ENGINE=InnoDB; |
686 | +CREATE TABLE t2 ( |
687 | +id INT NOT NULL AUTO_INCREMENT, |
688 | +UNIQUE KEY (id) |
689 | +) ENGINE=InnoDB; |
690 | +INSERT INTO t1 VALUES (), (), (); |
691 | +INSERT INTO t2 VALUES (), (), (); |
692 | +###################################### |
693 | + |
694 | +/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; |
695 | +/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; |
696 | +/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */; |
697 | +/*!40101 SET NAMES utf8 */; |
698 | +/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */; |
699 | +/*!40103 SET TIME_ZONE='+00:00' */; |
700 | +/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */; |
701 | +/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */; |
702 | +/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */; |
703 | +/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */; |
704 | +DROP TABLE IF EXISTS `t1`; |
705 | +/*!40101 SET @saved_cs_client = @@character_set_client */; |
706 | +/*!40101 SET character_set_client = utf8 */; |
707 | +CREATE TABLE `t1` ( |
708 | + `id` int(11) NOT NULL AUTO_INCREMENT, |
709 | + KEY `id` (`id`) |
710 | +) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1; |
711 | +/*!40101 SET character_set_client = @saved_cs_client */; |
712 | + |
713 | +LOCK TABLES `t1` WRITE; |
714 | +/*!40000 ALTER TABLE `t1` DISABLE KEYS */; |
715 | +INSERT INTO `t1` VALUES (1),(2),(3); |
716 | +/*!40000 ALTER TABLE `t1` ENABLE KEYS */; |
717 | +UNLOCK TABLES; |
718 | +DROP TABLE IF EXISTS `t2`; |
719 | +/*!40101 SET @saved_cs_client = @@character_set_client */; |
720 | +/*!40101 SET character_set_client = utf8 */; |
721 | +CREATE TABLE `t2` ( |
722 | + `id` int(11) NOT NULL AUTO_INCREMENT, |
723 | + UNIQUE KEY `id` (`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),(2),(3); |
730 | +/*!40000 ALTER TABLE `t2` ENABLE KEYS */; |
731 | +UNLOCK TABLES; |
732 | +/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */; |
733 | + |
734 | +/*!40101 SET SQL_MODE=@OLD_SQL_MODE */; |
735 | +/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */; |
736 | +/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */; |
737 | +/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */; |
738 | +/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */; |
739 | +/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */; |
740 | +/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */; |
741 | + |
742 | +###################################### |
743 | +DROP TABLE t1, t2; |
744 | +CREATE TABLE t1 ( |
745 | +a INT NOT NULL, |
746 | +UNIQUE KEY (a)) ENGINE=InnoDB; |
747 | +CREATE TABLE t2 ( |
748 | +a INT NOT NULL, |
749 | +b INT NOT NULL, |
750 | +UNIQUE KEY (a,b)) ENGINE=InnoDB; |
751 | +CREATE TABLE t3 ( |
752 | +a INT, |
753 | +b INT, |
754 | +UNIQUE KEY (a,b)) ENGINE=InnoDB; |
755 | +CREATE TABLE t4 ( |
756 | +a INT NOT NULL, |
757 | +b INT NOT NULL, |
758 | +PRIMARY KEY (a,b), |
759 | +UNIQUE KEY(b)) ENGINE=InnoDB; |
760 | +SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE |
761 | +TABLE_SCHEMA=DATABASE() AND |
762 | +TABLE_NAME='t1' AND |
763 | +COLUMN_KEY='PRI'; |
764 | +COUNT(*) |
765 | +1 |
766 | +SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE |
767 | +TABLE_SCHEMA=DATABASE() AND |
768 | +TABLE_NAME='t2' AND |
769 | +COLUMN_KEY='PRI'; |
770 | +COUNT(*) |
771 | +2 |
772 | +SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE |
773 | +TABLE_SCHEMA=DATABASE() AND |
774 | +TABLE_NAME='t3' AND |
775 | +COLUMN_KEY='PRI'; |
776 | +COUNT(*) |
777 | +0 |
778 | +SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE |
779 | +TABLE_SCHEMA=DATABASE() AND |
780 | +TABLE_NAME='t4' AND |
781 | +COLUMN_KEY='PRI'; |
782 | +COUNT(*) |
783 | +2 |
784 | +INSERT INTO t1 VALUES (1), (2), (3); |
785 | +INSERT INTO t2 VALUES (1,1), (2,2), (3,3); |
786 | +INSERT INTO t3 SELECT * FROM t2; |
787 | +INSERT INTO t4 SELECT * FROM t2; |
788 | +###################################### |
789 | + |
790 | +/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; |
791 | +/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; |
792 | +/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */; |
793 | +/*!40101 SET NAMES utf8 */; |
794 | +/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */; |
795 | +/*!40103 SET TIME_ZONE='+00:00' */; |
796 | +/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */; |
797 | +/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */; |
798 | +/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */; |
799 | +/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */; |
800 | +DROP TABLE IF EXISTS `t1`; |
801 | +/*!40101 SET @saved_cs_client = @@character_set_client */; |
802 | +/*!40101 SET character_set_client = utf8 */; |
803 | +CREATE TABLE `t1` ( |
804 | + `a` int(11) NOT NULL, |
805 | + UNIQUE KEY `a` (`a`) |
806 | +) ENGINE=InnoDB DEFAULT CHARSET=latin1; |
807 | +/*!40101 SET character_set_client = @saved_cs_client */; |
808 | + |
809 | +LOCK TABLES `t1` WRITE; |
810 | +/*!40000 ALTER TABLE `t1` DISABLE KEYS */; |
811 | +INSERT INTO `t1` VALUES (1),(2),(3); |
812 | +/*!40000 ALTER TABLE `t1` ENABLE KEYS */; |
813 | +UNLOCK TABLES; |
814 | +DROP TABLE IF EXISTS `t2`; |
815 | +/*!40101 SET @saved_cs_client = @@character_set_client */; |
816 | +/*!40101 SET character_set_client = utf8 */; |
817 | +CREATE TABLE `t2` ( |
818 | + `a` int(11) NOT NULL, |
819 | + `b` int(11) NOT NULL, |
820 | + UNIQUE KEY `a` (`a`,`b`) |
821 | +) ENGINE=InnoDB DEFAULT CHARSET=latin1; |
822 | +/*!40101 SET character_set_client = @saved_cs_client */; |
823 | + |
824 | +LOCK TABLES `t2` WRITE; |
825 | +/*!40000 ALTER TABLE `t2` DISABLE KEYS */; |
826 | +INSERT INTO `t2` VALUES (1,1),(2,2),(3,3); |
827 | +/*!40000 ALTER TABLE `t2` ENABLE KEYS */; |
828 | +UNLOCK TABLES; |
829 | +DROP TABLE IF EXISTS `t3`; |
830 | +/*!40101 SET @saved_cs_client = @@character_set_client */; |
831 | +/*!40101 SET character_set_client = utf8 */; |
832 | +CREATE TABLE `t3` ( |
833 | + `a` int(11) DEFAULT NULL, |
834 | + `b` int(11) DEFAULT NULL |
835 | +) ENGINE=InnoDB DEFAULT CHARSET=latin1; |
836 | +/*!40101 SET character_set_client = @saved_cs_client */; |
837 | + |
838 | +LOCK TABLES `t3` WRITE; |
839 | +/*!40000 ALTER TABLE `t3` DISABLE KEYS */; |
840 | +INSERT INTO `t3` VALUES (1,1),(2,2),(3,3); |
841 | +ALTER TABLE `t3` ADD UNIQUE KEY `a` (`a`,`b`); |
842 | +/*!40000 ALTER TABLE `t3` ENABLE KEYS */; |
843 | +UNLOCK TABLES; |
844 | +DROP TABLE IF EXISTS `t4`; |
845 | +/*!40101 SET @saved_cs_client = @@character_set_client */; |
846 | +/*!40101 SET character_set_client = utf8 */; |
847 | +CREATE TABLE `t4` ( |
848 | + `a` int(11) NOT NULL, |
849 | + `b` int(11) NOT NULL, |
850 | + PRIMARY KEY (`a`,`b`) |
851 | +) ENGINE=InnoDB DEFAULT CHARSET=latin1; |
852 | +/*!40101 SET character_set_client = @saved_cs_client */; |
853 | + |
854 | +LOCK TABLES `t4` WRITE; |
855 | +/*!40000 ALTER TABLE `t4` DISABLE KEYS */; |
856 | +INSERT INTO `t4` VALUES (1,1),(2,2),(3,3); |
857 | +ALTER TABLE `t4` ADD UNIQUE KEY `b` (`b`); |
858 | +/*!40000 ALTER TABLE `t4` ENABLE KEYS */; |
859 | +UNLOCK TABLES; |
860 | +/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */; |
861 | + |
862 | +/*!40101 SET SQL_MODE=@OLD_SQL_MODE */; |
863 | +/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */; |
864 | +/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */; |
865 | +/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */; |
866 | +/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */; |
867 | +/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */; |
868 | +/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */; |
869 | + |
870 | +###################################### |
871 | +DROP TABLE t1, t2, t3, t4; |
872 | +CREATE TABLE t1 ( |
873 | +id INT NOT NULL PRIMARY KEY |
874 | +) ENGINE=InnoDB; |
875 | +CREATE TABLE t2 ( |
876 | +id INT NOT NULL AUTO_INCREMENT, |
877 | +a INT NOT NULL, |
878 | +PRIMARY KEY (id), |
879 | +KEY (a), |
880 | +FOREIGN KEY (a) REFERENCES t2 (id) |
881 | +) ENGINE=InnoDB; |
882 | +INSERT INTO t1 VALUES (1), (2), (3); |
883 | +INSERT INTO t2 VALUES (1, 1), (2, 2), (3, 3); |
884 | +###################################### |
885 | + |
886 | +/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; |
887 | +/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; |
888 | +/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */; |
889 | +/*!40101 SET NAMES utf8 */; |
890 | +/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */; |
891 | +/*!40103 SET TIME_ZONE='+00:00' */; |
892 | +/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */; |
893 | +/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */; |
894 | +/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */; |
895 | +/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */; |
896 | +DROP TABLE IF EXISTS `t1`; |
897 | +/*!40101 SET @saved_cs_client = @@character_set_client */; |
898 | +/*!40101 SET character_set_client = utf8 */; |
899 | +CREATE TABLE `t1` ( |
900 | + `id` int(11) NOT NULL, |
901 | + PRIMARY KEY (`id`) |
902 | +) ENGINE=InnoDB DEFAULT CHARSET=latin1; |
903 | +/*!40101 SET character_set_client = @saved_cs_client */; |
904 | + |
905 | +LOCK TABLES `t1` WRITE; |
906 | +/*!40000 ALTER TABLE `t1` DISABLE KEYS */; |
907 | +INSERT INTO `t1` VALUES (1),(2),(3); |
908 | +/*!40000 ALTER TABLE `t1` ENABLE KEYS */; |
909 | +UNLOCK TABLES; |
910 | +DROP TABLE IF EXISTS `t2`; |
911 | +/*!40101 SET @saved_cs_client = @@character_set_client */; |
912 | +/*!40101 SET character_set_client = utf8 */; |
913 | +CREATE TABLE `t2` ( |
914 | + `id` int(11) NOT NULL AUTO_INCREMENT, |
915 | + `a` int(11) NOT NULL, |
916 | + PRIMARY KEY (`id`), |
917 | + CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`a`) REFERENCES `t2` (`id`) |
918 | +) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1; |
919 | +/*!40101 SET character_set_client = @saved_cs_client */; |
920 | + |
921 | +LOCK TABLES `t2` WRITE; |
922 | +/*!40000 ALTER TABLE `t2` DISABLE KEYS */; |
923 | +INSERT INTO `t2` VALUES (1,1),(2,2),(3,3); |
924 | +ALTER TABLE `t2` ADD KEY `a` (`a`); |
925 | +/*!40000 ALTER TABLE `t2` ENABLE KEYS */; |
926 | +UNLOCK TABLES; |
927 | +/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */; |
928 | + |
929 | +/*!40101 SET SQL_MODE=@OLD_SQL_MODE */; |
930 | +/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */; |
931 | +/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */; |
932 | +/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */; |
933 | +/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */; |
934 | +/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */; |
935 | +/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */; |
936 | + |
937 | +###################################### |
938 | +DROP TABLE t1, t2; |
939 | +CREATE TABLE t1 ( |
940 | +id INT NOT NULL AUTO_INCREMENT, |
941 | +uid INT NOT NULL, |
942 | +`id``` INT NOT NULL, |
943 | +```id` INT NOT NULL, |
944 | +# The following ones may be skipped and used in ALTER TABLE later |
945 | +KEY k1 (```id`, id), |
946 | +KEY k2 (```id`, `id```), |
947 | +# The following one should be kept in CREATE TABLE |
948 | +KEY k3 (id, uid), |
949 | +# The following one may be skipped again |
950 | +KEY k4 (id, `id```) |
951 | +) ENGINE=InnoDB; |
952 | +CREATE TABLE t2 ( |
953 | +id INT NOT NULL AUTO_INCREMENT, |
954 | +PRIMARY KEY (id), |
955 | +KEY k1 (id), |
956 | +KEY k2 (id) |
957 | +) ENGINE=InnoDB; |
958 | +Warnings: |
959 | +Note 1831 Duplicate index 'k2' defined on the table 'test.t2'. This is deprecated and will be disallowed in a future release. |
960 | +###################################### |
961 | + |
962 | +/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; |
963 | +/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; |
964 | +/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */; |
965 | +/*!40101 SET NAMES utf8 */; |
966 | +/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */; |
967 | +/*!40103 SET TIME_ZONE='+00:00' */; |
968 | +/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */; |
969 | +/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */; |
970 | +/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */; |
971 | +/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */; |
972 | +DROP TABLE IF EXISTS `t1`; |
973 | +/*!40101 SET @saved_cs_client = @@character_set_client */; |
974 | +/*!40101 SET character_set_client = utf8 */; |
975 | +CREATE TABLE `t1` ( |
976 | + `id` int(11) NOT NULL AUTO_INCREMENT, |
977 | + `uid` int(11) NOT NULL, |
978 | + `id``` int(11) NOT NULL, |
979 | + ```id` int(11) NOT NULL, |
980 | + KEY `k3` (`id`,`uid`) |
981 | +) ENGINE=InnoDB DEFAULT CHARSET=latin1; |
982 | +/*!40101 SET character_set_client = @saved_cs_client */; |
983 | + |
984 | +LOCK TABLES `t1` WRITE; |
985 | +/*!40000 ALTER TABLE `t1` DISABLE KEYS */; |
986 | +ALTER TABLE `t1` ADD KEY `k1` (```id`,`id`), ADD KEY `k2` (```id`,`id```), ADD KEY `k4` (`id`,`id```); |
987 | +/*!40000 ALTER TABLE `t1` ENABLE KEYS */; |
988 | +UNLOCK TABLES; |
989 | +DROP TABLE IF EXISTS `t2`; |
990 | +/*!40101 SET @saved_cs_client = @@character_set_client */; |
991 | +/*!40101 SET character_set_client = utf8 */; |
992 | +CREATE TABLE `t2` ( |
993 | + `id` int(11) NOT NULL AUTO_INCREMENT, |
994 | + PRIMARY KEY (`id`) |
995 | +) ENGINE=InnoDB DEFAULT CHARSET=latin1; |
996 | +/*!40101 SET character_set_client = @saved_cs_client */; |
997 | + |
998 | +LOCK TABLES `t2` WRITE; |
999 | +/*!40000 ALTER TABLE `t2` DISABLE KEYS */; |
1000 | +ALTER TABLE `t2` ADD KEY `k1` (`id`), ADD KEY `k2` (`id`); |
1001 | +/*!40000 ALTER TABLE `t2` ENABLE KEYS */; |
1002 | +UNLOCK TABLES; |
1003 | +/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */; |
1004 | + |
1005 | +/*!40101 SET SQL_MODE=@OLD_SQL_MODE */; |
1006 | +/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */; |
1007 | +/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */; |
1008 | +/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */; |
1009 | +/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */; |
1010 | +/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */; |
1011 | +/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */; |
1012 | + |
1013 | +###################################### |
1014 | +DROP TABLE t1, t2; |
1015 | |
1016 | === added file 'mysql-test/suite/innodb/r/percona_bug_999147.result' |
1017 | --- mysql-test/suite/innodb/r/percona_bug_999147.result 1970-01-01 00:00:00 +0000 |
1018 | +++ mysql-test/suite/innodb/r/percona_bug_999147.result 2012-12-14 14:59:00 +0000 |
1019 | @@ -0,0 +1,17 @@ |
1020 | +DROP TABLE IF EXISTS t1; |
1021 | +SET SESSION expand_fast_index_creation=ON; |
1022 | +CREATE TEMPORARY TABLE t1 (a INT, b INT, INDEX(a)); |
1023 | +SET debug="+d,crash_innodb_add_index_after"; |
1024 | +ALTER TABLE t1 ADD INDEX (b); |
1025 | +ERROR HY000: Lost connection to MySQL server during query |
1026 | +SELECT NAME, FLAG FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES; |
1027 | +NAME FLAG |
1028 | +SYS_DATAFILES 0 |
1029 | +SYS_FOREIGN 0 |
1030 | +SYS_FOREIGN_COLS 0 |
1031 | +SYS_TABLESPACES 0 |
1032 | +mysql/innodb_index_stats 1 |
1033 | +mysql/innodb_table_stats 1 |
1034 | +mysql/slave_master_info 1 |
1035 | +mysql/slave_relay_log_info 1 |
1036 | +mysql/slave_worker_info 1 |
1037 | |
1038 | === added file 'mysql-test/suite/innodb/t/percona_bug_999147-master.opt' |
1039 | --- mysql-test/suite/innodb/t/percona_bug_999147-master.opt 1970-01-01 00:00:00 +0000 |
1040 | +++ mysql-test/suite/innodb/t/percona_bug_999147-master.opt 2012-12-14 14:59:00 +0000 |
1041 | @@ -0,0 +1,1 @@ |
1042 | +--skip-stack-trace --skip-core-file --innodb-file-per-table=1 |
1043 | |
1044 | === added file 'mysql-test/suite/innodb/t/percona_bug_999147.test' |
1045 | --- mysql-test/suite/innodb/t/percona_bug_999147.test 1970-01-01 00:00:00 +0000 |
1046 | +++ mysql-test/suite/innodb/t/percona_bug_999147.test 2012-12-14 14:59:00 +0000 |
1047 | @@ -0,0 +1,29 @@ |
1048 | +# Test for Percona Server bug 999147 (A crash that leaves behind an |
1049 | +# InnoDB temporary table with indexes results in an unbootable server) |
1050 | +# https://bugs.launchpad.net/percona-server/+bug/999147 |
1051 | + |
1052 | +-- source include/not_embedded.inc |
1053 | +-- source include/not_valgrind.inc |
1054 | +-- source include/not_crashrep.inc |
1055 | +-- source include/have_debug.inc |
1056 | +-- source include/have_innodb.inc |
1057 | + |
1058 | +--disable_warnings |
1059 | +DROP TABLE IF EXISTS t1; |
1060 | +--enable_warnings |
1061 | + |
1062 | +SET SESSION expand_fast_index_creation=ON; |
1063 | + |
1064 | +CREATE TEMPORARY TABLE t1 (a INT, b INT, INDEX(a)); |
1065 | + |
1066 | +--exec echo "restart" > $MYSQLTEST_VARDIR/tmp/mysqld.1.expect |
1067 | + |
1068 | +SET debug="+d,crash_innodb_add_index_after"; |
1069 | +--error 2013 |
1070 | +ALTER TABLE t1 ADD INDEX (b); |
1071 | + |
1072 | +--enable_reconnect |
1073 | + |
1074 | +--source include/wait_until_connected_again.inc |
1075 | + |
1076 | +SELECT NAME, FLAG FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES; |
1077 | |
1078 | === added file 'mysql-test/suite/sys_vars/r/expand_fast_index_creation_basic.result' |
1079 | --- mysql-test/suite/sys_vars/r/expand_fast_index_creation_basic.result 1970-01-01 00:00:00 +0000 |
1080 | +++ mysql-test/suite/sys_vars/r/expand_fast_index_creation_basic.result 2012-12-14 14:59:00 +0000 |
1081 | @@ -0,0 +1,6 @@ |
1082 | +SELECT @@global.expand_fast_index_creation; |
1083 | +@@global.expand_fast_index_creation |
1084 | +0 |
1085 | +SELECT @@local.expand_fast_index_creation; |
1086 | +@@local.expand_fast_index_creation |
1087 | +0 |
1088 | |
1089 | === added file 'mysql-test/suite/sys_vars/t/expand_fast_index_creation_basic.test' |
1090 | --- mysql-test/suite/sys_vars/t/expand_fast_index_creation_basic.test 1970-01-01 00:00:00 +0000 |
1091 | +++ mysql-test/suite/sys_vars/t/expand_fast_index_creation_basic.test 2012-12-14 14:59:00 +0000 |
1092 | @@ -0,0 +1,2 @@ |
1093 | +SELECT @@global.expand_fast_index_creation; |
1094 | +SELECT @@local.expand_fast_index_creation; |
1095 | |
1096 | === added file 'mysql-test/t/percona_expand_fast_index_creation.test' |
1097 | --- mysql-test/t/percona_expand_fast_index_creation.test 1970-01-01 00:00:00 +0000 |
1098 | +++ mysql-test/t/percona_expand_fast_index_creation.test 2012-12-14 14:59:00 +0000 |
1099 | @@ -0,0 +1,70 @@ |
1100 | +###################################################################### |
1101 | +# Expand Fast Index Creation test |
1102 | +# Here we perform some DDL and ensure that Expand Fast Index Creation |
1103 | +# worked correctly |
1104 | +###################################################################### |
1105 | + |
1106 | +SET expand_fast_index_creation=ON; |
1107 | +CREATE TABLE idx (a INT PRIMARY KEY); |
1108 | +CREATE TABLE t (a INT PRIMARY KEY AUTO_INCREMENT, b FLOAT, c VARCHAR(15), d VARCHAR(17), z VARCHAR(1)) ENGINE=InnoDB; |
1109 | +INSERT INTO t (b) VALUES (RAND()); |
1110 | +INSERT INTO t (b) SELECT RAND() FROM t; |
1111 | +INSERT INTO t (b) SELECT RAND() FROM t; |
1112 | +INSERT INTO t (b) SELECT RAND() FROM t; |
1113 | +INSERT INTO t (b) SELECT RAND() FROM t; |
1114 | +INSERT INTO t (b) SELECT RAND() FROM t; |
1115 | +INSERT INTO t (b) SELECT RAND() FROM t; |
1116 | +INSERT INTO t (b) SELECT RAND() FROM t; |
1117 | +ALTER TABLE t ADD KEY k1(b), ADD KEY k2(a,b), ADD KEY k3(a,b,d), ADD UNIQUE KEY k4(a,c,d); |
1118 | + |
1119 | +# remember InnoDB ID's of INDEXES for t |
1120 | +INSERT INTO idx (a) SELECT i.INDEX_ID FROM |
1121 | + INFORMATION_SCHEMA.INNODB_SYS_INDEXES i, |
1122 | + INFORMATION_SCHEMA.INNODB_SYS_TABLES t |
1123 | +WHERE |
1124 | + t.NAME = 'test/t' AND i.TABLE_ID = t.TABLE_ID; |
1125 | + |
1126 | +# confirm that there are two indexes |
1127 | +SELECT i.NAME, i.TYPE, i.N_FIELDS, f.NAME, f.POS FROM |
1128 | + INFORMATION_SCHEMA.INNODB_SYS_FIELDS f, |
1129 | + INFORMATION_SCHEMA.INNODB_SYS_INDEXES i, |
1130 | + INFORMATION_SCHEMA.INNODB_SYS_TABLES t |
1131 | +WHERE |
1132 | + t.NAME = 'test/t' AND i.TABLE_ID = t.TABLE_ID AND |
1133 | + f.INDEX_ID = i.INDEX_ID |
1134 | +ORDER BY |
1135 | + i.NAME, f.NAME; |
1136 | + |
1137 | +# perform alter |
1138 | +SET profiling=1; |
1139 | +ALTER TABLE t MODIFY COLUMN z VARCHAR(2); |
1140 | +SET profiling=0; |
1141 | +# make sure expand fast index creation worked |
1142 | +SELECT COUNT(*) FROM INFORMATION_SCHEMA.PROFILING |
1143 | + WHERE STATE = 'restoring secondary keys' AND |
1144 | + QUERY_ID = (SELECT MAX(QUERY_ID) FROM INFORMATION_SCHEMA.PROFILING); |
1145 | + |
1146 | +# remember new index ID's |
1147 | +INSERT INTO idx (a) SELECT i.INDEX_ID FROM |
1148 | + INFORMATION_SCHEMA.INNODB_SYS_INDEXES i, |
1149 | + INFORMATION_SCHEMA.INNODB_SYS_TABLES t |
1150 | +WHERE |
1151 | + t.NAME = 'test/t' AND i.TABLE_ID = t.TABLE_ID; |
1152 | + |
1153 | +SET profiling=1; |
1154 | +# confirm that there are two indexes |
1155 | +SELECT i.NAME, i.TYPE, i.N_FIELDS, f.NAME, f.POS FROM |
1156 | + INFORMATION_SCHEMA.INNODB_SYS_FIELDS f, |
1157 | + INFORMATION_SCHEMA.INNODB_SYS_INDEXES i, |
1158 | + INFORMATION_SCHEMA.INNODB_SYS_TABLES t |
1159 | +WHERE |
1160 | + t.NAME = 'test/t' AND i.TABLE_ID = t.TABLE_ID AND |
1161 | + f.INDEX_ID = i.INDEX_ID |
1162 | +ORDER BY |
1163 | + i.NAME, f.NAME; |
1164 | + |
1165 | +# confirm that there are four different ID's |
1166 | +SELECT COUNT(DISTINCT a) FROM idx; |
1167 | + |
1168 | +DROP TABLE idx; |
1169 | +DROP TABLE t; |
1170 | |
1171 | === added file 'mysql-test/t/percona_mysqldump_innodb_optimize_keys.test' |
1172 | --- mysql-test/t/percona_mysqldump_innodb_optimize_keys.test 1970-01-01 00:00:00 +0000 |
1173 | +++ mysql-test/t/percona_mysqldump_innodb_optimize_keys.test 2012-12-14 14:59:00 +0000 |
1174 | @@ -0,0 +1,227 @@ |
1175 | +# Embedded server doesn't support external clients |
1176 | +--source include/not_embedded.inc |
1177 | + |
1178 | +# Fast index creation is only available in InnoDB plugin |
1179 | +--source include/have_innodb.inc |
1180 | + |
1181 | +# Save the initial number of concurrent sessions |
1182 | +--source include/count_sessions.inc |
1183 | + |
1184 | +--echo # |
1185 | +--echo # Test the --innodb-optimize-keys option. |
1186 | +--echo # |
1187 | + |
1188 | +--let $file=$MYSQLTEST_VARDIR/tmp/t1.sql |
1189 | + |
1190 | +# First test that the option has no effect on non-InnoDB tables |
1191 | + |
1192 | +CREATE TABLE t1 (a INT NOT NULL PRIMARY KEY, b INT, KEY(b)) ENGINE=MyISAM; |
1193 | + |
1194 | +--exec $MYSQL_DUMP --skip-comments --innodb-optimize-keys test t1 >$file |
1195 | + |
1196 | +--echo ###################################### |
1197 | +--cat_file $file |
1198 | +--echo ###################################### |
1199 | + |
1200 | +--remove_file $file |
1201 | + |
1202 | +DROP TABLE t1; |
1203 | + |
1204 | +# Check that for InnoDB tables secondary keys are created after the data is |
1205 | +# dumped but foreign ones are left in CREATE TABLE |
1206 | + |
1207 | +CREATE TABLE t2 (a INT PRIMARY KEY) ENGINE=InnoDB; |
1208 | +INSERT INTO t2 VALUES (0), (1), (2); |
1209 | + |
1210 | +CREATE TABLE t1 ( |
1211 | + id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, |
1212 | + a INT, b VARCHAR(255), c DECIMAL(10,3), |
1213 | + KEY (b), |
1214 | + UNIQUE KEY uniq(c,a), |
1215 | + FOREIGN KEY (a) REFERENCES t2(a) ON DELETE CASCADE |
1216 | +) ENGINE=InnoDB; |
1217 | + |
1218 | +INSERT INTO t1(a,b,c) VALUES (0, "0", 0.0), (1, "1", 1.1), (2, "2", 2.2); |
1219 | + |
1220 | +--exec $MYSQL_DUMP --skip-comments --innodb-optimize-keys test t1 t2 >$file |
1221 | + |
1222 | +--echo ###################################### |
1223 | +--cat_file $file |
1224 | +--echo ###################################### |
1225 | + |
1226 | +# Check that the resulting dump can be imported back |
1227 | + |
1228 | +--exec $MYSQL test < $file |
1229 | + |
1230 | +--remove_file $file |
1231 | + |
1232 | +DROP TABLE t1, t2; |
1233 | + |
1234 | +######################################################################## |
1235 | +# Bug #812179: AUTO_INCREMENT columns must be skipped by the |
1236 | +# --innodb-optimize-keys optimization in mysqldump |
1237 | +######################################################################## |
1238 | + |
1239 | +CREATE TABLE t1 ( |
1240 | + id INT NOT NULL AUTO_INCREMENT, |
1241 | + KEY (id) |
1242 | +) ENGINE=InnoDB; |
1243 | + |
1244 | +CREATE TABLE t2 ( |
1245 | + id INT NOT NULL AUTO_INCREMENT, |
1246 | + UNIQUE KEY (id) |
1247 | +) ENGINE=InnoDB; |
1248 | + |
1249 | +INSERT INTO t1 VALUES (), (), (); |
1250 | +INSERT INTO t2 VALUES (), (), (); |
1251 | + |
1252 | +--exec $MYSQL_DUMP --skip-comments --innodb-optimize-keys test t1 t2 >$file |
1253 | + |
1254 | +--echo ###################################### |
1255 | +--cat_file $file |
1256 | +--echo ###################################### |
1257 | + |
1258 | +# Check that the resulting dump can be imported back |
1259 | + |
1260 | +--exec $MYSQL test < $file |
1261 | + |
1262 | +--remove_file $file |
1263 | + |
1264 | +DROP TABLE t1, t2; |
1265 | + |
1266 | +######################################################################## |
1267 | +# Bug #851674: --innodb-optimize-keys does not work correctly with table |
1268 | +# without PRIMARY KEY |
1269 | +######################################################################## |
1270 | + |
1271 | +CREATE TABLE t1 ( |
1272 | + a INT NOT NULL, |
1273 | + UNIQUE KEY (a)) ENGINE=InnoDB; |
1274 | + |
1275 | +CREATE TABLE t2 ( |
1276 | + a INT NOT NULL, |
1277 | + b INT NOT NULL, |
1278 | + UNIQUE KEY (a,b)) ENGINE=InnoDB; |
1279 | + |
1280 | +CREATE TABLE t3 ( |
1281 | + a INT, |
1282 | + b INT, |
1283 | + UNIQUE KEY (a,b)) ENGINE=InnoDB; |
1284 | + |
1285 | +CREATE TABLE t4 ( |
1286 | + a INT NOT NULL, |
1287 | + b INT NOT NULL, |
1288 | + PRIMARY KEY (a,b), |
1289 | + UNIQUE KEY(b)) ENGINE=InnoDB; |
1290 | + |
1291 | +SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE |
1292 | + TABLE_SCHEMA=DATABASE() AND |
1293 | + TABLE_NAME='t1' AND |
1294 | + COLUMN_KEY='PRI'; |
1295 | +SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE |
1296 | + TABLE_SCHEMA=DATABASE() AND |
1297 | + TABLE_NAME='t2' AND |
1298 | + COLUMN_KEY='PRI'; |
1299 | +SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE |
1300 | + TABLE_SCHEMA=DATABASE() AND |
1301 | + TABLE_NAME='t3' AND |
1302 | + COLUMN_KEY='PRI'; |
1303 | +SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE |
1304 | + TABLE_SCHEMA=DATABASE() AND |
1305 | + TABLE_NAME='t4' AND |
1306 | + COLUMN_KEY='PRI'; |
1307 | + |
1308 | +INSERT INTO t1 VALUES (1), (2), (3); |
1309 | +INSERT INTO t2 VALUES (1,1), (2,2), (3,3); |
1310 | +INSERT INTO t3 SELECT * FROM t2; |
1311 | +INSERT INTO t4 SELECT * FROM t2; |
1312 | + |
1313 | +--exec $MYSQL_DUMP --skip-comments --innodb-optimize-keys test t1 t2 t3 t4 >$file |
1314 | + |
1315 | +--echo ###################################### |
1316 | +--cat_file $file |
1317 | +--echo ###################################### |
1318 | + |
1319 | +# Check that the resulting dump can be imported back |
1320 | + |
1321 | +--exec $MYSQL test < $file |
1322 | + |
1323 | +--remove_file $file |
1324 | + |
1325 | +DROP TABLE t1, t2, t3, t4; |
1326 | + |
1327 | +######################################################################## |
1328 | +# Bug #859078: --innodb-optimize-keys should ignore foreign keys |
1329 | +######################################################################## |
1330 | + |
1331 | +CREATE TABLE t1 ( |
1332 | + id INT NOT NULL PRIMARY KEY |
1333 | +) ENGINE=InnoDB; |
1334 | + |
1335 | +CREATE TABLE t2 ( |
1336 | + id INT NOT NULL AUTO_INCREMENT, |
1337 | + a INT NOT NULL, |
1338 | + PRIMARY KEY (id), |
1339 | + KEY (a), |
1340 | + FOREIGN KEY (a) REFERENCES t2 (id) |
1341 | +) ENGINE=InnoDB; |
1342 | + |
1343 | +INSERT INTO t1 VALUES (1), (2), (3); |
1344 | +INSERT INTO t2 VALUES (1, 1), (2, 2), (3, 3); |
1345 | + |
1346 | +--exec $MYSQL_DUMP --skip-comments --innodb-optimize-keys test t1 t2 >$file |
1347 | + |
1348 | +--echo ###################################### |
1349 | +--cat_file $file |
1350 | +--echo ###################################### |
1351 | + |
1352 | +# Check that the resulting dump can be imported back |
1353 | + |
1354 | +--exec $MYSQL test < $file |
1355 | + |
1356 | +--remove_file $file |
1357 | + |
1358 | +DROP TABLE t1, t2; |
1359 | + |
1360 | +######################################################################## |
1361 | +# Bug #1039536: mysqldump --innodb-optimize-keys can generate invalid table |
1362 | +# definitions |
1363 | +######################################################################## |
1364 | + |
1365 | +CREATE TABLE t1 ( |
1366 | + id INT NOT NULL AUTO_INCREMENT, |
1367 | + uid INT NOT NULL, |
1368 | + `id``` INT NOT NULL, |
1369 | + ```id` INT NOT NULL, |
1370 | + # The following ones may be skipped and used in ALTER TABLE later |
1371 | + KEY k1 (```id`, id), |
1372 | + KEY k2 (```id`, `id```), |
1373 | + # The following one should be kept in CREATE TABLE |
1374 | + KEY k3 (id, uid), |
1375 | + # The following one may be skipped again |
1376 | + KEY k4 (id, `id```) |
1377 | +) ENGINE=InnoDB; |
1378 | + |
1379 | +CREATE TABLE t2 ( |
1380 | + id INT NOT NULL AUTO_INCREMENT, |
1381 | + PRIMARY KEY (id), |
1382 | + KEY k1 (id), |
1383 | + KEY k2 (id) |
1384 | +) ENGINE=InnoDB; |
1385 | + |
1386 | +--exec $MYSQL_DUMP --skip-comments --innodb-optimize-keys test t1 t2 >$file |
1387 | + |
1388 | +--echo ###################################### |
1389 | +--cat_file $file |
1390 | +--echo ###################################### |
1391 | + |
1392 | +# Check that the resulting dump can be imported back |
1393 | + |
1394 | +--exec $MYSQL test < $file |
1395 | + |
1396 | +--remove_file $file |
1397 | + |
1398 | +DROP TABLE t1, t2; |
1399 | + |
1400 | +# Wait till we reached the initial number of concurrent sessions |
1401 | +--source include/wait_until_count_sessions.inc |
1402 | |
1403 | === modified file 'sql/handler.cc' |
1404 | --- sql/handler.cc 2012-11-08 21:40:31 +0000 |
1405 | +++ sql/handler.cc 2012-12-14 14:59:00 +0000 |
1406 | @@ -4259,12 +4259,14 @@ |
1407 | The exception is if we're about to roll back changes (commit= false). |
1408 | In this case, we might be rolling back after a failed lock upgrade, |
1409 | so we could be holding the same lock level as for inplace_alter_table(). |
1410 | + TABLE::mdl_ticket is 0 for temporary tables. |
1411 | */ |
1412 | - DBUG_ASSERT(ha_thd()->mdl_context.is_lock_owner(MDL_key::TABLE, |
1413 | + DBUG_ASSERT((table->s->tmp_table != NO_TMP_TABLE && !table->mdl_ticket) || |
1414 | + (ha_thd()->mdl_context.is_lock_owner(MDL_key::TABLE, |
1415 | table->s->db.str, |
1416 | table->s->table_name.str, |
1417 | MDL_EXCLUSIVE) || |
1418 | - !commit); |
1419 | + !commit)); |
1420 | |
1421 | return commit_inplace_alter_table(altered_table, ha_alter_info, commit); |
1422 | } |
1423 | |
1424 | === modified file 'sql/sql_alter.cc' |
1425 | --- sql/sql_alter.cc 2012-08-02 20:43:29 +0000 |
1426 | +++ sql/sql_alter.cc 2012-12-14 14:59:00 +0000 |
1427 | @@ -25,6 +25,9 @@ |
1428 | alter_list(rhs.alter_list, mem_root), |
1429 | key_list(rhs.key_list, mem_root), |
1430 | create_list(rhs.create_list, mem_root), |
1431 | + delayed_key_list(rhs.delayed_key_list, mem_root), |
1432 | + delayed_key_info(rhs.delayed_key_info), |
1433 | + delayed_key_count(rhs.delayed_key_count), |
1434 | flags(rhs.flags), |
1435 | keys_onoff(rhs.keys_onoff), |
1436 | partition_names(rhs.partition_names, mem_root), |
1437 | @@ -45,6 +48,7 @@ |
1438 | list_copy_and_replace_each_value(alter_list, mem_root); |
1439 | list_copy_and_replace_each_value(key_list, mem_root); |
1440 | list_copy_and_replace_each_value(create_list, mem_root); |
1441 | + list_copy_and_replace_each_value(delayed_key_list, mem_root); |
1442 | /* partition_names are not deeply copied currently */ |
1443 | } |
1444 | |
1445 | |
1446 | === modified file 'sql/sql_alter.h' |
1447 | --- sql/sql_alter.h 2012-08-02 20:43:29 +0000 |
1448 | +++ sql/sql_alter.h 2012-12-14 14:59:00 +0000 |
1449 | @@ -171,6 +171,12 @@ |
1450 | List<Key> key_list; |
1451 | // List of columns, used by both CREATE and ALTER TABLE. |
1452 | List<Create_field> create_list; |
1453 | + // List of keys, which creation is delayed to benefit from fast index creation |
1454 | + List<Key> delayed_key_list; |
1455 | + // Keys, which creation is delayed to benefit from fast index creation |
1456 | + KEY *delayed_key_info; |
1457 | + // Count of keys, which creation is delayed to benefit from fast index creation |
1458 | + uint delayed_key_count; |
1459 | // Type of ALTER TABLE operation. |
1460 | uint flags; |
1461 | // Enable or disable keys. |
1462 | |
1463 | === modified file 'sql/sql_class.h' |
1464 | --- sql/sql_class.h 2012-11-08 21:40:31 +0000 |
1465 | +++ sql/sql_class.h 2012-12-14 14:59:00 +0000 |
1466 | @@ -546,6 +546,7 @@ |
1467 | Gtid_specification gtid_next; |
1468 | Gtid_set_or_null gtid_next_list; |
1469 | |
1470 | + my_bool expand_fast_index_creation; |
1471 | } SV; |
1472 | |
1473 | |
1474 | |
1475 | === modified file 'sql/sql_table.cc' |
1476 | --- sql/sql_table.cc 2012-11-06 13:36:32 +0000 |
1477 | +++ sql/sql_table.cc 2012-12-14 14:59:00 +0000 |
1478 | @@ -3655,6 +3655,14 @@ |
1479 | if (!*key_info_buffer || ! key_part_info) |
1480 | DBUG_RETURN(TRUE); // Out of memory |
1481 | |
1482 | + List_iterator<Key> delayed_key_iterator(alter_info->delayed_key_list); |
1483 | + alter_info->delayed_key_count= 0; |
1484 | + if (alter_info->delayed_key_list.elements > 0) |
1485 | + { |
1486 | + alter_info->delayed_key_info= (KEY *) sql_calloc(sizeof(KEY) * |
1487 | + (*key_count)); |
1488 | + } |
1489 | + |
1490 | key_iterator.rewind(); |
1491 | key_number=0; |
1492 | for (; (key=key_iterator++) ; key_number++) |
1493 | @@ -4066,6 +4074,22 @@ |
1494 | // Check if a duplicate index is defined. |
1495 | check_duplicate_key(thd, key, key_info, &alter_info->key_list); |
1496 | |
1497 | + if (alter_info->delayed_key_list.elements > 0) |
1498 | + { |
1499 | + Key *delayed_key; |
1500 | + |
1501 | + delayed_key_iterator.rewind(); |
1502 | + while ((delayed_key= delayed_key_iterator++)) |
1503 | + { |
1504 | + if (delayed_key == key) |
1505 | + { |
1506 | + alter_info->delayed_key_info[alter_info->delayed_key_count++]= |
1507 | + *key_info; |
1508 | + break; |
1509 | + } |
1510 | + } |
1511 | + } |
1512 | + |
1513 | key_info++; |
1514 | } |
1515 | |
1516 | @@ -6557,6 +6581,10 @@ |
1517 | List<Create_field> new_create_list; |
1518 | /* New key definitions are added here */ |
1519 | List<Key> new_key_list; |
1520 | + /* List with secondary keys which should be created after copying the data */ |
1521 | + List<Key> delayed_key_list; |
1522 | + /* Foreign key list returned by handler::get_foreign_key_list() */ |
1523 | + List<FOREIGN_KEY_INFO> f_key_list; |
1524 | List_iterator<Alter_drop> drop_it(alter_info->drop_list); |
1525 | List_iterator<Create_field> def_it(alter_info->create_list); |
1526 | List_iterator<Alter_column> alter_it(alter_info->alter_list); |
1527 | @@ -6569,6 +6597,7 @@ |
1528 | uint used_fields= create_info->used_fields; |
1529 | KEY *key_info=table->key_info; |
1530 | bool rc= TRUE; |
1531 | + bool skip_secondary; |
1532 | |
1533 | DBUG_ENTER("mysql_prepare_alter_table"); |
1534 | |
1535 | @@ -6785,8 +6814,26 @@ |
1536 | /* |
1537 | Collect all keys which isn't in drop list. Add only those |
1538 | for which some fields exists. |
1539 | + |
1540 | + We also store secondary keys in delayed_key_list to make use of |
1541 | + the InnoDB fast index creation. The following conditions must be |
1542 | + met: |
1543 | + |
1544 | + - fast_index_creation is enabled for the current session |
1545 | + - expand_fast_index_creation is enabled for the current session; |
1546 | + - we are going to create an InnoDB table (this is checked later when the |
1547 | + target engine is known); |
1548 | + - the key most be a non-UNIQUE one; |
1549 | + - there are no foreign keys. This can be optimized later to exclude only |
1550 | + those keys which are a part of foreign key constraints. Currently we |
1551 | + simply disable this optimization for all keys if there are any foreign |
1552 | + key constraints in the table. |
1553 | */ |
1554 | |
1555 | + skip_secondary= thd->variables.expand_fast_index_creation && |
1556 | + !table->file->get_foreign_key_list(thd, &f_key_list) && |
1557 | + f_key_list.elements == 0; |
1558 | + |
1559 | for (uint i=0 ; i < table->s->keys ; i++,key_info++) |
1560 | { |
1561 | char *key_name= key_info->name; |
1562 | @@ -6908,6 +6955,10 @@ |
1563 | test(key_info->flags & HA_GENERATED_KEY), |
1564 | key_parts); |
1565 | new_key_list.push_back(key); |
1566 | + |
1567 | + if (skip_secondary && key_type == Key::MULTIPLE) { |
1568 | + delayed_key_list.push_back(key); |
1569 | + } |
1570 | } |
1571 | } |
1572 | { |
1573 | @@ -6915,6 +6966,23 @@ |
1574 | while ((key=key_it++)) // Add new keys |
1575 | { |
1576 | new_key_list.push_back(key); |
1577 | + |
1578 | + if (key->type != Key::FOREIGN_KEY) |
1579 | + { |
1580 | + if (skip_secondary && key->type == Key::MULTIPLE) { |
1581 | + delayed_key_list.push_back(key); |
1582 | + } |
1583 | + } |
1584 | + else if (skip_secondary) |
1585 | + { |
1586 | + /* |
1587 | + We are adding a foreign key so disable the secondary keys |
1588 | + optimization. |
1589 | + */ |
1590 | + skip_secondary= FALSE; |
1591 | + delayed_key_list.empty(); |
1592 | + } |
1593 | + |
1594 | if (key->name.str && |
1595 | !my_strcasecmp(system_charset_info, key->name.str, primary_key_name)) |
1596 | { |
1597 | @@ -6982,6 +7050,7 @@ |
1598 | rc= FALSE; |
1599 | alter_info->create_list.swap(new_create_list); |
1600 | alter_info->key_list.swap(new_key_list); |
1601 | + alter_info->delayed_key_list.swap(delayed_key_list); |
1602 | err: |
1603 | DBUG_RETURN(rc); |
1604 | } |
1605 | @@ -7414,6 +7483,131 @@ |
1606 | } |
1607 | |
1608 | |
1609 | +/* |
1610 | + Temporarily remove secondary keys previously stored in |
1611 | + alter_info->delayed_key_info. |
1612 | +*/ |
1613 | +static int |
1614 | +remove_secondary_keys(THD *thd, HA_CREATE_INFO* create_info, TABLE *table, |
1615 | + Alter_info *alter_info) |
1616 | +{ |
1617 | + uint i; |
1618 | + DBUG_ENTER("remove_secondary_keys"); |
1619 | + DBUG_ASSERT(alter_info->delayed_key_count > 0); |
1620 | + |
1621 | + /* |
1622 | + We need to mark all fields for read and write as being done in |
1623 | + mysql_alter_table. |
1624 | + */ |
1625 | + table->use_all_columns(); |
1626 | + |
1627 | + /* |
1628 | + Create Alter_info for the table and fill create_list with fields |
1629 | + definitions. Not that fields not changed, so we set field==ogrig_field. |
1630 | + */ |
1631 | + Alter_info alter_info_new; |
1632 | + Field **f_ptr, *field; |
1633 | + |
1634 | + for (f_ptr= table->field; (field= *f_ptr); f_ptr++) |
1635 | + { |
1636 | + Create_field* new_field= new Create_field(field, field); |
1637 | + alter_info_new.create_list.push_back(new_field); |
1638 | + } |
1639 | + |
1640 | + Alter_inplace_info ha_alter_info(create_info, &alter_info_new, |
1641 | + table->key_info, table->s->keys, |
1642 | +#ifdef WITH_PARTITION_STORAGE_ENGINE |
1643 | + thd->work_part_info, |
1644 | +#else |
1645 | + NULL, |
1646 | +#endif |
1647 | + FALSE); |
1648 | + |
1649 | + ha_alter_info.handler_flags= Alter_inplace_info::DROP_INDEX; |
1650 | + ha_alter_info.index_drop_count= alter_info->delayed_key_count; |
1651 | + |
1652 | + /* Fill index_drop_buffer with keys to drop */ |
1653 | + ha_alter_info.index_drop_buffer= (KEY**) thd->alloc(sizeof(KEY*) * |
1654 | + alter_info->delayed_key_count); |
1655 | + for (i= 0; i < alter_info->delayed_key_count; i++) |
1656 | + ha_alter_info.index_drop_buffer[i] = &(alter_info->delayed_key_info[i]); |
1657 | + |
1658 | + if (table->file->check_if_supported_inplace_alter(table, &ha_alter_info) == |
1659 | + HA_ALTER_INPLACE_NOT_SUPPORTED) |
1660 | + DBUG_RETURN(-1); |
1661 | + |
1662 | + if (table->file->ha_prepare_inplace_alter_table(table, &ha_alter_info) || |
1663 | + table->file->ha_inplace_alter_table(table, &ha_alter_info) || |
1664 | + table->file->ha_commit_inplace_alter_table(table, &ha_alter_info, true)) |
1665 | + { |
1666 | + table->file->ha_commit_inplace_alter_table(table, &ha_alter_info, false); |
1667 | + DBUG_RETURN(-1); |
1668 | + } |
1669 | + |
1670 | + DBUG_RETURN(0); |
1671 | +} |
1672 | + |
1673 | +/* |
1674 | + Restore secondary keys previously removed in remove_secondary_keys. |
1675 | +*/ |
1676 | + |
1677 | +static int |
1678 | +restore_secondary_keys(THD *thd, HA_CREATE_INFO* create_info, TABLE *table, |
1679 | + Alter_info *alter_info) |
1680 | +{ |
1681 | + uint i; |
1682 | + DBUG_ENTER("restore_secondary_keys"); |
1683 | + DBUG_ASSERT(alter_info->delayed_key_count > 0); |
1684 | + |
1685 | + thd_proc_info(thd, "restoring secondary keys"); |
1686 | + |
1687 | + /* |
1688 | + Create Alter_info for the table and fill create_list with fields |
1689 | + definitions. Not that fields not changed, so we set field==ogrig_field. |
1690 | + */ |
1691 | + Alter_info alter_info_new; |
1692 | + Field **f_ptr, *field; |
1693 | + |
1694 | + for (f_ptr= table->field; (field= *f_ptr); f_ptr++) |
1695 | + { |
1696 | + Create_field* new_field= new Create_field(field, field); |
1697 | + alter_info_new.create_list.push_back(new_field); |
1698 | + } |
1699 | + |
1700 | + Alter_inplace_info ha_alter_info(create_info, &alter_info_new, |
1701 | + alter_info->delayed_key_info, table->s->keys, |
1702 | +#ifdef WITH_PARTITION_STORAGE_ENGINE |
1703 | + thd->work_part_info, |
1704 | +#else |
1705 | + NULL, |
1706 | +#endif |
1707 | + FALSE); |
1708 | + |
1709 | + ha_alter_info.handler_flags= Alter_inplace_info::ADD_INDEX; |
1710 | + ha_alter_info.index_add_count= alter_info->delayed_key_count; |
1711 | + |
1712 | + ha_alter_info.index_add_buffer= (uint*) thd->alloc(sizeof(uint) * |
1713 | + alter_info->delayed_key_count); |
1714 | + |
1715 | + /* Fill index_add_buffer with key indexes from key_info_buffer */ |
1716 | + for (i= 0; i < alter_info->delayed_key_count; i++) |
1717 | + ha_alter_info.index_add_buffer[i] = i; |
1718 | + |
1719 | + if (table->file->check_if_supported_inplace_alter(table, &ha_alter_info) == |
1720 | + HA_ALTER_INPLACE_NOT_SUPPORTED) |
1721 | + DBUG_RETURN(-1); |
1722 | + |
1723 | + if (table->file->ha_prepare_inplace_alter_table(table, &ha_alter_info) || |
1724 | + table->file->ha_inplace_alter_table(table, &ha_alter_info) || |
1725 | + table->file->ha_commit_inplace_alter_table(table, &ha_alter_info, true)) |
1726 | + { |
1727 | + table->file->ha_commit_inplace_alter_table(table, &ha_alter_info, false); |
1728 | + DBUG_RETURN(-1); |
1729 | + } |
1730 | + |
1731 | + DBUG_RETURN(0); |
1732 | +} |
1733 | + |
1734 | /** |
1735 | Alter table |
1736 | |
1737 | @@ -8108,18 +8302,39 @@ |
1738 | */ |
1739 | if (!(new_table->file->ha_table_flags() & HA_NO_COPY_ON_ALTER)) |
1740 | { |
1741 | + |
1742 | + /* |
1743 | + Check if we can temporarily remove secondary indexes from the table |
1744 | + before copying the data and recreate them later to utilize InnoDB fast |
1745 | + index creation. |
1746 | + TODO: is there a better way to check for InnoDB? |
1747 | + */ |
1748 | + bool optimize_keys= (alter_info->delayed_key_count > 0) && |
1749 | + !my_strcasecmp(system_charset_info, |
1750 | + new_table->file->table_type(), "InnoDB"); |
1751 | new_table->next_number_field=new_table->found_next_number_field; |
1752 | THD_STAGE_INFO(thd, stage_copy_to_tmp_table); |
1753 | DBUG_EXECUTE_IF("abort_copy_table", { |
1754 | my_error(ER_LOCK_WAIT_TIMEOUT, MYF(0)); |
1755 | goto err_new_table_cleanup; |
1756 | }); |
1757 | + |
1758 | + if (optimize_keys) |
1759 | + { |
1760 | + /* ignore the error */ |
1761 | + remove_secondary_keys(thd, create_info, new_table, alter_info); |
1762 | + } |
1763 | + |
1764 | if (copy_data_between_tables(table, new_table, |
1765 | alter_info->create_list, ignore, |
1766 | order_num, order, &copied, &deleted, |
1767 | alter_info->keys_onoff, |
1768 | &alter_ctx)) |
1769 | goto err_new_table_cleanup; |
1770 | + |
1771 | + if (optimize_keys) |
1772 | + if (restore_secondary_keys(thd, create_info, new_table, alter_info)) |
1773 | + goto err_new_table_cleanup; |
1774 | } |
1775 | else |
1776 | { |
1777 | |
1778 | === modified file 'sql/sys_vars.cc' |
1779 | --- sql/sys_vars.cc 2012-10-30 16:26:13 +0000 |
1780 | +++ sql/sys_vars.cc 2012-12-14 14:59:00 +0000 |
1781 | @@ -1279,6 +1279,14 @@ |
1782 | ON_CHECK(event_scheduler_check), ON_UPDATE(event_scheduler_update)); |
1783 | #endif |
1784 | |
1785 | +static Sys_var_mybool Sys_expand_fast_index_creation( |
1786 | + "expand_fast_index_creation", |
1787 | + "Enable/disable improvements to the InnoDB fast index creation " |
1788 | + "functionality. Has no effect when fast index creation is disabled with " |
1789 | + "the fast-index-creation option", |
1790 | + SESSION_VAR(expand_fast_index_creation), CMD_LINE(OPT_ARG), |
1791 | + DEFAULT(FALSE)); |
1792 | + |
1793 | static Sys_var_ulong Sys_expire_logs_days( |
1794 | "expire_logs_days", |
1795 | "If non-zero, binary logs will be purged after expire_logs_days " |
1796 | |
1797 | === modified file 'storage/innobase/handler/ha_innodb.cc' |
1798 | --- storage/innobase/handler/ha_innodb.cc 2012-10-31 06:26:56 +0000 |
1799 | +++ storage/innobase/handler/ha_innodb.cc 2012-12-14 14:59:00 +0000 |
1800 | @@ -31,6 +31,8 @@ |
1801 | 51 Franklin Street, Suite 500, Boston, MA 02110-1335 USA |
1802 | |
1803 | *****************************************************************************/ |
1804 | + |
1805 | +#define MYSQL_SERVER |
1806 | |
1807 | #include <sql_table.h> // explain_filename, nz2, EXPLAIN_PARTITIONS_AS_COMMENT, |
1808 | // EXPLAIN_FILENAME_MAX_EXTRA_LENGTH |
1809 | @@ -1269,6 +1271,18 @@ |
1810 | } |
1811 | } |
1812 | |
1813 | +/******************************************************************//** |
1814 | +Returns true if expand_fast_index_creation is enabled for the current |
1815 | +session. |
1816 | +@return the value of the server's expand_fast_index_creation variable */ |
1817 | +ibool |
1818 | +thd_expand_fast_index_creation( |
1819 | +/*================================*/ |
1820 | + void* thd) |
1821 | +{ |
1822 | + return((ibool) (((THD*) thd)->variables.expand_fast_index_creation)); |
1823 | +} |
1824 | + |
1825 | /********************************************************************//** |
1826 | Obtain the InnoDB transaction of a MySQL thread. |
1827 | @return reference to transaction pointer */ |
1828 | |
1829 | === modified file 'storage/innobase/handler/handler0alter.cc' |
1830 | --- storage/innobase/handler/handler0alter.cc 2012-11-07 07:03:05 +0000 |
1831 | +++ storage/innobase/handler/handler0alter.cc 2012-12-14 14:59:00 +0000 |
1832 | @@ -2985,6 +2985,8 @@ |
1833 | |
1834 | ut_a(trx->lock.n_active_thrs == 0); |
1835 | |
1836 | + DBUG_EXECUTE_IF("crash_innodb_add_index_after", DBUG_SUICIDE();); |
1837 | + |
1838 | error_handling: |
1839 | /* After an error, remove all those index definitions from the |
1840 | dictionary which were defined. */ |
1841 | |
1842 | === modified file 'storage/innobase/include/ha_prototypes.h' |
1843 | --- storage/innobase/include/ha_prototypes.h 2012-08-30 07:46:21 +0000 |
1844 | +++ storage/innobase/include/ha_prototypes.h 2012-12-14 14:59:00 +0000 |
1845 | @@ -533,6 +533,16 @@ |
1846 | /*=================*/ |
1847 | int error_code); /*!< in: MySQL error code */ |
1848 | |
1849 | +/******************************************************************//** |
1850 | +Returns true if innodb_expand_fast_index_creation is enabled for the current |
1851 | +session. |
1852 | +@return the value of the server's innodb_expand_fast_index_creation variable */ |
1853 | + |
1854 | +ibool |
1855 | +thd_expand_fast_index_creation( |
1856 | +/*===========================*/ |
1857 | + void* thd); /*!< in: thread handle (THD*) */ |
1858 | + |
1859 | /*********************************************************************//** |
1860 | Compute the next autoinc value. |
1861 |