Fixes for new getopt

Bug fix when using --no-deaults
Sets ref_length to right value (faster rnd_pos() handling in InnoDB).
Fixed problem with multi-table-delete and InnoDB
Fixed problem with truncate table, which required a COMMIT before.
Fixed multi-table-delete bug with InnoDB.
Remove not used index from EXPLAIN


Docs/manual.texi:
  ChangeLog
include/my_getopt.h:
  Removed compiler warnings
myisam/myisamchk.c:
  Fixes for new getopt
mysql-test/r/explain.result:
  Updated test results
mysql-test/r/innodb.result:
  Updated test results
mysql-test/r/join.result:
  Updated test results
mysql-test/t/innodb.test:
  Updated test results
mysys/default.c:
  Bug fix when using --no-defaults
mysys/my_getopt.c:
  Fixes for new getopt
sql/ha_innodb.cc:
  Sets ref_length to right value (faster rnd_pos() handling).
  Fixed problem with multi-table-delete.
  Fixed problem with truncate table, which required a COMMIT before.
sql/opt_sum.cc:
  Remove not used index from EXPLAIN
sql/sql_cache.cc:
  Fixed core dump bug when not using query cache
sql/sql_select.cc:
  Remove not used index from EXPLAIN

13 files changed, 291 insertions, 86 deletions

diff --git a/Docs/manual.texi b/Docs/manual.texi
index 88660b5b29d..0147d314ae6 100644
--- a/Docs/manual.texi
+++ b/Docs/manual.texi
@@ -48374,9 +48374,20 @@ Our TODO section contains what we plan to have in 4.0. @xref{TODO MySQL 4.0}.
 
 @itemize @bullet
 @item
+Fixed some problems with @code{USE KEYS} / @code{IGNORE KEYS} when using
+many keys with the same start column.
+@item
 Don't use table scan with BerkeleyDB and InnoDB tables when we can use
 an index that covers the whole row.
 @item
+Optimized InnoDB sort-buffer handling to take less memory.
+@item
+Fixed bug in multi-table-delete and InnoDB tables.
+@item
+Fixed problem with @code{TRUNCATE} and InnoDB that gave the error
+@code{Can't execute the given command because you have active locked
+tables or an active transaction}.
+@item
 Added sql-mode flag @code{NO_UNSIGNED_SUBTRACTION} to disable unsigned
 arithmetic rules when it comes to subtraction. (This will make MySQL 4.0
 behave more closely to 3.23 with @code{UNSIGNED} columns).
diff --git a/include/my_getopt.h b/include/my_getopt.h
index 8321ad4cd1c..a1a052ac8e2 100644
--- a/include/my_getopt.h
+++ b/include/my_getopt.h
@@ -45,3 +45,8 @@ struct my_option
   my_bool    opt_is_var;                /* If true, the option is a variable */
 };
 
+extern int handle_options (int *argc, char ***argv, 
+			   const struct my_option *longopts, 
+			   my_bool (*get_one_option)(int,
+						     const struct my_option *,
+						     char *));
diff --git a/myisam/myisamchk.c b/myisam/myisamchk.c
index 2bec695a7c0..c1eb2fdf53f 100644
--- a/myisam/myisamchk.c
+++ b/myisam/myisamchk.c
@@ -155,7 +155,7 @@ static struct my_option my_long_options[] =
   {"analyze", "", 0, 0, 0, GET_NO_ARG, NO_ARG, 'a', 0, 0, 0, 0, 0, 0, 0},
   {"block-search", "", 0, 0, 0, GET_LONG, REQUIRED_ARG, 'b', 0, 0, 0, 0, 0, 0, 0},
   {"backup", "", 0, 0, 0, GET_NO_ARG, NO_ARG, 'B', 0, 0, 0, 0, 0, 0, 0},
-  {"character-sets-dir", "", 0, 0, 0, GET_STR, REQUIRED_ARG, OPT_CHARSETS_DIR, 0, 0, 0, 0, 0, 0, 0},
+  {"character-sets-dir", "", (gptr*) &set_charset_name, 0, 0, GET_STR, REQUIRED_ARG, 0, 0, 0, 0, 0, 0, 0, 1},
 
   {"check", "", 0, 0, 0, GET_NO_ARG, NO_ARG, 'c', 0, 0, 0, 0, 0, 0, 0},
   {"check-only-changed", "", 0, 0, 0, GET_NO_ARG, NO_ARG, 'C', 0, 0, 0, 0, 0, 0, 0},
@@ -191,6 +191,7 @@ static struct my_option my_long_options[] =
   {"verbose", "", 0, 0, 0, GET_NO_ARG, NO_ARG, 'v', 0, 0, 0, 0, 0, 0, 0},
   {"version", "", 0, 0, 0, GET_NO_ARG, NO_ARG, 'V', 0, 0, 0, 0, 0, 0, 0},
   {"wait", "", 0, 0, 0, GET_NO_ARG, NO_ARG, 'w', 0, 0, 0, 0, 0, 0, 0},
