diff options
| -rw-r--r-- | mysql-test/r/sp.result | 49 | ||||
| -rw-r--r-- | mysql-test/t/sp.test | 35 | ||||
| -rw-r--r-- | sql/sp_head.cc | 18 | ||||
| -rw-r--r-- | sql/sp_head.h | 8 | ||||
| -rw-r--r-- | sql/sql_cache.cc | 2 | ||||
| -rw-r--r-- | sql/sql_cache.h | 1 | ||||
| -rw-r--r-- | sql/sql_yacc.yy | 26 |
7 files changed, 129 insertions, 10 deletions
diff --git a/mysql-test/r/sp.result b/mysql-test/r/sp.result index f84b224b8e0..af24054934a 100644 --- a/mysql-test/r/sp.result +++ b/mysql-test/r/sp.result @@ -1719,10 +1719,10 @@ show processlist; end| call bug4902_2()| Id User Host db Command Time State Info -# root localhost test Query # NULL call bug4902_2() +# root localhost test Query # NULL show processlist call bug4902_2()| Id User Host db Command Time State Info -# root localhost test Query # NULL call bug4902_2() +# root localhost test Query # NULL show processlist drop procedure bug4902_2| drop table if exists t3| create procedure bug4904() @@ -1849,6 +1849,51 @@ select @x| NULL delete from t1| drop procedure bug4941| +drop procedure if exists bug3583| +create procedure bug3583() +begin +declare c int; +select * from t1; +select count(*) into c from t1; +select c; +end| +insert into t1 values ("x", 3), ("y", 5)| +set @x = @@query_cache_size| +set global query_cache_size = 10*1024*1024| +flush status| +flush query cache| +show status like 'Qcache_hits'| +Variable_name Value +Qcache_hits 0 +call bug3583()| +id data +x 3 +y 5 +c +2 +show status like 'Qcache_hits'| +Variable_name Value +Qcache_hits 0 +call bug3583()| +id data +x 3 +y 5 +c +2 +call bug3583()| +id data +x 3 +y 5 +c +2 +show status like 'Qcache_hits'| +Variable_name Value +Qcache_hits 2 +set global query_cache_size = @x| +flush status| +flush query cache| +delete from t1| +drop procedure bug3583| drop table if exists fac| create table fac (n int unsigned not null primary key, f bigint unsigned)| create procedure ifac(n int unsigned) diff --git a/mysql-test/t/sp.test b/mysql-test/t/sp.test index e2c82c9f0da..2f519a98488 100644 --- a/mysql-test/t/sp.test +++ b/mysql-test/t/sp.test @@ -2023,6 +2023,41 @@ drop procedure bug4941| # +# BUG#3583: query cache doesn't work for stored procedures +# +--disable_warnings +drop procedure if exists bug3583| +--enable_warnings +create procedure bug3583() +begin + declare c int; + + select * from t1; + select count(*) into c from t1; + select c; +end| + +insert into t1 values ("x", 3), ("y", 5)| +set @x = @@query_cache_size| +set global query_cache_size = 10*1024*1024| + +flush status| +flush query cache| +show status like 'Qcache_hits'| +call bug3583()| +show status like 'Qcache_hits'| +call bug3583()| +call bug3583()| +show status like 'Qcache_hits'| + +set global query_cache_size = @x| +flush status| +flush query cache| +delete from t1| +drop procedure bug3583| + + +# # Some "real" examples # diff --git a/sql/sp_head.cc b/sql/sp_head.cc index 6e4269ad8a5..6a17bc189c3 100644 --- a/sql/sp_head.cc +++ b/sql/sp_head.cc @@ -1153,9 +1153,25 @@ sp_instr_stmt::~sp_instr_stmt() int sp_instr_stmt::execute(THD *thd, uint *nextp) { + char *query; + uint32 query_length; DBUG_ENTER("sp_instr_stmt::execute"); DBUG_PRINT("info", ("command: %d", m_lex->sql_command)); - int res= exec_stmt(thd, m_lex); + int res; + + query= thd->query; + query_length= thd->query_length; + if (!(res= alloc_query(thd, m_query.str, m_query.length+1))) + { + if (query_cache_send_result_to_client(thd, + thd->query, thd->query_length) <= 0) + { + res= exec_stmt(thd, m_lex); + query_cache_end_of_result(thd); + } + thd->query= query; + thd->query_length= query_length; + } *nextp = m_ip+1; DBUG_RETURN(res); } diff --git a/sql/sp_head.h b/sql/sp_head.h index 9c308961aa4..6aa05838291 100644 --- a/sql/sp_head.h +++ b/sql/sp_head.h @@ -88,6 +88,7 @@ public: my_bool m_simple_case; // TRUE if parsing simple case, FALSE otherwise my_bool m_multi_results; // TRUE if a procedure with SELECT(s) my_bool m_in_handler; // TRUE if parser in a handler body + uchar *m_tmp_query; // Temporary pointer to sub query string uint m_old_cmq; // Old CLIENT_MULTI_QUERIES value st_sp_chistics *m_chistics; ulong m_sql_mode; // For SHOW CREATE @@ -314,9 +315,14 @@ class sp_instr_stmt : public sp_instr public: + LEX_STRING m_query; // For thd->query + sp_instr_stmt(uint ip, sp_pcontext *ctx) : sp_instr(ip, ctx), m_lex(NULL) - {} + { + m_query.str= 0; + m_query.length= 0; + } virtual ~sp_instr_stmt(); diff --git a/sql/sql_cache.cc b/sql/sql_cache.cc index 8ab7106978c..35bf6f8701f 100644 --- a/sql/sql_cache.cc +++ b/sql/sql_cache.cc @@ -611,6 +611,7 @@ void query_cache_insert(NET *net, const char *packet, ulong length) DBUG_VOID_RETURN; } header->result(result); + header->last_pkt_nr= net->pkt_nr; BLOCK_UNLOCK_WR(query_block); } else @@ -1085,6 +1086,7 @@ Query_cache::send_result_to_client(THD *thd, char *sql, uint query_length) ALIGN_SIZE(sizeof(Query_cache_result)))) break; // Client aborted result_block = result_block->next; + thd->net.pkt_nr= query->last_pkt_nr; // Keep packet number updated } while (result_block != first_result_block); #else { diff --git a/sql/sql_cache.h b/sql/sql_cache.h index 432c7659aa5..b54bc4f7b7b 100644 --- a/sql/sql_cache.h +++ b/sql/sql_cache.h @@ -116,6 +116,7 @@ struct Query_cache_query NET *wri; ulong len; uint8 tbls_type; + unsigned int last_pkt_nr; inline void init_n_lock(); void unlock_n_destroy(); diff --git a/sql/sql_yacc.yy b/sql/sql_yacc.yy index 920f6fd4a82..79c5c094c58 100644 --- a/sql/sql_yacc.yy +++ b/sql/sql_yacc.yy @@ -1788,17 +1788,21 @@ sp_opt_default: sp_proc_stmt: { - Lex->sphead->reset_lex(YYTHD); + LEX *lex= Lex; + + lex->sphead->reset_lex(YYTHD); + lex->sphead->m_tmp_query= lex->tok_start; } statement { LEX *lex= Lex; + sp_head *sp= lex->sphead; if ((lex->sql_command == SQLCOM_SELECT && !lex->result) || sp_multi_results_command(lex->sql_command)) { /* We maybe have one or more SELECT without INTO */ - lex->sphead->m_multi_results= TRUE; + sp->m_multi_results= TRUE; } if (lex->sql_command == SQLCOM_CHANGE_DB) { /* "USE db" doesn't work in a procedure */ @@ -1819,22 +1823,31 @@ sp_proc_stmt: especially triggers a tremendously, but it's nothing we can do about this at the moment. */ - if (lex->sphead->m_type != TYPE_ENUM_PROCEDURE) + if (sp->m_type != TYPE_ENUM_PROCEDURE) { send_error(YYTHD, ER_SP_BADSTATEMENT); YYABORT; } else { - sp_instr_stmt *i=new sp_instr_stmt(lex->sphead->instructions(), + sp_instr_stmt *i=new sp_instr_stmt(sp->instructions(), lex->spcont); + /* Extract the query statement from the tokenizer: + The end is either lex->tok_end or tok->ptr. */ + if (lex->ptr - lex->tok_end > 1) + i->m_query.length= lex->ptr - sp->m_tmp_query; + else + i->m_query.length= lex->tok_end - sp->m_tmp_query; + i->m_query.str= strmake_root(&YYTHD->mem_root, + (char *)sp->m_tmp_query, + i->m_query.length); i->set_lex(lex); - lex->sphead->add_instr(i); + sp->add_instr(i); lex->sp_lex_in_use= TRUE; } } - lex->sphead->restore_lex(YYTHD); + sp->restore_lex(YYTHD); } | RETURN_SYM expr { @@ -6482,6 +6495,7 @@ simple_ident: } $$ = (Item*) new Item_splocal($1, spv->offset); lex->variables_used= 1; + lex->safe_to_cache_query=0; } else { |