bk commit into 5.0 tree (msvensson:1.1964) BUG#6897

[msvensson]

Below is the list of changes that have just been committed into a local
5.0 repository of msvensson. When msvensson does a push these changes will
be propagated to the main repository and, within 24 hours after the
push, to the public repository.
For information on how to access the public repository
see http://dev.mysql.com/doc/mysql/en/installing-source-tree.html

ChangeSet
  1.1964 05/09/21 15:08:33 msvensson@neptunus.(none) +9 -0
  Bug #13231 mysqltest: fails to dectect when mysql_next_result fails
   - Added functionality to check errors returned from mysql_next_result
   - Exit from mysqltest when and unexpected error occurs.
   - The above fixes reveal problems with rpl000009, sp-error and query_cache-
   - Fix sp-error by adding an expected error
   - Fix rpl000009 by not sending "ok" from mysql_create_db when called with silent flag
from load_master_data
   - Fix query_cache by making sure the packet nr in packet header is updated before
sending
     result to client. Remove the fix for bug#3583, not needed. Test results for bug#6897
     updated and improved. The multiple resultsets retrieved when calling the sp was not
in the result set. Thus the 
     old version passed tests.
     Note! The rewrite_pkt_nr writes the new pkt_nr's directly into the query_cache which
is locked for read, might 
     need to set write lock or copy out if the query cache can be read simultaneously from
two threads.

  sql/sql_db.cc
    1.117 05/09/21 15:08:28 msvensson@neptunus.(none) +2 -1
    Don't send ok in mysql_create_db if silent flag is set.

  sql/sql_cache.h
    1.31 05/09/21 15:08:28 msvensson@neptunus.(none) +0 -1
    Remove last_pkt_nr variable

  sql/sql_cache.cc
    1.85 05/09/21 15:08:28 msvensson@neptunus.(none) +36 -6
    Rewrite pkt_nr in each packet of each block in the query cache before sending the
blocks to the client.
    Remove fix for bug#3583 "query cache doesn't work for stored procedures"

  mysql-test/t/sp-error.test
    1.88 05/09/21 15:08:28 msvensson@neptunus.(none) +25 -1
    Add missing --error 1326 before call to sp that closes a already closed cursor.
    Add test for bug9367

  mysql-test/t/query_cache.test
    1.48 05/09/21 15:08:28 msvensson@neptunus.(none) +39 -0
    Improve test for queries that are retrieved from query_cache  from both a regular
statment and a statement inside a stored procedure.

  mysql-test/r/sp-error.result
    1.84 05/09/21 15:08:28 msvensson@neptunus.(none) +1 -0
    Update test result to match the expected error from calling the sp closing a cursor
that is not open.

  mysql-test/r/query_cache.result
    1.64 05/09/21 15:08:28 msvensson@neptunus.(none) +117 -0
    Update test results
    Add the resultsets that wasn't there before.

  mysql-test/r/mysqltest.result
    1.13 05/09/21 15:08:28 msvensson@neptunus.(none) +0 -2
    mysqltest now dies when a query fails with wrong errno

  client/mysqltest.c
    1.160 05/09/21 15:08:28 msvensson@neptunus.(none) +52 -88
    Check and handle error after mysql_next_result
    Change several verbose_msg to die so that the error is properly reported
    Clean up of error handling code in run_query_stmt, check all errors and use common
    function handle_error.

# This is a BitKeeper patch.  What follows are the unified diffs for the
# set of deltas contained in the patch.  The rest of the patch, the part
# that BitKeeper cares about, is below these diffs.
# User:	msvensson
# Host:	neptunus.(none)
# Root:	/home/msvensson/mysql/bug13231/my50-bug13231

--- 1.84/sql/sql_cache.cc	2005-09-06 19:51:08 +02:00
+++ 1.85/sql/sql_cache.cc	2005-09-21 15:08:28 +02:00
@@ -614,7 +614,6 @@
       DBUG_VOID_RETURN;
     }
     header->result(result);
-    header->last_pkt_nr= net->pkt_nr;
     BLOCK_UNLOCK_WR(query_block);
   }
   else