+
   /* variables begin here */
   { "key_buffer_size", "", (gptr*) &check_param.use_buffers, (gptr*) &check_param.use_buffers, 0, GET_LONG, REQUIRED_ARG, OPT_KEY_BUFFER_SIZE, (long) USE_BUFFER_INIT, (long) MALLOC_OVERHEAD, (long) ~0L, (long) MALLOC_OVERHEAD, (long) IO_SIZE, 0, 1}, 
   { "myisam_block_size", "", (gptr*) &opt_myisam_block_size, (gptr*) &opt_myisam_block_size, 0, GET_LONG, REQUIRED_ARG, OPT_MYISAM_BLOCK_SIZE, MI_KEY_BLOCK_LENGTH, MI_MIN_KEY_BLOCK_LENGTH, MI_MAX_KEY_BLOCK_LENGTH, 0, MI_MIN_KEY_BLOCK_LENGTH, 0, 1}, 
@@ -202,7 +203,7 @@ static struct my_option my_long_options[] =
   { "ft_min_word_len", "", (gptr*) &ft_min_word_len, (gptr*) &ft_min_word_len, 0, GET_LONG, REQUIRED_ARG, OPT_FT_MIN_WORD_LEN, 4, 1, HA_FT_MAXLEN, 0, 1, 0, 1},
   { "ft_max_word_len", "", (gptr*) &ft_max_word_len, (gptr*) &ft_max_word_len, 0, GET_LONG, REQUIRED_ARG, OPT_FT_MAX_WORD_LEN, HA_FT_MAXLEN, 10, HA_FT_MAXLEN, 0, 1, 0, 1},
   { "ft_max_word_len_for_sort", "", (gptr*) &ft_max_word_len_for_sort, (gptr*) &ft_max_word_len_for_sort, 0, GET_LONG, REQUIRED_ARG, OPT_FT_MAX_WORD_LEN_FOR_SORT, 20, 4, HA_FT_MAXLEN, 0, 1, 0, 1},
-  { 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0}
+  { 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0}
 };
 
 
@@ -297,27 +298,29 @@ static void usage(void)
 		      (It may be VERY slow to do a sort the first time!)");
 
   print_defaults("my",load_default_groups);
-  printf("\nPossible variables for option --set-variable (-O) are:\n");
+  printf("\nThe variables you can set are:\n");
   for (i=0; my_long_options[i].name ; i++)
   {
     if (!my_long_options[i].opt_is_var)
       continue;
+#ifdef TO_BE_FIXED
     printf("%-20s  current value: %lu\n", my_long_options[i].name, 
 	   *my_long_options[i].value);
+#endif
   }
-
 }
 
 
 	 /* Read options */
 
