List:Commits« Previous MessageNext Message »
From:marc.alff Date:September 9 2006 3:14am
Subject:bk commit into 5.0 tree (malff:1.2263) BUG#12713
View as plain text  
Below is the list of changes that have just been committed into a local
5.0 repository of marcsql. When marcsql 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@stripped, 2006-09-08 20:14:16-07:00, malff@weblab.(none) +50 -0
  Bug#12713 (Error in a stored function called from a SELECT doesn't cause
    ROLLBACK of statem)
  
  This is a revised patch, that fixes comments from the first review.
  
  1)
  The usage of "if (transactional_table)" before calling
  ha_autocommit_or_rollback is flawed, as only 1 table is considered,
  which breaks integrity for statements involving many tables with a
  mix of transactional and non transactional engines.
  
  The serie of tests t/engine_mix_<xxx>_<yyy>.test has been written
  to cover this area. (tests naming pending decision)
  
  Existing calls to ha_commit_or_rollback() have been adjusted.
  
  2)
  Implementation of "implicit non-updating commits" in BDB and NDB
  has been adjusted to unregister these engines so that autocommit/rollback
  can be performed properly for selects.
  
  3)
  After a very carefull investigation, the list of statements that can
  modify transactional data has been identified, and documented in
  mysql_execute_command (See the comments there).
  
  4)
  All the statements that can cause side effects have been fixed to
  properly call ha_commit_or_rollback().
  
  5)
  Fixed the DO, MULTI-UPDATE, and MULTI-DELETE to call ha_autocommit_or_rollback
  
  6)
  A serie of tests t/autocommit_<xxx>.test has been written
  to verify that statement rollback *and* transaction rollback
  occur properly. (test naming pending decision)
  
  7)
  All the new tests have been organized in a manner consistent
  with the test reorganization performed recently in 5.1 for falcon.
  
  8)
  One unrelated issues has been found and documented for NDB,
  the tests written with the expected output (unverified),
  and disabled in t/disable.def
  
  9)
  The select_result class hierarchy has been changed,
  so that send_error() and send_eof() methods can perform a statement
  ha_autocommit_or_rollback() before sending a reply to the client.
  This implementation is the least intrusive to the existing design.

  mysql-test/include/autocommit_common.inc@stripped, 2006-09-08 18:52:38-07:00, malff@weblab.(none) +339 -0
    Test cases, statement rollback, transaction rollback

  mysql-test/include/autocommit_common.inc@stripped, 2006-09-08 18:52:38-07:00, malff@weblab.(none) +0 -0

  mysql-test/include/engine_mix_common.inc@stripped, 2006-09-08 18:51:11-07:00, malff@weblab.(none) +120 -0
    Test cases, transactions accross engines

  mysql-test/include/engine_mix_common.inc@stripped, 2006-09-08 18:51:11-07:00, malff@weblab.(none) +0 -0

  mysql-test/r/autocommit_bdb.result@stripped, 2006-09-08 18:52:38-07:00, malff@weblab.(none) +315 -0
    Test cases, statement rollback, transaction rollback

  mysql-test/r/autocommit_bdb.result@stripped, 2006-09-08 18:52:38-07:00, malff@weblab.(none) +0 -0

  mysql-test/r/autocommit_innodb.result@stripped, 2006-09-08 18:52:38-07:00, malff@weblab.(none) +315 -0
    Test cases, statement rollback, transaction rollback

  mysql-test/r/autocommit_innodb.result@stripped, 2006-09-08 18:52:38-07:00, malff@weblab.(none) +0 -0

  mysql-test/r/engine_mix_bdb_innodb.result@stripped, 2006-09-08 18:51:11-07:00, malff@weblab.(none) +87 -0
    Test cases, transactions accross engines

  mysql-test/r/engine_mix_bdb_innodb.result@stripped, 2006-09-08 18:51:11-07:00, malff@weblab.(none) +0 -0

  mysql-test/r/engine_mix_bdb_myisam.result@stripped, 2006-09-08 18:51:11-07:00, malff@weblab.(none) +113 -0
    Test cases, transactions accross engines

  mysql-test/r/engine_mix_bdb_myisam.result@stripped, 2006-09-08 18:51:11-07:00, malff@weblab.(none) +0 -0

  mysql-test/r/engine_mix_bdb_ndb.result@stripped, 2006-09-08 18:51:11-07:00, malff@weblab.(none) +87 -0
    Test cases, transactions accross engines

  mysql-test/r/engine_mix_bdb_ndb.result@stripped, 2006-09-08 18:51:11-07:00, malff@weblab.(none) +0 -0

  mysql-test/r/engine_mix_innodb_bdb.result@stripped, 2006-09-08 18:51:11-07:00, malff@weblab.(none) +87 -0
    Test cases, transactions accross engines

  mysql-test/r/engine_mix_innodb_bdb.result@stripped, 2006-09-08 18:51:11-07:00, malff@weblab.(none) +0 -0

  mysql-test/r/engine_mix_innodb_myisam.result@stripped, 2006-09-08 18:51:11-07:00, malff@weblab.(none) +113 -0
    Test cases, transactions accross engines

  mysql-test/r/engine_mix_innodb_myisam.result@stripped, 2006-09-08 18:51:11-07:00, malff@weblab.(none) +0 -0

  mysql-test/r/engine_mix_innodb_ndb.result@stripped, 2006-09-08 18:51:11-07:00, malff@weblab.(none) +87 -0
    Test cases, transactions accross engines

  mysql-test/r/engine_mix_innodb_ndb.result@stripped, 2006-09-08 18:51:11-07:00, malff@weblab.(none) +0 -0

  mysql-test/r/engine_mix_myisam_bdb.result@stripped, 2006-09-08 18:51:11-07:00, malff@weblab.(none) +91 -0
    Test cases, transactions accross engines

  mysql-test/r/engine_mix_myisam_bdb.result@stripped, 2006-09-08 18:51:11-07:00, malff@weblab.(none) +0 -0

  mysql-test/r/engine_mix_myisam_innodb.result@stripped, 2006-09-08 18:51:11-07:00, malff@weblab.(none) +91 -0
    Test cases, transactions accross engines

  mysql-test/r/engine_mix_myisam_innodb.result@stripped, 2006-09-08 18:51:11-07:00, malff@weblab.(none) +0 -0

  mysql-test/r/engine_mix_myisam_ndb.result@stripped, 2006-09-08 18:51:11-07:00, malff@weblab.(none) +91 -0
    Test cases, transactions accross engines

  mysql-test/r/engine_mix_myisam_ndb.result@stripped, 2006-09-08 18:51:11-07:00, malff@weblab.(none) +0 -0

  mysql-test/r/engine_mix_ndb_bdb.result@stripped, 2006-09-08 18:51:11-07:00, malff@weblab.(none) +87 -0
    Test cases, transactions accross engines

  mysql-test/r/engine_mix_ndb_bdb.result@stripped, 2006-09-08 18:51:11-07:00, malff@weblab.(none) +0 -0

  mysql-test/r/engine_mix_ndb_innodb.result@stripped, 2006-09-08 18:51:11-07:00, malff@weblab.(none) +87 -0
    Test cases, transactions accross engines

  mysql-test/r/engine_mix_ndb_innodb.result@stripped, 2006-09-08 18:51:11-07:00, malff@weblab.(none) +0 -0

  mysql-test/r/engine_mix_ndb_myisam.result@stripped, 2006-09-08 18:51:11-07:00, malff@weblab.(none) +113 -0
    Test cases, transactions accross engines

  mysql-test/r/engine_mix_ndb_myisam.result@stripped, 2006-09-08 18:51:11-07:00, malff@weblab.(none) +0 -0

  mysql-test/r/rpl_sp.result@stripped, 2006-09-08 18:00:48-07:00, malff@weblab.(none) +1 -2
    Adjusted, DO reports errors

  mysql-test/r/subselect.result@stripped, 2006-09-08 18:00:48-07:00, malff@weblab.(none) +1 -2
    Adjusted, DO reports errors

  mysql-test/t/autocommit_bdb.test@stripped, 2006-09-08 18:52:38-07:00, malff@weblab.(none) +6 -0
    Test cases, statement rollback, transaction rollback

  mysql-test/t/autocommit_bdb.test@stripped, 2006-09-08 18:52:38-07:00, malff@weblab.(none) +0 -0

  mysql-test/t/autocommit_innodb.test@stripped, 2006-09-08 18:52:38-07:00, malff@weblab.(none) +6 -0
    Test cases, statement rollback, transaction rollback

  mysql-test/t/autocommit_innodb.test@stripped, 2006-09-08 18:52:38-07:00, malff@weblab.(none) +0 -0

  mysql-test/t/disabled.def@stripped, 2006-09-08 18:54:34-07:00, malff@weblab.(none) +2 -0
    Disable unrelated ndb failures

  mysql-test/t/engine_mix_bdb_innodb.test@stripped, 2006-09-08 18:51:11-07:00, malff@weblab.(none) +8 -0
    Test cases, transactions accross engines

  mysql-test/t/engine_mix_bdb_innodb.test@stripped, 2006-09-08 18:51:11-07:00, malff@weblab.(none) +0 -0

  mysql-test/t/engine_mix_bdb_myisam.test@stripped, 2006-09-08 18:51:11-07:00, malff@weblab.(none) +8 -0
    Test cases, transactions accross engines

  mysql-test/t/engine_mix_bdb_myisam.test@stripped, 2006-09-08 18:51:11-07:00, malff@weblab.(none) +0 -0

  mysql-test/t/engine_mix_bdb_ndb.test@stripped, 2006-09-08 18:51:11-07:00, malff@weblab.(none) +8 -0
    Test cases, transactions accross engines

  mysql-test/t/engine_mix_bdb_ndb.test@stripped, 2006-09-08 18:51:11-07:00, malff@weblab.(none) +0 -0

  mysql-test/t/engine_mix_innodb_bdb.test@stripped, 2006-09-08 18:51:11-07:00, malff@weblab.(none) +8 -0
    Test cases, transactions accross engines

  mysql-test/t/engine_mix_innodb_bdb.test@stripped, 2006-09-08 18:51:11-07:00, malff@weblab.(none) +0 -0

  mysql-test/t/engine_mix_innodb_myisam.test@stripped, 2006-09-08 18:51:11-07:00, malff@weblab.(none) +8 -0
    Test cases, transactions accross engines

  mysql-test/t/engine_mix_innodb_myisam.test@stripped, 2006-09-08 18:51:11-07:00, malff@weblab.(none) +0 -0

  mysql-test/t/engine_mix_innodb_ndb.test@stripped, 2006-09-08 18:51:11-07:00, malff@weblab.(none) +8 -0
    Test cases, transactions accross engines

  mysql-test/t/engine_mix_innodb_ndb.test@stripped, 2006-09-08 18:51:11-07:00, malff@weblab.(none) +0 -0

  mysql-test/t/engine_mix_myisam_bdb.test@stripped, 2006-09-08 18:51:11-07:00, malff@weblab.(none) +8 -0
    Test cases, transactions accross engines

  mysql-test/t/engine_mix_myisam_bdb.test@stripped, 2006-09-08 18:51:11-07:00, malff@weblab.(none) +0 -0

  mysql-test/t/engine_mix_myisam_innodb.test@stripped, 2006-09-08 18:51:11-07:00, malff@weblab.(none) +8 -0
    Test cases, transactions accross engines

  mysql-test/t/engine_mix_myisam_innodb.test@stripped, 2006-09-08 18:51:11-07:00, malff@weblab.(none) +0 -0

  mysql-test/t/engine_mix_myisam_ndb.test@stripped, 2006-09-08 18:51:11-07:00, malff@weblab.(none) +8 -0
    Test cases, transactions accross engines

  mysql-test/t/engine_mix_myisam_ndb.test@stripped, 2006-09-08 18:51:11-07:00, malff@weblab.(none) +0 -0

  mysql-test/t/engine_mix_ndb_bdb.test@stripped, 2006-09-08 18:51:11-07:00, malff@weblab.(none) +8 -0
    Test cases, transactions accross engines

  mysql-test/t/engine_mix_ndb_bdb.test@stripped, 2006-09-08 18:51:11-07:00, malff@weblab.(none) +0 -0

  mysql-test/t/engine_mix_ndb_innodb.test@stripped, 2006-09-08 18:51:11-07:00, malff@weblab.(none) +8 -0
    Test cases, transactions accross engines

  mysql-test/t/engine_mix_ndb_innodb.test@stripped, 2006-09-08 18:51:11-07:00, malff@weblab.(none) +0 -0

  mysql-test/t/engine_mix_ndb_myisam.test@stripped, 2006-09-08 18:51:11-07:00, malff@weblab.(none) +8 -0
    Test cases, transactions accross engines

  mysql-test/t/engine_mix_ndb_myisam.test@stripped, 2006-09-08 18:51:11-07:00, malff@weblab.(none) +0 -0

  mysql-test/t/rpl_sp.test@stripped, 2006-09-08 18:00:48-07:00, malff@weblab.(none) +2 -1
    Adjusted, DO reports errors

  mysql-test/t/subselect.test@stripped, 2006-09-08 18:00:48-07:00, malff@weblab.(none) +2 -1
    Adjusted, DO reports errors

  sql/ha_berkeley.cc@stripped, 2006-09-08 18:00:48-07:00, malff@weblab.(none) +5 -0
    trans_unregister_ha for non-updating transactions

  sql/ha_ndbcluster.cc@stripped, 2006-09-08 18:00:48-07:00, malff@weblab.(none) +1 -0
    trans_unregister_ha for non-updating transactions

  sql/handler.cc@stripped, 2006-09-08 18:26:46-07:00, malff@weblab.(none) +71 -0
    trans_unregister_ha for non-updating transactions

  sql/handler.h@stripped, 2006-09-08 18:26:46-07:00, malff@weblab.(none) +1 -0
    trans_unregister_ha for non-updating transactions

  sql/sp_rcontext.cc@stripped, 2006-09-08 18:26:46-07:00, malff@weblab.(none) +16 -0
    send_error and send_eof refactoring for transactional selects

  sql/sp_rcontext.h@stripped, 2006-09-08 18:26:46-07:00, malff@weblab.(none) +2 -1
    send_error and send_eof refactoring for transactional selects

  sql/sql_class.cc@stripped, 2006-09-08 18:26:46-07:00, malff@weblab.(none) +93 -7
    send_error and send_eof refactoring for transactional selects

  sql/sql_class.h@stripped, 2006-09-08 18:26:46-07:00, malff@weblab.(none) +9 -2
    send_error and send_eof refactoring for transactional selects

  sql/sql_delete.cc@stripped, 2006-09-08 18:31:00-07:00, malff@weblab.(none) +15 -14
    revised ha_autocommit_or_rollback for DELETE and MULTI-DELETE

  sql/sql_do.cc@stripped, 2006-09-08 18:31:00-07:00, malff@weblab.(none) +16 -3
    revised ha_autocommit_or_rollback for DO

  sql/sql_insert.cc@stripped, 2006-09-08 18:31:00-07:00, malff@weblab.(none) +5 -4
    revised ha_autocommit_or_rollback for INSERT and MULTI-INSERT

  sql/sql_load.cc@stripped, 2006-09-08 18:31:00-07:00, malff@weblab.(none) +2 -4
    revised ha_autocommit_or_rollback for LOAD

  sql/sql_parse.cc@stripped, 2006-09-08 18:54:34-07:00, malff@weblab.(none) +212 -1
    Implementation of ha_autocommit_or_rollback for statements with side effects

  sql/sql_union.cc@stripped, 2006-09-08 18:32:03-07:00, malff@weblab.(none) +6 -0
    send_error and send_eof refactoring for transactional selects

  sql/sql_update.cc@stripped, 2006-09-08 18:31:00-07:00, malff@weblab.(none) +25 -17
    revised ha_autocommit_or_rollback for UPDATE and MULTI-UPDATE