@@ -937,6 +936,35 @@
 
 
 /*
+  Rewrite pkt_nr in each packet of the block
+
+*/
+void rewrite_pkt_nr(THD* thd, uchar *packet, ulong packet_len, ulong* left)
+{
+  uchar* end= packet + packet_len;
+  packet+= *left;
+  do
+  {
+    // Read packet header
+    uint32 len= uint3korr(packet);
+    uchar pkt_nr= packet[3];
+    DBUG_PRINT("info", ("len: %d, pkt_nr: %d",
+                        len, pkt_nr));
+
+    // Update packet header with new packet nr
+    packet[3]= (uchar)(thd->net.pkt_nr++);
+    DBUG_PRINT("info", ("new_pkt_nr: %d", thd->net.pkt_nr));
+
+    // Step to next packet header
+    packet+= len + NET_HEADER_SIZE;
+  } while(packet < end);
+
+  // Packet can continue in next block
+  *left= packet-end;
+}
+
+
+/*
   Check if the query is in the cache. If it was cached, send it
   to the user.
 
@@ -960,6 +988,7 @@
   Query_cache_block_table *block_table, *block_table_end;
   ulong tot_length;
   Query_cache_query_flags flags;
+  ulong length_in_next= 0;
   DBUG_ENTER("Query_cache::send_result_to_client");
 
   if (query_cache_size == 0 || thd->locked_tables ||
@@ -1181,15 +1210,16 @@
 			  result_block->length, result_block->used,
 			  result_block->headers_len()+
 			  ALIGN_SIZE(sizeof(Query_cache_result))));
-    
+
     Query_cache_result *result = result_block->result();
+    ulong result_len= result_block->used - result_block->headers_len() -
+      ALIGN_SIZE(sizeof(Query_cache_result));
+
+    rewrite_pkt_nr(thd, (uchar*)result->data(), result_len, &length_in_next);
     if (net_real_write(&thd->net, result->data(),
-		       result_block->used -
-		       result_block->headers_len() -
-		       ALIGN_SIZE(sizeof(Query_cache_result))))
+		       result_len))
       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
   {

--- 1.116/sql/sql_db.cc	2005-09-12 15:58:29 +02:00
+++ 1.117/sql/sql_db.cc	2005-09-21 15:08:28 +02:00
@@ -440,7 +440,8 @@
     push_warning_printf(thd, MYSQL_ERROR::WARN_LEVEL_NOTE,
                         ER_DB_CREATE_EXISTS, ER(ER_DB_CREATE_EXISTS), db);
     error= 0;
-    send_ok(thd);
+    if (!silent)
+      send_ok(thd);
     goto exit;
   }
   else

--- 1.12/mysql-test/r/mysqltest.result	2005-09-06 12:33:31 +02:00
+++ 1.13/mysql-test/r/mysqltest.result	2005-09-21 15:08:28 +02:00
@@ -134,7 +134,6 @@
 after_!errno_masked_error
 1146
 mysqltest: At line 1: query 'select 3 from t1' failed with wrong errno 1146 instead of
1000...
-mysqltest: At line 1: query 'select 3 from t1' failed with wrong errno 1146 instead of
1000...
 garbage ;
 ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to
your MySQL server version for the right syntax to use near 'garbage' at line 1
 select 1064 as "after_--enable_abort_on_error" ;
@@ -143,7 +142,6 @@
 select 3 from t1 ;
 ERROR 42S02: Table 'test.t1' doesn't exist
 mysqltest: At line 1: query 'select 3 from t1' failed with wrong errno 1146 instead of
1064...
-mysqltest: At line 1: query 'select 3 from t1' failed: 1146: Table 'test.t1' doesn't
exist
 hello
 hello
 ;;;;;;;;

--- 1.63/mysql-test/r/query_cache.result	2005-08-11 19:42:02 +02:00
+++ 1.64/mysql-test/r/query_cache.result	2005-09-21 15:08:28 +02:00
@@ -1058,8 +1058,23 @@
 select sql_cache * from t1;
 select sql_cache * from t1;
 end;//
+create procedure f2 () begin
+select sql_cache * from t1 where s1=1;
+select sql_cache * from t1;
+end;//
+create procedure f3 () begin
+select sql_cache * from t1;
+select sql_cache * from t1 where s1=1;
+end;//
+create procedure f4 () begin
+select sql_cache * from t1;
+select sql_cache * from t1 where s1=1;
+select sql_cache * from t1;
+select sql_cache * from t1 where s1=1;
+end;//
 call f1();
 s1
+s1
 show status like "Qcache_queries_in_cache";
 Variable_name	Value
 Qcache_queries_in_cache	1
@@ -1071,6 +1086,7 @@
 Qcache_hits	1
 call f1();
 s1
+s1
 show status like "Qcache_queries_in_cache";
 Variable_name	Value
 Qcache_queries_in_cache	1
@@ -1082,6 +1098,7 @@
 Qcache_hits	3
 call f1();
 s1
+s1
 select sql_cache * from t1;
 s1
 show status like "Qcache_queries_in_cache";
@@ -1109,9 +1126,13 @@
 call f1();
 s1
 1
+s1
+1
 call f1();
 s1
 1
+s1
+1
 select sql_cache * from t1;
 s1
 1
@@ -1124,6 +1145,102 @@
 show status like "Qcache_hits";
 Variable_name	Value
 Qcache_hits	9
+flush query cache;
+reset query cache;
+flush status;
+select sql_cache * from t1;
+s1
+1
+select sql_cache * from t1 where s1=1;
+s1
+1
+call f1();
+s1
+1
+s1
+1
+call f2();
+s1
+1
+s1
+1
+call f3();
+s1
+1
+s1
+1
+call f4();
+s1
+1
+s1
+1
+s1
+1
+s1
+1
+call f4();
+s1
+1
+s1
+1
+s1
+1
+s1
+1
+call f3();
+s1
+1
+s1
+1
+call f2();
+s1
+1
+s1
+1
+insert into t1 values (2);
+call f1();
+s1
+1
+2
+s1
+1
+2
+select sql_cache * from t1 where s1=1;
+s1
+1
+select sql_cache * from t1;
+s1
+1
+2
+call f1();
+s1
+1
+2
+s1
+1
+2
+call f3();
+s1
+1
+2
+s1
+1
+call f3();
+s1
+1
+2
+s1
+1
+call f1();
+s1
+1
+2
+s1
+1
+2
 drop procedure f1;
+drop procedure f2;
+drop procedure f3;
+drop procedure f4;
 drop table t1;
 set GLOBAL query_cache_size=0;

--- 1.47/mysql-test/t/query_cache.test	2005-08-07 23:32:24 +02:00
+++ 1.48/mysql-test/t/query_cache.test	2005-09-21 15:08:28 +02:00
@@ -776,6 +776,7 @@
 
 #
 # query in QC from normal execution and SP (BUG#6897)
+# improved to also test BUG#3583 and BUG#12990
 #
 flush query cache;
 reset query cache;
@@ -786,6 +787,20 @@
 select sql_cache * from t1;
 select sql_cache * from t1;
 end;//
+create procedure f2 () begin
+select sql_cache * from t1 where s1=1;
+select sql_cache * from t1;
+end;//
+create procedure f3 () begin
+select sql_cache * from t1;
+select sql_cache * from t1 where s1=1;
+end;//
+create procedure f4 () begin
+select sql_cache * from t1;
+select sql_cache * from t1 where s1=1;
+select sql_cache * from t1;
+select sql_cache * from t1 where s1=1;
+end;//
 delimiter ;//
 call f1();
 show status like "Qcache_queries_in_cache";
@@ -811,7 +826,31 @@
 show status like "Qcache_queries_in_cache";
 show status like "Qcache_inserts";
 show status like "Qcache_hits";
+flush query cache;
+reset query cache;
+flush status;
+select sql_cache * from t1;
+select sql_cache * from t1 where s1=1;
+call f1();
+call f2();
+call f3();
+call f4();
+call f4();
+call f3();
+call f2();
+insert into t1 values (2);
+call f1();
+select sql_cache * from t1 where s1=1;
+select sql_cache * from t1;
+call f1();
+call f3();
+call f3();
+call f1();
+
 drop procedure f1;
+drop procedure f2;
+drop procedure f3;
+drop procedure f4;
 drop table t1;
 set GLOBAL query_cache_size=0;
 

--- 1.30/sql/sql_cache.h	2005-09-06 19:51:08 +02:00
+++ 1.31/sql/sql_cache.h	2005-09-21 15:08:28 +02:00
@@ -112,7 +112,6 @@
   NET *wri;
   ulong len;
   uint8 tbls_type;
-  unsigned int last_pkt_nr;
 
   inline void init_n_lock();
   void unlock_n_destroy();

--- 1.159/client/mysqltest.c	2005-09-12 15:58:28 +02:00
+++ 1.160/client/mysqltest.c	2005-09-21 15:08:28 +02:00
@@ -3056,12 +3056,6 @@
       goto end;
     }
 
-    if (handle_no_error(q))
-    {
-      error= 1;
-      goto end;
-    }
-
     if (!disable_result_log)
     {
       ulong affected_rows;    /* Ok to be undef if 'disable_info' is set */
