Percona Server moved to https://jira.percona.com/projects/PS

Merge lp:~akopytov/percona-server/bug851674-5.1 into lp:percona-server/5.1

  1. bug851674-5.1
  2. Merge into 5.1
Proposed by Alexey Kopytov
Status: Merged
Merged at revision: 286
Proposed branch: lp:~akopytov/percona-server/bug851674-5.1
Merge into: lp:percona-server/5.1
Diff against target: 372 lines (+269/-8)
1 file modified
patches/innodb_expand_fast_index_creation.patch (+269/-8)
To merge this branch: bzr merge lp:~akopytov/percona-server/bug851674-5.1
Reviewer Review Type Date Requested Status
Laurynas Biveinis (community) Approve
Review via email: mp+75912@code.launchpad.net
To post a comment you must log in.
Revision history for this message
Alexey Kopytov (akopytov) wrote :

http://jenkins.percona.com/view/Percona%20Server%205.1/job/percona-server-5.1-param/140/ is unavailable because build #139 has been stuck for 3 days.

Revision history for this message
Laurynas Biveinis (laurynas-biveinis) wrote :

LGTM with:
1) Please use "||" in (pk_processed | !has_pk)
2) (Optional) Please assert that the my_snprintf buffer does not overflow.

review: Approve
Revision history for this message
Alexey Kopytov (akopytov) wrote :

On 22.09.11 15:26, Laurynas Biveinis wrote:
> Review: Approve
>
> LGTM with:
> 1) Please use "||" in (pk_processed | !has_pk)

Fixed, thanks!

> 2) (Optional) Please assert that the my_snprintf buffer does not overflow.
>

It turns out it's not even possible to check for overflows in
my_snprintf(), because unlike regular snprintf(), my_vsnprintf() (which
does the actual work) never returns values equal or greater than 'n'. It
returns the length of the resulting string (without terminating '\0')
which is (n-1) at most.

Preview Diff