# 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:	malff
# Host:	weblab.(none)
# Root:	/home/marcsql/TREE/mysql-5.0-12713_b

--- 1.161/sql/ha_berkeley.cc	2006-09-08 20:14:22 -07:00
+++ 1.162/sql/ha_berkeley.cc	2006-09-08 20:14:22 -07:00
@@ -257,7 +257,9 @@ static int berkeley_commit(THD *thd, boo
   DBUG_ENTER("berkeley_commit");
   DBUG_PRINT("trans",("ending transaction %s", all ? "all" : "stmt"));
   berkeley_trx_data *trx=(berkeley_trx_data *)thd->ha_data[berkeley_hton.slot];
+  DBUG_ASSERT(trx);
   DB_TXN **txn= all ? &trx->all : &trx->stmt;
+  DBUG_ASSERT(*txn);
   int error=txn_commit(*txn,0);
   *txn=0;
 #ifndef DBUG_OFF
@@ -272,7 +274,9 @@ static int berkeley_rollback(THD *thd, b
   DBUG_ENTER("berkeley_rollback");
   DBUG_PRINT("trans",("aborting transaction %s", all ? "all" : "stmt"));
   berkeley_trx_data *trx=(berkeley_trx_data *)thd->ha_data[berkeley_hton.slot];
+  DBUG_ASSERT(trx);
   DB_TXN **txn= all ? &trx->all : &trx->stmt;
+  DBUG_ASSERT(*txn);
   int error=txn_abort(*txn);
   *txn=0;
   DBUG_RETURN(error);
@@ -1892,6 +1896,7 @@ int ha_berkeley::external_lock(THD *thd,
 	DBUG_PRINT("trans",("commiting non-updating transaction"));
         error= txn_commit(trx->stmt,0);
         trx->stmt= transaction= 0;
+        trans_unregister_ha(thd, FALSE, &berkeley_hton);
       }
     }
   }

--- 1.217/sql/handler.cc	2006-09-08 20:14:22 -07:00
+++ 1.218/sql/handler.cc	2006-09-08 20:14:22 -07:00
@@ -611,6 +611,77 @@ void trans_register_ha(THD *thd, bool al
   DBUG_VOID_RETURN;
 }
 
+
+/*
+  Un-register a storage engine for a transaction
+
+  DESCRIPTION
+    If a storage engine detects that it's no longer needed in a
+    transactions, it may unregister itself to not participate
+    in future commits/rollbacks.
+    This is used in particular for "non-updating commits".
+    Note: this function should be used with care, to avoid
+    breaking the 2 phase commit semantic.
+*/
+void trans_unregister_ha(THD *thd, bool all, handlerton *ht_arg)
+{
+  THD_TRANS *trans;
+  handlerton **ht;
+  int ht_index= -1;
+  int i;
+  DBUG_ENTER("trans_unregister_ha");
+  DBUG_PRINT("enter",("%s", all ? "all" : "stmt"));
+
+  if (all)
+  {
+    trans= &thd->transaction.all;
+  }
+  else
+  {
+    trans= &thd->transaction.stmt;
+  }
+
+  /*
+     Searching for the handlerton in the transaction.
+   */
+  for (i= 0; i < trans->nht ; i++)
+  {
+    if (trans->ht[i] == ht_arg)
+    {
+      ht_index= i;
+      break;
+    }
+  }
+
+  if (ht_index >= 0)
+  {
+    if (trans->nht > 1)
+    {
+      /* Remove the handlerton found */
+      trans->ht[ht_index]= trans->ht[trans->nht -1];
+      trans->ht[trans->nht -1]= 0;
+      trans->nht--;
+
+      /* Re-evaluate the 2 phase commit property */
+      trans->no_2pc= 0;
+      for (ht=trans->ht; *ht; ht++)
+      {
+        trans->no_2pc|= ((*ht)->prepare==0);
+      }
+    }
+    else
+    {
+      /* The transaction is now empty */
+      trans->ht[0]= 0;
+      trans->nht= 0;
+      trans->no_2pc= 0;
+    }
+  }
+
+  DBUG_VOID_RETURN;
+}
+
+
 /*
   RETURN
       0  - ok

--- 1.177/sql/handler.h	2006-09-08 20:14:22 -07:00
+++ 1.178/sql/handler.h	2006-09-08 20:14:22 -07:00
@@ -961,6 +961,7 @@ int ha_release_savepoint(THD *thd, SAVEP
 
 /* these are called by storage engines */
 void trans_register_ha(THD *thd, bool all, handlerton *ht);
+void trans_unregister_ha(THD *thd, bool all, handlerton *ht);
 
 /*
   Storage engine has to assume the transaction will end up with 2pc if

--- 1.247/sql/sql_class.cc	2006-09-08 20:14:22 -07:00
+++ 1.248/sql/sql_class.cc	2006-09-08 20:14:22 -07:00
@@ -869,6 +869,32 @@ void select_result::cleanup()
   /* do nothing */
 }
 
+int select_result::complete_statement(int error)
+{
+  /*
+     Please note :
+     While is may seem that this code performs a commit or rollback
+     for every select statement, what really happens is the following:
+
+     - For selects with no side effects, only READ locks are used
+     during the select execution. As an optinization in JOIN::exec(),
+     handler::external_lock(F_UNLCK) is called early, which, upon
+     releasing the last lock for a given transactional engine,
+     will cause an implicit commit ("commiting non-updating transaction").
+     ha_autocommit_or_rollback() will be a no-op in this case.
+
+     - For selects with side effects, when involving stored functions
+     that may perform data modifications, at least a WRITE lock for
+     the table modified will be used. When releasing the READ locks as
+     part of the JOIN::exec() optimizations, the lock count in the engine
+     will never reach 0, the statement transaction is still pending,
+     and has to be properly commited or rollbacked to ensure data integrity.
+     ha_autocommit_or_rollback() will properly complete the stmt transaction.
+   */
+  error= ha_autocommit_or_rollback(thd, error);
+  return error;
+}
+
 static String default_line_term("\n",default_charset_info);
 static String default_escaped("\\",default_charset_info);
 static String default_field_term("\t",default_charset_info);
@@ -955,8 +981,17 @@ bool select_send::send_data(List<Item> &
   DBUG_RETURN(1);
 }
 
+void select_send::send_error(uint errcode,const char *err)
+{
+  my_message(errcode, err, MYF(0));
+
+  (void) complete_statement(errcode);
+}
+
 bool select_send::send_eof()
 {
+  int error= complete_statement(0);
+
   /* We may be passing the control from mysqld to the client: release the
      InnoDB adaptive hash S-latch to avoid thread deadlocks if it was reserved
      by thd */
@@ -968,14 +1003,14 @@ bool select_send::send_eof()
     mysql_unlock_tables(thd, thd->lock);
     thd->lock=0;
   }
-  if (!thd->net.report_error)
+  if (!thd->net.report_error && !error)
   {
     ::send_eof(thd);
     status= 0;
     return 0;
   }
-  else
-    return 1;
+
+  return 1;
 }
 
 
@@ -986,6 +1021,9 @@ bool select_send::send_eof()
 void select_to_file::send_error(uint errcode,const char *err)
 {
   my_message(errcode, err, MYF(0));
+
+  (void) complete_statement(errcode);
+
   if (file > 0)
   {
     (void) end_io_cache(&cache);
@@ -1001,8 +1039,17 @@ bool select_to_file::send_eof()
   int error= test(end_io_cache(&cache));
   if (my_close(file,MYF(MY_WME)))
     error= 1;
+
+  error= complete_statement(error);
+
   if (!error)
+  {
     ::send_ok(thd,row_count);
+  }
+  else
+  {
+    (void) my_delete(path,MYF(0));		// Delete file on error
+  }
   file= -1;
   return error;
 }
@@ -1338,6 +1385,18 @@ select_subselect::select_subselect(Item_
 }
 
 
+void select_subselect::send_error(uint errcode, const char *err)
+{
+  my_message(errcode, err, MYF(0));
+}
+
+
+bool select_subselect::send_eof()
+{
+  return FALSE;
+}
+
+
 bool select_singlerow_subselect::send_data(List<Item> &items)
 {
   DBUG_ENTER("select_singlerow_subselect::send_data");
@@ -1883,21 +1942,48 @@ bool select_dumpvar::send_data(List<Item
     {
       if ((xx=li++))
       {
-        xx->check(0);
-	xx->update();
+        if (xx->check(0))
+	  DBUG_RETURN(1);
+
+        if (thd->net.report_error)
+	  DBUG_RETURN(1);
+
+        if (xx->update())
+	  DBUG_RETURN(1);
+
+        if (thd->net.report_error)
+	  DBUG_RETURN(1);
       }
     }
   }
   DBUG_RETURN(0);
 }
 
+
+void select_dumpvar::send_error(uint errcode, const char *err)
+{
+  my_message(errcode, err, MYF(0));
+
+  (void) complete_statement(errcode);
+}
+
+
 bool select_dumpvar::send_eof()
 {
+  int error;
+
   if (! row_count)
     push_warning(thd, MYSQL_ERROR::WARN_LEVEL_WARN,
                  ER_SP_FETCH_NO_DATA, ER(ER_SP_FETCH_NO_DATA));
-  ::send_ok(thd,row_count);
-  return 0;
+
+  error= complete_statement(0);
+
+  if (!error)
+  {
+    ::send_ok(thd,row_count);
+  }
+
+  return error;
 }
 
 /****************************************************************************

--- 1.294/sql/sql_class.h	2006-09-08 20:14:22 -07:00
+++ 1.295/sql/sql_class.h	2006-09-08 20:14:22 -07:00
@@ -1684,7 +1684,7 @@ public:
   virtual bool send_fields(List<Item> &list, uint flags)=0;
   virtual bool send_data(List<Item> &items)=0;
   virtual bool initialize_tables (JOIN *join=0) { return 0; }
-  virtual void send_error(uint errcode,const char *err);
+  virtual void send_error(uint errcode,const char *err)=0;
   virtual bool send_eof()=0;
   virtual bool simple_select() { return 0; }
   virtual void abort() {}
@@ -1699,6 +1699,9 @@ public:
 #else
   void begin_dataset() {}
 #endif
+
+protected:
+  int complete_statement(int error);
 };
 
 
@@ -1723,6 +1726,7 @@ public:
   select_send() :status(0) {}
   bool send_fields(List<Item> &list, uint flags);
   bool send_data(List<Item> &items);
+  void send_error(uint errcode,const char *err);
   bool send_eof();
   bool simple_select() { return 1; }
   void abort();
@@ -1889,6 +1893,7 @@ public:
   select_union() :table(0) {}
   int prepare(List<Item> &list, SELECT_LEX_UNIT *u);
   bool send_data(List<Item> &items);
+  void send_error(uint errcode,const char *err);
   bool send_eof();
   bool flush();
 
@@ -1905,7 +1910,8 @@ protected:
 public:
   select_subselect(Item_subselect *item);
   bool send_data(List<Item> &items)=0;
-  bool send_eof() { return 0; };
+  void send_error(uint errcode,const char *err);
+  bool send_eof();
 };
 
 /* Single value subselect interface class */
@@ -2163,6 +2169,7 @@ public:
   ~select_dumpvar() {}
   int prepare(List<Item> &list, SELECT_LEX_UNIT *u);
   bool send_data(List<Item> &items);
+  void send_error(uint errcode,const char *err);
   bool send_eof();
   void cleanup();
 };