@@ -3106,12 +3100,9 @@
 	MYSQL_RES *warn_res=0;
 	uint count= mysql_warning_count(mysql);
 	if (!mysql_real_query(mysql, "SHOW WARNINGS", 13))
-	{
 	  warn_res= mysql_store_result(mysql);
-	}
 	if (!warn_res)
-	  verbose_msg("Warning count is %u but didn't get any warnings",
-		      count);
+	  die("Warning count is %u but didn't get any warnings", count);
 	else
 	{
 	  dynstr_append_mem(ds, "Warnings:\n", 10);
@@ -3142,15 +3133,28 @@
     }
     else if (q->record_file[0])
     {
-      error = check_result(ds, q->record_file, q->require_file);
+      error= check_result(ds, q->record_file, q->require_file);
     }
     if (res)
       mysql_free_result(res);
     last_result= 0;
     counter++;
   } while (!(err= mysql_next_result(mysql)));
-  if (err >= 1)
-    mysql_error(mysql);
+  if (err > 0)
+  {
+      /* We got an error from mysql_next_result, maybe expected */
+    if (handle_error(query, q, mysql_errno(mysql), mysql_error(mysql),
+                     mysql_sqlstate(mysql), ds))
+      error= 1;
+    goto end;
+  }
+
+  // If we come here the query is both executed and read successfully
+  if (handle_no_error(q))
+  {
+    error= 1;
+    goto end;
+  }
 
 end:
   free_replace();
