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