--- 1.178/sql/sql_delete.cc	2006-09-08 20:14:22 -07:00
+++ 1.179/sql/sql_delete.cc	2006-09-08 20:14:22 -07:00
@@ -306,10 +306,12 @@ cleanup:
       thd->options|=OPTION_STATUS_NO_TRANS_UPDATE;
   }
   free_underlaid_joins(thd, select_lex);
-  if (transactional_table)
+
+  /* WARNING: error == 0 *is* an error */
+  /* On success, error == -1 */
+  if (ha_autocommit_or_rollback(thd, (error >= 0)))
   {
-    if (ha_autocommit_or_rollback(thd,error >= 0))
-      error=1;
+    error= 1;
   }
 
   if (thd->lock)
@@ -466,9 +468,10 @@ bool mysql_multi_delete_prepare(THD *thd
 
 
 multi_delete::multi_delete(TABLE_LIST *dt, uint num_of_tables_arg)
-  : delete_tables(dt), deleted(0), found(0),
+  : delete_tables(dt), table_being_deleted(0), deleted(0), found(0),
     num_of_tables(num_of_tables_arg), error(0),
-    do_delete(0), transactional_tables(0), normal_tables(0)
+    do_delete(0), transactional_tables(0), normal_tables(0),
+    delete_while_scanning(0)
 {
   tempfiles= (Unique **) sql_calloc(sizeof(Unique *) * num_of_tables);
 }
@@ -635,12 +638,11 @@ void multi_delete::send_error(uint errco
   /* First send error what ever it is ... */
   my_message(errcode, err, MYF(0));
 
-  /* If nothing deleted return */
-  if (!deleted)
-    DBUG_VOID_RETURN;
-
-  /* Something already deleted so we have to invalidate cache */
-  query_cache_invalidate3(thd, delete_tables, 1);
+  if (deleted)
+  {
+    /* Something already deleted so we have to invalidate cache */
+    query_cache_invalidate3(thd, delete_tables, 1);
+  }
 
   /*
     If rows from the first table only has been deleted and it is
@@ -779,9 +781,8 @@ bool multi_delete::send_eof()
       thd->options|=OPTION_STATUS_NO_TRANS_UPDATE;
   }
   /* Commit or rollback the current SQL statement */
-  if (transactional_tables)
-    if (ha_autocommit_or_rollback(thd,local_error > 0))
-      local_error=1;
+  if (ha_autocommit_or_rollback(thd, local_error > 0))
+    local_error=1;
 
   if (!local_error)
   {

--- 1.199/sql/sql_insert.cc	2006-09-08 20:14:22 -07:00
+++ 1.200/sql/sql_insert.cc	2006-09-08 20:14:22 -07:00
@@ -651,8 +651,8 @@ bool mysql_insert(THD *thd,TABLE_LIST *t
           thd->options|=OPTION_STATUS_NO_TRANS_UPDATE;
       }
     }
-    if (transactional_table)
-      error=ha_autocommit_or_rollback(thd,error);
+
+    error= ha_autocommit_or_rollback(thd, error);
 
     if (thd->lock)
     {
@@ -2532,8 +2532,9 @@ bool select_insert::send_eof()
 			  table->file->has_transactions(), FALSE);
     mysql_bin_log.write(&qinfo);
   }
-  if ((error2=ha_autocommit_or_rollback(thd,error)) && ! error)
-    error=error2;
+
+  error= ha_autocommit_or_rollback(thd, error);
+
   if (error)
   {
     table->file->print_error(error,MYF(0));

--- 1.97/sql/sql_load.cc	2006-09-08 20:14:22 -07:00
+++ 1.98/sql/sql_load.cc	2006-09-08 20:14:22 -07:00
@@ -409,8 +409,7 @@ bool mysql_load(THD *thd,sql_exchange *e
 
   if (error)
   {
-    if (transactional_table)
-      ha_autocommit_or_rollback(thd,error);
+    (void) ha_autocommit_or_rollback(thd, error);
 
     if (read_file_from_client)
       while (!read_info.next_line())
@@ -478,8 +477,7 @@ bool mysql_load(THD *thd,sql_exchange *e
 					 ignore, transactional_table);
   }
 #endif /*!EMBEDDED_LIBRARY*/
-  if (transactional_table)
-    error=ha_autocommit_or_rollback(thd,error);
+  error= ha_autocommit_or_rollback(thd, error);
 
 err:
   if (thd->lock)

--- 1.569/sql/sql_parse.cc	2006-09-08 20:14:22 -07:00
+++ 1.570/sql/sql_parse.cc	2006-09-08 20:14:22 -07:00
@@ -2397,6 +2397,184 @@ static void reset_one_shot_variables(THD
     global read lock when it succeeds. This needs to be released by
     start_waiting_global_read_lock() after the operation.
 
+    Note on ha_autocommit_or_rollback() and transactional integrity
+    ===============================================================
+
+    The following statements do manipulate transactions explicitly:
+    - SQLCOM_BEGIN
+    - SQLCOM_COMMIT
+    - SQLCOM_ROLLBACK
+    - SQLCOM_RELEASE_SAVEPOINT
+    - SQLCOM_ROLLBACK_TO_SAVEPOINT
+    - SQLCOM_SAVEPOINT
+    - SQLCOM_XA_START
+    - SQLCOM_XA_END
+    - SQLCOM_XA_PREPARE
+    - SQLCOM_XA_COMMIT
+    - SQLCOM_XA_ROLLBACK
+    - SQLCOM_XA_RECOVER
+    For these statements, ha_autocommit_or_rollback should *not* be called.
+    See http://dev.mysql.com/doc/refman/5.1/en/transactional-commands.html
+
+    The following statements do manipulate transactions implicitly:
+    - Cursor
+      - SQLCOM_HA_OPEN
+      - SQLCOM_HA_CLOSE
+      - SQLCOM_HA_READ
+
+    - Table locks
+      - SQLCOM_UNLOCK_TABLES
+      - SQLCOM_LOCK_TABLES
+
+    - Data Definition
+      - SQLCOM_CREATE_TABLE
+      - SQLCOM_CREATE_INDEX
+      - SQLCOM_ALTER_TABLE
+      - SQLCOM_RENAME_TABLE
+      - SQLCOM_DROP_TABLE
+      - SQLCOM_DROP_INDEX
+      - SQLCOM_CREATE_DB
+      - SQLCOM_DROP_DB
+      - SQLCOM_ALTER_DB
+      - SQLCOM_CREATE_PROCEDURE
+      - SQLCOM_CREATE_SPFUNCTION
+      - SQLCOM_ALTER_PROCEDURE
+      - SQLCOM_ALTER_FUNCTION
+      - SQLCOM_DROP_PROCEDURE
+      - SQLCOM_DROP_FUNCTION
+      - SQLCOM_CREATE_VIEW
+      - SQLCOM_DROP_VIEW
+      - SQLCOM_CREATE_TRIGGER
+      - SQLCOM_DROP_TRIGGER
+
+    - Data Administration
+      - SQLCOM_CREATE_USER  (FIXME: why ?)
+      - SQLCOM_DROP_USER    (FIXME: why ?)
+      - SQLCOM_RENAME_USER  (FIXME: why ?)
+
+      - SQLCOM_TRUNCATE
+
+    These statements are implemented using code that is not transaction aware,
+    (all the Data Definition Statements manipulate files .frm and other files
+    directly in 5.0 for example)
+    As a result, the implementation for these commands commits the current
+    transaction first, then performs the DDL manipulation.
+    There is no point in calling ha_autocommit_or_rollback(), since the
+    code is *not* transaction-aware (for example, rollback may not be clean
+    and may leave .frm files around).
+
+    See http://dev.mysql.com/doc/refman/5.1/en/implicit-commit.html
+
+    The following statements:
+    - SQLCOM_PURGE
+    - SQLCOM_PURGE_BEFORE
+    - SQLCOM_RESET (REFRESH_MASTER)
+    - SQLCOM_SHOW_BINLOG_EVENTS
+    - SQLCOM_SHOW_BINLOGS
+    - SQLCOM_SHOW_MASTER_STAT
+    - SQLCOM_SHOW_SLAVE_HOSTS
+    - SQLCOM_CHANGE_MASTER
+    - SQLCOM_LOAD_MASTER_DATA
+    - SQLCOM_LOAD_MASTER_TABLE
+    - SQLCOM_RESET (REFRESH_SLAVE)
+    - SQLCOM_SHOW_SLAVE_STAT
+    - SQLCOM_SLAVE_START
+    - SQLCOM_SLAVE_STOP
+    are used for replication.
+    No call to ha_autocommit_or_rollback() is needed.
+
+    The following statements:
+    - SQLCOM_PREPARE
+    - SQLCOM_DEALLOCATE_PREPARE
+    - SQLCOM_EMPTY_QUERY
+    - SQLCOM_HELP
+    - SQLCOM_SHOW_WARNS
+    - SQLCOM_SHOW_ERRORS
+    - SQLCOM_ASSIGN_TO_KEYCACHE
+    - SQLCOM_PRELOAD_KEYS
+    - SQLCOM_SHOW_NDBCLUSTER_STATUS
+    - SQLCOM_SHOW_INNODB_STATUS
+    - SQLCOM_SHOW_MUTEX_STATUS
+    - SQLCOM_SHOW_PROCESSLIST
+    - SQLCOM_SHOW_STORAGE_ENGINES
+    - SQLCOM_SHOW_PRIVILEGES
+    - SQLCOM_SHOW_COLUMN_TYPES
+    - SQLCOM_SHOW_LOGS
+    - SQLCOM_SHOW_CREATE_DB
+    - SQLCOM_KILL
+    - SQLCOM_SHOW_GRANTS
+    - SQLCOM_SHOW_CREATE_PROC
+    - SQLCOM_SHOW_CREATE_FUNC
+    - SQLCOM_SHOW_PROC_CODE
+    - SQLCOM_SHOW_FUNC_CODE
+    are known to have no possible side effect on the transactional data.
+    No ha_autocommit_or_rollback() is needed, nor in some cases is desirable
+    (for very low level server state inspection like SQLCOM_SHOW_MUTEX_STATUS).
+
+    The following statements can directly or indirectly modify data,
+    so ha_autocommit_or_rollback() *must* be called for these.
+    Note that indirect manipulation of data is due to side effects,
+    when invoking a stored function, a stored procedure or a trigger.
+    - SQLCOM_SELECT and derivatives
+      - SQLCOM_SHOW_DATABASES
+      - SQLCOM_SHOW_TABLES
+      - SQLCOM_SHOW_TRIGGERS
+      - SQLCOM_SHOW_TABLE_STATUS
+      - SQLCOM_SHOW_OPEN_TABLES
+      - SQLCOM_SHOW_FIELDS
+      - SQLCOM_SHOW_STATUS
+      - SQLCOM_SHOW_VARIABLES
+      - SQLCOM_SHOW_CHARSETS
+      - SQLCOM_SHOW_COLLATIONS
+      - SQLCOM_SHOW_STATUS_PROC
+      - SQLCOM_SHOW_STATUS_FUNC
+    - SQLCOM_EXECUTE
+    - SQLCOM_DO
+    - SQLCOM_UPDATE
+    - SQLCOM_UPDATE_MULTI
+    - SQLCOM_REPLACE
+    - SQLCOM_INSERT
+    - SQLCOM_REPLACE_SELECT
+    - SQLCOM_INSERT_SELECT
+    - SQLCOM_DELETE
+    - SQLCOM_DELETE_MULTI
+    - SQLCOM_LOAD
+    - SQLCOM_CALL
+    - SQLCOM_SET_OPTION
+
+    The following commands:
+    - SQLCOM_REVOKE_ALL
+    - SQLCOM_REVOKE
+    - SQLCOM_GRANT
+    *do* modify data, but in the system tables which are not (as of 5.0)
+    transactional. A call to ha_autocommit_or_rollback() would generate
+    spurious warnings in case of rollback, and should be avoided for now.
+    TODO: Once the system tables are transactional, revise the implementation
+    of these commands.
+
+    The following commands:
+    - SQLCOM_ANALYZE
+    - SQLCOM_BACKUP_TABLE
+    - SQLCOM_CHECKSUM
+    - SQLCOM_OPTIMIZE
+    - SQLCOM_RESTORE_TABLE
+    - SQLCOM_REPAIR
+    are used for data maintenance and administration.
+    No call to ha_autocommit_or_rollback() is needed.
+    See http://dev.mysql.com/doc/refman/5.1/en/table-maintenance-sql.html
+
+    Note that the relative order of code that :
+    - lock tables with storage engines
+    - open / execute / closes cursors, read / write with storage engines
+    - generates a binlog event
+    - calls ha_autocommit_or_rollback()
+    - unlock tables with storage engines
+    - respond with send_ok(thd) / send_eof(thd) to the client
+    has to be *strictly* respected.
+    As a result, the place in the code where in practice
+    ha_autocommit_or_rollback() is called varies greatly between commands,
+    to comply with the of the original code structure.
+
   RETURN
     FALSE       OK
     TRUE        Error
@@ -2571,6 +2749,7 @@ mysql_execute_command(THD *thd)
           goto error;
 	query_cache_store_query(thd, all_tables);
 	res= handle_select(thd, lex, result, 0);
+        /* ha_autocommit_or_rollback() performed by select_send::send_eof() */
         if (result != lex->result)
           delete result;
       }
@@ -2585,6 +2764,11 @@ mysql_execute_command(THD *thd)
   case SQLCOM_EXECUTE:
   {
     mysql_sql_stmt_execute(thd);
+    /*
+      mysql_sql_stmt_execute() does recursively call
+      mysql_execute_command(), which performs the commit/rollback
+      for the prepared statement.
+    */
     break;
   }
   case SQLCOM_DEALLOCATE_PREPARE:
@@ -3279,9 +3463,18 @@ end_with_restore_list:
                                (ORDER *) select_lex->order_list.first,
                                unit->select_limit_cnt,
                                lex->duplicates, lex->ignore));
-    /* mysql_update return 2 if we need to switch to multi-update */
+    /*
+       mysql_update return 2 if we need to switch to multi-update.
+       Note that in this case, mysql_update() did no processing,
+       and that the current statement transaction is still opened.
+       It will be commited/rollbacked by the MULTI-UPDATE code below.
+       TODO: refactor this code.
+     */
     if (result != 2)
+    {
       break;
+    }
+    /* fall through */
   case SQLCOM_UPDATE_MULTI:
   {
     DBUG_ASSERT(first_table == all_tables && first_table != 0);
@@ -3336,6 +3529,7 @@ end_with_restore_list:
                             select_lex->where,
                             select_lex->options,
                             lex->duplicates, lex->ignore, unit, select_lex);
+    /* ha_autocommit_or_rollback() performed by multi_update::send_eof() */
     break;
   }
   case SQLCOM_REPLACE:
@@ -3357,6 +3551,7 @@ end_with_restore_list:
     res= mysql_insert(thd, all_tables, lex->field_list, lex->many_values,
 		      lex->update_list, lex->value_list,
                       lex->duplicates, lex->ignore);
+    /* ha_autocommit_or_rollback() performed by mysql_insert() */
     if (first_table->view && !first_table->contain_auto_increment)
       thd->last_insert_id= 0; // do not show last insert ID if VIEW have not it
     break;
@@ -3400,6 +3595,7 @@ end_with_restore_list:
                                              lex->duplicates, lex->ignore)))
       {
 	res= handle_select(thd, lex, result, OPTION_SETUP_TABLES_DONE);
+        /* ha_autocommit_or_rollback() performed by select_insert::send_eof() */
         /*
           Invalidate the table in the query cache if something changed
           after unlocking when changes become visible.
@@ -3464,6 +3660,7 @@ end_with_restore_list:
                        &select_lex->order_list,
                        unit->select_limit_cnt, select_lex->options,
                        FALSE);
+    /* ha_autocommit_or_rollback() performed by mysql_delete() */
     break;
   }
   case SQLCOM_DELETE_MULTI:
@@ -3510,6 +3707,14 @@ end_with_restore_list:
 			SELECT_NO_JOIN_CACHE | SELECT_NO_UNLOCK |
                         OPTION_SETUP_TABLES_DONE,
 			result, unit, select_lex);
+      DBUG_PRINT("info",("res: %d  report_error: %d",
+                        res, thd->net.report_error));
+      if (res)
+      {
+        result->send_error(ER_UNKNOWN_ERROR, ER(ER_UNKNOWN_ERROR));
+        result->abort();
+      }
+      /* ha_autocommit_or_rollback() performed by multi_delete::send_eof() */
       delete result;
     }
     else
@@ -3615,6 +3820,7 @@ end_with_restore_list:
     res= mysql_load(thd, lex->exchange, first_table, lex->field_list,
                     lex->update_list, lex->value_list, lex->duplicates,
                     lex->ignore, (bool) lex->local_file);
+    /* ha_autocommit_or_rollback() performed by mysql_load() */
     break;
   }
 
@@ -4474,6 +4680,11 @@ end_with_restore_list:
           So just execute the statement.
         */
 	res= sp->execute_procedure(thd, &lex->value_list);
+        /*
+          sp_head::execute_statement() does recursively call
+          mysql_execute_command(), which performs the commit/rollback
+          for the statements inside the procedure.
+        */
 	/*
           If warnings have been cleared, we have to clear total_warn_count
           too, otherwise the clients get confused.

--- 1.196/sql/sql_update.cc	2006-09-08 20:14:22 -07:00
+++ 1.197/sql/sql_update.cc	2006-09-08 20:14:22 -07:00
@@ -548,10 +548,12 @@ int mysql_update(THD *thd,
       thd->options|=OPTION_STATUS_NO_TRANS_UPDATE;
   }
   free_underlaid_joins(thd, select_lex);
-  if (transactional_table)
+
+  /* WARNING: error == 0 *is* an error */
+  /* On success, error == -1 */
+  if (ha_autocommit_or_rollback(thd, (error >= 0)))
   {
-    if (ha_autocommit_or_rollback(thd, error >= 0))
-      error=1;
+    error= 1;
   }
 
   if (thd->lock)
@@ -899,6 +901,8 @@ bool mysql_multi_update(THD *thd,
                         enum enum_duplicates handle_duplicates, bool ignore,
                         SELECT_LEX_UNIT *unit, SELECT_LEX *select_lex)
 {
+  int error= 0;
+  bool brc= FALSE;
   multi_update *result;
   DBUG_ENTER("mysql_multi_update");
 
@@ -914,7 +918,7 @@ bool mysql_multi_update(THD *thd,
                                MODE_STRICT_ALL_TABLES));
 
   List<Item> total_list;