-static my_bool get_one_option(int optid, const struct my_option *opt,
-			      char *argument)
+static my_bool
+get_one_option(int optid,
+	       const struct my_option *opt __attribute__((unused)),
+	       char *argument)
 {
   uint old_testflag;
-  char buff[255], *end;
 
-  switch(optid) {
+  switch (optid) {
   case 'a':
     check_param.testflag|= T_STATISTICS;
     break;
@@ -428,13 +431,10 @@ static my_bool get_one_option(int optid, const struct my_option *opt,
     print_version();
     exit(0);
   case OPT_CORRECT_CHECKSUM:
-    check_param.testflag|=T_CALC_CHECKSUM;
-    break;
-  case OPT_CHARSETS_DIR:
-    charsets_dir= argument;
-    break;
-  case OPT_SET_CHARSET:
-    set_charset_name= argument;
+    if (*argument && *argument == '0')
+      check_param.testflag&= ~T_CALC_CHECKSUM;
+    else
+      check_param.testflag|=T_CALC_CHECKSUM;
     break;
 #ifdef DEBUG					/* Only useful if debugging */
   case OPT_START_CHECK_POS:
@@ -445,13 +445,12 @@ static my_bool get_one_option(int optid, const struct my_option *opt,
     usage();
     exit(0);
   }
+  return 0;
 }
 
 
 static void get_options(register int *argc,register char ***argv)
 {
-  int c, option_index=0;
-
   load_defaults("my", load_default_groups, argc, argv);
   default_argv= *argv;
   if (isatty(fileno(stdout)))
diff --git a/mysql-test/r/explain.result b/mysql-test/r/explain.result
index 681baea5e07..5b4da25d535 100644
--- a/mysql-test/r/explain.result
+++ b/mysql-test/r/explain.result
@@ -16,7 +16,7 @@ table	type	possible_keys	key	key_len	ref	rows	Extra
 t1	const	str	str	11	const	1	
 explain select * from t1 ignore key (str) where str="foo";
 table	type	possible_keys	key	key_len	ref	rows	Extra
-t1	ALL	str	NULL	NULL	NULL	4	where used
+t1	ALL	NULL	NULL	NULL	NULL	4	where used
 explain select * from t1 use key (str,str) where str="foo";
 table	type	possible_keys	key	key_len	ref	rows	Extra
 t1	const	str	str	11	const	1	
diff --git a/mysql-test/r/innodb.result b/mysql-test/r/innodb.result
index 1a48b6d6294..73f3dcf4899 100644
--- a/mysql-test/r/innodb.result
+++ b/mysql-test/r/innodb.result
@@ -168,9 +168,9 @@ Table	Op	Msg_type	Msg_text
 test.t1	optimize	error	The handler for the table doesn't support check/repair
 show keys from t1;
 Table	Non_unique	Key_name	Seq_in_index	Column_name	Collation	Cardinality	Sub_part	Packed	Null	Index_type	Comment
-t1	0	PRIMARY	1	id	A	87	NULL	NULL		BTREE	
-t1	1	parent_id	1	parent_id	A	21	NULL	NULL		BTREE	
-t1	1	level	1	level	A	4	NULL	NULL		BTREE	
+t1	0	PRIMARY	1	id	A	NULL	NULL	NULL		BTREE	
+t1	1	parent_id	1	parent_id	A	NULL	NULL	NULL		BTREE	
+t1	1	level	1	level	A	NULL	NULL	NULL		BTREE	
 drop table t1;
 CREATE TABLE t1 (
 gesuchnr int(11) DEFAULT '0' NOT NULL,
@@ -211,7 +211,7 @@ Table	Op	Msg_type	Msg_text
 test.t1	analyze	error	The handler for the table doesn't support check/repair
 show keys from t1;
 Table	Non_unique	Key_name	Seq_in_index	Column_name	Collation	Cardinality	Sub_part	Packed	Null	Index_type	Comment
-t1	1	skr	1	a	A	1	NULL	NULL	YES	BTREE	
+t1	1	skr	1	a	A	NULL	NULL	NULL	YES	BTREE	
 drop table t1;
 create table t1 (a int,b varchar(20),key(a)) type=innodb;
 insert into t1 values (1,""), (2,"testing");
@@ -727,7 +727,7 @@ Table	Op	Msg_type	Msg_text
 test.t1	optimize	error	The handler for the table doesn't support check/repair
 show keys from t1;
 Table	Non_unique	Key_name	Seq_in_index	Column_name	Collation	Cardinality	Sub_part	Packed	Null	Index_type	Comment
-t1	0	PRIMARY	1	a	A	1	NULL	NULL		BTREE	
+t1	0	PRIMARY	1	a	A	NULL	NULL	NULL		BTREE	
 drop table t1;
 create table t1 (i int, j int ) TYPE=innodb;
 insert into t1 values (1,2);
@@ -859,6 +859,7 @@ commit;
 drop database mysqltest;
 show tables from mysqltest;
 Got one of the listed errors
+set autocommit=0;
 create table t1 (a int not null) type= innodb;
 insert into t1 values(1),(2);
 truncate table t1;
@@ -873,6 +874,21 @@ select * from t1;
 a
 commit;
 drop table t1;
+set autocommit=1;
+create table t1 (a int not null) type= innodb;
+insert into t1 values(1),(2);
+truncate table t1;
+insert into t1 values(1),(2);
+select * from t1;
+a
+1
+2
+truncate table t1;
+insert into t1 values(1),(2);
+delete from t1;
+select * from t1;
+a
+drop table t1;
 create table t1 (a int not null, b int not null, c int not null, primary key (a),key(b)) type=innodb;
 insert into t1 values (3,3,3),(1,1,1),(2,2,2),(4,4,4);
 explain select * from t1 order by a;
@@ -911,3 +927,65 @@ Unknown table 't1' in HANDLER
 handler t1 close;
 Unknown table 't1' in HANDLER
 drop table t1;
+CREATE TABLE t1 (
+number bigint(20) NOT NULL default '0',
+cname char(15) NOT NULL default '',
+carrier_id smallint(6) NOT NULL default '0',
+privacy tinyint(4) NOT NULL default '0',
+last_mod_date timestamp(14) NOT NULL,
+last_mod_id smallint(6) NOT NULL default '0',
+last_app_date timestamp(14) NOT NULL,
+last_app_id smallint(6) default '-1',
+version smallint(6) NOT NULL default '0',
+assigned_scps int(11) default '0',
+status tinyint(4) default '0'
+) TYPE=InnoDB;
+INSERT INTO t1 VALUES (4077711111,'SeanWheeler',90,2,20020111112846,500,00000000000000,-1,2,3,1);
+INSERT INTO t1 VALUES (9197722223,'berry',90,3,20020111112809,500,20020102114532,501,4,10,0);
+INSERT INTO t1 VALUES (650,'San Francisco',0,0,20011227111336,342,00000000000000,-1,1,24,1);
+INSERT INTO t1 VALUES (302467,'Sue\'s Subshop',90,3,20020109113241,500,20020102115111,501,7,24,0);
+INSERT INTO t1 VALUES (6014911113,'SudzCarwash',520,1,20020102115234,500,20020102115259,501,33,32768,0);
+INSERT INTO t1 VALUES (333,'tubs',99,2,20020109113440,501,20020109113440,500,3,10,0);
+CREATE TABLE t2 (
+number bigint(20) NOT NULL default '0',
+cname char(15) NOT NULL default '',
+carrier_id smallint(6) NOT NULL default '0',
+privacy tinyint(4) NOT NULL default '0',
+last_mod_date timestamp(14) NOT NULL,
+last_mod_id smallint(6) NOT NULL default '0',
+last_app_date timestamp(14) NOT NULL,
+last_app_id smallint(6) default '-1',
+version smallint(6) NOT NULL default '0',
+assigned_scps int(11) default '0',
+status tinyint(4) default '0'
+) TYPE=InnoDB;
+INSERT INTO t2 VALUES (4077711111,'SeanWheeler',0,2,20020111112853,500,00000000000000,-1,2,3,1);
+INSERT INTO t2 VALUES (9197722223,'berry',90,3,20020111112818,500,20020102114532,501,4,10,0);
+INSERT INTO t2 VALUES (650,'San Francisco',90,0,20020109113158,342,00000000000000,-1,1,24,1);
+INSERT INTO t2 VALUES (333,'tubs',99,2,20020109113453,501,20020109113453,500,3,10,0);
+select * from t1;
+number	cname	carrier_id	privacy	last_mod_date	last_mod_id	last_app_date	last_app_id	version	assigned_scps	status
+4077711111	SeanWheeler	90	2	20020111112846	500	00000000000000	-1	2	3	1
+9197722223	berry	90	3	20020111112809	500	20020102114532	501	4	10	0
+650	San Francisco	0	0	20011227111336	342	00000000000000	-1	1	24	1
+302467	Sue's Subshop	90	3	20020109113241	500	20020102115111	501	7	24	0
+6014911113	SudzCarwash	520	1	20020102115234	500	20020102115259	501	33	32768	0
+333	tubs	99	2	20020109113440	501	20020109113440	500	3	10	0
+select * from t2;
+number	cname	carrier_id	privacy	last_mod_date	last_mod_id	last_app_date	last_app_id	version	assigned_scps	status
+4077711111	SeanWheeler	0	2	20020111112853	500	00000000000000	-1	2	3	1
+9197722223	berry	90	3	20020111112818	500	20020102114532	501	4	10	0
+650	San Francisco	90	0	20020109113158	342	00000000000000	-1	1	24	1
+333	tubs	99	2	20020109113453	501	20020109113453	500	3	10	0
+delete t1, t2 from t1 left join t2 on t1.number=t2.number where (t1.carrier_id=90 and t1.number=t2.number) or (t2.carrier_id=90 and t1.number=t2.number) or  (t1.carrier_id=90 and t2.number is null);
+select * from t1;
+number	cname	carrier_id	privacy	last_mod_date	last_mod_id	last_app_date	last_app_id	version	assigned_scps	status
+6014911113	SudzCarwash	520	1	20020102115234	500	20020102115259	501	33	32768	0
+333	tubs	99	2	20020109113440	501	20020109113440	500	3	10	0
+select * from t2;
+number	cname	carrier_id	privacy	last_mod_date	last_mod_id	last_app_date	last_app_id	version	assigned_scps	status
+333	tubs	99	2	20020109113453	501	20020109113453	500	3	10	0
+select * from t2;
+number	cname	carrier_id	privacy	last_mod_date	last_mod_id	last_app_date	last_app_id	version	assigned_scps	status
+333	tubs	99	2	20020109113453	501	20020109113453	500	3	10	0
+drop table t1,t2;
diff --git a/mysql-test/r/join.result b/mysql-test/r/join.result
index 011465d7553..5069fbaf527 100644
--- a/mysql-test/r/join.result
+++ b/mysql-test/r/join.result
@@ -1,4 +1,4 @@
-drop table if exists t1,t2;
+drop table if exists t1,t2,t3;
 create table t1 (id int primary key);
 create table t2 (id int);
 insert into t1 values (75);
@@ -116,3 +116,73 @@ SELECT * from t1 WHERE t1.d IS NULL;
 d
 0000-00-00
 DROP TABLE t1,t2;
+CREATE TABLE t1 (
+Document_ID varchar(50) NOT NULL default '',
+Contractor_ID varchar(6) NOT NULL default '',
+Language_ID char(3) NOT NULL default '',
+Expiration_Date datetime default NULL,
+Publishing_Date datetime default NULL,
+Title text,
+Column_ID varchar(50) NOT NULL default '',
+PRIMARY KEY  (Language_ID,Document_ID,Contractor_ID)
+);
+INSERT INTO t1 VALUES ('xep80','1','ger','2001-12-31 20:00:00','2001-11-12 10:58:00','Kartenbestellung - jetzt auch online','anle'),('','999998','',NULL,NULL,NULL,'');
+CREATE TABLE t2 (
+Contractor_ID char(6) NOT NULL default '',
+Language_ID char(3) NOT NULL default '',
+Document_ID char(50) NOT NULL default '',
+CanRead char(1) default NULL,
+Customer_ID int(11) NOT NULL default '0',
+PRIMARY KEY  (Contractor_ID,Language_ID,Document_ID,Customer_ID)
+);
+INSERT INTO t2 VALUES ('5','ger','xep80','1',999999),('1','ger','xep80','1',999999);
+CREATE TABLE t3 (
+Language_ID char(3) NOT NULL default '',
+Column_ID char(50) NOT NULL default '',
+Contractor_ID char(6) NOT NULL default '',
+CanRead char(1) default NULL,
+Active char(1) default NULL,
+PRIMARY KEY  (Language_ID,Column_ID,Contractor_ID)
+);
+INSERT INTO t3 VALUES ('ger','home','1','1','1'),('ger','Test','1','0','0'),('ger','derclu','1','0','0'),('ger','clubne','1','0','0'),('ger','philos','1','0','0'),('ger','clubko','1','0','0'),('ger','clubim','1','1','1'),('ger','progra','1','0','0'),('ger','progvo','1','0','0'),('ger','progsp','1','0','0'),('ger','progau','1','0','0'),('ger','progku','1','0','0'),('ger','progss','1','0','0'),('ger','nachl','1','0','0'),('ger','mitgli','1','0','0'),('ger','mitsu','1','0','0'),('ger','mitbus','1','0','0'),('ger','ergmar','1','1','1'),('ger','home','4','1','1'),('ger','derclu','4','1','1'),('ger','clubne','4','0','0'),('ger','philos','4','1','1'),('ger','clubko','4','1','1'),('ger','clubim','4','1','1'),('ger','progra','4','1','1'),('ger','progvo','4','1','1'),('ger','progsp','4','1','1'),('ger','progau','4','0','0'),('ger','progku','4','1','1'),('ger','progss','4','1','1'),('ger','nachl','4','1','1'),('ger','mitgli','4','0','0'),('ger','mitsu','4','0','0'),('ger','mitbus','4','0','0'),('ger','ergmar','4','1','1'),('ger','progra2','1','0','0'),('ger','archiv','4','1','1'),('ger','anmeld','4','1','1'),('ger','thema','4','1','1'),('ger','edito','4','1','1'),('ger','madis','4','1','1'),('ger','enma','4','1','1'),('ger','madis','1','1','1'),('ger','enma','1','1','1'),('ger','vorsch','4','0','0'),('ger','veranst','4','0','0'),('ger','anle','4','1','1'),('ger','redak','4','1','1'),('ger','nele','4','1','1'),('ger','aukt','4','1','1'),('ger','callcenter','4','1','1'),('ger','anle','1','0','0');
+delete from t1 where Contractor_ID='999998';
+insert into t1 (Contractor_ID) Values ('999998');
+SELECT DISTINCT COUNT(t1.Title) FROM t1,
+t2, t3 WHERE 
+t1.Document_ID='xep80' AND t1.Contractor_ID='1' AND 
+t1.Language_ID='ger' AND '2001-12-21 23:14:24' >= 
+Publishing_Date AND '2001-12-21 23:14:24' <= Expiration_Date AND 
+t1.Document_ID = t2.Document_ID AND 
+t1.Language_ID = t2.Language_ID AND 
+t1.Contractor_ID = t2.Contractor_ID AND ( 
+t2.Customer_ID = '4'  OR 
+t2.Customer_ID = '999999'  OR 
+t2.Customer_ID = '1' )AND t2.CanRead 
+= '1'  AND t1.Column_ID=t3.Column_ID AND 
+t1.Language_ID=t3.Language_ID AND ( 
+t3.Contractor_ID = '4'  OR 
+t3.Contractor_ID = '999999'  OR 
+t3.Contractor_ID = '1') AND 
+t3.CanRead='1' AND t3.Active='1';
+COUNT(t1.Title)
+1
+SELECT DISTINCT COUNT(t1.Title) FROM t1,
+t2, t3 WHERE 
+t1.Document_ID='xep80' AND t1.Contractor_ID='1' AND 
+t1.Language_ID='ger' AND '2001-12-21 23:14:24' >= 
+Publishing_Date AND '2001-12-21 23:14:24' <= Expiration_Date AND 
+t1.Document_ID = t2.Document_ID AND 
+t1.Language_ID = t2.Language_ID AND 
+t1.Contractor_ID = t2.Contractor_ID AND ( 
+t2.Customer_ID = '4'  OR 
+t2.Customer_ID = '999999'  OR 
+t2.Customer_ID = '1' )AND t2.CanRead 
+= '1'  AND t1.Column_ID=t3.Column_ID AND 
+t1.Language_ID=t3.Language_ID AND ( 
+t3.Contractor_ID = '4'  OR 
+t3.Contractor_ID = '999999'  OR 
+t3.Contractor_ID = '1') AND 
+t3.CanRead='1' AND t3.Active='1';
+COUNT(t1.Title)
+1
+drop table t1,t2,t3;
diff --git a/mysql-test/t/innodb.test b/mysql-test/t/innodb.test
index a26049fcd83..1b7ee99e15a 100644
--- a/mysql-test/t/innodb.test
+++ b/mysql-test/t/innodb.test
@@ -532,9 +532,10 @@ drop database mysqltest;
 show tables from mysqltest;
 
 #
-# Test truncate table
+# Test truncate table with and without auto_commit
 #
 
+set autocommit=0;
 create table t1 (a int not null) type= innodb;
 insert into t1 values(1),(2);
 --error 1192
@@ -547,6 +548,18 @@ delete from t1;
 select * from t1;
 commit;
 drop table t1;
+set autocommit=1;
+
+create table t1 (a int not null) type= innodb;
+insert into t1 values(1),(2);
+truncate table t1;
+insert into t1 values(1),(2);
+select * from t1;
+truncate table t1;
+insert into t1 values(1),(2);
+delete from t1;
+select * from t1;
+drop table t1;
 
 #
 # Test of how ORDER BY works when doing it on the whole table
diff --git a/mysys/default.c b/mysys/default.c
index bd19971657f..d3c1b3815eb 100644
--- a/mysys/default.c
+++ b/mysys/default.c
@@ -94,6 +94,7 @@ void load_defaults(const char *conf_file, const char **groups,
     res[0]= **argv;				/* Copy program name */
     for (i=2 ; i < (uint) *argc ; i++)
       res[i-1]=argv[0][i];
+    res[i-1]=0;					/* End pointer */
     (*argc)--;
     *argv=res;
     *(MEM_ROOT*) ptr= alloc;			/* Save alloc root for free */
diff --git a/mysys/my_getopt.c b/mysys/my_getopt.c
index 63d949e843b..d16cd7d2d86 100644
--- a/mysys/my_getopt.c
+++ b/mysys/my_getopt.c
@@ -39,8 +39,10 @@ static void init_variables(const struct my_option *options);
 #define ERR_UNKNOWN_VARIABLE      6
 #define ERR_MUST_BE_VARIABLE      7
 #define ERR_UNKNOWN_SUFFIX        8
+#define ERR_NO_PTR_TO_VARIABLE	  9
 
-static char *special_opt_prefix[]= {"skip", "disable", "enable", "maximum", 0};
+static const char *special_opt_prefix[]=
+{"skip", "disable", "enable", "maximum", 0};
 
 
 /* 
@@ -52,11 +54,12 @@ static char *special_opt_prefix[]= {"skip", "disable", "enable", "maximum", 0};
   or unknown option. Check that option was given an argument if it requires
   one. Call function 'get_one_option()' once for each option.
 */
-extern int handle_options (int *argc, char ***argv, 
-			   const struct my_option *longopts, 
-			   my_bool (*get_one_option)(int,
-						     const struct my_option *,
-						     char *))
+
+int handle_options(int *argc, char ***argv, 
+		   const struct my_option *longopts, 
+		   my_bool (*get_one_option)(int,
+					     const struct my_option *,
+					     char *))
 {
   uint opt_found, argvpos= 0, length, spec_len, i;
   int err;
@@ -70,13 +73,13 @@ extern int handle_options (int *argc, char ***argv,
   for (pos= *argv; *pos; pos++)
   {
     char *cur_arg= *pos;
-    if (*cur_arg == '-' && *(cur_arg + 1) && !end_of_options) // must be opt.
+    if (*cur_arg == '-' && *(cur_arg + 1) && !end_of_options) /* must be opt */
     {
       char *argument= 0;
       must_be_var= 0;
       set_maximum_value= 0;
 
-      // check for long option, or --set-variable (-O)
+      /* check for long option, or --set-variable (-O) */
       if (*(cur_arg + 1) == '-' || *(cur_arg + 1) == 'O')
       {
 	if (*(cur_arg + 1) == 'O' || 
@@ -89,7 +92,7 @@ extern int handle_options (int *argc, char ***argv,
 	    cur_arg+= 2;
 	    if (!(*cur_arg))
 	    {
-	      // the argument must be in next argv
+	      /* the argument must be in next argv */
 	      if (!(*(pos + 1)))
 	      {
 		fprintf(stderr, "%s: Option '-O' requires an argument\n",
@@ -101,7 +104,7 @@ extern int handle_options (int *argc, char ***argv,
 	      (*argc)--;
 	    }
 	  }
-	  else // Option argument begins with string '--set-variable'
+	  else /* Option argument begins with string '--set-variable' */
 	  {
 	    cur_arg+= 14;
 	    if (*cur_arg == '=')
@@ -115,14 +118,14 @@ extern int handle_options (int *argc, char ***argv,
 		return ERR_ARGUMENT_REQUIRED;
 	      }
 	    }
-	    else if (*cur_arg) // garbage, or another option. break out
+	    else if (*cur_arg) /* garbage, or another option. break out */
 	    {
 	      cur_arg-= 14;
 	      must_be_var= 0;
 	    }
 	    else
 	    {
-	      // the argument must be in next argv
+	      /* the argument must be in next argv */
 	      if (!(*(pos + 1)))
 	      {
 		fprintf(stderr,
@@ -138,13 +141,14 @@ extern int handle_options (int *argc, char ***argv,
 	}
 	else if (!must_be_var)
 	{
-	  if (!*(cur_arg + 2))  // '--' means end of options, look no further
+	  /* '--' means end of options, look no further */
+	  if (!*(cur_arg + 2))
 	  {
 	    end_of_options= 1;
 	    (*argc)--;
 	    continue;
 	  }
-	  cur_arg+= 2;          // skip the double dash
+	  cur_arg+= 2;          /* skip the double dash */
 	}
 	for (optend= cur_arg; *optend && *optend != '='; optend++) ;
 	length= optend - cur_arg;
@@ -169,7 +173,9 @@ extern int handle_options (int *argc, char ***argv,
 	      if (!compare_strings(special_opt_prefix[i], cur_arg, spec_len) &&
 		  cur_arg[spec_len] == '-')
 	      {
-		// We were called with a special prefix, we can reuse opt_found
+		/*
+		  We were called with a special prefix, we can reuse opt_found
+		*/
 		cur_arg += (spec_len + 1);
 		if ((opt_found= findopt(cur_arg, length - (spec_len + 1),
 					&optp, &prev_found)))
@@ -183,15 +189,15 @@ extern int handle_options (int *argc, char ***argv,
 		    return ERR_AMBIGUOUS_OPTION;
 		  }
 		  if (i < DISABLE_OPTION_COUNT)
-		    optend= "=0";
+		    optend= (char*) "=0";
 		  else if (!compare_strings(special_opt_prefix[i],"enable",6))
-		    optend= "=1";
+		    optend= (char*) "=1";
 		  else if (!compare_strings(special_opt_prefix[i],"maximum",7))
 		  {
 		    set_maximum_value= 1;
 		    must_be_var= 1;
 		  }
-		  break; // note break from the inner loop, main loop continues
+		  break; /* break from the inner loop, main loop continues */
 		}
 	      }
 	    }
@@ -229,8 +235,8 @@ extern int handle_options (int *argc, char ***argv,
 	}
 	if (must_be_var && !optp->opt_is_var)
 	{
-	  fprintf(stderr, "%s: the argument to -O must be a variable\n",
-		  progname);
+	  fprintf(stderr, "%s: the argument '%s' is not an variable\n",
+		  progname, *pos);
 	  return ERR_MUST_BE_VARIABLE;
 	}
 	if (optp->arg_type == NO_ARG && *optend == '=')
@@ -253,9 +259,9 @@ extern int handle_options (int *argc, char ***argv,
 	  (*argc)--;
 	}
 	else if (*optend == '=')
-	  argument= *(optend + 1) ? optend + 1 : "";
+	  argument= *(optend + 1) ? optend + 1 : (char*) "";
       }
-      else  // must be short option
+      else  /* must be short option */
       {
 	my_bool skip;
 	for (skip= 0, optend= (cur_arg + 1); *optend && !skip; optend++)
@@ -290,8 +296,12 @@ extern int handle_options (int *argc, char ***argv,
 		  (*argc)--;
 		}
 	      }
-	      else if (*(optend + 1)) // we are hitting many options in 1 argv
-		get_one_option(optp->id, optp, 0);
+	      else
+	      {
+		/* we are hitting many options in 1 argv */
+		if (*(optend + 1))
+		  get_one_option(optp->id, optp, 0);
+	      }
 	      break;
 	    }
 	  }
@@ -301,6 +311,12 @@ extern int handle_options (int *argc, char ***argv,
       {
 	gptr *result_pos= (set_maximum_value) ?
 	  optp->u_max_value : optp->value;
+	if (!result_pos)
+	{
+	  fprintf(stderr,
+		  "%s: Can't set a value for %s\n", progname, optp->name);
+	  return ERR_NO_PTR_TO_VARIABLE;
+	}
 	if (optp->var_type == GET_LONG)
 	 *((long*) result_pos)= (long) getopt_ll(argument, optp, &err);
 	else if (optp->var_type == GET_LL)
@@ -313,9 +329,9 @@ extern int handle_options (int *argc, char ***argv,
       else
 	get_one_option(optp->id, optp, argument);
 
-      (*argc)--; // option handled (short or long), decrease argument count
+      (*argc)--; /* option handled (short or long), decrease argument count */
     }
-    else // non-option found
+    else /* non-option found */
       (*argv)[argvpos++]= cur_arg;
   }
   return 0;
@@ -340,14 +356,14 @@ static int findopt (char *optpat, uint length,
   int count;
   struct my_option *opt= (struct my_option *) *opt_res;
 
-  for (count= 0; opt->id; opt++)
+  for (count= 0; opt->name; opt++)
   {
-    if (!compare_strings(opt->name, optpat, length)) // match found
+    if (!compare_strings(opt->name, optpat, length)) /* match found */
     {
       (*opt_res)= opt;
       if (!count)
-	*ffname= (char *) opt->name;     // we only need to know one prev
-      if (length == strlen(opt->name))   // exact match
+	*ffname= (char *) opt->name;     /* we only need to know one prev */
+      if (length == strlen(opt->name))   /* exact match */
 	return 1;
       count++;
     }
diff --git a/sql/ha_innodb.cc b/sql/ha_innodb.cc
index a86bd21934b..f99e677913e 100644
--- a/sql/ha_innodb.cc
+++ b/sql/ha_innodb.cc
@@ -1020,18 +1020,20 @@ have moved .frm files to another database?",
 		primary_key = 0;
 		key_used_on_scan = 0;
 
- 		/* MySQL allocates the buffer for ref */
-
-  		ref_length = table->key_info->key_length
-  				+ table->key_info->key_parts + 10;
-
-  		/* One byte per key field is consumed to the SQL NULL
-		info of the field; we add also 10 bytes of safety margin */
+ 		/*
+		  MySQL allocates the buffer for ref.
+		  This includes all keys + one byte for each column
+		  that may be NULL.
+		  The ref_length must be exact as possible as
+		  all reference buffers are allocated based on this.
+		*/
+
+  		ref_length = table->key_info->key_length;
 	} else {
 		((row_prebuilt_t*)innobase_prebuilt)
 				->clust_index_was_generated = TRUE;
 
-  		ref_length = DATA_ROW_ID_LEN + 10;
+  		ref_length = DATA_ROW_ID_LEN;
 
 		DBUG_ASSERT(key_used_on_scan == MAX_KEY);
 	}
@@ -1312,7 +1314,12 @@ ha_innobase::store_key_val_for_row(
 		buff += key_part->length;
   	}
 
-	DBUG_RETURN(buff - buff_start);
+	/*
+	  We have to zero-fill the buffer to be able to compare two
+	  keys to see if they are equal
+	*/
+	bzero(buff, (ref_length- (uint) (buff - buff_start)));
+	return ref_length;
 }
 
 /******************************************************************
@@ -2107,7 +2114,7 @@ ha_innobase::change_active_index(
   KEY*		key;
 
   statistic_increment(ha_read_key_count, &LOCK_status);
-  DBUG_ENTER("index_read_idx");
+  DBUG_ENTER("change_active_index");
 
   active_index = keynr;
 
@@ -2394,8 +2401,9 @@ ha_innobase::rnd_pos(
 	row_prebuilt_t*	prebuilt = (row_prebuilt_t*) innobase_prebuilt;
 	int		error;
 	uint		keynr	= active_index;
-
 	DBUG_ENTER("rnd_pos");
+	DBUG_DUMP("key", pos, ref_stored_len);
+
 	statistic_increment(ha_read_rnd_count, &LOCK_status);
 
 	if (prebuilt->clust_index_was_generated) {
@@ -2410,11 +2418,15 @@ ha_innobase::rnd_pos(
 	}
 
 	if (error) {
+	        DBUG_PRINT("error",("Got error: %ld",error));
 		DBUG_RETURN(error);
 	}
 
 	error = index_read(buf, pos, ref_stored_len, HA_READ_KEY_EXACT);
-
+	if (error)
+	{
+	  DBUG_PRINT("error",("Got error: %ld",error));
+	}
 	change_active_index(keynr);
 
   	DBUG_RETURN(error);
@@ -2449,8 +2461,7 @@ ha_innobase::position(
 		len = store_key_val_for_row(primary_key, (char*) ref, record);
 	}
 
-	DBUG_ASSERT(len <= ref_length);
-
+	DBUG_ASSERT(len == ref_length);
 	ref_stored_len = len;
 }
 
@@ -2580,7 +2591,8 @@ create_index(
 
     	ind_type = 0;
 
-    	if (strcmp(key->name, "PRIMARY") == 0) {
+    	if (key_num == form->primary_key)
+	{
 		ind_type = ind_type | DICT_CLUSTERED;
 	}
 
@@ -2652,7 +2664,7 @@ ha_innobase::create(
 	int		error;
 	dict_table_t*	innobase_table;
 	trx_t*		trx;
-	int		primary_key_no	= -1;
+	int		primary_key_no;
 	KEY*		key;
 	uint		i;
 	char		name2[FN_REFLEN];
@@ -2668,7 +2680,7 @@ ha_innobase::create(
 
   	/* Create the table definition in InnoDB */
 
-  	if (error = create_table_def(trx, form, norm_name)) {
+  	if ((error = create_table_def(trx, form, norm_name))) {
 
 		trx_commit_for_mysql(trx);
 
@@ -2679,13 +2691,9 @@ ha_innobase::create(
 
 	/* Look for a primary key */
 
-	for (i = 0; i < form->keys; i++) {
-		key = form->key_info + i;
-
-    		if (strcmp(key->name, "PRIMARY") == 0) {
-    			primary_key_no = (int) i;
-    		}
-	}
+	primary_key_no= (table->primary_key != MAX_KEY ?
+			 (int) table->primary_key : 
+			 -1);
 
 	/* Our function row_get_mysql_key_number_for_index assumes
 	the primary key is always number 0, if it exists */
@@ -3057,7 +3065,7 @@ ha_innobase::estimate_number_of_rows(void)
 		prebuilt->trx->op_info = (char*) "";
 	}
 
-	return((ha_rows) estimate);
+	DBUG_RETURN((ha_rows) estimate);
 }
 
 /*************************************************************************
@@ -3328,6 +3336,7 @@ ha_innobase::external_lock(
 				 & (OPTION_NOT_AUTO_COMMIT | OPTION_BEGIN))) {
 
 		    		innobase_commit(thd, trx);
+				thd->transaction.all.innodb_active_trans=0;
 		  	}
 		}
 	}
diff --git a/sql/opt_sum.cc b/sql/opt_sum.cc
index 69fc5aa88ff..31611798064 100644
--- a/sql/opt_sum.cc
+++ b/sql/opt_sum.cc
@@ -299,7 +299,8 @@ static bool find_range_key(TABLE_REF *ref, Field* field, COND *cond)
   uint idx=0;
 
   /* Check if some key has field as first key part */
-  if (field->key_start && (! cond || ! (cond->used_tables() & table->map)))
+  if ((field->key_start & field->table->keys_in_use_for_query) &&A
+      (! cond || ! (cond->used_tables() & table->map)))
   {
     for (key_map key=field->key_start ; !(key & 1) ; idx++)
       key>>=1;
diff --git a/sql/sql_cache.cc b/sql/sql_cache.cc
index 5b2c9673fdc..c5ebeead05a 100644
--- a/sql/sql_cache.cc
+++ b/sql/sql_cache.cc
@@ -3001,7 +3001,7 @@ my_bool Query_cache::check_integrity(bool not_locked)
   uint i;
   DBUG_ENTER("check_integrity");
 
-  if (!initialized )
+  if (query_cache_size == 0)
   {
     DBUG_PRINT("qcache", ("Query Cache not initialized"));
     DBUG_RETURN(0);
diff --git a/sql/sql_select.cc b/sql/sql_select.cc
index 4351ba3a738..0911ea57515 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -1296,11 +1296,13 @@ add_key_field(KEY_FIELD **key_fields,uint and_level,
     else
     {
       JOIN_TAB *stat=field->table->reginfo.join_tab;
-      stat[0].keys|=field->key_start;		// Add possible keys
+      key_map possible_keys= (field->key_start &
+			      field->table->keys_in_use_for_query);
+      stat[0].keys|= possible_keys;		// Add possible keys
 
       if (!value)
       {						// Probably BETWEEN or IN
-	stat[0].const_keys |= field->key_start;
+	stat[0].const_keys |= possible_keys;
 	return;					// Can't be used as eq key
       }
 
@@ -1314,7 +1316,7 @@ add_key_field(KEY_FIELD **key_fields,uint and_level,
       */
       stat[0].key_dependent|=used_tables;
       if (value->const_item())
-	stat[0].const_keys |= field->key_start;
+	stat[0].const_keys |= possible_keys;
 
       /* We can't always use indexes when comparing a string index to a
 	 number. cmp_type() is checked to allow compare of dates to numbers */
@@ -4235,7 +4237,7 @@ do_select(JOIN *join,List<Item> *fields,TABLE *table,Procedure *procedure)
       my_errno=tmp;
       error= -1;
     }
-    if (table->file->index_end())
+    if ((tmp=table->file->index_end()))
     {
       my_errno=tmp;
       error= -1;