@@ -3203,8 +3207,7 @@
     abort_not_supported_test();
  
   if (q->abort_on_error)
-    die("query '%s' failed: %d: %s", query,
-        err_errno, err_error);
+    die("query '%s' failed: %d: %s", query, err_errno, err_error);
 
   for (i= 0 ; (uint) i < q->expected_errors ; i++)
   {
@@ -3243,13 +3246,11 @@
   if (i)
   {
     if (q->expected_errno[0].type == ERR_ERRNO)
-      verbose_msg("query '%s' failed with wrong errno %d instead of %d...",
-                  q->query, err_errno,
-                  q->expected_errno[0].code.errnum);
+      die("query '%s' failed with wrong errno %d instead of %d...",
+          q->query, err_errno, q->expected_errno[0].code.errnum);
     else
-      verbose_msg("query '%s' failed with wrong sqlstate %s instead of %s...",
-                  q->query, err_sqlstate,
-                  q->expected_errno[0].code.sqlstate);
+      die("query '%s' failed with wrong sqlstate %s instead of %s...",
+          q->query, err_sqlstate, q->expected_errno[0].code.sqlstate);
     DBUG_RETURN(1);
   }
 
@@ -3283,16 +3284,16 @@
       q->expected_errno[0].code.errnum != 0)
   {
     /* Error code we wanted was != 0, i.e. not an expected success */
-    verbose_msg("query '%s' succeeded - should have failed with errno %d...",
-                q->query, q->expected_errno[0].code.errnum);
+    die("query '%s' succeeded - should have failed with errno %d...",
+        q->query, q->expected_errno[0].code.errnum);
     DBUG_RETURN(1);
   }
   else if (q->expected_errno[0].type == ERR_SQLSTATE &&
            strcmp(q->expected_errno[0].code.sqlstate,"00000") != 0)
   {
     /* SQLSTATE we wanted was != "00000", i.e. not an expected success */
-    verbose_msg("query '%s' succeeded - should have failed with sqlstate %s...",
-                q->query, q->expected_errno[0].code.sqlstate);
+    die("query '%s' succeeded - should have failed with sqlstate %s...",
+        q->query, q->expected_errno[0].code.sqlstate);
     DBUG_RETURN(1);
   }
 