-  (void) mysql_select(thd, &select_lex->ref_pointer_array,
+  error= mysql_select(thd, &select_lex->ref_pointer_array,
                       table_list, select_lex->with_wild,
                       total_list,
                       conds, 0, (ORDER *) NULL, (ORDER *)NULL, (Item *) NULL,
@@ -922,9 +926,18 @@ bool mysql_multi_update(THD *thd,
                       options | SELECT_NO_JOIN_CACHE | SELECT_NO_UNLOCK |
                       OPTION_SETUP_TABLES_DONE,
                       result, unit, select_lex);
+  DBUG_PRINT("info",("error: %d  report_error: %d", error,
+		     thd->net.report_error));
+  if (error)
+  {
+    /* If we had a another error reported earlier then this will be ignored */
+    result->send_error(ER_UNKNOWN_ERROR, ER(ER_UNKNOWN_ERROR));
+    result->abort();
+  }
   delete result;
   thd->abort_on_warning= 0;
-  DBUG_RETURN(FALSE);
+  brc= (error ? TRUE : FALSE);
+  DBUG_RETURN(brc);
 }
 
 
@@ -936,7 +949,7 @@ multi_update::multi_update(TABLE_LIST *t
   :all_tables(table_list), leaves(leaves_list), update_tables(0),
    tmp_tables(0), updated(0), found(0), fields(field_list),
    values(value_list), table_count(0), copy_field(0),
-   handle_duplicates(handle_duplicates_arg), do_update(1), trans_safe(0),
+   handle_duplicates(handle_duplicates_arg), do_update(1), trans_safe(1),
    transactional_tables(1), ignore(ignore_arg)
 {}
 
@@ -1360,12 +1373,11 @@ void multi_update::send_error(uint errco
   /* First send error what ever it is ... */
   my_error(errcode, MYF(0), err);
 
-  /* If nothing updated return */
-  if (!updated)
-    return;
-
-  /* Something already updated so we have to invalidate cache */
-  query_cache_invalidate3(thd, update_tables, 1);
+  if (updated)
+  {
+    /* Something already updated so we have to invalidate cache */
+    query_cache_invalidate3(thd, update_tables, 1);
+  }
 
   /*
     If all tables that has been updated are trans safe then just do rollback.
@@ -1546,11 +1558,7 @@ bool multi_update::send_eof()
       thd->options|=OPTION_STATUS_NO_TRANS_UPDATE;
   }
 
-  if (transactional_tables)
-  {
-    if (ha_autocommit_or_rollback(thd, local_error != 0))
-      local_error=1;
-  }
+  local_error= ha_autocommit_or_rollback(thd, local_error);
 
   if (local_error > 0) // if the above log write did not fail ...
   {

--- 1.132/sql/sql_union.cc	2006-09-08 20:14:22 -07:00
+++ 1.133/sql/sql_union.cc	2006-09-08 20:14:22 -07:00
@@ -73,6 +73,12 @@ bool select_union::send_data(List<Item> 
 }
 
 
+void select_union::send_error(uint errcode, const char *err)
+{
+  my_message(errcode, err, MYF(0));
+}
+
+
 bool select_union::send_eof()
 {
   return 0;

--- 1.154/mysql-test/r/subselect.result	2006-09-08 20:14:22 -07:00
+++ 1.155/mysql-test/r/subselect.result	2006-09-08 20:14:22 -07:00
@@ -1600,8 +1600,7 @@ insert into t1 values (2);
 set sort_buffer_size = (select s1 from t1);
 ERROR 21000: Subquery returns more than 1 row
 do (select * from t1);
-Warnings:
-Error	1242	Subquery returns more than 1 row
+ERROR 21000: Subquery returns more than 1 row
 drop table t1;
 create table t1 (s1 char);
 insert into t1 values ('e');

--- 1.125/mysql-test/t/subselect.test	2006-09-08 20:14:22 -07:00
+++ 1.126/mysql-test/t/subselect.test	2006-09-08 20:14:22 -07:00
@@ -971,8 +971,9 @@ DROP TABLE t1,t2;
 create table t1 (s1 int);
 insert into t1 values (1);
 insert into t1 values (2);
--- error 1242
+-- error ER_SUBQUERY_NO_1_ROW
 set sort_buffer_size = (select s1 from t1);
+-- error ER_SUBQUERY_NO_1_ROW
 do (select * from t1);
 drop table t1;
 

--- 1.18/mysql-test/r/rpl_sp.result	2006-09-08 20:14:22 -07:00
+++ 1.19/mysql-test/r/rpl_sp.result	2006-09-08 20:14:22 -07:00
@@ -240,8 +240,7 @@ insert into t2 values(x),(x);
 return 10;
 end|
 do fn1(100);
-Warnings:
-Error	1062	Duplicate entry '100' for key 1
+ERROR 23000: Duplicate entry '100' for key 1
 select fn1(20);
 ERROR 23000: Duplicate entry '20' for key 1
 select * from t2;

--- 1.13/mysql-test/t/rpl_sp.test	2006-09-08 20:14:22 -07:00
+++ 1.14/mysql-test/t/rpl_sp.test	2006-09-08 20:14:22 -07:00
@@ -303,9 +303,10 @@ end|
 
 delimiter ;|
 
+--error ER_DUP_ENTRY
 do fn1(100);
 
---error 1062
+--error ER_DUP_ENTRY
 select fn1(20);
 
 select * from t2;

--- 1.33/mysql-test/t/disabled.def	2006-09-08 20:14:22 -07:00
+++ 1.34/mysql-test/t/disabled.def	2006-09-08 20:14:22 -07:00
@@ -11,3 +11,5 @@
 ##############################################################################
 
 ndb_load        : Bug#17233
+engine_mix_bdb_ndb : Bug#21590
+engine_mix_myisam_ndb : Bug#21590

--- 1.277/sql/ha_ndbcluster.cc	2006-09-08 20:14:22 -07:00
+++ 1.278/sql/ha_ndbcluster.cc	2006-09-08 20:14:22 -07:00
@@ -3693,6 +3693,7 @@ int ha_ndbcluster::external_lock(THD *th
         DBUG_PRINT("trans",("ending non-updating transaction"));
         ndb->closeTransaction(m_active_trans);
         thd_ndb->stmt= NULL;
+        trans_unregister_ha(thd, FALSE, &ndbcluster_hton);
       }
     }
     m_table_info= NULL;
--- New file ---
+++ mysql-test/include/autocommit_common.inc	06/09/08 18:52:38

## Bug#12713 (Error in a stored function called from a SELECT doesn't cause
##    ROLLBACK of statem)

##
## Pre-Requisites :
## - $engine_type should be set
##

set autocommit=1;

--disable_warnings
drop table if exists t1;
drop table if exists t2;
drop table if exists t3;
drop function if exists f2;
drop procedure if exists bug12713_call;
drop procedure if exists bug12713_dump_spvars;
--enable_warnings

eval create table t1 (a int) engine = $engine_type;
eval create table t2 (a int unique) engine = $engine_type;
eval create table t3 (a int) engine = $engine_type;

insert into t1 (a) values (1), (2);
insert into t3 (a) values (1), (2);

delimiter //;

## Cause a failure every time
create function f2(x int) returns int
begin
  insert into t2 (a) values (x);
  insert into t2 (a) values (x);
  return x;
end//

delimiter ;//

set autocommit=0;

##============================================================================
## Design notes
##
## In each case, statement rollback is expected.
## for transactional engines, the rollback should be properly executed
## for non transactional engines, the rollback may cause warnings.
##
## The test pattern is as follows
## - insert 1000+N
## - statement with a side effect, that fails to insert N twice
## - a statement rollback is expected (expecting 1 row 1000+N only) in t2
## - a rollback is performed
## - expecting a clean table t2.
##============================================================================

insert into t2 (a) values (1001);
--error ER_DUP_ENTRY
insert into t1 (a) values (f2(1));
select * from t2;
rollback;
select * from t2;
commit;

insert into t2 (a) values (1002);
--error ER_DUP_ENTRY
insert into t3 (a) select f2(2) from t1;
select * from t2;
rollback;
select * from t2;
commit;

insert into t2 (a) values (1003);
--error ER_DUP_ENTRY
update t1 set a= a + f2(3);
select * from t2;
rollback;
select * from t2;
commit;

insert into t2 (a) values (1004);
--error ER_DUP_ENTRY
update t1, t3 set t1.a = 0, t3.a = 0 where (f2(4) = 4) and (t1.a = t3.a);
select * from t2;
rollback;
select * from t2;
commit;

insert into t2 (a) values (1005);
--error ER_DUP_ENTRY
delete from t1 where (a = f2(5));
select * from t2;
rollback;
select * from t2;
commit;

insert into t2 (a) values (1006);
--error ER_DUP_ENTRY
delete from t1, t3 using t1, t3 where (f2(6) = 6) ;
select * from t2;
rollback;
select * from t2;
commit;

insert into t2 (a) values (1007);
--error ER_DUP_ENTRY
replace t1 values (f2(7));
select * from t2;
rollback;
select * from t2;
commit;

insert into t2 (a) values (1008);
--error ER_DUP_ENTRY
replace into t3 (a) select f2(8) from t1;
select * from t2;
rollback;
select * from t2;
commit;

insert into t2 (a) values (1009);
--error ER_DUP_ENTRY
select f2(9) from t1 ;
select * from t2;
rollback;
select * from t2;
commit;

insert into t2 (a) values (1010);
--error ER_DUP_ENTRY
show databases where (f2(10) = 10);
select * from t2;
rollback;
select * from t2;
commit;

insert into t2 (a) values (1011);
--error ER_DUP_ENTRY
show tables where (f2(11) = 11);
select * from t2;
rollback;
select * from t2;
commit;

insert into t2 (a) values (1012);
--error ER_DUP_ENTRY
show triggers where (f2(12) = 12);
select * from t2;
rollback;
select * from t2;
commit;

insert into t2 (a) values (1013);
--error ER_DUP_ENTRY
show table status where (f2(13) = 13);
select * from t2;
rollback;
select * from t2;
commit;

insert into t2 (a) values (1014);
--error ER_DUP_ENTRY
show open tables where (f2(14) = 14);
select * from t2;
rollback;
select * from t2;
commit;

insert into t2 (a) values (1015);
--error ER_DUP_ENTRY
show columns in mysql.proc where (f2(15) = 15);
select * from t2;
rollback;
select * from t2;
commit;

insert into t2 (a) values (1016);
--error ER_DUP_ENTRY
show status where (f2(16) = 16);
select * from t2;
rollback;
select * from t2;
commit;

insert into t2 (a) values (1017);
--error ER_DUP_ENTRY
show variables where (f2(17) = 17);
select * from t2;
rollback;
select * from t2;
commit;

insert into t2 (a) values (1018);
--error ER_DUP_ENTRY
show charset where (f2(18) = 18);
select * from t2;
rollback;
select * from t2;
commit;

insert into t2 (a) values (1019);
--error ER_DUP_ENTRY
show collation where (f2(19) = 19);
select * from t2;
rollback;
select * from t2;
commit;

insert into t2 (a) values (1020);
--error ER_DUP_ENTRY
show procedure status where (f2(20) = 20);
select * from t2;
rollback;
select * from t2;
commit;

insert into t2 (a) values (1021);
--error ER_DUP_ENTRY
show function status where (f2(21) = 21);
select * from t2;
rollback;
select * from t2;
commit;

insert into t2 (a) values (1022);
prepare stmt from "insert into t1 (a) values (f2(22))";
--error ER_DUP_ENTRY
execute stmt;
select * from t2;
rollback;
select * from t2;
commit;

insert into t2 (a) values (1023);
--error ER_DUP_ENTRY
do (f2(23));
select * from t2;
rollback;
select * from t2;
commit;

## Please note :
## This will insert a record 1024 in t1 (statement commit)
## This will insert a record 24 in t1 (statement commit)
## then will rollback the second insert only (24) (statement rollback)
## then will rollback the complete transaction (transaction rollback)

delimiter //;

create procedure bug12713_call ()
begin
  insert into t2 (a) values (24);
  insert into t2 (a) values (24);
end//

delimiter ;//

insert into t2 (a) values (1024);
--error ER_DUP_ENTRY
call bug12713_call();
select * from t2;
rollback;
select * from t2;
commit;

##============================================================================
## Testing select_to_file
##============================================================================

--system rm -f $MYSQLTEST_VARDIR/tmp/autocommit_out
insert into t2 (a) values (1025);

--replace_result $MYSQLTEST_VARDIR ..
--error ER_DUP_ENTRY
eval select f2(25) into outfile "$MYSQLTEST_VARDIR/tmp/autocommit_out" from t1;

## See Bug#22035 about that error 13
--replace_result $MYSQLTEST_VARDIR ..
--error 13
eval load data infile "$MYSQLTEST_VARDIR/tmp/autocommit_out" into table t1 (a);

select * from t2;
rollback;
select * from t2;
commit;

##============================================================================
## Testing select_dumpvar
##============================================================================

insert into t2 (a) values (1026);
--error ER_DUP_ENTRY
select f2(26) into @foo;
select * from t2;
rollback;
select * from t2;
commit;

##============================================================================
## Testing Select_fetch_into_spvars
##============================================================================

delimiter //;

create procedure bug12713_dump_spvars ()
begin
  declare foo int;

  declare continue handler for sqlexception
  begin
    select "Exception trapped";
  end;

  select f2(27) into foo;
  select * from t2;
end//

delimiter ;//

insert into t2 (a) values (1027);
call bug12713_dump_spvars ();
rollback;
select * from t2;
commit;


##============================================================================
## Cleanup
##============================================================================

set autocommit=1;

drop table t1;
drop table t2;
drop table t3;
drop function f2;
drop procedure bug12713_call;
drop procedure bug12713_dump_spvars;


--- New file ---
+++ mysql-test/include/engine_mix_common.inc	06/09/08 18:51:11

## Bug#12713 (Error in a stored function called from a SELECT doesn't cause
##    ROLLBACK of statem)

##
## Pre-Requisites :
## - $engine_type_1 should be set
## - $engine_type_2 should be set
##

set autocommit=1;

--disable_warnings
drop table if exists t1, t2;
drop function if exists f2_hard;
drop function if exists f2_soft;
--enable_warnings

eval create table t1 (a int) engine = $engine_type_1;
eval create table t2 (a int unique) engine = $engine_type_2;

insert into t1 (a) values (1), (2);

delimiter //;

## Cause a failure every time
create function f2_hard(x int) returns int
begin
  insert into t2 (a) values (x);
  insert into t2 (a) values (x);
  return x;
end//

## May or may not fail
## the second parameter is not used ...
## ... but the optimizer does not know that.
create function f2_soft(x int, y int) returns int
begin
  insert into t2 (a) values (x);
  return x;
end//
delimiter ;//

set autocommit=0;

## In each case, statement rollback is expected
## for transactional engines, the rollback should be properly executed
## for non transactional engines, the rollback may cause warnings

## Causing failures in engine_2 only

--error ER_DUP_ENTRY
insert into t1 (a) values (f2_hard(1));
select * from t2;
rollback;
select * from t2;
commit;

--error ER_DUP_ENTRY
update t1 set a= a + f2_hard(2);
select * from t2;
rollback;
select * from t2;
commit;

--error ER_DUP_ENTRY
delete from t1 where (a = f2_hard(3));
select * from t2;
rollback;
select * from t2;
commit;

--error ER_DUP_ENTRY
select f2_hard(4) from t1 ;
select * from t2;
rollback;
select * from t2;
commit;

## Causing engine_2 to have pending transactions,
## then engine_1 to have pending transactions,
## then failures in engine_2
## The extras 'delete from t2' are to cleanup after non transactional engines.

delete from t2;
commit;

--error ER_DUP_ENTRY
insert into t1 (a) values (f2_soft(1, 2)), (f2_soft(1, 3));
select * from t2;
rollback;
select * from t2;
commit;
delete from t2;
commit;

--error ER_DUP_ENTRY
update t1 set a= a + f2_soft(2, a);
select * from t2;
rollback;
select * from t2;
commit;
delete from t2;
commit;

--error ER_DUP_ENTRY
select f2_soft(4, a) from t1 ;
select * from t2;
rollback;
select * from t2;
commit;
delete from t2;
commit;

set autocommit=1;

drop table t1, t2;
drop function f2_hard;
drop function f2_soft;


--- New file ---
+++ mysql-test/r/autocommit_bdb.result	06/09/08 18:52:38
set autocommit=1;
drop table if exists t1;
drop table if exists t2;
drop table if exists t3;
drop function if exists f2;
drop procedure if exists bug12713_call;
drop procedure if exists bug12713_dump_spvars;
create table t1 (a int) engine = BerkeleyDB;
create table t2 (a int unique) engine = BerkeleyDB;
create table t3 (a int) engine = BerkeleyDB;
insert into t1 (a) values (1), (2);
insert into t3 (a) values (1), (2);
create function f2(x int) returns int
begin
insert into t2 (a) values (x);
insert into t2 (a) values (x);
return x;
end//
set autocommit=0;
insert into t2 (a) values (1001);
insert into t1 (a) values (f2(1));
ERROR 23000: Duplicate entry '1' for key 1
select * from t2;
a
1001
rollback;
select * from t2;
a
commit;
insert into t2 (a) values (1002);
insert into t3 (a) select f2(2) from t1;
ERROR 23000: Duplicate entry '2' for key 1
select * from t2;
a
1002
rollback;
select * from t2;
a
commit;
insert into t2 (a) values (1003);
update t1 set a= a + f2(3);
ERROR 23000: Duplicate entry '3' for key 1
select * from t2;
a
1003
rollback;
select * from t2;
a
commit;
insert into t2 (a) values (1004);
update t1, t3 set t1.a = 0, t3.a = 0 where (f2(4) = 4) and (t1.a = t3.a);
ERROR 23000: Duplicate entry '4' for key 1
select * from t2;
a
1004
rollback;
select * from t2;
a
commit;
insert into t2 (a) values (1005);
delete from t1 where (a = f2(5));
ERROR 23000: Duplicate entry '5' for key 1
select * from t2;
a
1005
rollback;
select * from t2;
a
commit;
insert into t2 (a) values (1006);
delete from t1, t3 using t1, t3 where (f2(6) = 6) ;
ERROR 23000: Duplicate entry '6' for key 1
select * from t2;
a
1006
rollback;
select * from t2;
a
commit;
insert into t2 (a) values (1007);
replace t1 values (f2(7));
ERROR 23000: Duplicate entry '7' for key 1
select * from t2;
a
1007
rollback;
select * from t2;
a
commit;
insert into t2 (a) values (1008);
replace into t3 (a) select f2(8) from t1;
ERROR 23000: Duplicate entry '8' for key 1
select * from t2;
a
1008
rollback;
select * from t2;
a
commit;
insert into t2 (a) values (1009);
select f2(9) from t1 ;
ERROR 23000: Duplicate entry '9' for key 1
select * from t2;
a
1009
rollback;
select * from t2;
a
commit;
insert into t2 (a) values (1010);
show databases where (f2(10) = 10);
ERROR 23000: Duplicate entry '10' for key 1
select * from t2;
a
1010
rollback;
select * from t2;
a
commit;
insert into t2 (a) values (1011);
show tables where (f2(11) = 11);
ERROR 23000: Duplicate entry '11' for key 1
select * from t2;
a
1011
rollback;
select * from t2;
a
commit;
insert into t2 (a) values (1012);
show triggers where (f2(12) = 12);
ERROR 23000: Duplicate entry '12' for key 1
select * from t2;
a
1012
rollback;
select * from t2;
a
commit;
insert into t2 (a) values (1013);
show table status where (f2(13) = 13);
ERROR 23000: Duplicate entry '13' for key 1
select * from t2;
a
1013
rollback;
select * from t2;
a
commit;
insert into t2 (a) values (1014);
show open tables where (f2(14) = 14);
ERROR 23000: Duplicate entry '14' for key 1
select * from t2;
a
1014
rollback;
select * from t2;
a
commit;
insert into t2 (a) values (1015);
show columns in mysql.proc where (f2(15) = 15);
ERROR 23000: Duplicate entry '15' for key 1
select * from t2;
a
1015
rollback;
select * from t2;
a
commit;
insert into t2 (a) values (1016);
show status where (f2(16) = 16);
ERROR 23000: Duplicate entry '16' for key 1
select * from t2;
a
1016
rollback;
select * from t2;
a
commit;
insert into t2 (a) values (1017);
show variables where (f2(17) = 17);
ERROR 23000: Duplicate entry '17' for key 1
select * from t2;
a
1017
rollback;
select * from t2;
a
commit;
insert into t2 (a) values (1018);
show charset where (f2(18) = 18);
ERROR 23000: Duplicate entry '18' for key 1
select * from t2;
a
1018
rollback;
select * from t2;
a
commit;
insert into t2 (a) values (1019);
show collation where (f2(19) = 19);
ERROR 23000: Duplicate entry '19' for key 1
select * from t2;
a
1019
rollback;
select * from t2;
a
commit;
insert into t2 (a) values (1020);
show procedure status where (f2(20) = 20);
ERROR 23000: Duplicate entry '20' for key 1
select * from t2;
a
1020
rollback;
select * from t2;
a
commit;
insert into t2 (a) values (1021);
show function status where (f2(21) = 21);
ERROR 23000: Duplicate entry '21' for key 1
select * from t2;
a
1021
rollback;
select * from t2;
a
commit;
insert into t2 (a) values (1022);
prepare stmt from "insert into t1 (a) values (f2(22))";
execute stmt;
ERROR 23000: Duplicate entry '22' for key 1
select * from t2;
a
1022
rollback;
select * from t2;
a
commit;
insert into t2 (a) values (1023);
do (f2(23));
ERROR 23000: Duplicate entry '23' for key 1
select * from t2;
a
1023
rollback;
select * from t2;
a
commit;
create procedure bug12713_call ()
begin
insert into t2 (a) values (24);
insert into t2 (a) values (24);
end//
insert into t2 (a) values (1024);
call bug12713_call();
ERROR 23000: Duplicate entry '24' for key 1
select * from t2;
a
24
1024
rollback;
select * from t2;
a
commit;
insert into t2 (a) values (1025);
select f2(25) into outfile "../tmp/autocommit_out" from t1;
ERROR 23000: Duplicate entry '25' for key 1
load data infile "../tmp/autocommit_out" into table t1 (a);
ERROR HY000: Can't get stat of '../tmp/autocommit_out' (Errcode: 2)
select * from t2;
a
1025
rollback;
select * from t2;
a
commit;
insert into t2 (a) values (1026);
select f2(26) into @foo;
ERROR 23000: Duplicate entry '26' for key 1
select * from t2;
a
1026
rollback;
select * from t2;
a
commit;
create procedure bug12713_dump_spvars ()
begin
declare foo int;
declare continue handler for sqlexception
begin
select "Exception trapped";
end;
select f2(27) into foo;
select * from t2;
end//
insert into t2 (a) values (1027);
call bug12713_dump_spvars ();
Exception trapped
Exception trapped
a
1027
rollback;
select * from t2;
a
commit;
set autocommit=1;
drop table t1;
drop table t2;
drop table t3;
drop function f2;
drop procedure bug12713_call;
drop procedure bug12713_dump_spvars;

--- New file ---
+++ mysql-test/r/autocommit_innodb.result	06/09/08 18:52:38
set autocommit=1;
drop table if exists t1;
drop table if exists t2;
drop table if exists t3;
drop function if exists f2;
drop procedure if exists bug12713_call;
drop procedure if exists bug12713_dump_spvars;
create table t1 (a int) engine = InnoDB;
create table t2 (a int unique) engine = InnoDB;
create table t3 (a int) engine = InnoDB;
insert into t1 (a) values (1), (2);
insert into t3 (a) values (1), (2);
create function f2(x int) returns int
begin
insert into t2 (a) values (x);
insert into t2 (a) values (x);
return x;
end//
set autocommit=0;
insert into t2 (a) values (1001);
insert into t1 (a) values (f2(1));
ERROR 23000: Duplicate entry '1' for key 1
select * from t2;
a
1001
rollback;
select * from t2;
a
commit;
insert into t2 (a) values (1002);
insert into t3 (a) select f2(2) from t1;
ERROR 23000: Duplicate entry '2' for key 1
select * from t2;
a
1002
rollback;
select * from t2;
a
commit;
insert into t2 (a) values (1003);
update t1 set a= a + f2(3);
ERROR 23000: Duplicate entry '3' for key 1
select * from t2;
a
1003
rollback;
select * from t2;
a
commit;
insert into t2 (a) values (1004);
update t1, t3 set t1.a = 0, t3.a = 0 where (f2(4) = 4) and (t1.a = t3.a);
ERROR 23000: Duplicate entry '4' for key 1
select * from t2;
a
1004
rollback;
select * from t2;
a
commit;
insert into t2 (a) values (1005);
delete from t1 where (a = f2(5));
ERROR 23000: Duplicate entry '5' for key 1
select * from t2;
a
1005
rollback;
select * from t2;
a
commit;
insert into t2 (a) values (1006);
delete from t1, t3 using t1, t3 where (f2(6) = 6) ;
ERROR 23000: Duplicate entry '6' for key 1
select * from t2;
a
1006
rollback;
select * from t2;
a
commit;
insert into t2 (a) values (1007);
replace t1 values (f2(7));
ERROR 23000: Duplicate entry '7' for key 1
select * from t2;
a
1007
rollback;
select * from t2;
a
commit;
insert into t2 (a) values (1008);
replace into t3 (a) select f2(8) from t1;
ERROR 23000: Duplicate entry '8' for key 1
select * from t2;
a
1008
rollback;
select * from t2;
a
commit;
insert into t2 (a) values (1009);
select f2(9) from t1 ;
ERROR 23000: Duplicate entry '9' for key 1
select * from t2;
a
1009
rollback;
select * from t2;
a
commit;
insert into t2 (a) values (1010);
show databases where (f2(10) = 10);
ERROR 23000: Duplicate entry '10' for key 1
select * from t2;
a
1010
rollback;
select * from t2;
a
commit;
insert into t2 (a) values (1011);
show tables where (f2(11) = 11);
ERROR 23000: Duplicate entry '11' for key 1
select * from t2;
a
1011
rollback;
select * from t2;
a
commit;
insert into t2 (a) values (1012);
show triggers where (f2(12) = 12);
ERROR 23000: Duplicate entry '12' for key 1
select * from t2;
a
1012
rollback;
select * from t2;
a
commit;
insert into t2 (a) values (1013);
show table status where (f2(13) = 13);
ERROR 23000: Duplicate entry '13' for key 1
select * from t2;
a
1013
rollback;
select * from t2;
a
commit;
insert into t2 (a) values (1014);
show open tables where (f2(14) = 14);
ERROR 23000: Duplicate entry '14' for key 1
select * from t2;
a
1014
rollback;
select * from t2;
a
commit;
insert into t2 (a) values (1015);
show columns in mysql.proc where (f2(15) = 15);
ERROR 23000: Duplicate entry '15' for key 1
select * from t2;
a
1015
rollback;
select * from t2;
a
commit;
insert into t2 (a) values (1016);
show status where (f2(16) = 16);
ERROR 23000: Duplicate entry '16' for key 1
select * from t2;
a
1016
rollback;
select * from t2;
a
commit;
insert into t2 (a) values (1017);
show variables where (f2(17) = 17);
ERROR 23000: Duplicate entry '17' for key 1
select * from t2;
a
1017
rollback;
select * from t2;
a
commit;
insert into t2 (a) values (1018);
show charset where (f2(18) = 18);
ERROR 23000: Duplicate entry '18' for key 1
select * from t2;
a
1018
rollback;
select * from t2;
a
commit;
insert into t2 (a) values (1019);
show collation where (f2(19) = 19);
ERROR 23000: Duplicate entry '19' for key 1
select * from t2;
a
1019
rollback;
select * from t2;
a
commit;
insert into t2 (a) values (1020);
show procedure status where (f2(20) = 20);
ERROR 23000: Duplicate entry '20' for key 1
select * from t2;
a
1020
rollback;
select * from t2;
a
commit;
insert into t2 (a) values (1021);
show function status where (f2(21) = 21);
ERROR 23000: Duplicate entry '21' for key 1
select * from t2;
a
1021
rollback;
select * from t2;
a
commit;
insert into t2 (a) values (1022);
prepare stmt from "insert into t1 (a) values (f2(22))";
execute stmt;
ERROR 23000: Duplicate entry '22' for key 1
select * from t2;
a
1022
rollback;
select * from t2;
a
commit;
insert into t2 (a) values (1023);
do (f2(23));
ERROR 23000: Duplicate entry '23' for key 1
select * from t2;
a
1023
rollback;
select * from t2;
a
commit;
create procedure bug12713_call ()
begin
insert into t2 (a) values (24);
insert into t2 (a) values (24);
end//
insert into t2 (a) values (1024);
call bug12713_call();
ERROR 23000: Duplicate entry '24' for key 1
select * from t2;
a
24
1024
rollback;
select * from t2;
a
commit;
insert into t2 (a) values (1025);
select f2(25) into outfile "../tmp/autocommit_out" from t1;
ERROR 23000: Duplicate entry '25' for key 1
load data infile "../tmp/autocommit_out" into table t1 (a);
ERROR HY000: Can't get stat of '../tmp/autocommit_out' (Errcode: 2)
select * from t2;
a
1025
rollback;
select * from t2;
a
commit;
insert into t2 (a) values (1026);
select f2(26) into @foo;
ERROR 23000: Duplicate entry '26' for key 1
select * from t2;
a
1026
rollback;
select * from t2;
a
commit;
create procedure bug12713_dump_spvars ()
begin
declare foo int;
declare continue handler for sqlexception
begin
select "Exception trapped";
end;
select f2(27) into foo;
select * from t2;
end//
insert into t2 (a) values (1027);
call bug12713_dump_spvars ();
Exception trapped
Exception trapped
a
1027
rollback;
select * from t2;
a
commit;
set autocommit=1;
drop table t1;
drop table t2;
drop table t3;
drop function f2;
drop procedure bug12713_call;
drop procedure bug12713_dump_spvars;

--- New file ---
+++ mysql-test/r/engine_mix_bdb_innodb.result	06/09/08 18:51:11
set autocommit=1;
drop table if exists t1, t2;
drop function if exists f2_hard;
drop function if exists f2_soft;
create table t1 (a int) engine = BerkeleyDB;
create table t2 (a int unique) engine = InnoDB;
insert into t1 (a) values (1), (2);
create function f2_hard(x int) returns int
begin
insert into t2 (a) values (x);
insert into t2 (a) values (x);
return x;
end//
create function f2_soft(x int, y int) returns int
begin
insert into t2 (a) values (x);
return x;
end//
set autocommit=0;
insert into t1 (a) values (f2_hard(1));
ERROR 23000: Duplicate entry '1' for key 1
select * from t2;
a
rollback;
select * from t2;
a
commit;
update t1 set a= a + f2_hard(2);
ERROR 23000: Duplicate entry '2' for key 1
select * from t2;
a
rollback;
select * from t2;
a
commit;
delete from t1 where (a = f2_hard(3));
ERROR 23000: Duplicate entry '3' for key 1
select * from t2;
a
rollback;
select * from t2;
a
commit;
select f2_hard(4) from t1 ;
ERROR 23000: Duplicate entry '4' for key 1
select * from t2;
a
rollback;
select * from t2;
a
commit;
delete from t2;
commit;
insert into t1 (a) values (f2_soft(1, 2)), (f2_soft(1, 3));
ERROR 23000: Duplicate entry '1' for key 1
select * from t2;
a
rollback;
select * from t2;
a
commit;
delete from t2;
commit;
update t1 set a= a + f2_soft(2, a);
ERROR 23000: Duplicate entry '2' for key 1
select * from t2;
a
rollback;
select * from t2;
a
commit;
delete from t2;
commit;
select f2_soft(4, a) from t1 ;
ERROR 23000: Duplicate entry '4' for key 1
select * from t2;
a
rollback;
select * from t2;
a
commit;
delete from t2;
commit;
set autocommit=1;
drop table t1, t2;
drop function f2_hard;
drop function f2_soft;

--- New file ---
+++ mysql-test/r/engine_mix_bdb_myisam.result	06/09/08 18:51:11
set autocommit=1;
drop table if exists t1, t2;
drop function if exists f2_hard;
drop function if exists f2_soft;
create table t1 (a int) engine = BerkeleyDB;
create table t2 (a int unique) engine = MyISAM;
insert into t1 (a) values (1), (2);
create function f2_hard(x int) returns int
begin
insert into t2 (a) values (x);
insert into t2 (a) values (x);
return x;
end//
create function f2_soft(x int, y int) returns int
begin
insert into t2 (a) values (x);
return x;
end//
set autocommit=0;
insert into t1 (a) values (f2_hard(1));
ERROR 23000: Duplicate entry '1' for key 1
select * from t2;
a
1
rollback;
select * from t2;
a
1
commit;
update t1 set a= a + f2_hard(2);
ERROR 23000: Duplicate entry '2' for key 1
select * from t2;
a
1
2
rollback;
select * from t2;
a
1
2
commit;
delete from t1 where (a = f2_hard(3));
ERROR 23000: Duplicate entry '3' for key 1
select * from t2;
a
1
2
3
rollback;
select * from t2;
a
1
2
3
commit;
select f2_hard(4) from t1 ;
ERROR 23000: Duplicate entry '4' for key 1
select * from t2;
a
1
2
3
4
rollback;
select * from t2;
a
1
2
3
4
commit;
delete from t2;
commit;
insert into t1 (a) values (f2_soft(1, 2)), (f2_soft(1, 3));
ERROR 23000: Duplicate entry '1' for key 1
select * from t2;
a
1
rollback;
select * from t2;
a
1
commit;
delete from t2;
commit;
update t1 set a= a + f2_soft(2, a);
ERROR 23000: Duplicate entry '2' for key 1
select * from t2;
a
2
rollback;
select * from t2;
a
2
commit;
delete from t2;
commit;
select f2_soft(4, a) from t1 ;
ERROR 23000: Duplicate entry '4' for key 1
select * from t2;
a
4
rollback;
select * from t2;
a
4
commit;
delete from t2;
commit;
set autocommit=1;
drop table t1, t2;
drop function f2_hard;
drop function f2_soft;

--- New file ---
+++ mysql-test/r/engine_mix_bdb_ndb.result	06/09/08 18:51:11
set autocommit=1;
drop table if exists t1, t2;
drop function if exists f2_hard;
drop function if exists f2_soft;
create table t1 (a int) engine = BerkeleyDB;
create table t2 (a int unique) engine = ndbcluster;
insert into t1 (a) values (1), (2);
create function f2_hard(x int) returns int
begin
insert into t2 (a) values (x);
insert into t2 (a) values (x);
return x;
end//
create function f2_soft(x int, y int) returns int
begin
insert into t2 (a) values (x);
return x;
end//
set autocommit=0;
insert into t1 (a) values (f2_hard(1));
ERROR 23000: Duplicate entry '1' for key 1
select * from t2;
a
rollback;
select * from t2;
a
commit;
update t1 set a= a + f2_hard(2);
ERROR 23000: Duplicate entry '2' for key 1
select * from t2;
a
rollback;
select * from t2;
a
commit;
delete from t1 where (a = f2_hard(3));
ERROR 23000: Duplicate entry '3' for key 1
select * from t2;
a
rollback;
select * from t2;
a
commit;
select f2_hard(4) from t1 ;
ERROR 23000: Duplicate entry '4' for key 1
select * from t2;
a
rollback;
select * from t2;
a
commit;
delete from t2;
commit;
insert into t1 (a) values (f2_soft(1, 2)), (f2_soft(1, 3));
ERROR 23000: Duplicate entry '1' for key 1
select * from t2;
a
rollback;
select * from t2;
a
commit;
delete from t2;
commit;
update t1 set a= a + f2_soft(2, a);
ERROR 23000: Duplicate entry '2' for key 1
select * from t2;
a
rollback;
select * from t2;
a
commit;
delete from t2;
commit;
select f2_soft(4, a) from t1 ;
ERROR 23000: Duplicate entry '4' for key 1
select * from t2;
a
rollback;
select * from t2;
a
commit;
delete from t2;
commit;
set autocommit=1;
drop table t1, t2;
drop function f2_hard;
drop function f2_soft;

--- New file ---
+++ mysql-test/r/engine_mix_innodb_bdb.result	06/09/08 18:51:11
set autocommit=1;
drop table if exists t1, t2;
drop function if exists f2_hard;
drop function if exists f2_soft;
create table t1 (a int) engine = InnoDB;
create table t2 (a int unique) engine = BerkeleyDB;
insert into t1 (a) values (1), (2);
create function f2_hard(x int) returns int
begin
insert into t2 (a) values (x);
insert into t2 (a) values (x);
return x;
end//
create function f2_soft(x int, y int) returns int
begin
insert into t2 (a) values (x);
return x;
end//
set autocommit=0;
insert into t1 (a) values (f2_hard(1));
ERROR 23000: Duplicate entry '1' for key 1
select * from t2;
a
rollback;
select * from t2;
a
commit;
update t1 set a= a + f2_hard(2);
ERROR 23000: Duplicate entry '2' for key 1
select * from t2;
a
rollback;
select * from t2;
a
commit;
delete from t1 where (a = f2_hard(3));
ERROR 23000: Duplicate entry '3' for key 1
select * from t2;
a
rollback;
select * from t2;
a
commit;
select f2_hard(4) from t1 ;
ERROR 23000: Duplicate entry '4' for key 1
select * from t2;
a
rollback;
select * from t2;
a
commit;
delete from t2;
commit;
insert into t1 (a) values (f2_soft(1, 2)), (f2_soft(1, 3));
ERROR 23000: Duplicate entry '1' for key 1
select * from t2;
a
rollback;
select * from t2;
a
commit;
delete from t2;
commit;
update t1 set a= a + f2_soft(2, a);
ERROR 23000: Duplicate entry '2' for key 1
select * from t2;
a
rollback;
select * from t2;
a
commit;
delete from t2;
commit;
select f2_soft(4, a) from t1 ;
ERROR 23000: Duplicate entry '4' for key 1
select * from t2;
a
rollback;
select * from t2;
a
commit;
delete from t2;
commit;
set autocommit=1;
drop table t1, t2;
drop function f2_hard;
drop function f2_soft;

--- New file ---
+++ mysql-test/r/engine_mix_innodb_myisam.result	06/09/08 18:51:11
set autocommit=1;
drop table if exists t1, t2;
drop function if exists f2_hard;
drop function if exists f2_soft;
create table t1 (a int) engine = InnoDB;
create table t2 (a int unique) engine = MyISAM;
insert into t1 (a) values (1), (2);
create function f2_hard(x int) returns int
begin
insert into t2 (a) values (x);
insert into t2 (a) values (x);
return x;
end//
create function f2_soft(x int, y int) returns int
begin
insert into t2 (a) values (x);
return x;
end//
set autocommit=0;
insert into t1 (a) values (f2_hard(1));
ERROR 23000: Duplicate entry '1' for key 1
select * from t2;
a
1
rollback;
select * from t2;
a
1
commit;
update t1 set a= a + f2_hard(2);
ERROR 23000: Duplicate entry '2' for key 1
select * from t2;
a
1
2
rollback;
select * from t2;
a
1
2
commit;
delete from t1 where (a = f2_hard(3));
ERROR 23000: Duplicate entry '3' for key 1
select * from t2;
a
1
2
3
rollback;
select * from t2;
a
1
2
3
commit;
select f2_hard(4) from t1 ;
ERROR 23000: Duplicate entry '4' for key 1
select * from t2;
a
1
2
3
4
rollback;
select * from t2;
a
1
2
3
4
commit;
delete from t2;
commit;
insert into t1 (a) values (f2_soft(1, 2)), (f2_soft(1, 3));
ERROR 23000: Duplicate entry '1' for key 1
select * from t2;
a
1
rollback;
select * from t2;
a
1
commit;
delete from t2;
commit;
update t1 set a= a + f2_soft(2, a);
ERROR 23000: Duplicate entry '2' for key 1
select * from t2;
a
2
rollback;
select * from t2;
a
2
commit;
delete from t2;
commit;
select f2_soft(4, a) from t1 ;
ERROR 23000: Duplicate entry '4' for key 1
select * from t2;
a
4
rollback;
select * from t2;
a
4
commit;
delete from t2;
commit;
set autocommit=1;
drop table t1, t2;
drop function f2_hard;
drop function f2_soft;

--- New file ---
+++ mysql-test/r/engine_mix_innodb_ndb.result	06/09/08 18:51:11
set autocommit=1;
drop table if exists t1, t2;
drop function if exists f2_hard;
drop function if exists f2_soft;
create table t1 (a int) engine = ndbcluster;
create table t2 (a int unique) engine = InnoDB;
insert into t1 (a) values (1), (2);
create function f2_hard(x int) returns int
begin
insert into t2 (a) values (x);
insert into t2 (a) values (x);
return x;
end//
create function f2_soft(x int, y int) returns int
begin
insert into t2 (a) values (x);
return x;
end//
set autocommit=0;
insert into t1 (a) values (f2_hard(1));
ERROR 23000: Duplicate entry '1' for key 1
select * from t2;
a
rollback;
select * from t2;
a
commit;
update t1 set a= a + f2_hard(2);
ERROR 23000: Duplicate entry '2' for key 1
select * from t2;
a
rollback;
select * from t2;
a
commit;
delete from t1 where (a = f2_hard(3));
ERROR 23000: Duplicate entry '3' for key 1
select * from t2;
a
rollback;
select * from t2;
a
commit;
select f2_hard(4) from t1 ;
ERROR 23000: Duplicate entry '4' for key 1
select * from t2;
a
rollback;
select * from t2;
a
commit;
delete from t2;
commit;
insert into t1 (a) values (f2_soft(1, 2)), (f2_soft(1, 3));
ERROR 23000: Duplicate entry '1' for key 1
select * from t2;
a
rollback;
select * from t2;
a
commit;
delete from t2;
commit;
update t1 set a= a + f2_soft(2, a);
ERROR 23000: Duplicate entry '2' for key 1
select * from t2;
a
rollback;
select * from t2;
a
commit;
delete from t2;
commit;
select f2_soft(4, a) from t1 ;
ERROR 23000: Duplicate entry '4' for key 1
select * from t2;
a
rollback;
select * from t2;
a
commit;
delete from t2;
commit;
set autocommit=1;
drop table t1, t2;
drop function f2_hard;
drop function f2_soft;

--- New file ---
+++ mysql-test/r/engine_mix_myisam_bdb.result	06/09/08 18:51:11
set autocommit=1;
drop table if exists t1, t2;
drop function if exists f2_hard;
drop function if exists f2_soft;
create table t1 (a int) engine = MyISAM;
create table t2 (a int unique) engine = BerkeleyDB;
insert into t1 (a) values (1), (2);
create function f2_hard(x int) returns int
begin
insert into t2 (a) values (x);
insert into t2 (a) values (x);
return x;
end//
create function f2_soft(x int, y int) returns int
begin
insert into t2 (a) values (x);
return x;
end//
set autocommit=0;
insert into t1 (a) values (f2_hard(1));
ERROR 23000: Duplicate entry '1' for key 1
select * from t2;
a
rollback;
select * from t2;
a
commit;
update t1 set a= a + f2_hard(2);
ERROR 23000: Duplicate entry '2' for key 1
select * from t2;
a
rollback;
select * from t2;
a
commit;
delete from t1 where (a = f2_hard(3));
ERROR 23000: Duplicate entry '3' for key 1
select * from t2;
a
rollback;
select * from t2;
a
commit;
select f2_hard(4) from t1 ;
ERROR 23000: Duplicate entry '4' for key 1
select * from t2;
a
rollback;
select * from t2;
a
commit;
delete from t2;
commit;
insert into t1 (a) values (f2_soft(1, 2)), (f2_soft(1, 3));
ERROR 23000: Duplicate entry '1' for key 1
select * from t2;
a
rollback;
Warnings:
Warning	1196	Some non-transactional changed tables couldn't be rolled back
select * from t2;
a
commit;
delete from t2;
commit;
update t1 set a= a + f2_soft(2, a);
ERROR 23000: Duplicate entry '2' for key 1
select * from t2;
a
rollback;
Warnings:
Warning	1196	Some non-transactional changed tables couldn't be rolled back
select * from t2;
a
commit;
delete from t2;
commit;
select f2_soft(4, a) from t1 ;
ERROR 23000: Duplicate entry '4' for key 1
select * from t2;
a
rollback;
select * from t2;
a
commit;
delete from t2;
commit;
set autocommit=1;
drop table t1, t2;
drop function f2_hard;
drop function f2_soft;

--- New file ---
+++ mysql-test/r/engine_mix_myisam_innodb.result	06/09/08 18:51:11
set autocommit=1;
drop table if exists t1, t2;
drop function if exists f2_hard;
drop function if exists f2_soft;
create table t1 (a int) engine = MyISAM;
create table t2 (a int unique) engine = InnoDB;
insert into t1 (a) values (1), (2);
create function f2_hard(x int) returns int
begin
insert into t2 (a) values (x);
insert into t2 (a) values (x);
return x;
end//
create function f2_soft(x int, y int) returns int
begin
insert into t2 (a) values (x);
return x;
end//
set autocommit=0;
insert into t1 (a) values (f2_hard(1));
ERROR 23000: Duplicate entry '1' for key 1
select * from t2;
a
rollback;
select * from t2;
a
commit;
update t1 set a= a + f2_hard(2);
ERROR 23000: Duplicate entry '2' for key 1
select * from t2;
a
rollback;
select * from t2;
a
commit;
delete from t1 where (a = f2_hard(3));
ERROR 23000: Duplicate entry '3' for key 1
select * from t2;
a
rollback;
select * from t2;
a
commit;
select f2_hard(4) from t1 ;
ERROR 23000: Duplicate entry '4' for key 1
select * from t2;
a
rollback;
select * from t2;
a
commit;
delete from t2;
commit;
insert into t1 (a) values (f2_soft(1, 2)), (f2_soft(1, 3));
ERROR 23000: Duplicate entry '1' for key 1
select * from t2;
a
rollback;
Warnings:
Warning	1196	Some non-transactional changed tables couldn't be rolled back
select * from t2;
a
commit;
delete from t2;
commit;
update t1 set a= a + f2_soft(2, a);
ERROR 23000: Duplicate entry '2' for key 1
select * from t2;
a
rollback;
Warnings:
Warning	1196	Some non-transactional changed tables couldn't be rolled back
select * from t2;
a
commit;
delete from t2;
commit;
select f2_soft(4, a) from t1 ;
ERROR 23000: Duplicate entry '4' for key 1
select * from t2;
a
rollback;
select * from t2;
a
commit;
delete from t2;
commit;
set autocommit=1;
drop table t1, t2;
drop function f2_hard;
drop function f2_soft;

--- New file ---
+++ mysql-test/r/engine_mix_myisam_ndb.result	06/09/08 18:51:11
set autocommit=1;
drop table if exists t1, t2;
drop function if exists f2_hard;
drop function if exists f2_soft;
create table t1 (a int) engine = MyISAM;
create table t2 (a int unique) engine = ndbcluster;
insert into t1 (a) values (1), (2);
create function f2_hard(x int) returns int
begin
insert into t2 (a) values (x);
insert into t2 (a) values (x);
return x;
end//
create function f2_soft(x int, y int) returns int
begin
insert into t2 (a) values (x);
return x;
end//
set autocommit=0;
insert into t1 (a) values (f2_hard(1));
ERROR 23000: Duplicate entry '1' for key 1
select * from t2;
a
rollback;
select * from t2;
a
commit;
update t1 set a= a + f2_hard(2);
ERROR 23000: Duplicate entry '2' for key 1
select * from t2;
a
rollback;
select * from t2;
a
commit;
delete from t1 where (a = f2_hard(3));
ERROR 23000: Duplicate entry '3' for key 1
select * from t2;
a
rollback;
select * from t2;
a
commit;
select f2_hard(4) from t1 ;
ERROR 23000: Duplicate entry '4' for key 1
select * from t2;
a
rollback;
select * from t2;
a
commit;
delete from t2;
commit;
insert into t1 (a) values (f2_soft(1, 2)), (f2_soft(1, 3));
ERROR 23000: Duplicate entry '1' for key 1
select * from t2;
a
rollback;
Warnings:
Warning	1196	Some non-transactional changed tables couldn't be rolled back
select * from t2;
a
commit;
delete from t2;
commit;
update t1 set a= a + f2_soft(2, a);
ERROR 23000: Duplicate entry '2' for key 1
select * from t2;
a
rollback;
Warnings:
Warning	1196	Some non-transactional changed tables couldn't be rolled back
select * from t2;
a
commit;
delete from t2;
commit;
select f2_soft(4, a) from t1 ;
ERROR 23000: Duplicate entry '4' for key 1
select * from t2;
a
rollback;
select * from t2;
a
commit;
delete from t2;
commit;
set autocommit=1;
drop table t1, t2;
drop function f2_hard;
drop function f2_soft;

--- New file ---
+++ mysql-test/r/engine_mix_ndb_bdb.result	06/09/08 18:51:11
set autocommit=1;
drop table if exists t1, t2;
drop function if exists f2_hard;
drop function if exists f2_soft;
create table t1 (a int) engine = ndbcluster;
create table t2 (a int unique) engine = BerkeleyDB;
insert into t1 (a) values (1), (2);
create function f2_hard(x int) returns int
begin
insert into t2 (a) values (x);
insert into t2 (a) values (x);
return x;
end//
create function f2_soft(x int, y int) returns int
begin
insert into t2 (a) values (x);
return x;
end//
set autocommit=0;
insert into t1 (a) values (f2_hard(1));
ERROR 23000: Duplicate entry '1' for key 1
select * from t2;
a
rollback;
select * from t2;
a
commit;
update t1 set a= a + f2_hard(2);
ERROR 23000: Duplicate entry '2' for key 1
select * from t2;
a
rollback;
select * from t2;
a
commit;
delete from t1 where (a = f2_hard(3));
ERROR 23000: Duplicate entry '3' for key 1
select * from t2;
a
rollback;
select * from t2;
a
commit;
select f2_hard(4) from t1 ;
ERROR 23000: Duplicate entry '4' for key 1
select * from t2;
a
rollback;
select * from t2;
a
commit;
delete from t2;
commit;
insert into t1 (a) values (f2_soft(1, 2)), (f2_soft(1, 3));
ERROR 23000: Duplicate entry '1' for key 1
select * from t2;
a
rollback;
select * from t2;
a
commit;
delete from t2;
commit;
update t1 set a= a + f2_soft(2, a);
ERROR 23000: Duplicate entry '2' for key 1
select * from t2;
a
rollback;
select * from t2;
a
commit;
delete from t2;
commit;
select f2_soft(4, a) from t1 ;
ERROR 23000: Duplicate entry '4' for key 1
select * from t2;
a
rollback;
select * from t2;
a
commit;
delete from t2;
commit;
set autocommit=1;
drop table t1, t2;
drop function f2_hard;
drop function f2_soft;

--- New file ---
+++ mysql-test/r/engine_mix_ndb_innodb.result	06/09/08 18:51:11
set autocommit=1;
drop table if exists t1, t2;
drop function if exists f2_hard;
drop function if exists f2_soft;
create table t1 (a int) engine = ndbcluster;
create table t2 (a int unique) engine = InnoDB;
insert into t1 (a) values (1), (2);
create function f2_hard(x int) returns int
begin
insert into t2 (a) values (x);
insert into t2 (a) values (x);
return x;
end//
create function f2_soft(x int, y int) returns int
begin
insert into t2 (a) values (x);
return x;
end//
set autocommit=0;
insert into t1 (a) values (f2_hard(1));
ERROR 23000: Duplicate entry '1' for key 1
select * from t2;
a
rollback;
select * from t2;
a
commit;
update t1 set a= a + f2_hard(2);
ERROR 23000: Duplicate entry '2' for key 1
select * from t2;
a
rollback;
select * from t2;
a
commit;
delete from t1 where (a = f2_hard(3));
ERROR 23000: Duplicate entry '3' for key 1
select * from t2;
a
rollback;
select * from t2;
a
commit;
select f2_hard(4) from t1 ;
ERROR 23000: Duplicate entry '4' for key 1
select * from t2;
a
rollback;
select * from t2;
a
commit;
delete from t2;
commit;
insert into t1 (a) values (f2_soft(1, 2)), (f2_soft(1, 3));
ERROR 23000: Duplicate entry '1' for key 1
select * from t2;
a
rollback;
select * from t2;
a
commit;
delete from t2;
commit;
update t1 set a= a + f2_soft(2, a);
ERROR 23000: Duplicate entry '2' for key 1
select * from t2;
a
rollback;
select * from t2;
a
commit;
delete from t2;
commit;
select f2_soft(4, a) from t1 ;
ERROR 23000: Duplicate entry '4' for key 1
select * from t2;
a
rollback;
select * from t2;
a
commit;
delete from t2;
commit;
set autocommit=1;
drop table t1, t2;
drop function f2_hard;
drop function f2_soft;

--- New file ---
+++ mysql-test/r/engine_mix_ndb_myisam.result	06/09/08 18:51:11
set autocommit=1;
drop table if exists t1, t2;
drop function if exists f2_hard;
drop function if exists f2_soft;
create table t1 (a int) engine = ndbcluster;
create table t2 (a int unique) engine = MyISAM;
insert into t1 (a) values (1), (2);
create function f2_hard(x int) returns int
begin
insert into t2 (a) values (x);
insert into t2 (a) values (x);
return x;
end//
create function f2_soft(x int, y int) returns int
begin
insert into t2 (a) values (x);
return x;
end//
set autocommit=0;
insert into t1 (a) values (f2_hard(1));
ERROR 23000: Duplicate entry '1' for key 1
select * from t2;
a
1
rollback;
select * from t2;
a
1
commit;
update t1 set a= a + f2_hard(2);
ERROR 23000: Duplicate entry '2' for key 1
select * from t2;
a
1
2
rollback;
select * from t2;
a
1
2
commit;
delete from t1 where (a = f2_hard(3));
ERROR 23000: Duplicate entry '3' for key 1
select * from t2;
a
1
2
3
rollback;
select * from t2;
a
1
2
3
commit;
select f2_hard(4) from t1 ;
ERROR 23000: Duplicate entry '4' for key 1
select * from t2;
a
1
2
3
4
rollback;
select * from t2;
a
1
2
3
4
commit;
delete from t2;
commit;
insert into t1 (a) values (f2_soft(1, 2)), (f2_soft(1, 3));
ERROR 23000: Duplicate entry '1' for key 1
select * from t2;
a
1
rollback;
select * from t2;
a
1
commit;
delete from t2;
commit;
update t1 set a= a + f2_soft(2, a);
ERROR 23000: Duplicate entry '2' for key 1
select * from t2;
a
2
rollback;
select * from t2;
a
2
commit;
delete from t2;
commit;
select f2_soft(4, a) from t1 ;
ERROR 23000: Duplicate entry '4' for key 1
select * from t2;
a
4
rollback;
select * from t2;
a
4
commit;
delete from t2;
commit;
set autocommit=1;
drop table t1, t2;
drop function f2_hard;
drop function f2_soft;

--- New file ---
+++ mysql-test/t/autocommit_bdb.test	06/09/08 18:52:38
-- source include/have_bdb.inc

let $engine_type = BerkeleyDB;

-- source include/autocommit_common.inc


--- New file ---
+++ mysql-test/t/autocommit_innodb.test	06/09/08 18:52:38
-- source include/have_innodb.inc

let $engine_type = InnoDB;

-- source include/autocommit_common.inc


--- New file ---
+++ mysql-test/t/engine_mix_bdb_innodb.test	06/09/08 18:51:11
-- source include/have_bdb.inc
-- source include/have_innodb.inc

let $engine_type_1 = BerkeleyDB;
let $engine_type_2 = InnoDB;

-- source include/engine_mix_common.inc


--- New file ---
+++ mysql-test/t/engine_mix_bdb_myisam.test	06/09/08 18:51:11
-- source include/have_bdb.inc
## -- source include/have_myisam.inc

let $engine_type_1 = BerkeleyDB;
let $engine_type_2 = MyISAM;

-- source include/engine_mix_common.inc


--- New file ---
+++ mysql-test/t/engine_mix_bdb_ndb.test	06/09/08 18:51:11
-- source include/have_bdb.inc
-- source include/have_ndb.inc

let $engine_type_1 = BerkeleyDB;
let $engine_type_2 = ndbcluster;

-- source include/engine_mix_common.inc


--- New file ---
+++ mysql-test/t/engine_mix_innodb_bdb.test	06/09/08 18:51:11
-- source include/have_innodb.inc
-- source include/have_bdb.inc

let $engine_type_1 = InnoDB;
let $engine_type_2 = BerkeleyDB;

-- source include/engine_mix_common.inc


--- New file ---
+++ mysql-test/t/engine_mix_innodb_myisam.test	06/09/08 18:51:11
-- source include/have_innodb.inc
## -- source include/have_myisam.inc

let $engine_type_1 = InnoDB;
let $engine_type_2 = MyISAM;

-- source include/engine_mix_common.inc


--- New file ---
+++ mysql-test/t/engine_mix_innodb_ndb.test	06/09/08 18:51:11
-- source include/have_innodb.inc
-- source include/have_ndb.inc

let $engine_type_2 = InnoDB;
let $engine_type_1 = ndbcluster;

-- source include/engine_mix_common.inc


--- New file ---
+++ mysql-test/t/engine_mix_myisam_bdb.test	06/09/08 18:51:11
## -- source include/have_myisam.inc
-- source include/have_bdb.inc

let $engine_type_1 = MyISAM;
let $engine_type_2 = BerkeleyDB;

-- source include/engine_mix_common.inc


--- New file ---
+++ mysql-test/t/engine_mix_myisam_innodb.test	06/09/08 18:51:11
## -- source include/have_myisam.inc
-- source include/have_innodb.inc

let $engine_type_1 = MyISAM;
let $engine_type_2 = InnoDB;

-- source include/engine_mix_common.inc


--- New file ---
+++ mysql-test/t/engine_mix_myisam_ndb.test	06/09/08 18:51:11
## -- source include/have_myisam.inc
-- source include/have_ndb.inc

let $engine_type_1 = MyISAM;
let $engine_type_2 = ndbcluster;

-- source include/engine_mix_common.inc


--- New file ---
+++ mysql-test/t/engine_mix_ndb_bdb.test	06/09/08 18:51:11
-- source include/have_ndb.inc
-- source include/have_bdb.inc

let $engine_type_1 = ndbcluster;
let $engine_type_2 = BerkeleyDB;

-- source include/engine_mix_common.inc


--- New file ---
+++ mysql-test/t/engine_mix_ndb_innodb.test	06/09/08 18:51:11
-- source include/have_ndb.inc
-- source include/have_innodb.inc

let $engine_type_1 = ndbcluster;
let $engine_type_2 = InnoDB;

-- source include/engine_mix_common.inc


--- New file ---
+++ mysql-test/t/engine_mix_ndb_myisam.test	06/09/08 18:51:11
-- source include/have_ndb.inc
## -- source include/have_myisam.inc

let $engine_type_1 = ndbcluster;
let $engine_type_2 = MyISAM;

-- source include/engine_mix_common.inc



--- 1.11/sql/sql_do.cc	2006-09-08 20:14:22 -07:00
+++ 1.12/sql/sql_do.cc	2006-09-08 20:14:22 -07:00
@@ -21,6 +21,8 @@
 
 bool mysql_do(THD *thd, List<Item> &values)
 {
+  int tx_error;
+  bool result;
   List_iterator<Item> li(values);
   Item *value;
   DBUG_ENTER("mysql_do");
@@ -29,7 +31,18 @@ bool mysql_do(THD *thd, List<Item> &valu
   while ((value = li++))
     value->val_int();
   free_underlaid_joins(thd, &thd->lex->select_lex);
-  thd->clear_error(); // DO always is OK
-  send_ok(thd);
-  DBUG_RETURN(FALSE);
+
+  tx_error= thd->net.report_error;
+  tx_error= ha_autocommit_or_rollback(thd, tx_error);
+
+  if (tx_error)
+  {
+    result= TRUE;
+  }
+  else
+  {
+    send_ok(thd);
+    result= FALSE;
+  }
+  DBUG_RETURN(result);
 }

--- 1.43/sql/sp_rcontext.cc	2006-09-08 20:14:22 -07:00
+++ 1.44/sql/sp_rcontext.cc	2006-09-08 20:14:22 -07:00
@@ -607,3 +607,19 @@ bool Select_fetch_into_spvars::send_data
   }
   return FALSE;
 }
+
+
+void Select_fetch_into_spvars::send_error(uint errcode, const char *err)
+{
+  my_message(errcode, err, MYF(0));
+
+  (void) complete_statement(errcode);
+}
+
+
+bool Select_fetch_into_spvars::send_eof()
+{
+  int error= complete_statement(0);
+  return (error ? TRUE : FALSE);
+}
+

--- 1.33/sql/sp_rcontext.h	2006-09-08 20:14:22 -07:00
+++ 1.34/sql/sp_rcontext.h	2006-09-08 20:14:22 -07:00
@@ -278,7 +278,8 @@ public:
   uint get_field_count() { return field_count; }
   void set_spvar_list(List<struct sp_variable> *vars) { spvar_list= vars; }
 
-  virtual bool send_eof() { return FALSE; }
+  virtual void send_error(uint errcode, const char *err);
+  virtual bool send_eof();
   virtual bool send_data(List<Item> &items);
   virtual int prepare(List<Item> &list, SELECT_LEX_UNIT *u);
 };
Thread
bk commit into 5.0 tree (malff:1.2263) BUG#12713marc.alff9 Sep