[H/L] Next/Prev Comment, [J/K] Next/Prev File, [N/P] Next/Prev Hunk
=== modified file 'patches/innodb_expand_fast_index_creation.patch'
--- patches/innodb_expand_fast_index_creation.patch 2011-09-06 11:50:30 +0000
+++ patches/innodb_expand_fast_index_creation.patch 2011-09-22 11:51:23 +0000
@@ -54,7 +54,7 @@
54 {"insert-ignore", OPT_INSERT_IGNORE, "Insert rows with INSERT IGNORE.",54 {"insert-ignore", OPT_INSERT_IGNORE, "Insert rows with INSERT IGNORE.",
55 &opt_ignore, &opt_ignore, 0, GET_BOOL, NO_ARG, 0, 0, 0, 0,55 &opt_ignore, &opt_ignore, 0, GET_BOOL, NO_ARG, 0, 0, 0, 0,
56 0, 0},56 0, 0},
57@@ -2246,6 +2256,128 @@57@@ -2246,6 +2256,182 @@
58 }58 }
59 59
60 /*60 /*
@@ -83,6 +83,8 @@
83+ SYNOPSIS83+ SYNOPSIS
84+ skip_secondary_keys()84+ skip_secondary_keys()
85+ create_str SHOW CREATE TABLE output85+ create_str SHOW CREATE TABLE output
86+ has_pk TRUE, if the table has PRIMARY KEY
87+ (or UNIQUE key on non-nullable columns)
86+88+
87+89+
88+ DESCRIPTION90+ DESCRIPTION
@@ -93,11 +95,12 @@
93+ mysqldump sets foreign_key_checks to 0 anyway.95+ mysqldump sets foreign_key_checks to 0 anyway.
94+*/96+*/
95+97+
96+static void skip_secondary_keys(char *create_str)98+static void skip_secondary_keys(char *create_str, my_bool has_pk)
97+{99+{
98+ char *ptr, *strend;100+ char *ptr, *strend;
99+ char *last_comma = NULL;101+ char *last_comma = NULL;
100+ HASH ignored_columns;102+ HASH ignored_columns;
103+ my_bool pk_processed= FALSE;
101+104+
102+ if (hash_init(&ignored_columns, charset_info, 16, 0, 0,105+ if (hash_init(&ignored_columns, charset_info, 16, 0, 0,
103+ (hash_get_key) get_table_key,106+ (hash_get_key) get_table_key,
@@ -110,6 +113,7 @@
110+ while (*ptr)113+ while (*ptr)
111+ {114+ {
112+ char *tmp, *orig_ptr, c;115+ char *tmp, *orig_ptr, c;
116+ my_bool is_unique;
113+117+
114+ orig_ptr= ptr;118+ orig_ptr= ptr;
115+ /* Skip leading whitespace */119+ /* Skip leading whitespace */
@@ -124,7 +128,8 @@
124+128+
125+ /* Is it a secondary index definition? */129+ /* Is it a secondary index definition? */
126+ if (c == '\n' &&130+ if (c == '\n' &&
127+ (!strncmp(ptr, "UNIQUE KEY ", sizeof("UNIQUE KEY ") - 1) ||131+ (((is_unique= !strncmp(ptr, "UNIQUE KEY ", sizeof("UNIQUE KEY ")-1)) &&
132+ (pk_processed || !has_pk)) ||
128+ !strncmp(ptr, "KEY ", sizeof("KEY ") - 1) ||133+ !strncmp(ptr, "KEY ", sizeof("KEY ") - 1) ||
129+ !strncmp(ptr, "CONSTRAINT ", sizeof("CONSTRAINT ") - 1)) &&134+ !strncmp(ptr, "CONSTRAINT ", sizeof("CONSTRAINT ") - 1)) &&
130+ !contains_ignored_column(&ignored_columns, ptr))135+ !contains_ignored_column(&ignored_columns, ptr))
@@ -152,6 +157,10 @@
152+ {157+ {
153+ char *end;158+ char *end;
154+159+
160+ if ((has_pk && is_unique && !pk_processed) ||
161+ !strncmp(ptr, "PRIMARY KEY ", sizeof("PRIMARY KEY ") - 1))
162+ pk_processed= TRUE;
163+
155+ if (strstr(ptr, "AUTO_INCREMENT") && *ptr == '`')164+ if (strstr(ptr, "AUTO_INCREMENT") && *ptr == '`')
156+ {165+ {
157+ /*166+ /*
@@ -180,20 +189,83 @@
180+}189+}
181+190+
182+/*191+/*
192+ Check if the table has a primary key defined either explicitly or
193+ implicitly (i.e. a unique key on non-nullable columns).
194+
195+ SYNOPSIS
196+ my_bool has_primary_key(const char *table_name)
197+
198+ table_name quoted table name
199+
200+ RETURNS TRUE if the table has a primary key
201+
202+ DESCRIPTION
203+*/
204+
205+static my_bool has_primary_key(const char *table_name)
206+{
207+ MYSQL_RES *res= NULL;
208+ MYSQL_ROW row;
209+ char query_buff[QUERY_LENGTH];
210+ my_bool has_pk= TRUE;
211+
212+ my_snprintf(query_buff, sizeof(query_buff),
213+ "SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE "
214+ "TABLE_SCHEMA=DATABASE() AND TABLE_NAME='%s' AND "
215+ "COLUMN_KEY='PRI'", table_name);
216+ if (mysql_query(mysql, query_buff) || !(res= mysql_store_result(mysql)) ||
217+ !(row= mysql_fetch_row(res)))
218+ {
219+ fprintf(stderr, "Warning: Couldn't determine if table %s has a "
220+ "primary key (%s). "
221+ "--innodb-optimize-keys may work inefficiently.\n",
222+ table_name, mysql_error(mysql));
223+ goto cleanup;
224+ }
225+
226+ has_pk= atoi(row[0]) > 0;
227+
228+cleanup:
229+ if (res)
230+ mysql_free_result(res);
231+
232+ return has_pk;
233+}
234+
235+
236+/*
183 get_table_structure -- retrievs database structure, prints out corresponding237 get_table_structure -- retrievs database structure, prints out corresponding
184 CREATE statement and fills out insert_pat if the table is the type we will238 CREATE statement and fills out insert_pat if the table is the type we will
185 be dumping.239 be dumping.
186@@ -2486,6 +2618,9 @@240@@ -2282,6 +2468,7 @@
241 int len;
242 MYSQL_RES *result;
243 MYSQL_ROW row;
244+ my_bool has_pk;
245 DBUG_ENTER("get_table_structure");
246 DBUG_PRINT("enter", ("db: %s table: %s", db, table));
247
248@@ -2323,6 +2510,9 @@
249 result_table= quote_name(table, table_buff, 1);
250 opt_quoted_table= quote_name(table, table_buff2, 0);
251
252+ if (opt_innodb_optimize_keys && !strcmp(table_type, "InnoDB"))
253+ has_pk= has_primary_key(table);
254+
255 if (opt_order_by_primary)
256 order_by= primary_key_fields(result_table);
257
258@@ -2486,6 +2676,9 @@
187 259
188 row= mysql_fetch_row(result);260 row= mysql_fetch_row(result);
189 261
190+ if (opt_innodb_optimize_keys && !strcmp(table_type, "InnoDB"))262+ if (opt_innodb_optimize_keys && !strcmp(table_type, "InnoDB"))
191+ skip_secondary_keys(row[1]);263+ skip_secondary_keys(row[1], has_pk);
192+264+
193 fprintf(sql_file, (opt_compatible_mode & 3) ? "%s;\n" :265 fprintf(sql_file, (opt_compatible_mode & 3) ? "%s;\n" :
194 "/*!40101 SET @saved_cs_client = @@character_set_client */;\n"266 "/*!40101 SET @saved_cs_client = @@character_set_client */;\n"
195 "/*!40101 SET character_set_client = utf8 */;\n"267 "/*!40101 SET character_set_client = utf8 */;\n"
196@@ -3578,6 +3713,27 @@268@@ -3578,6 +3771,27 @@
197 goto err;269 goto err;
198 }270 }
199 271
@@ -223,7 +295,7 @@
223 {295 {
224--- /dev/null296--- /dev/null
225+++ b/mysql-test/r/percona_mysqldump_innodb_optimize_keys.result297+++ b/mysql-test/r/percona_mysqldump_innodb_optimize_keys.result
226@@ -0,0 +1,171 @@298@@ -0,0 +1,299 @@
227+#299+#
228+# Test the --innodb-optimize-keys option.300+# Test the --innodb-optimize-keys option.
229+#301+#
@@ -395,6 +467,134 @@
395+467+
396+######################################468+######################################
397+DROP TABLE t1, t2;469+DROP TABLE t1, t2;
470+CREATE TABLE t1 (
471+a INT NOT NULL,
472+UNIQUE KEY (a)) ENGINE=InnoDB;
473+CREATE TABLE t2 (
474+a INT NOT NULL,
475+b INT NOT NULL,
476+UNIQUE KEY (a,b)) ENGINE=InnoDB;
477+CREATE TABLE t3 (
478+a INT,
479+b INT,
480+UNIQUE KEY (a,b)) ENGINE=InnoDB;
481+CREATE TABLE t4 (
482+a INT NOT NULL,
483+b INT NOT NULL,
484+PRIMARY KEY (a,b),
485+UNIQUE KEY(b)) ENGINE=InnoDB;
486+SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE
487+TABLE_SCHEMA=DATABASE() AND
488+TABLE_NAME='t1' AND
489+COLUMN_KEY='PRI';
490+COUNT(*)
491+1
492+SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE
493+TABLE_SCHEMA=DATABASE() AND
494+TABLE_NAME='t2' AND
495+COLUMN_KEY='PRI';
496+COUNT(*)
497+2
498+SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE
499+TABLE_SCHEMA=DATABASE() AND
500+TABLE_NAME='t3' AND
501+COLUMN_KEY='PRI';
502+COUNT(*)
503+0
504+SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE
505+TABLE_SCHEMA=DATABASE() AND
506+TABLE_NAME='t4' AND
507+COLUMN_KEY='PRI';
508+COUNT(*)
509+2
510+INSERT INTO t1 VALUES (1), (2), (3);
511+INSERT INTO t2 VALUES (1,1), (2,2), (3,3);
512+INSERT INTO t3 SELECT * FROM t2;
513+INSERT INTO t4 SELECT * FROM t2;
514+######################################
515+
516+/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
517+/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
518+/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
519+/*!40101 SET NAMES utf8 */;
520+/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
521+/*!40103 SET TIME_ZONE='+00:00' */;
522+/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
523+/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
524+/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
525+/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
526+DROP TABLE IF EXISTS `t1`;
527+/*!40101 SET @saved_cs_client = @@character_set_client */;
528+/*!40101 SET character_set_client = utf8 */;
529+CREATE TABLE `t1` (
530+ `a` int(11) NOT NULL,
531+ UNIQUE KEY `a` (`a`)
532+) ENGINE=InnoDB DEFAULT CHARSET=latin1;
533+/*!40101 SET character_set_client = @saved_cs_client */;
534+
535+LOCK TABLES `t1` WRITE;
536+/*!40000 ALTER TABLE `t1` DISABLE KEYS */;
537+INSERT INTO `t1` VALUES (1),(2),(3);
538+/*!40000 ALTER TABLE `t1` ENABLE KEYS */;
539+UNLOCK TABLES;
540+DROP TABLE IF EXISTS `t2`;
541+/*!40101 SET @saved_cs_client = @@character_set_client */;
542+/*!40101 SET character_set_client = utf8 */;
543+CREATE TABLE `t2` (
544+ `a` int(11) NOT NULL,
545+ `b` int(11) NOT NULL,
546+ UNIQUE KEY `a` (`a`,`b`)
547+) ENGINE=InnoDB DEFAULT CHARSET=latin1;
548+/*!40101 SET character_set_client = @saved_cs_client */;
549+
550+LOCK TABLES `t2` WRITE;
551+/*!40000 ALTER TABLE `t2` DISABLE KEYS */;
552+INSERT INTO `t2` VALUES (1,1),(2,2),(3,3);
553+/*!40000 ALTER TABLE `t2` ENABLE KEYS */;
554+UNLOCK TABLES;
555+DROP TABLE IF EXISTS `t3`;
556+/*!40101 SET @saved_cs_client = @@character_set_client */;
557+/*!40101 SET character_set_client = utf8 */;
558+CREATE TABLE `t3` (
559+ `a` int(11) DEFAULT NULL,
560+ `b` int(11) DEFAULT NULL
561+) ENGINE=InnoDB DEFAULT CHARSET=latin1;
562+/*!40101 SET character_set_client = @saved_cs_client */;
563+
564+LOCK TABLES `t3` WRITE;
565+/*!40000 ALTER TABLE `t3` DISABLE KEYS */;
566+INSERT INTO `t3` VALUES (1,1),(2,2),(3,3);
567+ALTER TABLE `t3` ADD UNIQUE KEY `a` (`a`,`b`);
568+/*!40000 ALTER TABLE `t3` ENABLE KEYS */;
569+UNLOCK TABLES;
570+DROP TABLE IF EXISTS `t4`;
571+/*!40101 SET @saved_cs_client = @@character_set_client */;
572+/*!40101 SET character_set_client = utf8 */;
573+CREATE TABLE `t4` (
574+ `a` int(11) NOT NULL,
575+ `b` int(11) NOT NULL,
576+ PRIMARY KEY (`a`,`b`)
577+) ENGINE=InnoDB DEFAULT CHARSET=latin1;
578+/*!40101 SET character_set_client = @saved_cs_client */;
579+
580+LOCK TABLES `t4` WRITE;
581+/*!40000 ALTER TABLE `t4` DISABLE KEYS */;
582+INSERT INTO `t4` VALUES (1,1),(2,2),(3,3);
583+ALTER TABLE `t4` ADD UNIQUE KEY `b` (`b`);
584+/*!40000 ALTER TABLE `t4` ENABLE KEYS */;
585+UNLOCK TABLES;
586+/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
587+
588+/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
589+/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
590+/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
591+/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
592+/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
593+/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
594+/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
595+
596+######################################
597+DROP TABLE t1, t2, t3, t4;
398--- a/mysql-test/suite/innodb_plugin/r/innodb.result598--- a/mysql-test/suite/innodb_plugin/r/innodb.result
399+++ b/mysql-test/suite/innodb_plugin/r/innodb.result599+++ b/mysql-test/suite/innodb_plugin/r/innodb.result
400@@ -1679,7 +1679,7 @@600@@ -1679,7 +1679,7 @@
@@ -437,7 +637,7 @@
437 # Save the original values of some variables in order to be able to637 # Save the original values of some variables in order to be able to
438--- /dev/null638--- /dev/null
439+++ b/mysql-test/t/percona_mysqldump_innodb_optimize_keys.test639+++ b/mysql-test/t/percona_mysqldump_innodb_optimize_keys.test
440@@ -0,0 +1,94 @@640@@ -0,0 +1,155 @@
441+# Embedded server doesn't support external clients641+# Embedded server doesn't support external clients
442+--source include/not_embedded.inc642+--source include/not_embedded.inc
443+643+
@@ -530,6 +730,67 @@
530+730+
531+DROP TABLE t1, t2;731+DROP TABLE t1, t2;
532+732+
733+########################################################################
734+# Bug #851674: --innodb-optimize-keys does not work correctly with table
735+# without PRIMARY KEY
736+########################################################################
737+
738+CREATE TABLE t1 (
739+ a INT NOT NULL,
740+ UNIQUE KEY (a)) ENGINE=InnoDB;
741+
742+CREATE TABLE t2 (
743+ a INT NOT NULL,
744+ b INT NOT NULL,
745+ UNIQUE KEY (a,b)) ENGINE=InnoDB;
746+
747+CREATE TABLE t3 (
748+ a INT,
749+ b INT,
750+ UNIQUE KEY (a,b)) ENGINE=InnoDB;
751+
752+CREATE TABLE t4 (
753+ a INT NOT NULL,
754+ b INT NOT NULL,
755+ PRIMARY KEY (a,b),
756+ UNIQUE KEY(b)) ENGINE=InnoDB;
757+
758+SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE
759+ TABLE_SCHEMA=DATABASE() AND
760+ TABLE_NAME='t1' AND
761+ COLUMN_KEY='PRI';
762+SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE
763+ TABLE_SCHEMA=DATABASE() AND
764+ TABLE_NAME='t2' AND
765+ COLUMN_KEY='PRI';
766+SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE
767+ TABLE_SCHEMA=DATABASE() AND
768+ TABLE_NAME='t3' AND
769+ COLUMN_KEY='PRI';
770+SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE
771+ TABLE_SCHEMA=DATABASE() AND
772+ TABLE_NAME='t4' AND
773+ COLUMN_KEY='PRI';
774+
775+INSERT INTO t1 VALUES (1), (2), (3);
776+INSERT INTO t2 VALUES (1,1), (2,2), (3,3);
777+INSERT INTO t3 SELECT * FROM t2;
778+INSERT INTO t4 SELECT * FROM t2;
779+
780+--exec $MYSQL_DUMP --skip-comments --innodb-optimize-keys test t1 t2 t3 t4 >$file
781+
782+--echo ######################################
783+--cat_file $file
784+--echo ######################################
785+
786+# Check that the resulting dump can be imported back
787+
788+--exec $MYSQL test < $file
789+
790+--remove_file $file
791+
792+DROP TABLE t1, t2, t3, t4;
793+
533+# Wait till we reached the initial number of concurrent sessions794+# Wait till we reached the initial number of concurrent sessions
534+--source include/wait_until_count_sessions.inc795+--source include/wait_until_count_sessions.inc
535--- a/sql/sql_lex.cc796--- a/sql/sql_lex.cc

Subscribers

People subscribed via source and target branches