@@ -3312,7 +3313,7 @@
 {
   int error= 0;             /* Function return code if "goto end;" */
   int err;                  /* Temporary storage of return code from calls */
-  int query_len, got_error_on_execute;
+  int query_len;
   ulonglong num_rows;
   char *query;
   MYSQL_RES *res= NULL;     /* Note that here 'res' is meta data result set */
@@ -3328,7 +3329,7 @@
   */
   if (!(stmt= mysql_stmt_init(mysql)))
     die("unable init stmt structure");
-  
+
   if (q->type != Q_EVAL)
   {
     query= q->query;
@@ -3369,30 +3370,21 @@
 
   if (err != 0)
   {
-    if (q->abort_on_error)
-    {
-      die("query '%s' failed: %d: %s", query,
-          mysql_stmt_errno(stmt), mysql_stmt_error(stmt));
-    }
-    else
-    {
-      /*
-        Preparing is part of normal execution and some errors may be expected
-      */
-      error= handle_error(query, q, mysql_stmt_errno(stmt),
-                          mysql_stmt_error(stmt), mysql_stmt_sqlstate(stmt),
-                          ds);
-      goto end;
-    }
+    /*
+      Preparing is part of normal execution and some errors may be expected
+    */
+    if (handle_error(query, q,  mysql_stmt_errno(stmt),
+                     mysql_stmt_error(stmt), mysql_stmt_sqlstate(stmt), ds))
+      error= 1;
+    goto end;
   }
 
   /* We may have got warnings already, collect them if any */
-  /* FIXME we only want this if the statement succeeds I think */ 
   if (!disable_ps_warnings)
     run_query_stmt_handle_warnings(mysql, ds);
 
   /*
-    No need to call mysql_stmt_bind_param() because we have no 
+    No need to call mysql_stmt_bind_param() because we have no
     parameter markers.
 
     To optimize performance we use a global 'stmt' that is initiated
@@ -3401,24 +3393,13 @@
     prepared statement.
   */
 
-  if ((got_error_on_execute= mysql_stmt_execute(stmt)) != 0) /* 0 == Success */
+  if (mysql_stmt_execute(stmt) != 0) /* 0 == Success */
   {
-    if (q->abort_on_error)
-    {
-      /* We got an error, unexpected */
-      die("unable to execute statement '%s': "
-          "%s (mysql_stmt_errno=%d returned=%d)",
-          query, mysql_stmt_error(stmt),
-          mysql_stmt_errno(stmt), got_error_on_execute);
-    }
-    else
-    {
-      /* We got an error, maybe expected */
-      error= handle_error(query, q, mysql_stmt_errno(stmt),
-                          mysql_stmt_error(stmt), mysql_stmt_sqlstate(stmt),
-                          ds);
-      goto end;
-    }
+    /* We got an error, maybe expected */
+    if (handle_error(query, q, mysql_stmt_errno(stmt),
+                     mysql_stmt_error(stmt), mysql_stmt_sqlstate(stmt), ds))
+      error= 1;
+    goto end;
   }
 
   /*
@@ -3428,11 +3409,10 @@
   */
   {
     my_bool one= 1;
-    if (mysql_stmt_attr_set(stmt, STMT_ATTR_UPDATE_MAX_LENGTH,
-                            (void*) &one) != 0)
+    if ((err= mysql_stmt_attr_set(stmt, STMT_ATTR_UPDATE_MAX_LENGTH,
+                                  (void*) &one)) != 0)
       die("unable to set stmt attribute "
-          "'STMT_ATTR_UPDATE_MAX_LENGTH': %s (returned=%d)",
-          query, err);
+          "'STMT_ATTR_UPDATE_MAX_LENGTH' err: %d", err);
   }
 
   /*
@@ -3441,22 +3421,11 @@
   */
   if ((err= mysql_stmt_store_result(stmt)) != 0)
   {
-    if (q->abort_on_error)
-    {
-      /* We got an error, unexpected */
-      die("unable to execute statement '%s': "
-          "%s (mysql_stmt_errno=%d returned=%d)",
-          query, mysql_stmt_error(stmt),
-          mysql_stmt_errno(stmt), got_error_on_execute);
-    }
-    else
-    {
-      /* We got an error, maybe expected */
-      error= handle_error(query, q, mysql_stmt_errno(stmt),
-                          mysql_stmt_error(stmt), mysql_stmt_sqlstate(stmt),
-                          ds);
-      goto end;
-    }
+    /* We got an error, maybe expected */
+    if(handle_error(query, q, mysql_stmt_errno(stmt),
+                    mysql_stmt_error(stmt), mysql_stmt_sqlstate(stmt), ds))
+      error = 1;
+    goto end;
   }
 
   /* If we got here the statement was both executed and read succeesfully */
@@ -3480,8 +3449,6 @@
     MYSQL_FIELD *field= mysql_fetch_fields(res);
     uint num_fields= mysql_num_fields(res);
 
-    /* FIXME check error from the above? */
-
     if (display_metadata)
       run_query_display_metadata(field, num_fields, ds);
 
@@ -3634,9 +3601,6 @@
     mysql_free_result(res);     /* Free normal result set with meta data */
   last_result= 0;               /* FIXME have no idea what this is about... */
 
-  if (err >= 1)
-    mysql_error(mysql);         /* FIXME strange, has no effect... */
-
 end:
   free_replace();
   last_result=0;
@@ -3727,8 +3691,8 @@
     {
       MYSQL_RES *warn_res= mysql_store_result(mysql);
       if (!warn_res)
-        verbose_msg("Warning count is %u but didn't get any warnings",
-                    count);
+        die("Warning count is %u but didn't get any warnings",
+            count);
       else
       {
         dynstr_append_mem(ds, "Warnings:\n", 10);

--- 1.83/mysql-test/r/sp-error.result	2005-09-13 17:16:04 +02:00
+++ 1.84/mysql-test/r/sp-error.result	2005-09-21 15:08:28 +02:00
@@ -692,6 +692,7 @@
 call SP001();
 TEMP_SUM
 0
+ERROR 24000: Cursor is not open
 drop procedure SP001;
 drop table t1, t2;
 drop function if exists bug11394|

--- 1.87/mysql-test/t/sp-error.test	2005-09-13 17:16:05 +02:00
+++ 1.88/mysql-test/t/sp-error.test	2005-09-21 15:08:28 +02:00
@@ -991,6 +991,7 @@
   SELECT 'end of proc';
 END P1|
 delimiter ;|
+--error 1326
 call SP001();
 drop procedure SP001;
 drop table t1, t2;
@@ -1187,7 +1188,30 @@
 create trigger bug12712
   before insert on t1 for each row set session autocommit = 0;
 
-
+#
+# BUG#9367: Stored procedures: client hang after "show warnings"
+#
+--disable_parsing
+--disable_warnings
+drop procedure if exists bug9367;
+--enable_warnings
+create table t1 (s1 int);
+select s1 from t1;
+delimiter |;
+create procedure bug9367()
+begin
+  declare v int;
+  declare c cursor for select s1 from t1;
+  open c;
+  show warnings;
+  fetch c into v;
+  select v;
+end|
+delimiter ;|
+call bug9367();
+drop procedure bug9367;
+drop table t1;
+--enable_parsing
 #
 # BUG#NNNN: New bug synopsis
 #