Merge lp:~percona-dev/percona-server/5.5-bug744103 into lp:~percona-dev/percona-server/5.5.10
- 5.5-bug744103
- Merge into 5.5.10
Proposed by
Alexey Kopytov
Status: | Merged |
---|---|
Merged at revision: | 94 |
Proposed branch: | lp:~percona-dev/percona-server/5.5-bug744103 |
Merge into: | lp:~percona-dev/percona-server/5.5.10 |
Diff against target: |
731 lines (+719/-0) 2 files modified
innodb_expand_fast_index_creation.patch (+717/-0) series (+2/-0) |
To merge this branch: | bzr merge lp:~percona-dev/percona-server/5.5-bug744103 |
Related bugs: |
Reviewer | Review Type | Date Requested | Status |
---|---|---|---|
Fred Linhoss (community) | documentation | Approve | |
Vadim Tkachenko | Approve | ||
Review via email: mp+55780@code.launchpad.net |
Commit message
Description of the change
To post a comment you must log in.
Revision history for this message
Fred Linhoss (fred-linhoss) wrote : | # |
Documentation in progress; approving MP so as not to hold it up.
review:
Approve
(documentation)
Preview Diff
[H/L] Next/Prev Comment, [J/K] Next/Prev File, [N/P] Next/Prev Hunk
1 | === added file 'innodb_expand_fast_index_creation.patch' |
2 | --- innodb_expand_fast_index_creation.patch 1970-01-01 00:00:00 +0000 |
3 | +++ innodb_expand_fast_index_creation.patch 2011-04-03 13:31:27 +0000 |
4 | @@ -0,0 +1,717 @@ |
5 | +# name : innodb_expand_fast_index_creation.patch |
6 | +# maintainer : Alexey |
7 | +# |
8 | +# Expands the applicability of InnoDB fast index creation to mysqldump, |
9 | +# ALTER TABLE and OPTIMIZE TABLE. |
10 | +# |
11 | +--- a/client/client_priv.h 2011-03-31 13:59:26.000000000 +0400 |
12 | ++++ b/client/client_priv.h 2011-03-31 16:04:28.000000000 +0400 |
13 | +@@ -92,6 +92,7 @@ |
14 | + OPT_NO_REMOVE_EOL_CARRET, |
15 | + OPT_DEFAULT_AUTH, |
16 | + OPT_DEFAULT_PLUGIN, |
17 | ++ OPT_INNODB_OPTIMIZE_KEYS, |
18 | + OPT_MAX_CLIENT_OPTION |
19 | + }; |
20 | + |
21 | +--- a/client/mysqldump.c 2011-03-31 13:59:24.000000000 +0400 |
22 | ++++ b/client/mysqldump.c 2011-03-31 16:05:40.000000000 +0400 |
23 | +@@ -45,6 +45,7 @@ |
24 | + #include <m_ctype.h> |
25 | + #include <hash.h> |
26 | + #include <stdarg.h> |
27 | ++#include <my_list.h> |
28 | + |
29 | + #include "client_priv.h" |
30 | + #include "mysql.h" |
31 | +@@ -141,6 +142,8 @@ |
32 | + |
33 | + static my_bool server_supports_sql_no_fcache= FALSE; |
34 | + |
35 | ++static my_bool opt_innodb_optimize_keys= FALSE; |
36 | ++ |
37 | + /* |
38 | + Dynamic_string wrapper functions. In this file use these |
39 | + wrappers, they will terminate the process if there is |
40 | +@@ -186,6 +189,8 @@ |
41 | + |
42 | + HASH ignore_table; |
43 | + |
44 | ++LIST *skipped_keys_list; |
45 | ++ |
46 | + static struct my_option my_long_options[] = |
47 | + { |
48 | + {"all-databases", 'A', |
49 | +@@ -349,6 +354,11 @@ |
50 | + "in dump produced with --dump-slave.", &opt_include_master_host_port, |
51 | + &opt_include_master_host_port, 0, GET_BOOL, NO_ARG, |
52 | + 0, 0, 0, 0, 0, 0}, |
53 | ++ {"innodb-optimize-keys", OPT_INNODB_OPTIMIZE_KEYS, |
54 | ++ "Use InnoDB fast index creation by creating secondary indexes after " |
55 | ++ "dumping the data.", |
56 | ++ &opt_innodb_optimize_keys, &opt_innodb_optimize_keys, 0, GET_BOOL, NO_ARG, |
57 | ++ 0, 0, 0, 0, 0, 0}, |
58 | + {"insert-ignore", OPT_INSERT_IGNORE, "Insert rows with INSERT IGNORE.", |
59 | + &opt_ignore, &opt_ignore, 0, GET_BOOL, NO_ARG, 0, 0, 0, 0, |
60 | + 0, 0}, |
61 | +@@ -2230,6 +2240,77 @@ |
62 | + } |
63 | + |
64 | + /* |
65 | ++ Remove secondary/foreign key definitions from a given SHOW CREATE TABLE string |
66 | ++ and store them into a temporary list to be used later. |
67 | ++ |
68 | ++ SYNOPSIS |
69 | ++ skip_secondary_keys() |
70 | ++ create_str SHOW CREATE TABLE output |
71 | ++ |
72 | ++ |
73 | ++ DESCRIPTION |
74 | ++ |
75 | ++ Stores all lines starting with "KEY" or "UNIQUE KEY" or "CONSTRAINT" |
76 | ++ into skipped_keys_list and removes them from the input string. |
77 | ++ Ignoring FOREIGN KEYS constraints when creating the table is ok, because |
78 | ++ mysqldump sets foreign_key_checks to 0 anyway. |
79 | ++*/ |
80 | ++ |
81 | ++static void skip_secondary_keys(char *create_str) |
82 | ++{ |
83 | ++ char *ptr, *strend; |
84 | ++ char *last_comma = NULL; |
85 | ++ |
86 | ++ strend= create_str + strlen(create_str); |
87 | ++ |
88 | ++ ptr= create_str; |
89 | ++ while (*ptr) |
90 | ++ { |
91 | ++ char *tmp, *orig_ptr; |
92 | ++ |
93 | ++ orig_ptr= ptr; |
94 | ++ /* Skip leading whitespace */ |
95 | ++ while (*ptr && my_isspace(charset_info, *ptr)) |
96 | ++ ptr++; |
97 | ++ |
98 | ++ /* Read the next line */ |
99 | ++ for (tmp= ptr; *tmp != '\n' && *tmp != '\0'; tmp++); |
100 | ++ |
101 | ++ /* Is it a secondary index definition? */ |
102 | ++ if (*tmp == '\n' && |
103 | ++ (!strncmp(ptr, "UNIQUE KEY ", sizeof("UNIQUE KEY ") - 1) || |
104 | ++ !strncmp(ptr, "KEY ", sizeof("KEY ") - 1) || |
105 | ++ !strncmp(ptr, "CONSTRAINT ", sizeof("CONSTRAINT ") - 1))) |
106 | ++ { |
107 | ++ char *data, *end= tmp - 1; |
108 | ++ |
109 | ++ /* Remove the trailing comma */ |
110 | ++ if (*end == ',') |
111 | ++ end--; |
112 | ++ data= my_strndup(ptr, end - ptr + 1, MYF(MY_FAE)); |
113 | ++ skipped_keys_list= list_cons(data, skipped_keys_list); |
114 | ++ |
115 | ++ memmove(orig_ptr, tmp + 1, strend - tmp); |
116 | ++ ptr= orig_ptr; |
117 | ++ strend-= tmp + 1 - ptr; |
118 | ++ |
119 | ++ /* Remove the comma on the previos line */ |
120 | ++ if (last_comma != NULL) |
121 | ++ { |
122 | ++ *last_comma= ' '; |
123 | ++ last_comma = NULL; |
124 | ++ } |
125 | ++ } |
126 | ++ else |
127 | ++ { |
128 | ++ if (tmp[-1] == ',') |
129 | ++ last_comma= tmp - 1; |
130 | ++ ptr= (*tmp == '\0') ? tmp : tmp + 1; |
131 | ++ } |
132 | ++ } |
133 | ++} |
134 | ++ |
135 | ++/* |
136 | + get_table_structure -- retrievs database structure, prints out corresponding |
137 | + CREATE statement and fills out insert_pat if the table is the type we will |
138 | + be dumping. |
139 | +@@ -2470,6 +2551,9 @@ |
140 | + |
141 | + row= mysql_fetch_row(result); |
142 | + |
143 | ++ if (opt_innodb_optimize_keys && !strcmp(table_type, "InnoDB")) |
144 | ++ skip_secondary_keys(row[1]); |
145 | ++ |
146 | + fprintf(sql_file, (opt_compatible_mode & 3) ? "%s;\n" : |
147 | + "/*!40101 SET @saved_cs_client = @@character_set_client */;\n" |
148 | + "/*!40101 SET character_set_client = utf8 */;\n" |
149 | +@@ -3564,6 +3648,27 @@ |
150 | + goto err; |
151 | + } |
152 | + |
153 | ++ /* Perform delayed secondary index creation for --innodb-optimize-keys */ |
154 | ++ if (skipped_keys_list) |
155 | ++ { |
156 | ++ uint keys; |
157 | ++ skipped_keys_list= list_reverse(skipped_keys_list); |
158 | ++ fprintf(md_result_file, "ALTER TABLE %s ", opt_quoted_table); |
159 | ++ for (keys= list_length(skipped_keys_list); keys > 0; keys--) |
160 | ++ { |
161 | ++ LIST *node= skipped_keys_list; |
162 | ++ char *def= node->data; |
163 | ++ |
164 | ++ fprintf(md_result_file, "ADD %s%s", def, (keys > 1) ? ", " : ";\n"); |
165 | ++ |
166 | ++ skipped_keys_list= list_delete(skipped_keys_list, node); |
167 | ++ my_free(def); |
168 | ++ my_free(node); |
169 | ++ } |
170 | ++ |
171 | ++ DBUG_ASSERT(skipped_keys_list == NULL); |
172 | ++ } |
173 | ++ |
174 | + /* Moved enable keys to before unlock per bug 15977 */ |
175 | + if (opt_disable_keys) |
176 | + { |
177 | +--- /dev/null 1970-01-01 00:00:00.000000000 +0000 |
178 | ++++ b/mysql-test/r/percona_mysqldump_innodb_optimize_keys.result 2011-03-31 16:03:41.000000000 +0400 |
179 | +@@ -0,0 +1,109 @@ |
180 | ++# |
181 | ++# Test the --innodb-optimize-keys option. |
182 | ++# |
183 | ++CREATE TABLE t1 (a INT NOT NULL PRIMARY KEY, b INT, KEY(b)) ENGINE=MyISAM; |
184 | ++###################################### |
185 | ++ |
186 | ++/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; |
187 | ++/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; |
188 | ++/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */; |
189 | ++/*!40101 SET NAMES utf8 */; |
190 | ++/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */; |
191 | ++/*!40103 SET TIME_ZONE='+00:00' */; |
192 | ++/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */; |
193 | ++/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */; |
194 | ++/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */; |
195 | ++/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */; |
196 | ++DROP TABLE IF EXISTS `t1`; |
197 | ++/*!40101 SET @saved_cs_client = @@character_set_client */; |
198 | ++/*!40101 SET character_set_client = utf8 */; |
199 | ++CREATE TABLE `t1` ( |
200 | ++ `a` int(11) NOT NULL, |
201 | ++ `b` int(11) DEFAULT NULL, |
202 | ++ PRIMARY KEY (`a`), |
203 | ++ KEY `b` (`b`) |
204 | ++) ENGINE=MyISAM DEFAULT CHARSET=latin1; |
205 | ++/*!40101 SET character_set_client = @saved_cs_client */; |
206 | ++ |
207 | ++LOCK TABLES `t1` WRITE; |
208 | ++/*!40000 ALTER TABLE `t1` DISABLE KEYS */; |
209 | ++/*!40000 ALTER TABLE `t1` ENABLE KEYS */; |
210 | ++UNLOCK TABLES; |
211 | ++/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */; |
212 | ++ |
213 | ++/*!40101 SET SQL_MODE=@OLD_SQL_MODE */; |
214 | ++/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */; |
215 | ++/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */; |
216 | ++/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */; |
217 | ++/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */; |
218 | ++/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */; |
219 | ++/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */; |
220 | ++ |
221 | ++###################################### |
222 | ++DROP TABLE t1; |
223 | ++CREATE TABLE t2 (a INT PRIMARY KEY) ENGINE=InnoDB; |
224 | ++INSERT INTO t2 VALUES (0), (1), (2); |
225 | ++CREATE TABLE t1 ( |
226 | ++id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, |
227 | ++a INT, b VARCHAR(255), c DECIMAL(10,3), |
228 | ++KEY (b), |
229 | ++UNIQUE KEY uniq(c,a), |
230 | ++FOREIGN KEY (a) REFERENCES t2(a) ON DELETE CASCADE |
231 | ++) ENGINE=InnoDB; |
232 | ++INSERT INTO t1(a,b,c) VALUES (0, "0", 0.0), (1, "1", 1.1), (2, "2", 2.2); |
233 | ++###################################### |
234 | ++ |
235 | ++/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; |
236 | ++/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; |
237 | ++/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */; |
238 | ++/*!40101 SET NAMES utf8 */; |
239 | ++/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */; |
240 | ++/*!40103 SET TIME_ZONE='+00:00' */; |
241 | ++/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */; |
242 | ++/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */; |
243 | ++/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */; |
244 | ++/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */; |
245 | ++DROP TABLE IF EXISTS `t1`; |
246 | ++/*!40101 SET @saved_cs_client = @@character_set_client */; |
247 | ++/*!40101 SET character_set_client = utf8 */; |
248 | ++CREATE TABLE `t1` ( |
249 | ++ `id` int(11) NOT NULL AUTO_INCREMENT, |
250 | ++ `a` int(11) DEFAULT NULL, |
251 | ++ `b` varchar(255) DEFAULT NULL, |
252 | ++ `c` decimal(10,3) DEFAULT NULL, |
253 | ++ PRIMARY KEY (`id`) |
254 | ++) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1; |
255 | ++/*!40101 SET character_set_client = @saved_cs_client */; |
256 | ++ |
257 | ++LOCK TABLES `t1` WRITE; |
258 | ++/*!40000 ALTER TABLE `t1` DISABLE KEYS */; |
259 | ++INSERT INTO `t1` VALUES (1,0,'0',0.000),(2,1,'1',1.100),(3,2,'2',2.200); |
260 | ++ALTER TABLE `t1` ADD UNIQUE KEY `uniq` (`c`,`a`), ADD KEY `b` (`b`), ADD KEY `a` (`a`), ADD CONSTRAINT `t1_ibfk_1` FOREIGN KEY (`a`) REFERENCES `t2` (`a`) ON DELETE CASCADE; |
261 | ++/*!40000 ALTER TABLE `t1` ENABLE KEYS */; |
262 | ++UNLOCK TABLES; |
263 | ++DROP TABLE IF EXISTS `t2`; |
264 | ++/*!40101 SET @saved_cs_client = @@character_set_client */; |
265 | ++/*!40101 SET character_set_client = utf8 */; |
266 | ++CREATE TABLE `t2` ( |
267 | ++ `a` int(11) NOT NULL, |
268 | ++ PRIMARY KEY (`a`) |
269 | ++) ENGINE=InnoDB DEFAULT CHARSET=latin1; |
270 | ++/*!40101 SET character_set_client = @saved_cs_client */; |
271 | ++ |
272 | ++LOCK TABLES `t2` WRITE; |
273 | ++/*!40000 ALTER TABLE `t2` DISABLE KEYS */; |
274 | ++INSERT INTO `t2` VALUES (0),(1),(2); |
275 | ++/*!40000 ALTER TABLE `t2` ENABLE KEYS */; |
276 | ++UNLOCK TABLES; |
277 | ++/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */; |
278 | ++ |
279 | ++/*!40101 SET SQL_MODE=@OLD_SQL_MODE */; |
280 | ++/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */; |
281 | ++/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */; |
282 | ++/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */; |
283 | ++/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */; |
284 | ++/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */; |
285 | ++/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */; |
286 | ++ |
287 | ++###################################### |
288 | ++DROP TABLE t1, t2; |
289 | +--- a/mysql-test/suite/innodb/r/innodb.result 2011-03-31 13:59:27.000000000 +0400 |
290 | ++++ b/mysql-test/suite/innodb/r/innodb.result 2011-03-31 16:06:56.000000000 +0400 |
291 | +@@ -1673,7 +1673,7 @@ |
292 | + 71 |
293 | + SELECT variable_value - @innodb_rows_inserted_orig FROM information_schema.global_status WHERE LOWER(variable_name) = 'innodb_rows_inserted'; |
294 | + variable_value - @innodb_rows_inserted_orig |
295 | +-1066 |
296 | ++1108 |
297 | + SELECT variable_value - @innodb_rows_updated_orig FROM information_schema.global_status WHERE LOWER(variable_name) = 'innodb_rows_updated'; |
298 | + variable_value - @innodb_rows_updated_orig |
299 | + 866 |
300 | +--- a/mysql-test/suite/innodb/t/innodb-index.test 2011-03-09 18:11:39.000000000 +0300 |
301 | ++++ b/mysql-test/suite/innodb/t/innodb-index.test 2011-03-31 16:03:41.000000000 +0400 |
302 | +@@ -28,6 +28,11 @@ |
303 | + show create table t1; |
304 | + --error ER_MULTIPLE_PRI_KEY |
305 | + alter table t1 add primary key (c); |
306 | ++# Suppress the error log messages occuring on duplicate key error |
307 | ++# during ALTER TABLE when using fast index creation |
308 | ++--disable_query_log |
309 | ++call mtr.add_suppression("Cannot find index PRIMARY in InnoDB index translation table."); |
310 | ++--enable_query_log |
311 | + --error ER_DUP_ENTRY |
312 | + alter table t1 drop primary key, add primary key (b); |
313 | + create unique index c on t1 (c); |
314 | +--- a/mysql-test/suite/innodb/t/innodb.test 2011-03-09 18:11:39.000000000 +0300 |
315 | ++++ b/mysql-test/suite/innodb/t/innodb.test 2011-03-31 16:03:41.000000000 +0400 |
316 | +@@ -21,6 +21,12 @@ |
317 | + |
318 | + -- source include/have_innodb.inc |
319 | + |
320 | ++# Suppress the error log message occuring on duplicate key error |
321 | ++# during ALTER TABLE when using fast index creation |
322 | ++--disable_query_log |
323 | ++call mtr.add_suppression("Cannot find index v_2 in InnoDB index translation table."); |
324 | ++--enable_query_log |
325 | ++ |
326 | + let $MYSQLD_DATADIR= `select @@datadir`; |
327 | + |
328 | + # Save the original values of some variables in order to be able to |
329 | +--- /dev/null 1970-01-01 00:00:00.000000000 +0000 |
330 | ++++ b/mysql-test/t/percona_mysqldump_innodb_optimize_keys.test 2011-03-31 16:03:41.000000000 +0400 |
331 | +@@ -0,0 +1,62 @@ |
332 | ++# Embedded server doesn't support external clients |
333 | ++--source include/not_embedded.inc |
334 | ++ |
335 | ++# Fast index creation is only available in InnoDB plugin |
336 | ++--source include/have_innodb.inc |
337 | ++ |
338 | ++# Save the initial number of concurrent sessions |
339 | ++--source include/count_sessions.inc |
340 | ++ |
341 | ++--echo # |
342 | ++--echo # Test the --innodb-optimize-keys option. |
343 | ++--echo # |
344 | ++ |
345 | ++--let $file=$MYSQLTEST_VARDIR/tmp/t1.sql |
346 | ++ |
347 | ++# First test that the option has no effect on non-InnoDB tables |
348 | ++ |
349 | ++CREATE TABLE t1 (a INT NOT NULL PRIMARY KEY, b INT, KEY(b)) ENGINE=MyISAM; |
350 | ++ |
351 | ++--exec $MYSQL_DUMP --skip-comments --innodb-optimize-keys test t1 >$file |
352 | ++ |
353 | ++--echo ###################################### |
354 | ++--cat_file $file |
355 | ++--echo ###################################### |
356 | ++ |
357 | ++--remove_file $file |
358 | ++ |
359 | ++DROP TABLE t1; |
360 | ++ |
361 | ++ |
362 | ++# Check that for InnoDB tables secondary and foreign keys are created |
363 | ++# after the data is dumped |
364 | ++ |
365 | ++CREATE TABLE t2 (a INT PRIMARY KEY) ENGINE=InnoDB; |
366 | ++INSERT INTO t2 VALUES (0), (1), (2); |
367 | ++ |
368 | ++CREATE TABLE t1 ( |
369 | ++ id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, |
370 | ++ a INT, b VARCHAR(255), c DECIMAL(10,3), |
371 | ++ KEY (b), |
372 | ++ UNIQUE KEY uniq(c,a), |
373 | ++ FOREIGN KEY (a) REFERENCES t2(a) ON DELETE CASCADE |
374 | ++) ENGINE=InnoDB; |
375 | ++ |
376 | ++INSERT INTO t1(a,b,c) VALUES (0, "0", 0.0), (1, "1", 1.1), (2, "2", 2.2); |
377 | ++ |
378 | ++--exec $MYSQL_DUMP --skip-comments --innodb-optimize-keys test t1 t2 >$file |
379 | ++ |
380 | ++--echo ###################################### |
381 | ++--cat_file $file |
382 | ++--echo ###################################### |
383 | ++ |
384 | ++# Check that the resulting dump can be imported back |
385 | ++ |
386 | ++--exec $MYSQL test < $file |
387 | ++ |
388 | ++--remove_file $file |
389 | ++ |
390 | ++DROP TABLE t1, t2; |
391 | ++ |
392 | ++# Wait till we reached the initial number of concurrent sessions |
393 | ++--source include/wait_until_count_sessions.inc |
394 | +--- a/sql/sql_lex.cc 2011-03-31 13:59:24.000000000 +0400 |
395 | ++++ b/sql/sql_lex.cc 2011-03-31 16:03:41.000000000 +0400 |
396 | +@@ -1630,6 +1630,9 @@ |
397 | + alter_list(rhs.alter_list, mem_root), |
398 | + key_list(rhs.key_list, mem_root), |
399 | + create_list(rhs.create_list, mem_root), |
400 | ++ delayed_key_list(rhs.delayed_key_list, mem_root), |
401 | ++ delayed_key_info(rhs.delayed_key_info), |
402 | ++ delayed_key_count(rhs.delayed_key_count), |
403 | + flags(rhs.flags), |
404 | + keys_onoff(rhs.keys_onoff), |
405 | + tablespace_op(rhs.tablespace_op), |
406 | +@@ -1652,6 +1655,7 @@ |
407 | + list_copy_and_replace_each_value(alter_list, mem_root); |
408 | + list_copy_and_replace_each_value(key_list, mem_root); |
409 | + list_copy_and_replace_each_value(create_list, mem_root); |
410 | ++ list_copy_and_replace_each_value(delayed_key_list, mem_root); |
411 | + /* partition_names are not deeply copied currently */ |
412 | + } |
413 | + |
414 | +--- a/sql/sql_lex.h 2011-03-31 13:59:25.000000000 +0400 |
415 | ++++ b/sql/sql_lex.h 2011-03-31 16:03:41.000000000 +0400 |
416 | +@@ -1004,6 +1004,9 @@ |
417 | + List<Alter_column> alter_list; |
418 | + List<Key> key_list; |
419 | + List<Create_field> create_list; |
420 | ++ List<Key> delayed_key_list; |
421 | ++ KEY *delayed_key_info; |
422 | ++ uint delayed_key_count; |
423 | + uint flags; |
424 | + enum enum_enable_or_disable keys_onoff; |
425 | + enum tablespace_op_type tablespace_op; |
426 | +@@ -1015,6 +1018,8 @@ |
427 | + |
428 | + |
429 | + Alter_info() : |
430 | ++ delayed_key_info(NULL), |
431 | ++ delayed_key_count(0), |
432 | + flags(0), |
433 | + keys_onoff(LEAVE_AS_IS), |
434 | + tablespace_op(NO_TABLESPACE_OP), |
435 | +@@ -1030,6 +1035,9 @@ |
436 | + alter_list.empty(); |
437 | + key_list.empty(); |
438 | + create_list.empty(); |
439 | ++ delayed_key_list.empty(); |
440 | ++ delayed_key_info= NULL; |
441 | ++ delayed_key_count= 0; |
442 | + flags= 0; |
443 | + keys_onoff= LEAVE_AS_IS; |
444 | + tablespace_op= NO_TABLESPACE_OP; |
445 | +--- a/sql/sql_table.cc 2011-03-31 13:59:12.000000000 +0400 |
446 | ++++ b/sql/sql_table.cc 2011-03-31 16:13:40.000000000 +0400 |
447 | +@@ -2773,7 +2773,7 @@ |
448 | + file The handler for the new table. |
449 | + key_info_buffer OUT An array of KEY structs for the indexes. |
450 | + key_count OUT The number of elements in the array. |
451 | +- select_field_count The number of fields coming from a select table. |
452 | ++ select_field_count The number of fields coming from a select table. |
453 | + |
454 | + DESCRIPTION |
455 | + Prepares the table and key structures for table creation. |
456 | +@@ -3119,7 +3119,6 @@ |
457 | + } |
458 | + |
459 | + /* Create keys */ |
460 | +- |
461 | + List_iterator<Key> key_iterator(alter_info->key_list); |
462 | + List_iterator<Key> key_iterator2(alter_info->key_list); |
463 | + uint key_parts=0, fk_key_count=0; |
464 | +@@ -3217,6 +3216,14 @@ |
465 | + if (!*key_info_buffer || ! key_part_info) |
466 | + DBUG_RETURN(TRUE); // Out of memory |
467 | + |
468 | ++ List_iterator<Key> delayed_key_iterator(alter_info->delayed_key_list); |
469 | ++ alter_info->delayed_key_count= 0; |
470 | ++ if (alter_info->delayed_key_list.elements > 0) |
471 | ++ { |
472 | ++ alter_info->delayed_key_info= (KEY *) sql_calloc(sizeof(KEY) * |
473 | ++ (*key_count)); |
474 | ++ } |
475 | ++ |
476 | + key_iterator.rewind(); |
477 | + key_number=0; |
478 | + for (; (key=key_iterator++) ; key_number++) |
479 | +@@ -3635,6 +3642,22 @@ |
480 | + key_info->comment.str= key->key_create_info.comment.str; |
481 | + } |
482 | + |
483 | ++ if (alter_info->delayed_key_list.elements > 0) |
484 | ++ { |
485 | ++ Key *delayed_key; |
486 | ++ |
487 | ++ delayed_key_iterator.rewind(); |
488 | ++ while ((delayed_key= delayed_key_iterator++)) |
489 | ++ { |
490 | ++ if (delayed_key == key) |
491 | ++ { |
492 | ++ alter_info->delayed_key_info[alter_info->delayed_key_count++]= |
493 | ++ *key_info; |
494 | ++ break; |
495 | ++ } |
496 | ++ } |
497 | ++ } |
498 | ++ |
499 | + key_info++; |
500 | + } |
501 | + if (!unique_key && !primary_key && |
502 | +@@ -5247,6 +5270,10 @@ |
503 | + List<Create_field> new_create_list; |
504 | + /* New key definitions are added here */ |
505 | + List<Key> new_key_list; |
506 | ++ /* List with secondary keys which should be created after copying the data */ |
507 | ++ List<Key> delayed_key_list; |
508 | ++ /* Foreign key list returned by handler::get_foreign_key_list() */ |
509 | ++ List<FOREIGN_KEY_INFO> f_key_list; |
510 | + List_iterator<Alter_drop> drop_it(alter_info->drop_list); |
511 | + List_iterator<Create_field> def_it(alter_info->create_list); |
512 | + List_iterator<Alter_column> alter_it(alter_info->alter_list); |
513 | +@@ -5259,6 +5286,7 @@ |
514 | + uint used_fields= create_info->used_fields; |
515 | + KEY *key_info=table->key_info; |
516 | + bool rc= TRUE; |
517 | ++ bool skip_secondary; |
518 | + |
519 | + DBUG_ENTER("mysql_prepare_alter_table"); |
520 | + |
521 | +@@ -5286,6 +5314,7 @@ |
522 | + char *tablespace= static_cast<char *>(thd->alloc(FN_LEN + 1)); |
523 | + /* |
524 | + Regular alter table of disk stored table (no tablespace/storage change) |
525 | ++ |
526 | + Copy tablespace name |
527 | + */ |
528 | + if (tablespace && |
529 | +@@ -5436,7 +5465,23 @@ |
530 | + /* |
531 | + Collect all keys which isn't in drop list. Add only those |
532 | + for which some fields exists. |
533 | +- */ |
534 | ++ |
535 | ++ We also skip secondary keys for InnoDB tables and store them in |
536 | ++ delayed_key_list to create them later after copying the data to make use of |
537 | ++ the InnoDB fast index creation. The following conditions must be met: |
538 | ++ |
539 | ++ - we are going to create an InnoDB table; |
540 | ++ - the key most be a non-UNIQUE one; |
541 | ++ - there are no foreign keys. This can be optimized later to exclude only |
542 | ++ those keys which are a part of foreign key constraints. Currently we |
543 | ++ simply disable this optimization for all keys if there are any foreign |
544 | ++ key constraints in the table. |
545 | ++ */ |
546 | ++ |
547 | ++ skip_secondary= |
548 | ++ !my_strcasecmp(system_charset_info, table->file->table_type(), "InnoDB") && |
549 | ++ !table->file->get_foreign_key_list(thd, &f_key_list) && |
550 | ++ f_key_list.elements == 0; |
551 | + |
552 | + for (uint i=0 ; i < table->s->keys ; i++,key_info++) |
553 | + { |
554 | +@@ -5553,6 +5598,8 @@ |
555 | + test(key_info->flags & HA_GENERATED_KEY), |
556 | + key_parts); |
557 | + new_key_list.push_back(key); |
558 | ++ if (skip_secondary && key_type == Key::MULTIPLE) |
559 | ++ delayed_key_list.push_back(key); |
560 | + } |
561 | + } |
562 | + { |
563 | +@@ -5560,7 +5607,21 @@ |
564 | + while ((key=key_it++)) // Add new keys |
565 | + { |
566 | + if (key->type != Key::FOREIGN_KEY) |
567 | ++ { |
568 | + new_key_list.push_back(key); |
569 | ++ if (skip_secondary && key->type == Key::MULTIPLE) |
570 | ++ delayed_key_list.push_back(key); |
571 | ++ } |
572 | ++ else if (skip_secondary) |
573 | ++ { |
574 | ++ /* |
575 | ++ We are adding a foreign key so disable the secondary keys |
576 | ++ optimization. |
577 | ++ */ |
578 | ++ skip_secondary= FALSE; |
579 | ++ delayed_key_list.empty(); |
580 | ++ } |
581 | ++ |
582 | + if (key->name.str && |
583 | + !my_strcasecmp(system_charset_info, key->name.str, primary_key_name)) |
584 | + { |
585 | +@@ -5609,12 +5670,100 @@ |
586 | + rc= FALSE; |
587 | + alter_info->create_list.swap(new_create_list); |
588 | + alter_info->key_list.swap(new_key_list); |
589 | ++ alter_info->delayed_key_list.swap(delayed_key_list); |
590 | + err: |
591 | + DBUG_RETURN(rc); |
592 | + } |
593 | + |
594 | + |
595 | + /* |
596 | ++ Temporarily remove secondary keys previously stored in |
597 | ++ alter_info->delayed_key_info. |
598 | ++*/ |
599 | ++static int |
600 | ++remove_secondary_keys(THD *thd, TABLE *table, Alter_info *alter_info) |
601 | ++{ |
602 | ++ uint *key_numbers; |
603 | ++ uint key_counter= 0; |
604 | ++ uint i; |
605 | ++ int error; |
606 | ++ DBUG_ENTER("remove_secondary_keys"); |
607 | ++ DBUG_ASSERT(alter_info->delayed_key_count > 0); |
608 | ++ |
609 | ++ key_numbers= (uint *) thd->alloc(sizeof(uint) * |
610 | ++ alter_info->delayed_key_count); |
611 | ++ for (i= 0; i < alter_info->delayed_key_count; i++) |
612 | ++ { |
613 | ++ KEY *key= alter_info->delayed_key_info + i; |
614 | ++ uint j; |
615 | ++ |
616 | ++ for (j= 0; j < table->s->keys; j++) |
617 | ++ { |
618 | ++ if (!strcmp(table->key_info[j].name, key->name)) |
619 | ++ { |
620 | ++ key_numbers[key_counter++]= j; |
621 | ++ break; |
622 | ++ } |
623 | ++ } |
624 | ++ } |
625 | ++ |
626 | ++ DBUG_ASSERT(key_counter == alter_info->delayed_key_count); |
627 | ++ |
628 | ++ if ((error= table->file->prepare_drop_index(table, key_numbers, |
629 | ++ key_counter)) || |
630 | ++ (error= table->file->final_drop_index(table))) |
631 | ++ { |
632 | ++ table->file->print_error(error, MYF(0)); |
633 | ++ } |
634 | ++ |
635 | ++ DBUG_RETURN(error); |
636 | ++} |
637 | ++ |
638 | ++/* |
639 | ++ Restore secondary keys previously removed in remove_secondary_keys. |
640 | ++*/ |
641 | ++ |
642 | ++static int |
643 | ++restore_secondary_keys(THD *thd, TABLE *table, Alter_info *alter_info) |
644 | ++{ |
645 | ++ uint i; |
646 | ++ int error; |
647 | ++ DBUG_ENTER("restore_secondary_keys"); |
648 | ++ DBUG_ASSERT(alter_info->delayed_key_count > 0); |
649 | ++ |
650 | ++ thd_proc_info(thd, "restoring secondary keys"); |
651 | ++ |
652 | ++ /* Fix the key parts */ |
653 | ++ for (i= 0; i < alter_info->delayed_key_count; i++) |
654 | ++ { |
655 | ++ KEY *key = alter_info->delayed_key_info + i; |
656 | ++ KEY_PART_INFO *key_part; |
657 | ++ KEY_PART_INFO *part_end; |
658 | ++ |
659 | ++ part_end= key->key_part + key->key_parts; |
660 | ++ for (key_part= key->key_part; key_part < part_end; key_part++) |
661 | ++ key_part->field= table->field[key_part->fieldnr]; |
662 | ++ } |
663 | ++ |
664 | ++ if ((error= table->file->add_index(table, alter_info->delayed_key_info, |
665 | ++ alter_info->delayed_key_count))) |
666 | ++ { |
667 | ++ /* |
668 | ++ Exchange the key_info for the error message. If we exchange |
669 | ++ key number by key name in the message later, we need correct info. |
670 | ++ */ |
671 | ++ KEY *save_key_info= table->key_info; |
672 | ++ table->key_info= alter_info->delayed_key_info; |
673 | ++ table->file->print_error(error, MYF(0)); |
674 | ++ table->key_info= save_key_info; |
675 | ++ |
676 | ++ DBUG_RETURN(error); |
677 | ++ } |
678 | ++ |
679 | ++ DBUG_RETURN(0); |
680 | ++} |
681 | ++ |
682 | ++/* |
683 | + Alter table |
684 | + |
685 | + SYNOPSIS |
686 | +@@ -6345,6 +6494,12 @@ |
687 | + else |
688 | + create_info->data_file_name=create_info->index_file_name=0; |
689 | + |
690 | ++ /* |
691 | ++ Postpone secondary index creation for InnoDB tables if the data has to be |
692 | ++ copied. |
693 | ++ TODO: is there a better way to check for InnoDB? |
694 | ++ */ |
695 | ++ |
696 | + DEBUG_SYNC(thd, "alter_table_before_create_table_no_lock"); |
697 | + DBUG_EXECUTE_IF("sleep_before_create_table_no_lock", |
698 | + my_sleep(100000);); |
699 | +@@ -6408,6 +6563,13 @@ |
700 | + /* We don't want update TIMESTAMP fields during ALTER TABLE. */ |
701 | + new_table->timestamp_field_type= TIMESTAMP_NO_AUTO_SET; |
702 | + new_table->next_number_field=new_table->found_next_number_field; |
703 | ++ |
704 | ++ if (alter_info->delayed_key_count > 0) |
705 | ++ { |
706 | ++ /* ignore the error */ |
707 | ++ error= remove_secondary_keys(thd, new_table, alter_info); |
708 | ++ } |
709 | ++ |
710 | + thd_proc_info(thd, "copy to tmp table"); |
711 | + DBUG_EXECUTE_IF("abort_copy_table", { |
712 | + my_error(ER_LOCK_WAIT_TIMEOUT, MYF(0)); |
713 | +@@ -6418,6 +6580,8 @@ |
714 | + order_num, order, &copied, &deleted, |
715 | + alter_info->keys_onoff, |
716 | + alter_info->error_if_not_empty); |
717 | ++ if (!error && alter_info->delayed_key_count > 0) |
718 | ++ error= restore_secondary_keys(thd, new_table, alter_info); |
719 | + } |
720 | + else |
721 | + { |
722 | |
723 | === modified file 'series' |
724 | --- series 2011-03-10 11:41:30 +0000 |
725 | +++ series 2011-04-03 13:31:27 +0000 |
726 | @@ -45,3 +45,5 @@ |
727 | mysql_remove_eol_carret.patch |
728 | processlist_row_stats.patch |
729 | mysql-test.diff |
730 | +innodb_expand_fast_index_creation.patch |
731 | + |
good for me after documentation.