diff options
| -rw-r--r-- | include/m_string.h | 2 | ||||
| -rw-r--r-- | myisam/mi_create.c | 8 | ||||
| -rw-r--r-- | mysql-test/r/bdb-crash.result | 7 | ||||
| -rw-r--r-- | mysql-test/r/grant_cache.result | 42 | ||||
| -rw-r--r-- | mysql-test/r/myisam.result | 22 | ||||
| -rw-r--r-- | mysql-test/r/order_by.result | 97 | ||||
| -rw-r--r-- | mysql-test/t/bdb-crash.test | 4 | ||||
| -rw-r--r-- | mysql-test/t/grant_cache.test | 12 | ||||
| -rw-r--r-- | mysql-test/t/myisam.test | 14 | ||||
| -rw-r--r-- | mysql-test/t/order_by.test | 17 | ||||
| -rw-r--r-- | sql/ha_berkeley.cc | 11 | ||||
| -rw-r--r-- | sql/sql_cache.cc | 17 | ||||
| -rw-r--r-- | sql/sql_select.cc | 22 | ||||
| -rw-r--r-- | sql/sql_table.cc | 6 |
14 files changed, 165 insertions, 116 deletions
diff --git a/include/m_string.h b/include/m_string.h index 21aa736acd4..c86801ae2d2 100644 --- a/include/m_string.h +++ b/include/m_string.h @@ -125,7 +125,7 @@ extern void bmove_align(gptr dst,const gptr src,uint len); #ifdef HAVE_purify #include <assert.h> #define memcpy_overlap(A,B,C) \ -DBUG_ASSERT((A) == (B) || ((A)+(C)) <= (B) || ((B)+(C)) <= (A)); \ +DBUG_ASSERT((A) <= (B) || ((B)+(C)) <= (A)); \ bmove((byte*) (A),(byte*) (B),(size_t) (C)); #else #define memcpy_overlap(A,B,C) memcpy((A), (B), (C)) diff --git a/myisam/mi_create.c b/myisam/mi_create.c index 72633966b54..5682862c39a 100644 --- a/myisam/mi_create.c +++ b/myisam/mi_create.c @@ -383,7 +383,13 @@ int mi_create(const char *name,uint keys,MI_KEYDEF *keydefs, my_errno=HA_WRONG_CREATE_OPTION; goto err; } - if ((keydef->flag & (HA_NOSAME | HA_NULL_PART_KEY)) == HA_NOSAME) + /* + key_segs may be 0 in the case when we only want to be able to + add on row into the table. This can happen with some DISTINCT queries + in MySQL + */ + if ((keydef->flag & (HA_NOSAME | HA_NULL_PART_KEY)) == HA_NOSAME && + key_segs) share.state.rec_per_key_part[key_segs-1]=1L; length+=key_length; keydef->block_length= MI_BLOCK_SIZE(length,pointer,MI_MAX_KEYPTR_SIZE); diff --git a/mysql-test/r/bdb-crash.result b/mysql-test/r/bdb-crash.result index 778890e85e3..e414934b07c 100644 --- a/mysql-test/r/bdb-crash.result +++ b/mysql-test/r/bdb-crash.result @@ -37,3 +37,10 @@ analyze table t1; Table Op Msg_type Msg_text test.t1 analyze status Operation need committed state drop table t1; +create table t1 (a int) engine=bdb; +set autocommit=0; +insert into t1 values(1); +analyze table t1; +Table Op Msg_type Msg_text +test.t1 analyze status Operation need committed state +drop table t1; diff --git a/mysql-test/r/grant_cache.result b/mysql-test/r/grant_cache.result index f49db67a9f7..fc4073d23b1 100644 --- a/mysql-test/r/grant_cache.result +++ b/mysql-test/r/grant_cache.result @@ -41,17 +41,53 @@ grant SELECT on mysqltest.* to mysqltest_1@localhost; grant SELECT on mysqltest.t1 to mysqltest_2@localhost; grant SELECT on test.t1 to mysqltest_2@localhost; grant SELECT(a) on mysqltest.t1 to mysqltest_3@localhost; +show status like "Qcache_queries_in_cache"; +Variable_name Value +Qcache_queries_in_cache 6 +show status like "Qcache_hits"; +Variable_name Value +Qcache_hits 0 +show status like "Qcache_not_cached"; +Variable_name Value +Qcache_not_cached 0 select "user1"; user1 user1 +show status like "Qcache_queries_in_cache"; +Variable_name Value +Qcache_queries_in_cache 6 +show status like "Qcache_hits"; +Variable_name Value +Qcache_hits 0 +show status like "Qcache_not_cached"; +Variable_name Value +Qcache_not_cached 1 select * from t1; a b c 1 1 1 2 2 2 +show status like "Qcache_queries_in_cache"; +Variable_name Value +Qcache_queries_in_cache 6 +show status like "Qcache_hits"; +Variable_name Value +Qcache_hits 1 +show status like "Qcache_not_cached"; +Variable_name Value +Qcache_not_cached 1 select a from t1 ; a 1 2 +show status like "Qcache_queries_in_cache"; +Variable_name Value +Qcache_queries_in_cache 6 +show status like "Qcache_hits"; +Variable_name Value +Qcache_hits 2 +show status like "Qcache_not_cached"; +Variable_name Value +Qcache_not_cached 1 select c from t1; c 1 @@ -94,7 +130,7 @@ Variable_name Value Qcache_hits 7 show status like "Qcache_not_cached"; Variable_name Value -Qcache_not_cached 3 +Qcache_not_cached 2 select "user3"; user3 user3 @@ -118,7 +154,7 @@ Variable_name Value Qcache_hits 7 show status like "Qcache_not_cached"; Variable_name Value -Qcache_not_cached 8 +Qcache_not_cached 7 select "user4"; user4 user4 @@ -144,7 +180,7 @@ Variable_name Value Qcache_hits 8 show status like "Qcache_not_cached"; Variable_name Value -Qcache_not_cached 9 +Qcache_not_cached 8 delete from mysql.user where user in ("mysqltest_1","mysqltest_2","mysqltest_3"); delete from mysql.db where user in ("mysqltest_1","mysqltest_2","mysqltest_3"); delete from mysql.tables_priv where user in ("mysqltest_1","mysqltest_2","mysqltest_3"); diff --git a/mysql-test/r/myisam.result b/mysql-test/r/myisam.result index 1433c50f25c..5dc4803137e 100644 --- a/mysql-test/r/myisam.result +++ b/mysql-test/r/myisam.result @@ -460,6 +460,28 @@ a concat(b,'.') 1 a. 3 a . drop table t1; +create table t1 (a int not null); +create table t2 (a int not null, primary key (a)); +insert into t1 values (1); +insert into t2 values (1),(2); +select sql_big_result distinct t1.a from t1,t2 order by t2.a; +a +1 +select distinct t1.a from t1,t2 order by t2.a; +a +1 +select sql_big_result distinct t1.a from t1,t2; +a +1 +explain select sql_big_result distinct t1.a from t1,t2 order by t2.a; +table type possible_keys key key_len ref rows Extra +t1 system NULL NULL NULL NULL 1 Using temporary +t2 index NULL PRIMARY 4 NULL 2 Using index; Distinct +explain select distinct t1.a from t1,t2 order by t2.a; +table type possible_keys key key_len ref rows Extra +t1 system NULL NULL NULL NULL 1 Using temporary +t2 index NULL PRIMARY 4 NULL 2 Using index; Distinct +drop table t1,t2; CREATE TABLE t1 (`a` int(11) NOT NULL default '0', `b` int(11) NOT NULL default '0', UNIQUE KEY `a` USING RTREE (`a`,`b`)) ENGINE=MyISAM; ERROR 42000: This version of MySQL doesn't yet support 'RTREE INDEX' create table t1 (a int, b varchar(200), c text not null) checksum=1; diff --git a/mysql-test/r/order_by.result b/mysql-test/r/order_by.result index 006b507d409..2e9fe1995d0 100644 --- a/mysql-test/r/order_by.result +++ b/mysql-test/r/order_by.result @@ -546,100 +546,3 @@ a b 1 2 5 NULL DROP TABLE t1; -CREATE TABLE t1 ( -FieldKey varchar(36) NOT NULL default '', -LongVal bigint(20) default NULL, -StringVal mediumtext, -KEY FieldKey (FieldKey), -KEY LongField (FieldKey,LongVal), -KEY StringField (FieldKey,StringVal(32)) -); -INSERT INTO t1 VALUES ('0',3,'0'),('0',2,'1'),('0',1,'2'),('1',2,'1'),('1',1,'3'), ('1',0,'2'),('2',3,'0'),('2',2,'1'),('2',1,'2'),('2',3,'0'),('2',2,'1'),('2',1,'2'),('3',2,'1'),('3',1,'2'),('3','3','3'); -EXPLAIN SELECT * FROM t1 WHERE FieldKey = '1' ORDER BY LongVal; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref FieldKey,LongField,StringField LongField 36 const 3 Using where -SELECT * FROM t1 WHERE FieldKey = '1' ORDER BY LongVal; -FieldKey LongVal StringVal -1 0 2 -1 1 3 -1 2 1 -EXPLAIN SELECT * FROM t1 WHERE FieldKey > '2' ORDER BY LongVal; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range FieldKey,LongField,StringField FieldKey 36 NULL 4 Using where; Using filesort -SELECT * FROM t1 WHERE FieldKey > '2' ORDER BY LongVal; -FieldKey LongVal StringVal -3 1 2 -3 2 1 -3 3 3 -EXPLAIN SELECT * FROM t1 WHERE FieldKey > '2' ORDER BY FieldKey, LongVal; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range FieldKey,LongField,StringField LongField 36 NULL 4 Using where -SELECT * FROM t1 WHERE FieldKey > '2' ORDER BY FieldKey, LongVal; -FieldKey LongVal StringVal -3 1 2 -3 2 1 -3 3 3 -DROP TABLE t1; -CREATE TABLE t1 (a INT, b INT); -SET @id=0; -UPDATE t1 SET a=0 ORDER BY (a=@id), b; -DROP TABLE t1; -CREATE TABLE t1 ( id smallint(6) unsigned NOT NULL default '0', menu tinyint(4) NOT NULL default '0', KEY id (id), KEY menu (menu)) ENGINE=MyISAM; -INSERT INTO t1 VALUES (11384, 2),(11392, 2); -SELECT id FROM t1 WHERE id <11984 AND menu =2 ORDER BY id DESC LIMIT 1 ; -id -11392 -drop table t1; -create table t1(a int, b int, index(b)); -insert into t1 values (2, 1), (1, 1), (4, NULL), (3, NULL), (6, 2), (5, 2); -explain select * from t1 where b=1 or b is null order by a; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref_or_null b b 5 const 3 Using where; Using filesort -select * from t1 where b=1 or b is null order by a; -a b -1 1 -2 1 -3 NULL -4 NULL -explain select * from t1 where b=2 or b is null order by a; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref_or_null b b 5 const 4 Using where; Using filesort -select * from t1 where b=2 or b is null order by a; -a b -3 NULL -4 NULL -5 2 -6 2 -drop table t1; -create table t1 (a int not null auto_increment, b int not null, c int not null, d int not null, -key(a,b,d), key(c,b,a)); -create table t2 like t1; -insert into t1 values (NULL, 1, 2, 0), (NULL, 2, 1, 1), (NULL, 3, 4, 2), (NULL, 4, 3, 3); -insert into t2 select null, b, c, d from t1; -insert into t1 select null, b, c, d from t2; -insert into t2 select null, b, c, d from t1; -insert into t1 select null, b, c, d from t2; -insert into t2 select null, b, c, d from t1; -insert into t1 select null, b, c, d from t2; -insert into t2 select null, b, c, d from t1; -insert into t1 select null, b, c, d from t2; -insert into t2 select null, b, c, d from t1; -insert into t1 select null, b, c, d from t2; -optimize table t1; -Table Op Msg_type Msg_text -test.t1 optimize status OK -set @row=10; -insert into t1 select 1, b, c + (@row:=@row - 1) * 10, d - @row from t2 limit 10; -select * from t1 where a=1 and b in (1) order by c, b, a; -a b c d -1 1 2 0 -1 1 12 -1 -1 1 52 -5 -1 1 92 -9 -select * from t1 where a=1 and b in (1); -a b c d -1 1 92 -9 -1 1 52 -5 -1 1 12 -1 -1 1 2 0 -drop table t1, t2; diff --git a/mysql-test/t/bdb-crash.test b/mysql-test/t/bdb-crash.test index cea69df6993..3cd78821000 100644 --- a/mysql-test/t/bdb-crash.test +++ b/mysql-test/t/bdb-crash.test @@ -39,8 +39,7 @@ drop table t1; # # Test for bug #2342 "Running ANALYZE TABLE on bdb table -# inside a transaction hangs server thread" -# +# inside a transaction hangs server thread" create table t1 (a int) engine=bdb; @@ -48,4 +47,3 @@ set autocommit=0; insert into t1 values(1); analyze table t1; drop table t1; - diff --git a/mysql-test/t/grant_cache.test b/mysql-test/t/grant_cache.test index 9ba845d6baa..5ebf621ff40 100644 --- a/mysql-test/t/grant_cache.test +++ b/mysql-test/t/grant_cache.test @@ -43,10 +43,22 @@ grant SELECT(a) on mysqltest.t1 to mysqltest_3@localhost; # The following queries should be fetched from cache connect (user1,localhost,mysqltest_1,,mysqltest,$MASTER_MYPORT,master.sock); connection user1; +show status like "Qcache_queries_in_cache"; +show status like "Qcache_hits"; +show status like "Qcache_not_cached"; select "user1"; +show status like "Qcache_queries_in_cache"; +show status like "Qcache_hits"; +show status like "Qcache_not_cached"; select * from t1; +show status like "Qcache_queries_in_cache"; +show status like "Qcache_hits"; +show status like "Qcache_not_cached"; # The pre and end space are intentional select a from t1 ; +show status like "Qcache_queries_in_cache"; +show status like "Qcache_hits"; +show status like "Qcache_not_cached"; select c from t1; show status like "Qcache_queries_in_cache"; show status like "Qcache_hits"; diff --git a/mysql-test/t/myisam.test b/mysql-test/t/myisam.test index 65bc4f1fff6..95847ba1af7 100644 --- a/mysql-test/t/myisam.test +++ b/mysql-test/t/myisam.test @@ -448,6 +448,20 @@ select a,concat(b,'.') from t1; drop table t1; # +# Test keys with 0 segments. (Bug #3203) +# +create table t1 (a int not null); +create table t2 (a int not null, primary key (a)); +insert into t1 values (1); +insert into t2 values (1),(2); +select sql_big_result distinct t1.a from t1,t2 order by t2.a; +select distinct t1.a from t1,t2 order by t2.a; +select sql_big_result distinct t1.a from t1,t2; +explain select sql_big_result distinct t1.a from t1,t2 order by t2.a; +explain select distinct t1.a from t1,t2 order by t2.a; +drop table t1,t2; + +# # Test RTREE index # --error 1235 diff --git a/mysql-test/t/order_by.test b/mysql-test/t/order_by.test index 2cd8dbbb95a..aa3e7429bf2 100644 --- a/mysql-test/t/order_by.test +++ b/mysql-test/t/order_by.test @@ -347,6 +347,23 @@ SELECT * FROM t1 ORDER BY (a + b) DESC; DROP TABLE t1; # +# Test of FORCE INDEX ... ORDER BY +# + +create table t1(id int not null auto_increment primary key, t char(12)); +disable_query_log; +let $1 = 1000; +while ($1) + { + eval insert into t1(t) values ('$1'); + dec $1; + } +enable_query_log; +explain select id,t from t1 order by id; +explain select id,t from t1 force index (primary) order by id; +drop table t1;s + +# # Test of test_if_subkey() function # CREATE TABLE t1 ( diff --git a/sql/ha_berkeley.cc b/sql/ha_berkeley.cc index 4a1ce43cf70..612a9c33b45 100644 --- a/sql/ha_berkeley.cc +++ b/sql/ha_berkeley.cc @@ -2123,10 +2123,19 @@ static void print_msg(THD *thd, const char *table_name, const char *op_name, int ha_berkeley::analyze(THD* thd, HA_CHECK_OPT* check_opt) { - DB_BTREE_STAT *stat=0; uint i; + DB_BTREE_STAT *stat=0; DB_TXN_STAT *txn_stat_ptr= 0; + /* + Original bdb documentation says: + "The DB->stat method cannot be transaction-protected. + For this reason, it should be called in a thread of + control that has no open cursors or active transactions." + So, let's check if there are any changes have been done since + the beginning of the transaction.. + */ + if (!db_env->txn_stat(db_env, &txn_stat_ptr, 0) && txn_stat_ptr && txn_stat_ptr->st_nactive>=2) { diff --git a/sql/sql_cache.cc b/sql/sql_cache.cc index 40d9b1b42d8..52b0b05a757 100644 --- a/sql/sql_cache.cc +++ b/sql/sql_cache.cc @@ -601,7 +601,6 @@ void query_cache_insert(NET *net, const char *packet, ulong length) if (!query_cache.append_result_data(&result, length, (gptr) packet, query_block)) { - query_cache.refused++; DBUG_PRINT("warning", ("Can't append data")); header->result(result); DBUG_PRINT("qcache", ("free query 0x%lx", (ulong) query_block)); @@ -885,7 +884,8 @@ void Query_cache::store_query(THD *thd, TABLE_LIST *tables_used) } } else - statistic_increment(refused, &structure_guard_mutex); + if (thd->lex.sql_command == SQLCOM_SELECT) + statistic_increment(refused, &structure_guard_mutex); end: DBUG_VOID_RETURN; @@ -1024,7 +1024,6 @@ Query_cache::send_result_to_client(THD *thd, char *sql, uint query_length) DBUG_PRINT("qcache", ("probably no SELECT access to %s.%s => return to normal processing", table_list.db, table_list.alias)); - refused++; // This is actually a hit STRUCT_UNLOCK(&structure_guard_mutex); thd->lex->safe_to_cache_query=0; // Don't try to cache this BLOCK_UNLOCK_RD(query_block); @@ -1680,6 +1679,12 @@ void Query_cache::free_query(Query_cache_block *query_block) */ if (result_block != 0) { + if (result_block->type != Query_cache_block::RESULT) + { + // removing unfinished query + refused++; + inserts--; + } Query_cache_block *block = result_block; do { @@ -1688,6 +1693,12 @@ void Query_cache::free_query(Query_cache_block *query_block) free_memory_block(current); } while (block != result_block); } + else + { + // removing unfinished query + refused++; + inserts--; + } query->unlock_n_destroy(); free_memory_block(query_block); diff --git a/sql/sql_select.cc b/sql/sql_select.cc index 34bfabd845f..2ac1b116635 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -7161,7 +7161,8 @@ test_if_skip_sort_order(JOIN_TAB *tab,ORDER *order,ha_rows select_limit, /* check if we can use a key to resolve the group */ /* Tables using JT_NEXT are handled here */ uint nr; - key_map keys; + key_map keys_to_use, keys; + keys_to_use.set_all(); /* If not used with LIMIT, only use keys if the whole query can be @@ -7169,14 +7170,21 @@ test_if_skip_sort_order(JOIN_TAB *tab,ORDER *order,ha_rows select_limit, retrieving all rows through an index. */ if (select_limit >= table->file->records) - { - keys=*table->file->keys_to_use_for_scanning(); - keys.merge(table->used_keys); + +nning(); + + + +ified in FORCE INDEX clause, + +n ORDER BY. + + +ry); + } else - keys.set_all(); - - keys.intersect(usable_keys); + keys= usable_keys; for (nr=0; nr < table->keys ; nr++) { diff --git a/sql/sql_table.cc b/sql/sql_table.cc index 48007b44f43..b3728f4833e 100644 --- a/sql/sql_table.cc +++ b/sql/sql_table.cc @@ -1672,6 +1672,12 @@ static int mysql_admin_table(THD* thd, TABLE_LIST* tables, open_for_modify= false; break; + case HA_ADMIN_REJECT: + net_store_data(packet,"status"); + net_store_data(packet,"Operation need committed state"); + open_for_modify= false; + break; + case HA_ADMIN_ALREADY_DONE: protocol->store("status", 6, system_charset_info); protocol->store("Table is already up to date", 27, system_charset_info); |