List:Commits« Previous MessageNext Message »
From:Mats Kindahl Date:March 5 2009 9:57am
Subject:bzr commit into mysql-5.1 branch (mats:2810) Bug#43362
View as plain text  
#At file:///home/bzr/bugs/b42757-5.1-performance-version/ based on revid:mikael@stripped

 2810 Mats Kindahl	2009-03-05 [merge]
      Merging in fix for BUG#43362.

    added:
      mysql-test/include/implicit_commit_helper.inc
      mysql-test/r/implicit_commit.result
      mysql-test/t/implicit_commit.test
    modified:
      mysql-test/include/commit.inc
      mysql-test/r/commit_1innodb.result
      sql/events.cc
      sql/mysql_priv.h
      sql/sql_class.h
      sql/sql_delete.cc
      sql/sql_parse.cc
      sql/sql_table.cc
      tests/mysql_client_test.c
=== modified file 'mysql-test/include/commit.inc'
--- a/mysql-test/include/commit.inc	2009-01-23 12:22:05 +0000
+++ b/mysql-test/include/commit.inc	2009-03-05 09:54:47 +0000
@@ -729,15 +729,15 @@ call p_verify_status_increment(4, 4, 4, 
 alter table t3 add column (b int);
 call p_verify_status_increment(2, 0, 2, 0);
 alter table t3 rename t4;
-call p_verify_status_increment(1, 0, 1, 0);
+call p_verify_status_increment(2, 0, 2, 0);
 rename table t4 to t3;
-call p_verify_status_increment(1, 0, 1, 0);
+call p_verify_status_increment(0, 0, 0, 0);
 truncate table t3;
-call p_verify_status_increment(4, 4, 4, 4);
+call p_verify_status_increment(2, 2, 2, 2);
 create view v1 as select * from t2;
-call p_verify_status_increment(1, 0, 1, 0);
+call p_verify_status_increment(2, 0, 2, 0);
 check table t1;
-call p_verify_status_increment(3, 0, 3, 0);
+call p_verify_status_increment(2, 0, 2, 0);
 --echo # Sic: after this bug is fixed, CHECK leaves no pending transaction
 commit;
 call p_verify_status_increment(0, 0, 0, 0);

=== added file 'mysql-test/include/implicit_commit_helper.inc'
--- a/mysql-test/include/implicit_commit_helper.inc	1970-01-01 00:00:00 +0000
+++ b/mysql-test/include/implicit_commit_helper.inc	2009-03-05 09:54:47 +0000
@@ -0,0 +1,5 @@
+INSERT INTO db1.trans (a) VALUES (1);
+--disable_result_log
+eval $statement;
+--enable_result_log
+CALL db1.test_if_commit();

=== modified file 'mysql-test/r/commit_1innodb.result'
--- a/mysql-test/r/commit_1innodb.result	2009-02-10 14:44:58 +0000
+++ b/mysql-test/r/commit_1innodb.result	2009-03-05 09:54:47 +0000
@@ -845,25 +845,25 @@ call p_verify_status_increment(2, 0, 2, 
 SUCCESS
 
 alter table t3 rename t4;
-call p_verify_status_increment(1, 0, 1, 0);
+call p_verify_status_increment(2, 0, 2, 0);
 SUCCESS
 
 rename table t4 to t3;
-call p_verify_status_increment(1, 0, 1, 0);
+call p_verify_status_increment(0, 0, 0, 0);
 SUCCESS
 
 truncate table t3;
-call p_verify_status_increment(4, 4, 4, 4);
+call p_verify_status_increment(2, 2, 2, 2);
 ERROR
-Expected commit increment: 4 actual: 2
+Expected prepare increment: 2 actual: 0
 create view v1 as select * from t2;
-call p_verify_status_increment(1, 0, 1, 0);
+call p_verify_status_increment(2, 0, 2, 0);
 SUCCESS
 
 check table t1;
 Table	Op	Msg_type	Msg_text
 test.t1	check	status	OK
-call p_verify_status_increment(3, 0, 3, 0);
+call p_verify_status_increment(2, 0, 2, 0);
 SUCCESS
 
 # Sic: after this bug is fixed, CHECK leaves no pending transaction

=== added file 'mysql-test/r/implicit_commit.result'
--- a/mysql-test/r/implicit_commit.result	1970-01-01 00:00:00 +0000
+++ b/mysql-test/r/implicit_commit.result	2009-03-05 09:54:47 +0000
@@ -0,0 +1,1050 @@
+SET GLOBAL EVENT_SCHEDULER = OFF;
+SET BINLOG_FORMAT = STATEMENT;
+CREATE DATABASE db1;
+USE db1;
+CREATE TABLE t1 (a INT, KEY a(a)) ENGINE=INNODB;
+INSERT INTO t1 VALUES (1),(2),(3),(4),(5);
+CREATE TABLE t3 (a INT) ENGINE=MyISAM;
+INSERT INTO t3 SELECT * FROM t1;
+CREATE TABLE trans (a INT) ENGINE=INNODB;
+CREATE PROCEDURE test_if_commit()
+BEGIN
+ROLLBACK;
+SELECT IF (COUNT(*) > 0, "YES", "NO") AS "IMPLICIT COMMIT" FROM trans;
+DELETE FROM trans;
+COMMIT;
+END|
+SET AUTOCOMMIT = FALSE;
+#
+# SQLCOM_SELECT
+#
+INSERT INTO db1.trans (a) VALUES (1);
+select 1 as res from t1 where (1) in (select * from t1);
+CALL db1.test_if_commit();
+IMPLICIT COMMIT
+NO
+#
+# SQLCOM_CREATE_TABLE LIKE
+#
+INSERT INTO db1.trans (a) VALUES (1);
+create table t2 like t1;
+CALL db1.test_if_commit();
+IMPLICIT COMMIT
+YES
+#
+# SQLCOM_SHOW_CREATE
+#
+INSERT INTO db1.trans (a) VALUES (1);
+show create table t2;
+CALL db1.test_if_commit();
+IMPLICIT COMMIT
+NO
+#
+# SQLCOM_DROP_TABLE
+#
+INSERT INTO db1.trans (a) VALUES (1);
+drop table t2;
+CALL db1.test_if_commit();
+IMPLICIT COMMIT
+YES
+#
+# SQLCOM_CREATE_TABLE TEMPORARY
+#
+INSERT INTO db1.trans (a) VALUES (1);
+create temporary table t2 as select * from t1;
+CALL db1.test_if_commit();
+IMPLICIT COMMIT
+NO
+Warnings:
+Warning	1196	Some non-transactional changed tables couldn't be rolled back
+#
+# SQLCOM_DROP_TABLE TEMPORARY
+#
+INSERT INTO db1.trans (a) VALUES (1);
+drop temporary table t2;
+CALL db1.test_if_commit();
+IMPLICIT COMMIT
+NO
+#
+# SQLCOM_CREATE_TABLE
+#
+INSERT INTO db1.trans (a) VALUES (1);
+create table t2 as select * from t1;
+CALL db1.test_if_commit();
+IMPLICIT COMMIT
+YES
+#
+# SQLCOM_UPDATE
+#
+INSERT INTO db1.trans (a) VALUES (1);
+update t2 set a=a+1 where (1) in (select * from t1);
+CALL db1.test_if_commit();
+IMPLICIT COMMIT
+NO
+Warnings:
+Warning	1196	Some non-transactional changed tables couldn't be rolled back
+#
+# SQLCOM_INSERT
+#
+INSERT INTO db1.trans (a) VALUES (1);
+insert into t2 set a=((1) in (select * from t1));
+CALL db1.test_if_commit();
+IMPLICIT COMMIT
+NO
+Warnings:
+Warning	1196	Some non-transactional changed tables couldn't be rolled back
+#
+# SQLCOM_INSERT_SELECT
+#
+INSERT INTO db1.trans (a) VALUES (1);
+insert into t2 select * from t1;
+CALL db1.test_if_commit();
+IMPLICIT COMMIT
+NO
+Warnings:
+Warning	1196	Some non-transactional changed tables couldn't be rolled back
+#
+# SQLCOM_REPLACE
+#
+INSERT INTO db1.trans (a) VALUES (1);
+replace t2 set a=((1) in (select * from t1));
+CALL db1.test_if_commit();
+IMPLICIT COMMIT
+NO
+Warnings:
+Warning	1196	Some non-transactional changed tables couldn't be rolled back
+#
+# SQLCOM_REPLACE_SELECT
+#
+INSERT INTO db1.trans (a) VALUES (1);
+replace t2 select * from t1;
+CALL db1.test_if_commit();
+IMPLICIT COMMIT
+NO
+Warnings:
+Warning	1196	Some non-transactional changed tables couldn't be rolled back
+#
+# SQLCOM_DELETE
+#
+INSERT INTO db1.trans (a) VALUES (1);
+delete from t2 where (1) in (select * from t1);
+CALL db1.test_if_commit();
+IMPLICIT COMMIT
+NO
+Warnings:
+Warning	1196	Some non-transactional changed tables couldn't be rolled back
+#
+# SQLCOM_DELETE_MULTI
+#
+INSERT INTO db1.trans (a) VALUES (1);
+delete t2, t3 from t2, t3 where (1) in (select * from t1);
+CALL db1.test_if_commit();
+IMPLICIT COMMIT
+NO
+#
+# SQLCOM_UPDATE_MULTI
+#
+select * from t2;
+a
+INSERT INTO db1.trans (a) VALUES (1);
+update t2, t3 set t3.a=t2.a, t2.a=null where (1) in (select * from t1);
+CALL db1.test_if_commit();
+IMPLICIT COMMIT
+NO
+#
+# SQLCOM_LOAD
+#
+create table t4 (a varchar(100));
+INSERT INTO db1.trans (a) VALUES (1);
+load data infile '../../std_data/words.dat' into table t4;
+CALL db1.test_if_commit();
+IMPLICIT COMMIT
+NO
+Warnings:
+Warning	1196	Some non-transactional changed tables couldn't be rolled back
+drop table t4;
+#
+# SQLCOM_SHOW_DATABASES
+#
+INSERT INTO db1.trans (a) VALUES (1);
+show databases where (1) in (select * from t1);
+CALL db1.test_if_commit();
+IMPLICIT COMMIT
+NO
+#
+# SQLCOM_SHOW_TABLES
+#
+INSERT INTO db1.trans (a) VALUES (1);
+show tables where (1) in (select * from t1);
+CALL db1.test_if_commit();
+IMPLICIT COMMIT
+NO
+#
+# SQLCOM_SHOW_FIELDS
+#
+INSERT INTO db1.trans (a) VALUES (1);
+show fields from t1 where (1) in (select * from t1);
+CALL db1.test_if_commit();
+IMPLICIT COMMIT
+NO
+#
+# SQLCOM_SHOW_KEYS
+#
+INSERT INTO db1.trans (a) VALUES (1);
+show keys from t1 where (1) in (select * from t1);
+CALL db1.test_if_commit();
+IMPLICIT COMMIT
+NO
+#
+# SQLCOM_SHOW_VARIABLES
+#
+INSERT INTO db1.trans (a) VALUES (1);
+show variables where (1) in (select * from t1);
+CALL db1.test_if_commit();
+IMPLICIT COMMIT
+NO
+#
+# SQLCOM_SHOW_STATUS
+#
+#
+# SQLCOM_SHOW_ENGINE_MUTEX
+#
+INSERT INTO db1.trans (a) VALUES (1);
+show engine all mutex;
+CALL db1.test_if_commit();
+IMPLICIT COMMIT
+NO
+#
+# SQLCOM_SHOW_PROCESSLIST
+#
+INSERT INTO db1.trans (a) VALUES (1);
+show processlist;
+CALL db1.test_if_commit();
+IMPLICIT COMMIT
+NO
+#
+# SQLCOM_SHOW_ENGINE_LOGS
+#
+INSERT INTO db1.trans (a) VALUES (1);
+show engine all logs;
+CALL db1.test_if_commit();
+IMPLICIT COMMIT
+NO
+#
+# SQLCOM_SHOW_ENGINE_STATUS
+#
+INSERT INTO db1.trans (a) VALUES (1);
+show engine all status;
+CALL db1.test_if_commit();
+IMPLICIT COMMIT
+NO
+#
+# SQLCOM_SHOW_CHARSETS
+#
+INSERT INTO db1.trans (a) VALUES (1);
+show charset where (1) in (select * from t1);
+CALL db1.test_if_commit();
+IMPLICIT COMMIT
+NO
+#
+# SQLCOM_SHOW_COLLATIONS
+#
+INSERT INTO db1.trans (a) VALUES (1);
+show collation where (1) in (select * from t1);
+CALL db1.test_if_commit();
+IMPLICIT COMMIT
+NO
+#
+# SQLCOM_SHOW_TABLE_STATUS
+#
+INSERT INTO db1.trans (a) VALUES (1);
+show table status where (1) in (select * from t1);
+CALL db1.test_if_commit();
+IMPLICIT COMMIT
+NO
+#
+# SQLCOM_SHOW_TRIGGERS
+#
+INSERT INTO db1.trans (a) VALUES (1);
+show triggers where (1) in (select * from t1);
+CALL db1.test_if_commit();
+IMPLICIT COMMIT
+NO
+#
+# SQLCOM_SHOW_OPEN_TABLES
+#
+INSERT INTO db1.trans (a) VALUES (1);
+show open tables where (1) in (select * from t1);
+CALL db1.test_if_commit();
+IMPLICIT COMMIT
+NO
+#
+# SQLCOM_SHOW_STATUS_PROC
+#
+#
+# SQLCOM_SHOW_STATUS_FUNC
+#
+#
+# SQLCOM_SET_OPTION
+#
+INSERT INTO db1.trans (a) VALUES (1);
+set @a=((1) in (select * from t1));
+CALL db1.test_if_commit();
+IMPLICIT COMMIT
+NO
+#
+# SQLCOM_DO
+#
+INSERT INTO db1.trans (a) VALUES (1);
+do ((1) in (select * from t1));
+CALL db1.test_if_commit();
+IMPLICIT COMMIT
+NO
+#
+# SQLCOM_CALL
+#
+create procedure p1(a int) begin end;
+drop procedure p1;
+#
+# SQLCOM_CREATE_VIEW
+#
+INSERT INTO db1.trans (a) VALUES (1);
+create view v1 as select * from t1;
+CALL db1.test_if_commit();
+IMPLICIT COMMIT
+YES
+#
+# SQLCOM_ALTER_VIEW
+#
+INSERT INTO db1.trans (a) VALUES (1);
+alter view v1 as select 2;
+CALL db1.test_if_commit();
+IMPLICIT COMMIT
+YES
+#
+# SQLCOM_DROP_VIEW
+#
+INSERT INTO db1.trans (a) VALUES (1);
+drop view v1;
+CALL db1.test_if_commit();
+IMPLICIT COMMIT
+YES
+#
+# SQLCOM_CREATE_INDEX
+#
+INSERT INTO db1.trans (a) VALUES (1);
+create index idx1 on t1(a);
+CALL db1.test_if_commit();
+IMPLICIT COMMIT
+YES
+#
+# SQLCOM_DROP_INDEX
+#
+INSERT INTO db1.trans (a) VALUES (1);
+drop index idx1 on t1;
+CALL db1.test_if_commit();
+IMPLICIT COMMIT
+YES
+#
+# SQLCOM_ALTER_TABLE
+#
+INSERT INTO db1.trans (a) VALUES (1);
+alter table t1 add column b int;
+CALL db1.test_if_commit();
+IMPLICIT COMMIT
+YES
+INSERT INTO db1.trans (a) VALUES (1);
+alter table t1 change b c int;
+CALL db1.test_if_commit();
+IMPLICIT COMMIT
+YES
+INSERT INTO db1.trans (a) VALUES (1);
+alter table t1 drop column c;
+CALL db1.test_if_commit();
+IMPLICIT COMMIT
+YES
+#
+# SQLCOM_ALTER_TABLE TEMPORARY
+#
+create temporary table t4 (a int);
+INSERT INTO db1.trans (a) VALUES (1);
+alter table t1 add column b int;
+CALL db1.test_if_commit();
+IMPLICIT COMMIT
+YES
+INSERT INTO db1.trans (a) VALUES (1);
+alter table t1 change b c int;
+CALL db1.test_if_commit();
+IMPLICIT COMMIT
+YES
+INSERT INTO db1.trans (a) VALUES (1);
+alter table t1 drop column c;
+CALL db1.test_if_commit();
+IMPLICIT COMMIT
+YES
+drop table t4;
+#
+# SQLCOM_TRUNCATE
+#
+insert into t2 select * from t1;
+INSERT INTO db1.trans (a) VALUES (1);
+truncate table t2;
+CALL db1.test_if_commit();
+IMPLICIT COMMIT
+YES
+insert into t2 select * from t1;
+#
+# SQLCOM_TRUNCATE TEMPORARY
+#
+create temporary table t4 as select * from t1;
+INSERT INTO db1.trans (a) VALUES (1);
+truncate table t4;
+CALL db1.test_if_commit();
+IMPLICIT COMMIT
+YES
+drop temporary table t4;
+#
+# SQLCOM_SHOW_MASTER_STAT
+#
+INSERT INTO db1.trans (a) VALUES (1);
+show master status;
+CALL db1.test_if_commit();
+IMPLICIT COMMIT
+NO
+#
+# SQLCOM_SHOW_SLAVE_STAT
+#
+INSERT INTO db1.trans (a) VALUES (1);
+show slave status;
+CALL db1.test_if_commit();
+IMPLICIT COMMIT
+NO
+#
+# SQLCOM_GRANT
+#
+INSERT INTO db1.trans (a) VALUES (1);
+grant all on test.t1 to mysqltest_2@localhost with grant option;
+CALL db1.test_if_commit();
+IMPLICIT COMMIT
+YES
+#
+# SQLCOM_REVOKE
+#
+INSERT INTO db1.trans (a) VALUES (1);
+revoke select on test.t1 from mysqltest_2@localhost;
+CALL db1.test_if_commit();
+IMPLICIT COMMIT
+YES
+#
+# SQLCOM_REVOKE_ALL
+#
+INSERT INTO db1.trans (a) VALUES (1);
+revoke all on test.t1 from mysqltest_2@localhost;
+CALL db1.test_if_commit();
+IMPLICIT COMMIT
+YES
+#
+# SQLCOM_SHOW_GRANTS
+#
+INSERT INTO db1.trans (a) VALUES (1);
+show grants;
+CALL db1.test_if_commit();
+IMPLICIT COMMIT
+NO
+INSERT INTO db1.trans (a) VALUES (1);
+show grants for current_user();
+CALL db1.test_if_commit();
+IMPLICIT COMMIT
+NO
+#
+# SQLCOM_LOCK_TABLES
+#
+INSERT INTO db1.trans (a) VALUES (1);
+lock tables t1 write, trans write;
+CALL db1.test_if_commit();
+IMPLICIT COMMIT
+YES
+#
+# SQLCOM_UNLOCK_TABLES
+#
+INSERT INTO db1.trans (a) VALUES (1);
+unlock tables;
+CALL db1.test_if_commit();
+IMPLICIT COMMIT
+YES
+#
+# SQLCOM_CREATE_DB
+#
+INSERT INTO db1.trans (a) VALUES (1);
+create database db2;
+CALL db1.test_if_commit();
+IMPLICIT COMMIT
+YES
+#
+# SQLCOM_CHANGE_DB
+#
+create table db2.t1 (a int);
+insert into db2.t1 values (1);
+commit;
+INSERT INTO db1.trans (a) VALUES (1);
+use db2;
+CALL db1.test_if_commit();
+IMPLICIT COMMIT
+NO
+#
+# SQLCOM_SHOW_CREATE_DB
+#
+INSERT INTO db1.trans (a) VALUES (1);
+show create database db2;
+CALL db1.test_if_commit();
+IMPLICIT COMMIT
+NO
+#
+# SQLCOM_ALTER_DB
+#
+#
+# SQLCOM_ALTER_DB_UPGRADE
+#
+#
+# SQLCOM_DROP_DB
+#
+use db1;
+INSERT INTO db1.trans (a) VALUES (1);
+drop database db2;
+CALL db1.test_if_commit();
+IMPLICIT COMMIT
+YES
+#
+# SQLCOM_REPAIR
+#
+INSERT INTO db1.trans (a) VALUES (1);
+repair table t2;
+CALL db1.test_if_commit();
+IMPLICIT COMMIT
+YES
+INSERT INTO db1.trans (a) VALUES (1);
+repair table t2 use_frm;
+CALL db1.test_if_commit();
+IMPLICIT COMMIT
+YES
+#
+# SQLCOM_OPTIMIZE
+#
+INSERT INTO db1.trans (a) VALUES (1);
+optimize table t1;
+CALL db1.test_if_commit();
+IMPLICIT COMMIT
+YES
+#
+# SQLCOM_CHECK
+#
+INSERT INTO db1.trans (a) VALUES (1);
+check table t1;
+CALL db1.test_if_commit();
+IMPLICIT COMMIT
+YES
+INSERT INTO db1.trans (a) VALUES (1);
+check table t1 extended;
+CALL db1.test_if_commit();
+IMPLICIT COMMIT
+YES
+#
+# SQLCOM_ASSIGN_TO_KEYCACHE
+#
+set global keycache.key_buffer_size=128*1024;
+INSERT INTO db1.trans (a) VALUES (1);
+cache index t3 in keycache;
+CALL db1.test_if_commit();
+IMPLICIT COMMIT
+YES
+set global keycache.key_buffer_size=0;
+#
+# SQLCOM_PRELOAD_KEYS
+#
+INSERT INTO db1.trans (a) VALUES (1);
+load index into cache t3;
+CALL db1.test_if_commit();
+IMPLICIT COMMIT
+YES
+#
+# SQLCOM_FLUSH
+#
+INSERT INTO db1.trans (a) VALUES (1);
+flush local privileges;
+CALL db1.test_if_commit();
+IMPLICIT COMMIT
+YES
+INSERT INTO db1.trans (a) VALUES (1);
+flush privileges;
+CALL db1.test_if_commit();
+IMPLICIT COMMIT
+YES
+#
+# SQLCOM_KILL
+#
+#
+# SQLCOM_ANALYZE
+#
+INSERT INTO db1.trans (a) VALUES (1);
+analyze table t1;
+CALL db1.test_if_commit();
+IMPLICIT COMMIT
+YES
+#
+# SQLCOM_ROLLBACK
+#
+INSERT INTO db1.trans (a) VALUES (1);
+rollback;
+CALL db1.test_if_commit();
+IMPLICIT COMMIT
+NO
+#
+# SQLCOM_ROLLBACK_TO_SAVEPOINT
+#
+#
+# SQLCOM_COMMIT
+#
+INSERT INTO db1.trans (a) VALUES (1);
+commit;
+CALL db1.test_if_commit();
+IMPLICIT COMMIT
+YES
+#
+# SQLCOM_SAVEPOINT
+#
+INSERT INTO db1.trans (a) VALUES (1);
+savepoint sp1;
+CALL db1.test_if_commit();
+IMPLICIT COMMIT
+NO
+#
+# SQLCOM_RELEASE_SAVEPOINT
+#
+#
+# SQLCOM_SLAVE_START
+#
+#
+# SQLCOM_SLAVE_STOP
+#
+#
+# SQLCOM_BEGIN
+#
+INSERT INTO db1.trans (a) VALUES (1);
+begin;
+CALL db1.test_if_commit();
+IMPLICIT COMMIT
+YES
+#
+# SQLCOM_CHANGE_MASTER
+#
+#
+# SQLCOM_RENAME_TABLE
+#
+INSERT INTO db1.trans (a) VALUES (1);
+rename table t3 to t4;
+CALL db1.test_if_commit();
+IMPLICIT COMMIT
+YES
+INSERT INTO db1.trans (a) VALUES (1);
+rename table t4 to t3;
+CALL db1.test_if_commit();
+IMPLICIT COMMIT
+YES
+#
+# SQLCOM_RESET
+#
+#
+# SQLCOM_PURGE
+#
+#
+# SQLCOM_PURGE_BEFORE
+#
+#
+# SQLCOM_SHOW_BINLOGS
+#
+#
+# SQLCOM_HA_OPEN
+#
+INSERT INTO db1.trans (a) VALUES (1);
+handler t1 open as ha1;
+CALL db1.test_if_commit();
+IMPLICIT COMMIT
+NO
+#
+# SQLCOM_HA_READ
+#
+INSERT INTO db1.trans (a) VALUES (1);
+handler ha1 read a first;
+CALL db1.test_if_commit();
+IMPLICIT COMMIT
+NO
+#
+# SQLCOM_HA_CLOSE
+#
+INSERT INTO db1.trans (a) VALUES (1);
+handler ha1 close;
+CALL db1.test_if_commit();
+IMPLICIT COMMIT
+NO
+#
+# SQLCOM_SHOW_SLAVE_HOSTS
+#
+INSERT INTO db1.trans (a) VALUES (1);
+show slave hosts;
+CALL db1.test_if_commit();
+IMPLICIT COMMIT
+NO
+#
+# SQLCOM_SHOW_BINLOG_EVENTS
+#
+INSERT INTO db1.trans (a) VALUES (1);
+show binlog events;
+CALL db1.test_if_commit();
+IMPLICIT COMMIT
+NO
+#
+# SQLCOM_SHOW_NEW_MASTER
+#
+#
+# SQLCOM_SHOW_WARNS
+#
+INSERT INTO db1.trans (a) VALUES (1);
+show warnings;
+CALL db1.test_if_commit();
+IMPLICIT COMMIT
+NO
+#
+# SQLCOM_EMPTY_QUERY
+#
+#
+# SQLCOM_SHOW_ERRORS
+#
+INSERT INTO db1.trans (a) VALUES (1);
+show errors;
+CALL db1.test_if_commit();
+IMPLICIT COMMIT
+NO
+#
+# SQLCOM_SHOW_STORAGE_ENGINES
+#
+INSERT INTO db1.trans (a) VALUES (1);
+show engines;
+CALL db1.test_if_commit();
+IMPLICIT COMMIT
+NO
+#
+# SQLCOM_SHOW_PRIVILEGES
+#
+INSERT INTO db1.trans (a) VALUES (1);
+show privileges;
+CALL db1.test_if_commit();
+IMPLICIT COMMIT
+NO
+#
+# SQLCOM_HELP
+#
+INSERT INTO db1.trans (a) VALUES (1);
+help 'foo';
+CALL db1.test_if_commit();
+IMPLICIT COMMIT
+NO
+#
+# SQLCOM_CREATE_USER
+#
+INSERT INTO db1.trans (a) VALUES (1);
+create user trxusr1;
+CALL db1.test_if_commit();
+IMPLICIT COMMIT
+YES
+#
+# SQLCOM_RENAME_USER
+#
+INSERT INTO db1.trans (a) VALUES (1);
+rename user 'trxusr1' to 'trxusr2';
+CALL db1.test_if_commit();
+IMPLICIT COMMIT
+YES
+#
+# SQLCOM_DROP_USER
+#
+INSERT INTO db1.trans (a) VALUES (1);
+drop user trxusr2;
+CALL db1.test_if_commit();
+IMPLICIT COMMIT
+YES
+#
+# SQLCOM_CHECKSUM
+#
+INSERT INTO db1.trans (a) VALUES (1);
+checksum table t1;
+CALL db1.test_if_commit();
+IMPLICIT COMMIT
+NO
+#
+# SQLCOM_CREATE_PROCEDURE
+#
+INSERT INTO db1.trans (a) VALUES (1);
+create procedure p1(a int) begin end;
+CALL db1.test_if_commit();
+IMPLICIT COMMIT
+YES
+#
+# SQLCOM_ALTER_PROCEDURE
+#
+INSERT INTO db1.trans (a) VALUES (1);
+alter procedure p1 comment 'foobar';
+CALL db1.test_if_commit();
+IMPLICIT COMMIT
+YES
+#
+# SQLCOM_SHOW_CREATE_PROC
+#
+INSERT INTO db1.trans (a) VALUES (1);
+show create procedure p1;
+CALL db1.test_if_commit();
+IMPLICIT COMMIT
+NO
+#
+# SQLCOM_SHOW_STATUS_PROC
+#
+INSERT INTO db1.trans (a) VALUES (1);
+show procedure status;
+CALL db1.test_if_commit();
+IMPLICIT COMMIT
+NO
+#
+# SQLCOM_SHOW_PROC_CODE
+#
+INSERT INTO db1.trans (a) VALUES (1);
+show procedure code p1;
+CALL db1.test_if_commit();
+IMPLICIT COMMIT
+NO
+#
+# SQLCOM_DROP_PROCEDURE
+#
+INSERT INTO db1.trans (a) VALUES (1);
+drop procedure p1;
+CALL db1.test_if_commit();
+IMPLICIT COMMIT
+YES
+#
+# SQLCOM_CREATE_FUNCTION
+#
+#
+# SQLCOM_DROP_FUNCTION
+#
+#
+# SQLCOM_CREATE_SPFUNCTION
+#
+INSERT INTO db1.trans (a) VALUES (1);
+create function f1() returns int return 69;
+CALL db1.test_if_commit();
+IMPLICIT COMMIT
+YES
+#
+# SQLCOM_ALTER_FUNCTION
+#
+INSERT INTO db1.trans (a) VALUES (1);
+alter function f1 comment 'comment';
+CALL db1.test_if_commit();
+IMPLICIT COMMIT
+YES
+#
+# SQLCOM_SHOW_CREATE_FUNC
+#
+INSERT INTO db1.trans (a) VALUES (1);
+show create function f1;
+CALL db1.test_if_commit();
+IMPLICIT COMMIT
+NO
+#
+# SQLCOM_SHOW_STATUS_FUNC
+#
+INSERT INTO db1.trans (a) VALUES (1);
+show function status like '%f%';
+CALL db1.test_if_commit();
+IMPLICIT COMMIT
+NO
+#
+# SQLCOM_SHOW_FUNC_CODE
+#
+INSERT INTO db1.trans (a) VALUES (1);
+show function code f1;
+CALL db1.test_if_commit();
+IMPLICIT COMMIT
+NO
+#
+# SQLCOM_PREPARE
+#
+INSERT INTO db1.trans (a) VALUES (1);
+prepare stmt1 from "insert into t1 values (5)";
+CALL db1.test_if_commit();
+IMPLICIT COMMIT
+NO
+#
+# SQLCOM_EXECUTE
+#
+INSERT INTO db1.trans (a) VALUES (1);
+execute stmt1;
+CALL db1.test_if_commit();
+IMPLICIT COMMIT
+NO
+#
+# SQLCOM_DEALLOCATE_PREPARE
+#
+INSERT INTO db1.trans (a) VALUES (1);
+deallocate prepare stmt1;
+CALL db1.test_if_commit();
+IMPLICIT COMMIT
+NO
+#
+# SQLCOM_CREATE_TRIGGER
+#
+INSERT INTO db1.trans (a) VALUES (1);
+create trigger trg1 before insert on t1 for each row set @a:=1;
+CALL db1.test_if_commit();
+IMPLICIT COMMIT
+YES
+#
+# SQLCOM_SHOW_CREATE_TRIGGER
+#
+INSERT INTO db1.trans (a) VALUES (1);
+show create trigger trg1;
+CALL db1.test_if_commit();
+IMPLICIT COMMIT
+NO
+#
+# SQLCOM_DROP_TRIGGER
+#
+INSERT INTO db1.trans (a) VALUES (1);
+drop trigger trg1;
+CALL db1.test_if_commit();
+IMPLICIT COMMIT
+YES
+#
+# SQLCOM_XA_START
+#
+#
+# SQLCOM_XA_END
+#
+#
+# SQLCOM_XA_PREPARE
+#
+#
+# SQLCOM_XA_COMMIT
+#
+#
+# SQLCOM_XA_ROLLBACK
+#
+#
+# SQLCOM_XA_RECOVER
+#
+#
+# SQLCOM_ALTER_TABLESPACE
+#
+#
+# SQLCOM_INSTALL_PLUGIN
+#
+#
+# SQLCOM_SHOW_PLUGINS
+#
+#
+# SQLCOM_UNINSTALL_PLUGIN
+#
+#
+# SQLCOM_SHOW_AUTHORS
+#
+INSERT INTO db1.trans (a) VALUES (1);
+show authors;
+CALL db1.test_if_commit();
+IMPLICIT COMMIT
+NO
+#
+# SQLCOM_BINLOG_BASE64_EVENT
+#
+#
+# SQLCOM_SHOW_CONTRIBUTORS
+#
+INSERT INTO db1.trans (a) VALUES (1);
+show contributors;
+CALL db1.test_if_commit();
+IMPLICIT COMMIT
+NO
+#
+# SQLCOM_CREATE_SERVER
+#
+#
+# SQLCOM_ALTER_SERVER
+#
+#
+# SQLCOM_DROP_SERVER
+#
+#
+# SQLCOM_CREATE_EVENT
+#
+INSERT INTO db1.trans (a) VALUES (1);
+create event ev1 on schedule every 1 second do insert into t1 values (6);
+CALL db1.test_if_commit();
+IMPLICIT COMMIT
+YES
+#
+# SQLCOM_ALTER_EVENT
+#
+INSERT INTO db1.trans (a) VALUES (1);
+alter event ev1 rename to ev2 disable;
+CALL db1.test_if_commit();
+IMPLICIT COMMIT
+YES
+#
+# SQLCOM_SHOW_CREATE_EVENT
+#
+INSERT INTO db1.trans (a) VALUES (1);
+show create event ev2;
+CALL db1.test_if_commit();
+IMPLICIT COMMIT
+NO
+#
+# SQLCOM_SHOW_EVENTS
+#
+INSERT INTO db1.trans (a) VALUES (1);
+show events;
+CALL db1.test_if_commit();
+IMPLICIT COMMIT
+NO
+#
+# SQLCOM_DROP_EVENT
+#
+INSERT INTO db1.trans (a) VALUES (1);
+drop event ev2;
+CALL db1.test_if_commit();
+IMPLICIT COMMIT
+YES
+#
+# SQLCOM_SHOW_ARCHIVE
+#
+#
+# SQLCOM_BACKUP_TEST
+#
+#
+# SQLCOM_SHOW_PROFILE
+#
+INSERT INTO db1.trans (a) VALUES (1);
+show profile memory;
+CALL db1.test_if_commit();
+IMPLICIT COMMIT
+NO
+#
+# SQLCOM_SHOW_PROFILES
+#
+INSERT INTO db1.trans (a) VALUES (1);
+show profiles;
+CALL db1.test_if_commit();
+IMPLICIT COMMIT
+NO
+DROP TABLE t1;
+DROP TABLE t2;
+DROP TABLE t3;
+USE test;
+DROP DATABASE db1;
+End of tests

=== added file 'mysql-test/t/implicit_commit.test'
--- a/mysql-test/t/implicit_commit.test	1970-01-01 00:00:00 +0000
+++ b/mysql-test/t/implicit_commit.test	2009-03-05 09:54:47 +0000
@@ -0,0 +1,1142 @@
+source include/have_innodb.inc;
+source include/have_log_bin.inc;
+
+SET GLOBAL EVENT_SCHEDULER = OFF;
+SET BINLOG_FORMAT = STATEMENT;
+
+LET $OLD_DB= `SELECT DATABASE()`;
+
+CREATE DATABASE db1;
+USE db1;
+CREATE TABLE t1 (a INT, KEY a(a)) ENGINE=INNODB;
+INSERT INTO t1 VALUES (1),(2),(3),(4),(5);
+CREATE TABLE t3 (a INT) ENGINE=MyISAM;
+INSERT INTO t3 SELECT * FROM t1;
+CREATE TABLE trans (a INT) ENGINE=INNODB;
+
+DELIMITER |;
+
+CREATE PROCEDURE test_if_commit()
+BEGIN
+  ROLLBACK;
+  SELECT IF (COUNT(*) > 0, "YES", "NO") AS "IMPLICIT COMMIT" FROM trans;
+  DELETE FROM trans;
+  COMMIT;
+END|
+
+DELIMITER ;|
+
+SET AUTOCOMMIT = FALSE;
+
+--echo #
+--echo # SQLCOM_SELECT
+--echo #
+
+let $statement=
+  select 1 as res from t1 where (1) in (select * from t1);
+source include/implicit_commit_helper.inc;
+
+--echo #
+--echo # SQLCOM_CREATE_TABLE LIKE
+--echo #
+
+let $statement=
+  create table t2 like t1;
+source include/implicit_commit_helper.inc;
+
+--echo #
+--echo # SQLCOM_SHOW_CREATE
+--echo #
+
+let $statement=
+  show create table t2;
+source include/implicit_commit_helper.inc;
+
+--echo #
+--echo # SQLCOM_DROP_TABLE
+--echo #
+
+let $statement=
+  drop table t2;
+source include/implicit_commit_helper.inc;
+
+--echo #
+--echo # SQLCOM_CREATE_TABLE TEMPORARY
+--echo #
+
+let $statement=
+  create temporary table t2 as select * from t1;
+source include/implicit_commit_helper.inc;
+
+--echo #
+--echo # SQLCOM_DROP_TABLE TEMPORARY
+--echo #
+
+let $statement=
+  drop temporary table t2;
+source include/implicit_commit_helper.inc;
+
+--echo #
+--echo # SQLCOM_CREATE_TABLE
+--echo #
+
+let $statement=
+  create table t2 as select * from t1;
+source include/implicit_commit_helper.inc;
+
+--echo #
+--echo # SQLCOM_UPDATE
+--echo #
+
+let $statement=
+  update t2 set a=a+1 where (1) in (select * from t1);
+source include/implicit_commit_helper.inc;
+
+--echo #
+--echo # SQLCOM_INSERT
+--echo #
+
+let $statement=
+  insert into t2 set a=((1) in (select * from t1));
+source include/implicit_commit_helper.inc;
+
+--echo #
+--echo # SQLCOM_INSERT_SELECT
+--echo #
+
+let $statement=
+  insert into t2 select * from t1;
+source include/implicit_commit_helper.inc;
+
+--echo #
+--echo # SQLCOM_REPLACE
+--echo #
+
+let $statement=
+  replace t2 set a=((1) in (select * from t1));
+source include/implicit_commit_helper.inc;
+
+--echo #
+--echo # SQLCOM_REPLACE_SELECT
+--echo #
+
+let $statement=
+  replace t2 select * from t1;
+source include/implicit_commit_helper.inc;
+
+--echo #
+--echo # SQLCOM_DELETE
+--echo #
+
+let $statement=
+  delete from t2 where (1) in (select * from t1);
+source include/implicit_commit_helper.inc;
+
+--echo #
+--echo # SQLCOM_DELETE_MULTI
+--echo #
+
+let $statement=
+  delete t2, t3 from t2, t3 where (1) in (select * from t1);
+source include/implicit_commit_helper.inc;
+
+--echo #
+--echo # SQLCOM_UPDATE_MULTI
+--echo #
+
+select * from t2;
+let $statement=
+  update t2, t3 set t3.a=t2.a, t2.a=null where (1) in (select * from t1);
+source include/implicit_commit_helper.inc;
+
+--echo #
+--echo # SQLCOM_LOAD
+--echo #
+
+create table t4 (a varchar(100));
+
+let $statement=
+  load data infile '../../std_data/words.dat' into table t4;
+source include/implicit_commit_helper.inc;
+
+drop table t4;
+
+--echo #
+--echo # SQLCOM_SHOW_DATABASES
+--echo #
+
+let $statement=
+  show databases where (1) in (select * from t1);
+source include/implicit_commit_helper.inc;
+
+--echo #
+--echo # SQLCOM_SHOW_TABLES
+--echo #
+
+let $statement=
+  show tables where (1) in (select * from t1);
+source include/implicit_commit_helper.inc;
+
+--echo #
+--echo # SQLCOM_SHOW_FIELDS
+--echo #
+
+let $statement=
+  show fields from t1 where (1) in (select * from t1);
+source include/implicit_commit_helper.inc;
+
+--echo #
+--echo # SQLCOM_SHOW_KEYS
+--echo #
+
+let $statement=
+  show keys from t1 where (1) in (select * from t1);
+source include/implicit_commit_helper.inc;
+
+--echo #
+--echo # SQLCOM_SHOW_VARIABLES
+--echo #
+
+let $statement=
+  show variables where (1) in (select * from t1);
+source include/implicit_commit_helper.inc;
+
+--echo #
+--echo # SQLCOM_SHOW_STATUS
+--echo #
+
+#
+# Bug#37908
+#
+#let $statement=
+#  show status where (1) in (select * from t1);
+#source include/implicit_commit_helper.inc;
+#
+
+--echo #
+--echo # SQLCOM_SHOW_ENGINE_MUTEX
+--echo #
+
+let $statement=
+  show engine all mutex;
+source include/implicit_commit_helper.inc;
+
+--echo #
+--echo # SQLCOM_SHOW_PROCESSLIST
+--echo #
+
+let $statement=
+  show processlist;
+source include/implicit_commit_helper.inc;
+
+--echo #
+--echo # SQLCOM_SHOW_ENGINE_LOGS
+--echo #
+
+let $statement=
+  show engine all logs;
+source include/implicit_commit_helper.inc;
+
+--echo #
+--echo # SQLCOM_SHOW_ENGINE_STATUS
+--echo #
+
+let $statement=
+  show engine all status;
+source include/implicit_commit_helper.inc;
+
+--echo #
+--echo # SQLCOM_SHOW_CHARSETS
+--echo #
+
+let $statement=
+  show charset where (1) in (select * from t1);
+source include/implicit_commit_helper.inc;
+
+--echo #
+--echo # SQLCOM_SHOW_COLLATIONS
+--echo #
+
+let $statement=
+  show collation where (1) in (select * from t1);
+source include/implicit_commit_helper.inc;
+
+--echo #
+--echo # SQLCOM_SHOW_TABLE_STATUS
+--echo #
+
+let $statement=
+  show table status where (1) in (select * from t1);
+source include/implicit_commit_helper.inc;
+
+--echo #
+--echo # SQLCOM_SHOW_TRIGGERS
+--echo #
+
+let $statement=
+  show triggers where (1) in (select * from t1);
+source include/implicit_commit_helper.inc;
+
+--echo #
+--echo # SQLCOM_SHOW_OPEN_TABLES
+--echo #
+
+let $statement=
+  show open tables where (1) in (select * from t1);
+source include/implicit_commit_helper.inc;
+
+--echo #
+--echo # SQLCOM_SHOW_STATUS_PROC
+--echo #
+
+#
+# Bug#37908
+#
+#let $statement=
+#  show procedure status where (1) in (select * from t1);
+#source include/implicit_commit_helper.inc;
+#
+
+--echo #
+--echo # SQLCOM_SHOW_STATUS_FUNC
+--echo #
+
+#
+# Bug#37908
+#
+#let $statement=
+#  show function status where (1) in (select * from t1);
+#source include/implicit_commit_helper.inc;
+#
+
+--echo #
+--echo # SQLCOM_SET_OPTION
+--echo #
+
+let $statement=
+  set @a=((1) in (select * from t1));
+source include/implicit_commit_helper.inc;
+
+--echo #
+--echo # SQLCOM_DO
+--echo #
+
+let $statement=
+  do ((1) in (select * from t1));
+source include/implicit_commit_helper.inc;
+
+--echo #
+--echo # SQLCOM_CALL
+--echo #
+
+create procedure p1(a int) begin end;
+
+#
+# Bug#37949
+#
+#let $statement=
+#  call p1((1) in (select * from t1));
+#source include/implicit_commit_helper.inc;
+#
+
+drop procedure p1;
+
+--echo #
+--echo # SQLCOM_CREATE_VIEW
+--echo #
+
+let $statement=
+  create view v1 as select * from t1;
+source include/implicit_commit_helper.inc;
+
+--echo #
+--echo # SQLCOM_ALTER_VIEW
+--echo #
+
+let $statement=
+  alter view v1 as select 2;
+source include/implicit_commit_helper.inc;
+
+--echo #
+--echo # SQLCOM_DROP_VIEW
+--echo #
+
+let $statement=
+  drop view v1;
+source include/implicit_commit_helper.inc;
+
+--echo #
+--echo # SQLCOM_CREATE_INDEX
+--echo #
+
+let $statement=
+  create index idx1 on t1(a);
+source include/implicit_commit_helper.inc;
+
+--echo #
+--echo # SQLCOM_DROP_INDEX
+--echo #
+
+let $statement=
+  drop index idx1 on t1;
+source include/implicit_commit_helper.inc;
+
+--echo #
+--echo # SQLCOM_ALTER_TABLE
+--echo #
+
+let $statement=
+  alter table t1 add column b int;
+source include/implicit_commit_helper.inc;
+
+let $statement=
+  alter table t1 change b c int;
+source include/implicit_commit_helper.inc;
+
+let $statement=
+  alter table t1 drop column c;
+source include/implicit_commit_helper.inc;
+
+--echo #
+--echo # SQLCOM_ALTER_TABLE TEMPORARY
+--echo #
+
+create temporary table t4 (a int);
+
+let $statement=
+  alter table t1 add column b int;
+source include/implicit_commit_helper.inc;
+
+let $statement=
+  alter table t1 change b c int;
+source include/implicit_commit_helper.inc;
+
+let $statement=
+  alter table t1 drop column c;
+source include/implicit_commit_helper.inc;
+
+drop table t4;
+
+--echo #
+--echo # SQLCOM_TRUNCATE
+--echo #
+
+insert into t2 select * from t1;
+let $statement=
+  truncate table t2;
+source include/implicit_commit_helper.inc;
+insert into t2 select * from t1;
+
+--echo #
+--echo # SQLCOM_TRUNCATE TEMPORARY
+--echo #
+
+create temporary table t4 as select * from t1;
+let $statement=
+  truncate table t4;
+source include/implicit_commit_helper.inc;
+drop temporary table t4;
+
+--echo #
+--echo # SQLCOM_SHOW_MASTER_STAT
+--echo #
+
+let $statement=
+  show master status;
+source include/implicit_commit_helper.inc;
+
+--echo #
+--echo # SQLCOM_SHOW_SLAVE_STAT
+--echo #
+
+let $statement=
+  show slave status;
+source include/implicit_commit_helper.inc;
+
+--echo #
+--echo # SQLCOM_GRANT
+--echo #
+
+let $statement=
+  grant all on test.t1 to mysqltest_2@localhost with grant option;
+source include/implicit_commit_helper.inc;
+
+--echo #
+--echo # SQLCOM_REVOKE
+--echo #
+let $statement=
+  revoke select on test.t1 from mysqltest_2@localhost;
+source include/implicit_commit_helper.inc;
+
+--echo #
+--echo # SQLCOM_REVOKE_ALL
+--echo #
+
+let $statement=
+  revoke all on test.t1 from mysqltest_2@localhost;
+source include/implicit_commit_helper.inc;
+
+--echo #
+--echo # SQLCOM_SHOW_GRANTS
+--echo #
+
+let $statement=
+  show grants;
+source include/implicit_commit_helper.inc;
+
+let $statement=
+  show grants for current_user();
+source include/implicit_commit_helper.inc;
+
+--echo #
+--echo # SQLCOM_LOCK_TABLES
+--echo #
+
+let $statement=
+  lock tables t1 write, trans write;
+source include/implicit_commit_helper.inc;
+
+--echo #
+--echo # SQLCOM_UNLOCK_TABLES
+--echo #
+
+let $statement=
+  unlock tables;
+source include/implicit_commit_helper.inc;
+
+#
+# Missing test for lock tables transactional.
+#
+
+--echo #
+--echo # SQLCOM_CREATE_DB
+--echo #
+
+let $statement=
+  create database db2;
+source include/implicit_commit_helper.inc;
+
+--echo #
+--echo # SQLCOM_CHANGE_DB
+--echo #
+
+create table db2.t1 (a int);
+insert into db2.t1 values (1);
+commit;
+
+let $statement=
+  use db2;
+source include/implicit_commit_helper.inc;
+
+--echo #
+--echo # SQLCOM_SHOW_CREATE_DB
+--echo #
+
+let $statement=
+  show create database db2;
+source include/implicit_commit_helper.inc;
+
+--echo #
+--echo # SQLCOM_ALTER_DB
+--echo #
+
+#let $statement=
+#  alter database db2 character set koi8r;
+#source include/implicit_commit_helper.inc;
+
+#let $statement=
+#  alter database db2 collate cp1251_general_cs;
+#source include/implicit_commit_helper.inc;
+
+--echo #
+--echo # SQLCOM_ALTER_DB_UPGRADE
+--echo #
+
+#let $statement=
+#  alter database `#mysql50#db3` upgrade data directory name;
+#source include/implicit_commit_helper.inc;
+
+--echo #
+--echo # SQLCOM_DROP_DB
+--echo #
+
+use db1;
+
+let $statement=
+  drop database db2;
+source include/implicit_commit_helper.inc;
+
+--echo #
+--echo # SQLCOM_REPAIR
+--echo #
+
+let $statement=
+  repair table t2;
+source include/implicit_commit_helper.inc;
+
+let $statement=
+  repair table t2 use_frm;
+source include/implicit_commit_helper.inc;
+
+--echo #
+--echo # SQLCOM_OPTIMIZE
+--echo #
+
+let $statement=
+  optimize table t1;
+source include/implicit_commit_helper.inc;
+
+--echo #
+--echo # SQLCOM_CHECK
+--echo #
+
+let $statement=
+  check table t1;
+source include/implicit_commit_helper.inc;
+
+let $statement=
+  check table t1 extended;
+source include/implicit_commit_helper.inc;
+
+--echo #
+--echo # SQLCOM_ASSIGN_TO_KEYCACHE
+--echo #
+
+set global keycache.key_buffer_size=128*1024;
+
+let $statement=
+  cache index t3 in keycache;
+source include/implicit_commit_helper.inc;
+
+set global keycache.key_buffer_size=0;
+
+--echo #
+--echo # SQLCOM_PRELOAD_KEYS
+--echo #
+
+let $statement=
+  load index into cache t3;
+source include/implicit_commit_helper.inc;
+
+--echo #
+--echo # SQLCOM_FLUSH
+--echo #
+
+let $statement=
+  flush local privileges;
+source include/implicit_commit_helper.inc;
+
+let $statement=
+  flush privileges;
+source include/implicit_commit_helper.inc;
+
+--echo #
+--echo # SQLCOM_KILL
+--echo #
+
+--echo #
+--echo # SQLCOM_ANALYZE
+--echo #
+
+let $statement=
+  analyze table t1;
+source include/implicit_commit_helper.inc;
+
+--echo #
+--echo # SQLCOM_ROLLBACK
+--echo #
+
+let $statement=
+  rollback;
+source include/implicit_commit_helper.inc;
+
+--echo #
+--echo # SQLCOM_ROLLBACK_TO_SAVEPOINT
+--echo #
+
+
+--echo #
+--echo # SQLCOM_COMMIT
+--echo #
+
+let $statement=
+  commit;
+source include/implicit_commit_helper.inc;
+
+--echo #
+--echo # SQLCOM_SAVEPOINT
+--echo #
+
+let $statement=
+  savepoint sp1;
+source include/implicit_commit_helper.inc;
+
+--echo #
+--echo # SQLCOM_RELEASE_SAVEPOINT
+--echo #
+
+--echo #
+--echo # SQLCOM_SLAVE_START
+--echo #
+
+--echo #
+--echo # SQLCOM_SLAVE_STOP
+--echo #
+
+--echo #
+--echo # SQLCOM_BEGIN
+--echo #
+
+let $statement=
+  begin;
+source include/implicit_commit_helper.inc;
+
+--echo #
+--echo # SQLCOM_CHANGE_MASTER
+--echo #
+
+--echo #
+--echo # SQLCOM_RENAME_TABLE
+--echo #
+
+let $statement=
+  rename table t3 to t4;
+source include/implicit_commit_helper.inc;
+
+let $statement=
+  rename table t4 to t3;
+source include/implicit_commit_helper.inc;
+
+--echo #
+--echo # SQLCOM_RESET
+--echo #
+
+--echo #
+--echo # SQLCOM_PURGE
+--echo #
+
+--echo #
+--echo # SQLCOM_PURGE_BEFORE
+--echo #
+
+--echo #
+--echo # SQLCOM_SHOW_BINLOGS
+--echo #
+
+--echo #
+--echo # SQLCOM_HA_OPEN
+--echo #
+
+let $statement=
+  handler t1 open as ha1;
+source include/implicit_commit_helper.inc;
+
+--echo #
+--echo # SQLCOM_HA_READ
+--echo #
+
+let $statement=
+  handler ha1 read a first;
+source include/implicit_commit_helper.inc;
+
+--echo #
+--echo # SQLCOM_HA_CLOSE
+--echo #
+
+let $statement=
+  handler ha1 close;
+source include/implicit_commit_helper.inc;
+
+--echo #
+--echo # SQLCOM_SHOW_SLAVE_HOSTS
+--echo #
+
+let $statement=
+  show slave hosts;
+source include/implicit_commit_helper.inc;
+
+--echo #
+--echo # SQLCOM_SHOW_BINLOG_EVENTS
+--echo #
+
+let $statement=
+  show binlog events;
+source include/implicit_commit_helper.inc;
+
+--echo #
+--echo # SQLCOM_SHOW_NEW_MASTER
+--echo #
+
+--echo #
+--echo # SQLCOM_SHOW_WARNS
+--echo #
+
+let $statement=
+  show warnings;
+source include/implicit_commit_helper.inc;
+
+--echo #
+--echo # SQLCOM_EMPTY_QUERY
+--echo #
+
+--echo #
+--echo # SQLCOM_SHOW_ERRORS
+--echo #
+
+let $statement=
+  show errors;
+source include/implicit_commit_helper.inc;
+
+--echo #
+--echo # SQLCOM_SHOW_STORAGE_ENGINES
+--echo #
+
+let $statement=
+  show engines;
+source include/implicit_commit_helper.inc;
+
+--echo #
+--echo # SQLCOM_SHOW_PRIVILEGES
+--echo #
+
+let $statement=
+  show privileges;
+source include/implicit_commit_helper.inc;
+
+--echo #
+--echo # SQLCOM_HELP
+--echo #
+
+let $statement=
+  help 'foo';
+source include/implicit_commit_helper.inc;
+
+--echo #
+--echo # SQLCOM_CREATE_USER
+--echo #
+
+let $statement=
+  create user trxusr1;
+source include/implicit_commit_helper.inc;
+
+--echo #
+--echo # SQLCOM_RENAME_USER
+--echo #
+
+let $statement=
+  rename user 'trxusr1' to 'trxusr2';
+source include/implicit_commit_helper.inc;
+
+--echo #
+--echo # SQLCOM_DROP_USER
+--echo #
+
+let $statement=
+  drop user trxusr2;
+source include/implicit_commit_helper.inc;
+
+--echo #
+--echo # SQLCOM_CHECKSUM
+--echo #
+
+let $statement=
+  checksum table t1;
+source include/implicit_commit_helper.inc;
+
+--echo #
+--echo # SQLCOM_CREATE_PROCEDURE
+--echo #
+
+let $statement=
+  create procedure p1(a int) begin end;
+source include/implicit_commit_helper.inc;
+
+--echo #
+--echo # SQLCOM_ALTER_PROCEDURE
+--echo #
+
+let $statement=
+  alter procedure p1 comment 'foobar';
+source include/implicit_commit_helper.inc;
+
+--echo #
+--echo # SQLCOM_SHOW_CREATE_PROC
+--echo #
+
+let $statement=
+  show create procedure p1;
+source include/implicit_commit_helper.inc;
+
+--echo #
+--echo # SQLCOM_SHOW_STATUS_PROC
+--echo #
+
+let $statement=
+  show procedure status;
+source include/implicit_commit_helper.inc;
+
+--echo #
+--echo # SQLCOM_SHOW_PROC_CODE
+--echo #
+
+let $statement=
+  show procedure code p1;
+source include/implicit_commit_helper.inc;
+
+--echo #
+--echo # SQLCOM_DROP_PROCEDURE
+--echo #
+
+let $statement=
+  drop procedure p1;
+source include/implicit_commit_helper.inc;
+
+--echo #
+--echo # SQLCOM_CREATE_FUNCTION
+--echo #
+
+--echo #
+--echo # SQLCOM_DROP_FUNCTION
+--echo #
+
+--echo #
+--echo # SQLCOM_CREATE_SPFUNCTION
+--echo #
+
+let $statement=
+  create function f1() returns int return 69;
+source include/implicit_commit_helper.inc;
+
+--echo #
+--echo # SQLCOM_ALTER_FUNCTION
+--echo #
+
+let $statement=
+  alter function f1 comment 'comment';
+source include/implicit_commit_helper.inc;
+
+--echo #
+--echo # SQLCOM_SHOW_CREATE_FUNC
+--echo #
+
+let $statement=
+  show create function f1;
+source include/implicit_commit_helper.inc;
+
+--echo #
+--echo # SQLCOM_SHOW_STATUS_FUNC
+--echo #
+
+let $statement=
+  show function status like '%f%';
+source include/implicit_commit_helper.inc;
+
+--echo #
+--echo # SQLCOM_SHOW_FUNC_CODE
+--echo #
+
+let $statement=
+  show function code f1;
+source include/implicit_commit_helper.inc;
+
+--echo #
+--echo # SQLCOM_PREPARE
+--echo #
+
+let $statement=
+  prepare stmt1 from "insert into t1 values (5)";
+source include/implicit_commit_helper.inc;
+
+--echo #
+--echo # SQLCOM_EXECUTE
+--echo #
+
+let $statement=
+  execute stmt1;
+source include/implicit_commit_helper.inc;
+
+--echo #
+--echo # SQLCOM_DEALLOCATE_PREPARE
+--echo #
+
+let $statement=
+  deallocate prepare stmt1;
+source include/implicit_commit_helper.inc;
+
+--echo #
+--echo # SQLCOM_CREATE_TRIGGER
+--echo #
+
+let $statement=
+  create trigger trg1 before insert on t1 for each row set @a:=1;
+source include/implicit_commit_helper.inc;
+
+--echo #
+--echo # SQLCOM_SHOW_CREATE_TRIGGER
+--echo #
+
+let $statement=
+  show create trigger trg1;
+source include/implicit_commit_helper.inc;
+
+--echo #
+--echo # SQLCOM_DROP_TRIGGER
+--echo #
+
+let $statement=
+  drop trigger trg1;
+source include/implicit_commit_helper.inc;
+
+--echo #
+--echo # SQLCOM_XA_START
+--echo #
+
+--echo #
+--echo # SQLCOM_XA_END
+--echo #
+
+--echo #
+--echo # SQLCOM_XA_PREPARE
+--echo #
+
+--echo #
+--echo # SQLCOM_XA_COMMIT
+--echo #
+
+--echo #
+--echo # SQLCOM_XA_ROLLBACK
+--echo #
+
+--echo #
+--echo # SQLCOM_XA_RECOVER
+--echo #
+
+--echo #
+--echo # SQLCOM_ALTER_TABLESPACE
+--echo #
+
+--echo #
+--echo # SQLCOM_INSTALL_PLUGIN
+--echo #
+
+--echo #
+--echo # SQLCOM_SHOW_PLUGINS
+--echo #
+
+--echo #
+--echo # SQLCOM_UNINSTALL_PLUGIN
+--echo #
+
+--echo #
+--echo # SQLCOM_SHOW_AUTHORS
+--echo #
+
+let $statement=
+  show authors;
+source include/implicit_commit_helper.inc;
+
+--echo #
+--echo # SQLCOM_BINLOG_BASE64_EVENT
+--echo #
+
+--echo #
+--echo # SQLCOM_SHOW_CONTRIBUTORS
+--echo #
+
+let $statement=
+  show contributors;
+source include/implicit_commit_helper.inc;
+
+--echo #
+--echo # SQLCOM_CREATE_SERVER
+--echo #
+
+--echo #
+--echo # SQLCOM_ALTER_SERVER
+--echo #
+
+--echo #
+--echo # SQLCOM_DROP_SERVER
+--echo #
+
+--echo #
+--echo # SQLCOM_CREATE_EVENT
+--echo #
+
+let $statement=
+  create event ev1 on schedule every 1 second do insert into t1 values (6);
+source include/implicit_commit_helper.inc;
+
+--echo #
+--echo # SQLCOM_ALTER_EVENT
+--echo #
+
+let $statement=
+  alter event ev1 rename to ev2 disable;
+source include/implicit_commit_helper.inc;
+
+--echo #
+--echo # SQLCOM_SHOW_CREATE_EVENT
+--echo #
+
+let $statement=
+  show create event ev2;
+source include/implicit_commit_helper.inc;
+
+--echo #
+--echo # SQLCOM_SHOW_EVENTS
+--echo #
+
+let $statement=
+  show events;
+source include/implicit_commit_helper.inc;
+
+--echo #
+--echo # SQLCOM_DROP_EVENT
+--echo #
+
+let $statement=
+  drop event ev2;
+source include/implicit_commit_helper.inc;
+
+--echo #
+--echo # SQLCOM_SHOW_ARCHIVE
+--echo #
+
+#
+# --error ER_NOT_ALLOWED_COMMAND
+#
+#let $statement=
+#  show backup 'backup_db1.ba';
+#source include/implicit_commit_helper.inc;
+#
+
+--echo #
+--echo # SQLCOM_BACKUP_TEST
+--echo #
+
+# BACKUP_TEST
+
+--echo #
+--echo # SQLCOM_SHOW_PROFILE
+--echo #
+
+let $statement=
+  show profile memory;
+source include/implicit_commit_helper.inc;
+
+--echo #
+--echo # SQLCOM_SHOW_PROFILES
+--echo #
+
+let $statement=
+  show profiles;
+source include/implicit_commit_helper.inc;
+
+DROP TABLE t1;
+DROP TABLE t2;
+DROP TABLE t3;
+eval USE $OLD_DB;
+DROP DATABASE db1;
+
+--echo End of tests

=== modified file 'sql/events.cc'
--- a/sql/events.cc	2008-07-15 17:46:02 +0000
+++ b/sql/events.cc	2009-03-05 09:54:47 +0000
@@ -364,15 +364,6 @@ Events::create_event(THD *thd, Event_par
   int ret;
   DBUG_ENTER("Events::create_event");
 
-  /*
-    Let's commit the transaction first - MySQL manual specifies
-    that a DDL issues an implicit commit, and it doesn't say "successful
-    DDL", so that an implicit commit is a property of any successfully
-    parsed DDL statement.
-  */
-  if (end_active_trans(thd))
-    DBUG_RETURN(TRUE);
-
   if (check_if_system_tables_error())
     DBUG_RETURN(TRUE);
 
@@ -468,13 +459,6 @@ Events::update_event(THD *thd, Event_par
 
   DBUG_ENTER("Events::update_event");
 
-  /*
-    For consistency, implicit COMMIT should be the first thing in the
-    execution chain.
-  */
-  if (end_active_trans(thd))
-    DBUG_RETURN(TRUE);
-
   if (check_if_system_tables_error())
     DBUG_RETURN(TRUE);
 
@@ -591,20 +575,6 @@ Events::drop_event(THD *thd, LEX_STRING 
   int ret;
   DBUG_ENTER("Events::drop_event");
 
-  /*
-    In MySQL, DDL must always commit: since mysql.* tables are
-    non-transactional, we must modify them outside a transaction
-    to not break atomicity.
-    But the second and more important reason to commit here
-    regardless whether we're actually changing mysql.event table
-    or not is replication: end_active_trans syncs the binary log,
-    and unless we run DDL in it's own transaction it may simply
-    never appear on the slave in case the outside transaction
-    rolls back.
-  */
-  if (end_active_trans(thd))
-    DBUG_RETURN(TRUE);
-
   if (check_if_system_tables_error())
     DBUG_RETURN(TRUE);
 

=== modified file 'sql/mysql_priv.h'
--- a/sql/mysql_priv.h	2009-02-17 12:24:09 +0000
+++ b/sql/mysql_priv.h	2009-03-05 09:57:10 +0000
@@ -2036,8 +2036,8 @@ extern struct my_option my_long_options[
 extern const LEX_STRING view_type;
 extern scheduler_functions thread_scheduler;
 extern TYPELIB thread_handling_typelib;
-extern uint8 uc_update_queries[SQLCOM_END+1];
 extern uint sql_command_flags[];
+extern uint server_command_flags[];
 extern TYPELIB log_output_typelib;
 
 /* optional things, have_* variables */

=== modified file 'sql/sql_class.h'
--- a/sql/sql_class.h	2009-02-17 12:24:09 +0000
+++ b/sql/sql_class.h	2009-03-05 09:57:10 +0000
@@ -2926,11 +2926,11 @@ public:
 
 /* Bits in sql_command_flags */
 
-#define CF_CHANGES_DATA		1
-#define CF_HAS_ROW_COUNT	2
-#define CF_STATUS_COMMAND	4
-#define CF_SHOW_TABLE_COMMAND	8
-#define CF_WRITE_LOGS_COMMAND  16
+#define CF_CHANGES_DATA           (1U << 0)
+#define CF_HAS_ROW_COUNT          (1U << 1)
+#define CF_STATUS_COMMAND         (1U << 2)
+#define CF_SHOW_TABLE_COMMAND     (1U << 3)
+#define CF_WRITE_LOGS_COMMAND     (1U << 4)
 /**
   Must be set for SQL statements that may contain
   Item expressions and/or use joins and tables.
@@ -2944,7 +2944,54 @@ public:
   reprepare. Consequently, complex item expressions and
   joins are currently prohibited in these statements.
 */
-#define CF_REEXECUTION_FRAGILE 32
+#define CF_REEXECUTION_FRAGILE    (1U << 5)
+/**
+  Implicitly commit before the SQL statement is executed.
+
+  Statements marked with this flag will cause any active
+  transaction to end (commit) before proceeding with the
+  command execution.
+
+  This flag should be set for statements that probably can't
+  be rolled back or that do not expect any previously metadata
+  locked tables.
+*/
+#define CF_IMPLICT_COMMIT_BEGIN   (1U << 6)
+/**
+  Implicitly commit after the SQL statement.
+
+  Statements marked with this flag are automatically committed
+  at the end of the statement.
+
+  This flag should be set for statements that will implicitly
+  open and take metadata locks on system tables that should not
+  be carried for the whole duration of a active transaction.
+*/
+#define CF_IMPLICIT_COMMIT_END    (1U << 7)
+/**
+  CF_IMPLICT_COMMIT_BEGIN and CF_IMPLICIT_COMMIT_END are used
+  to ensure that the active transaction is implicitly committed
+  before and after every DDL statement and any statement that
+  modifies our currently non-transactional system tables.
+*/
+#define CF_AUTO_COMMIT_TRANS  (CF_IMPLICT_COMMIT_BEGIN | CF_IMPLICIT_COMMIT_END)
+
+/* Bits in server_command_flags */
+
+/**
+  Skip the increase of the global query id counter. Commonly set for
+  commands that are stateless (won't cause any change on the server
+  internal states).
+*/
+#define CF_SKIP_QUERY_ID        (1U << 0)
+
+/**
+  Skip the increase of the number of statements that clients have
+  sent to the server. Commonly used for commands that will cause
+  a statement to be executed but the statement might have not been
+  sent by the user (ie: stored procedure).
+*/
+#define CF_SKIP_QUESTIONS       (1U << 1)
 
 /* Functions in sql_class.cc */
 

=== modified file 'sql/sql_delete.cc'
--- a/sql/sql_delete.cc	2009-02-09 22:51:59 +0000
+++ b/sql/sql_delete.cc	2009-03-05 09:54:47 +0000
@@ -981,8 +981,6 @@ static bool mysql_truncate_by_delete(THD
   mysql_init_select(thd->lex);
   thd->clear_current_stmt_binlog_row_based();
   error= mysql_delete(thd, table_list, NULL, NULL, HA_POS_ERROR, LL(0), TRUE);
-  ha_autocommit_or_rollback(thd, error);
-  end_trans(thd, error ? ROLLBACK : COMMIT);
   thd->current_stmt_binlog_row_based= save_binlog_row_based;
   DBUG_RETURN(error);
 }

=== modified file 'sql/sql_parse.cc'
--- a/sql/sql_parse.cc	2009-02-17 12:24:09 +0000
+++ b/sql/sql_parse.cc	2009-03-05 09:57:10 +0000
@@ -230,6 +230,50 @@ static bool some_non_temp_table_to_be_up
 }
 
 
+/*
+  Implicitly commit a active transaction if statement requires so.
+
+  @param thd    Thread handle.
+  @param mask   Bitmask used for the SQL command match.
+
+*/
+static bool opt_implicit_commit(THD *thd, uint mask)
+{
+  LEX *lex= thd->lex;
+  bool res= FALSE, skip= FALSE;
+  DBUG_ENTER("opt_implicit_commit");
+
+  if (!(sql_command_flags[lex->sql_command] & mask))
+    DBUG_RETURN(FALSE);
+
+  switch (lex->sql_command) {
+  case SQLCOM_DROP_TABLE:
+    skip= lex->drop_temporary;
+    break;
+  case SQLCOM_ALTER_TABLE:
+  case SQLCOM_CREATE_TABLE:
+    /* If CREATE TABLE of non-temporary table, do implicit commit */
+    skip= (lex->create_info.options & HA_LEX_CREATE_TMP_TABLE);
+    break;
+  case SQLCOM_SET_OPTION:
+    skip= lex->autocommit ? FALSE : TRUE;
+    break;
+  default:
+    break;
+  }
+
+  if (!skip)
+  {
+    /* Commit or rollback the statement transaction. */
+    ha_autocommit_or_rollback(thd, thd->is_error());
+    /* Commit the normal transaction if one is active. */
+    res= end_active_trans(thd);
+  }
+
+  DBUG_RETURN(res);
+}
+
+
 /**
   Mark all commands that somehow changes a table.
 
@@ -244,28 +288,46 @@ static bool some_non_temp_table_to_be_up
 */
 
 uint sql_command_flags[SQLCOM_END+1];
+uint server_command_flags[COM_END+1];
 
 void init_update_queries(void)
 {
-  bzero((uchar*) &sql_command_flags, sizeof(sql_command_flags));
+  /* Initialize the server command flags array. */
+  memset(server_command_flags, 0, sizeof(server_command_flags));
 
-  sql_command_flags[SQLCOM_CREATE_TABLE]=   CF_CHANGES_DATA | CF_REEXECUTION_FRAGILE;
-  sql_command_flags[SQLCOM_CREATE_INDEX]=   CF_CHANGES_DATA;
-  sql_command_flags[SQLCOM_ALTER_TABLE]=    CF_CHANGES_DATA | CF_WRITE_LOGS_COMMAND;
-  sql_command_flags[SQLCOM_TRUNCATE]=       CF_CHANGES_DATA | CF_WRITE_LOGS_COMMAND;
-  sql_command_flags[SQLCOM_DROP_TABLE]=     CF_CHANGES_DATA;
+  server_command_flags[COM_STATISTICS]= CF_SKIP_QUERY_ID | CF_SKIP_QUESTIONS;
+  server_command_flags[COM_PING]=       CF_SKIP_QUERY_ID | CF_SKIP_QUESTIONS;
+  server_command_flags[COM_STMT_PREPARE]= CF_SKIP_QUESTIONS;
+  server_command_flags[COM_STMT_CLOSE]=   CF_SKIP_QUESTIONS;
+  server_command_flags[COM_STMT_RESET]=   CF_SKIP_QUESTIONS;
+
+  /* Initialize the sql command flags array. */
+  memset(sql_command_flags, 0, sizeof(sql_command_flags));
+
+  sql_command_flags[SQLCOM_CREATE_TABLE]=   CF_CHANGES_DATA | CF_REEXECUTION_FRAGILE |
+                                            CF_AUTO_COMMIT_TRANS;
+  sql_command_flags[SQLCOM_CREATE_INDEX]=   CF_CHANGES_DATA | CF_AUTO_COMMIT_TRANS;
+  sql_command_flags[SQLCOM_ALTER_TABLE]=    CF_CHANGES_DATA | CF_WRITE_LOGS_COMMAND |
+                                            CF_AUTO_COMMIT_TRANS;
+  sql_command_flags[SQLCOM_TRUNCATE]=       CF_CHANGES_DATA | CF_WRITE_LOGS_COMMAND |
+                                            CF_AUTO_COMMIT_TRANS;
+  sql_command_flags[SQLCOM_DROP_TABLE]=     CF_CHANGES_DATA | CF_AUTO_COMMIT_TRANS;
   sql_command_flags[SQLCOM_LOAD]=           CF_CHANGES_DATA | CF_REEXECUTION_FRAGILE;
-  sql_command_flags[SQLCOM_CREATE_DB]=      CF_CHANGES_DATA;
-  sql_command_flags[SQLCOM_DROP_DB]=        CF_CHANGES_DATA;
-  sql_command_flags[SQLCOM_RENAME_TABLE]=   CF_CHANGES_DATA;
+  sql_command_flags[SQLCOM_CREATE_DB]=      CF_CHANGES_DATA | CF_AUTO_COMMIT_TRANS;
+  sql_command_flags[SQLCOM_DROP_DB]=        CF_CHANGES_DATA | CF_AUTO_COMMIT_TRANS;
+  sql_command_flags[SQLCOM_ALTER_DB_UPGRADE]= CF_AUTO_COMMIT_TRANS;
+  sql_command_flags[SQLCOM_RENAME_TABLE]=   CF_CHANGES_DATA | CF_AUTO_COMMIT_TRANS;
   sql_command_flags[SQLCOM_BACKUP_TABLE]=   CF_CHANGES_DATA;
   sql_command_flags[SQLCOM_RESTORE_TABLE]=  CF_CHANGES_DATA;
-  sql_command_flags[SQLCOM_DROP_INDEX]=     CF_CHANGES_DATA;
-  sql_command_flags[SQLCOM_CREATE_VIEW]=    CF_CHANGES_DATA | CF_REEXECUTION_FRAGILE;
-  sql_command_flags[SQLCOM_DROP_VIEW]=      CF_CHANGES_DATA;
-  sql_command_flags[SQLCOM_CREATE_EVENT]=   CF_CHANGES_DATA;
-  sql_command_flags[SQLCOM_ALTER_EVENT]=    CF_CHANGES_DATA;
-  sql_command_flags[SQLCOM_DROP_EVENT]=     CF_CHANGES_DATA;
+  sql_command_flags[SQLCOM_DROP_INDEX]=     CF_CHANGES_DATA | CF_AUTO_COMMIT_TRANS;
+  sql_command_flags[SQLCOM_CREATE_VIEW]=    CF_CHANGES_DATA | CF_REEXECUTION_FRAGILE |
+                                            CF_AUTO_COMMIT_TRANS;
+  sql_command_flags[SQLCOM_DROP_VIEW]=      CF_CHANGES_DATA | CF_AUTO_COMMIT_TRANS;
+  sql_command_flags[SQLCOM_CREATE_TRIGGER]= CF_AUTO_COMMIT_TRANS;
+  sql_command_flags[SQLCOM_DROP_TRIGGER]=   CF_AUTO_COMMIT_TRANS;
+  sql_command_flags[SQLCOM_CREATE_EVENT]=   CF_CHANGES_DATA | CF_AUTO_COMMIT_TRANS;
+  sql_command_flags[SQLCOM_ALTER_EVENT]=    CF_CHANGES_DATA | CF_AUTO_COMMIT_TRANS;
+  sql_command_flags[SQLCOM_DROP_EVENT]=     CF_CHANGES_DATA | CF_AUTO_COMMIT_TRANS;
 
   sql_command_flags[SQLCOM_UPDATE]=	    CF_CHANGES_DATA | CF_HAS_ROW_COUNT |
                                             CF_REEXECUTION_FRAGILE;
@@ -284,7 +346,8 @@ void init_update_queries(void)
   sql_command_flags[SQLCOM_REPLACE_SELECT]= CF_CHANGES_DATA | CF_HAS_ROW_COUNT |
                                             CF_REEXECUTION_FRAGILE;
   sql_command_flags[SQLCOM_SELECT]=         CF_REEXECUTION_FRAGILE;
-  sql_command_flags[SQLCOM_SET_OPTION]=     CF_REEXECUTION_FRAGILE;
+  sql_command_flags[SQLCOM_SET_OPTION]=     CF_REEXECUTION_FRAGILE |
+                                            CF_AUTO_COMMIT_TRANS;
   sql_command_flags[SQLCOM_DO]=             CF_REEXECUTION_FRAGILE;
 
   sql_command_flags[SQLCOM_SHOW_STATUS_PROC]= CF_STATUS_COMMAND | CF_REEXECUTION_FRAGILE;
@@ -349,9 +412,29 @@ void init_update_queries(void)
     The following admin table operations are allowed
     on log tables.
   */
-  sql_command_flags[SQLCOM_REPAIR]=           CF_WRITE_LOGS_COMMAND;
-  sql_command_flags[SQLCOM_OPTIMIZE]=         CF_WRITE_LOGS_COMMAND;
-  sql_command_flags[SQLCOM_ANALYZE]=          CF_WRITE_LOGS_COMMAND;
+  sql_command_flags[SQLCOM_REPAIR]=           CF_WRITE_LOGS_COMMAND |
+                                              CF_AUTO_COMMIT_TRANS;
+  sql_command_flags[SQLCOM_OPTIMIZE]=         CF_WRITE_LOGS_COMMAND |
+                                              CF_AUTO_COMMIT_TRANS;
+  sql_command_flags[SQLCOM_ANALYZE]=          CF_WRITE_LOGS_COMMAND |
+                                              CF_AUTO_COMMIT_TRANS;
+
+  sql_command_flags[SQLCOM_CREATE_USER]=      CF_AUTO_COMMIT_TRANS;
+  sql_command_flags[SQLCOM_DROP_USER]=        CF_AUTO_COMMIT_TRANS;
+  sql_command_flags[SQLCOM_RENAME_USER]=      CF_AUTO_COMMIT_TRANS;
+  sql_command_flags[SQLCOM_REVOKE_ALL]=       CF_AUTO_COMMIT_TRANS;
+  sql_command_flags[SQLCOM_REVOKE]=           CF_AUTO_COMMIT_TRANS;
+  sql_command_flags[SQLCOM_GRANT]=            CF_AUTO_COMMIT_TRANS;
+
+  sql_command_flags[SQLCOM_CREATE_PROCEDURE]=   CF_AUTO_COMMIT_TRANS;
+  sql_command_flags[SQLCOM_CREATE_SPFUNCTION]=  CF_AUTO_COMMIT_TRANS;
+  sql_command_flags[SQLCOM_ALTER_PROCEDURE]=    CF_AUTO_COMMIT_TRANS;
+  sql_command_flags[SQLCOM_ALTER_FUNCTION]=     CF_AUTO_COMMIT_TRANS;
+  sql_command_flags[SQLCOM_ASSIGN_TO_KEYCACHE]= CF_AUTO_COMMIT_TRANS;
+  sql_command_flags[SQLCOM_PRELOAD_KEYS]=       CF_AUTO_COMMIT_TRANS;
+
+  sql_command_flags[SQLCOM_FLUSH]=              CF_AUTO_COMMIT_TRANS;
+  sql_command_flags[SQLCOM_CHECK]=              CF_AUTO_COMMIT_TRANS;
 }
 
 
@@ -976,27 +1059,13 @@ bool dispatch_command(enum enum_server_c
   thd->set_time();
   VOID(pthread_mutex_lock(&LOCK_thread_count));
   thd->query_id= global_query_id;
-
-  switch( command ) {
-  /* Ignore these statements. */
-  case COM_STATISTICS:
-  case COM_PING:
-    break;
-  /* Only increase id on these statements but don't count them. */
-  case COM_STMT_PREPARE: 
-  case COM_STMT_CLOSE:
-  case COM_STMT_RESET:
-    next_query_id();
-    break;
-  /* Increase id and count all other statements. */
-  default:
-    statistic_increment(thd->status_var.questions, &LOCK_status);
+  if (!(server_command_flags[command] & CF_SKIP_QUERY_ID))
     next_query_id();
-  }
-
   thread_running++;
-  /* TODO: set thd->lex->sql_command to SQLCOM_END here */
-  VOID(pthread_mutex_unlock(&LOCK_thread_count));
+  pthread_mutex_unlock(&LOCK_thread_count);
+
+  if (!(server_command_flags[command] & CF_SKIP_QUESTIONS))
+    statistic_increment(thd->status_var.questions, &LOCK_status);
 
   /**
     Clear the set of flags that are expected to be cleared at the
@@ -1436,11 +1505,16 @@ bool dispatch_command(enum enum_server_c
     bool not_used;
     status_var_increment(thd->status_var.com_stat[SQLCOM_FLUSH]);
     ulong options= (ulong) (uchar) packet[0];
+    if (end_active_trans(thd))
+      break;
     if (check_global_access(thd,RELOAD_ACL))
       break;
     general_log_print(thd, command, NullS);
-    if (!reload_acl_and_cache(thd, options, (TABLE_LIST*) 0, &not_used))
-      my_ok(thd);
+    if (reload_acl_and_cache(thd, options, (TABLE_LIST*) 0, &not_used))
+      break;
+    if (end_active_trans(thd))
+      break;
+    my_ok(thd);
     break;
   }
 #ifndef EMBEDDED_LIBRARY
@@ -1654,8 +1728,8 @@ void log_slow_statement(THD *thd)
   */
   if (thd->enable_slow_log && !thd->user_time)
   {
-    ulonglong end_utime_of_query= thd->current_utime();
     thd_proc_info(thd, "logging slow query");
+    ulonglong end_utime_of_query= thd->current_utime();
 
     if (((end_utime_of_query - thd->utime_after_lock) >
          thd->variables.long_query_time ||
@@ -2172,6 +2246,15 @@ mysql_execute_command(THD *thd)
 
   DBUG_ASSERT(thd->transaction.stmt.modified_non_trans_table == FALSE);
   
+  /*
+    End a active transaction so that this command will have it's
+    own transaction and will also sync the binary log. If a DDL is
+    not run in it's own transaction it may simply never appear on
+    the slave in case the outside transaction rolls back.
+  */
+  if (opt_implicit_commit(thd, CF_IMPLICT_COMMIT_BEGIN))
+    goto error;
+
   switch (lex->sql_command) {
 
   case SQLCOM_SHOW_EVENTS:
@@ -2218,21 +2301,27 @@ mysql_execute_command(THD *thd)
   case SQLCOM_SHOW_STORAGE_ENGINES:
   case SQLCOM_SHOW_PROFILE:
   case SQLCOM_SELECT:
+  {
     thd->status_var.last_query_cost= 0.0;
+
+    /*
+      lex->exchange != NULL implies SELECT .. INTO OUTFILE and this
+      requires FILE_ACL access.
+    */
+    ulong privileges_requested= lex->exchange ? SELECT_ACL | FILE_ACL :
+      SELECT_ACL;
     if (all_tables)
-    {
       res= check_table_access(thd,
-                              lex->exchange ? SELECT_ACL | FILE_ACL :
-                              SELECT_ACL,
+                              privileges_requested,
                               all_tables, UINT_MAX, FALSE);
-    }
     else
       res= check_access(thd,
-                        lex->exchange ? SELECT_ACL | FILE_ACL : SELECT_ACL,
+                        privileges_requested,
                         any_db, 0, 0, 0, 0);
     if (!res)
       res= execute_sqlcom_select(thd, all_tables);
     break;
+  }
   case SQLCOM_PREPARE:
   {
     mysql_sql_stmt_prepare(thd);
@@ -2491,15 +2580,6 @@ mysql_execute_command(THD *thd)
 
   case SQLCOM_CREATE_TABLE:
   {
-    /* If CREATE TABLE of non-temporary table, do implicit commit */
-    if (!(lex->create_info.options & HA_LEX_CREATE_TMP_TABLE))
-    {
-      if (end_active_trans(thd))
-      {
-	res= -1;
-	break;
-      }
-    }
     DBUG_ASSERT(first_table == all_tables && first_table != 0);
     bool link_to_local;
     // Skip first table, which is the table we are creating
@@ -2715,8 +2795,6 @@ end_with_restore_list:
     DBUG_ASSERT(first_table == all_tables && first_table != 0);
     if (check_one_table_access(thd, INDEX_ACL, all_tables))
       goto error; /* purecov: inspected */
-    if (end_active_trans(thd))
-      goto error;
     /*
       Currently CREATE INDEX or DROP INDEX cause a full table rebuild
       and thus classify as slow administrative statements just like
@@ -2828,9 +2906,6 @@ end_with_restore_list:
                             WARN_OPTION_IGNORED, ER(WARN_OPTION_IGNORED),
                             "INDEX DIRECTORY");
       create_info.data_file_name= create_info.index_file_name= NULL;
-      /* ALTER TABLE ends previous transaction */
-      if (end_active_trans(thd))
-	goto error;
 
       if (!thd->locked_tables &&
           !(need_start_waiting= !wait_if_global_read_lock(thd, 0, 1)))
@@ -2875,7 +2950,7 @@ end_with_restore_list:
         goto error;
     }
 
-    if (end_active_trans(thd) || mysql_rename_tables(thd, first_table, 0))
+    if (mysql_rename_tables(thd, first_table, 0))
       goto error;
     break;
   }
@@ -3228,11 +3303,6 @@ end_with_restore_list:
     break;
   }
   case SQLCOM_TRUNCATE:
-    if (end_active_trans(thd))
-    {
-      res= -1;
-      break;
-    }
     DBUG_ASSERT(first_table == all_tables && first_table != 0);
     if (check_one_table_access(thd, DROP_ACL, all_tables))
       goto error;
@@ -3339,8 +3409,6 @@ end_with_restore_list:
     {
       if (check_table_access(thd, DROP_ACL, all_tables, UINT_MAX, FALSE))
 	goto error;				/* purecov: inspected */
-      if (end_active_trans(thd))
-        goto error;
     }
     else
     {
@@ -3438,9 +3506,6 @@ end_with_restore_list:
   {
     List<set_var_base> *lex_var_list= &lex->var_list;
 
-    if (lex->autocommit && end_active_trans(thd))
-      goto error;
-
     if ((check_table_access(thd, SELECT_ACL, all_tables, UINT_MAX, FALSE) ||
 	 open_and_lock_tables(thd, all_tables)))
       goto error;
@@ -3532,11 +3597,6 @@ end_with_restore_list:
       prepared statement- safe.
     */
     HA_CREATE_INFO create_info(lex->create_info);
-    if (end_active_trans(thd))
-    {
-      res= -1;
-      break;
-    }
     char *alias;
     if (!(alias=thd->strmake(lex->name.str, lex->name.length)) ||
         check_db_name(&lex->name))
@@ -3569,11 +3629,6 @@ end_with_restore_list:
   }
   case SQLCOM_DROP_DB:
   {
-    if (end_active_trans(thd))
-    {
-      res= -1;
-      break;
-    }
     if (check_db_name(&lex->name))
     {
       my_error(ER_WRONG_DB_NAME, MYF(0), lex->name.str);
@@ -3610,11 +3665,6 @@ end_with_restore_list:
   case SQLCOM_ALTER_DB_UPGRADE:
   {
     LEX_STRING *db= & lex->name;
-    if (end_active_trans(thd))
-    {
-      res= 1;
-      break;
-    }
 #ifdef HAVE_REPLICATION
     if (thd->slave_thread && 
        (!rpl_filter->db_ok(db->str) ||
@@ -3777,8 +3827,6 @@ end_with_restore_list:
     if (check_access(thd, INSERT_ACL, "mysql", 0, 1, 1, 0) &&
         check_global_access(thd,CREATE_USER_ACL))
       break;
-    if (end_active_trans(thd))
-      goto error;
     /* Conditionally writes to binlog */
     if (!(res= mysql_create_user(thd, lex->users_list)))
       my_ok(thd);
@@ -3789,8 +3837,6 @@ end_with_restore_list:
     if (check_access(thd, DELETE_ACL, "mysql", 0, 1, 1, 0) &&
         check_global_access(thd,CREATE_USER_ACL))
       break;
-    if (end_active_trans(thd))
-      goto error;
     /* Conditionally writes to binlog */
     if (!(res= mysql_drop_user(thd, lex->users_list)))
       my_ok(thd);
@@ -3801,8 +3847,6 @@ end_with_restore_list:
     if (check_access(thd, UPDATE_ACL, "mysql", 0, 1, 1, 0) &&
         check_global_access(thd,CREATE_USER_ACL))
       break;
-    if (end_active_trans(thd))
-      goto error;
     /* Conditionally writes to binlog */
     if (!(res= mysql_rename_user(thd, lex->users_list)))
       my_ok(thd);
@@ -3810,8 +3854,6 @@ end_with_restore_list:
   }
   case SQLCOM_REVOKE_ALL:
   {
-    if (end_active_trans(thd))
-      goto error;
     if (check_access(thd, UPDATE_ACL, "mysql", 0, 1, 1, 0) &&
         check_global_access(thd,CREATE_USER_ACL))
       break;
@@ -3823,9 +3865,6 @@ end_with_restore_list:
   case SQLCOM_REVOKE:
   case SQLCOM_GRANT:
   {
-    if (end_active_trans(thd))
-      goto error;
-
     if (check_access(thd, lex->grant | lex->grant_tot_col | GRANT_ACL,
 		     first_table ?  first_table->db : select_lex->db,
 		     first_table ? &first_table->grant.privilege : 0,
@@ -4170,9 +4209,6 @@ end_with_restore_list:
                      is_schema_db(lex->sphead->m_db.str)))
       goto create_sp_error;
 
-    if (end_active_trans(thd))
-      goto create_sp_error;
-
     name= lex->sphead->name(&namelen);
 #ifdef HAVE_DLOPEN
     if (lex->sphead->m_type == TYPE_ENUM_FUNCTION)
@@ -4367,8 +4403,6 @@ create_sp_error:
                                  lex->sql_command == SQLCOM_ALTER_PROCEDURE, 0))
 	  goto error;
 
-        if (end_active_trans(thd)) 
-          goto error;
 	memcpy(&lex->sp_chistics, &chistics, sizeof(lex->sp_chistics));
         if ((sp->m_type == TYPE_ENUM_FUNCTION) &&
             !trust_function_creators &&  mysql_bin_log.is_open() &&
@@ -4568,16 +4602,12 @@ create_sp_error:
         Note: SQLCOM_CREATE_VIEW also handles 'ALTER VIEW' commands
         as specified through the thd->lex->create_view_mode flag.
       */
-      if (end_active_trans(thd))
-        goto error;
-
       res= mysql_create_view(thd, first_table, thd->lex->create_view_mode);
       break;
     }
   case SQLCOM_DROP_VIEW:
     {
-      if (check_table_access(thd, DROP_ACL, all_tables, UINT_MAX, FALSE) ||
-          end_active_trans(thd))
+      if (check_table_access(thd, DROP_ACL, all_tables, UINT_MAX, FALSE))
         goto error;
       /* Conditionally writes to binlog. */
       res= mysql_drop_view(thd, first_table, thd->lex->drop_mode);
@@ -4585,9 +4615,6 @@ create_sp_error:
     }
   case SQLCOM_CREATE_TRIGGER:
   {
-    if (end_active_trans(thd))
-      goto error;
-
     /* Conditionally writes to binlog. */
     res= mysql_create_or_drop_trigger(thd, all_tables, 1);
 
@@ -4595,9 +4622,6 @@ create_sp_error:
   }
   case SQLCOM_DROP_TRIGGER:
   {
-    if (end_active_trans(thd))
-      goto error;
-
     /* Conditionally writes to binlog. */
     res= mysql_create_or_drop_trigger(thd, all_tables, 0);
     break;
@@ -4919,6 +4943,12 @@ finish:
     */
     start_waiting_global_read_lock(thd);
   }
+
+  /* If commit fails, we should be able to reset the OK status. */
+  thd->main_da.can_overwrite_status= TRUE;
+  opt_implicit_commit(thd, CF_IMPLICIT_COMMIT_END);
+  thd->main_da.can_overwrite_status= FALSE;
+
   DBUG_RETURN(res || thd->is_error());
 }
 

=== modified file 'sql/sql_table.cc'
--- a/sql/sql_table.cc	2009-02-05 06:16:00 +0000
+++ b/sql/sql_table.cc	2009-03-05 09:54:47 +0000
@@ -4159,8 +4159,6 @@ static bool mysql_admin_table(THD* thd, 
   int result_code;
   DBUG_ENTER("mysql_admin_table");
 
-  if (end_active_trans(thd))
-    DBUG_RETURN(1);
   field_list.push_back(item = new Item_empty_string("Table", NAME_CHAR_LEN*2));
   item->maybe_null = 1;
   field_list.push_back(item = new Item_empty_string("Op", 10));

=== modified file 'tests/mysql_client_test.c'
--- a/tests/mysql_client_test.c	2009-01-15 10:28:10 +0000
+++ b/tests/mysql_client_test.c	2009-03-05 09:54:47 +0000
@@ -17769,6 +17769,59 @@ static void test_bug36326()
 #endif
 
 
+/**
+  Test that COM_REFRESH issues a implicit commit.
+*/
+
+static void test_wl4284_1()
+{
+  int rc;
+  MYSQL_ROW row;
+  MYSQL_RES *result;
+
+  DBUG_ENTER("test_wl4284_1");
+  myheader("test_wl4284_1");
+
+  /* set AUTOCOMMIT to OFF */
+  rc= mysql_autocommit(mysql, FALSE);
+  myquery(rc);
+
+  rc= mysql_query(mysql, "DROP TABLE IF EXISTS trans");
+  myquery(rc);
+
+  rc= mysql_query(mysql, "CREATE TABLE trans (a INT) ENGINE= InnoDB");
+  myquery(rc);
+
+  rc= mysql_query(mysql, "INSERT INTO trans VALUES(1)");
+  myquery(rc);
+
+  rc= mysql_refresh(mysql, REFRESH_GRANT | REFRESH_TABLES);
+  myquery(rc);
+
+  rc= mysql_rollback(mysql);
+  myquery(rc);
+
+  rc= mysql_query(mysql, "SELECT * FROM trans");
+  myquery(rc);
+
+  result= mysql_use_result(mysql);
+  mytest(result);
+
+  row= mysql_fetch_row(result);
+  mytest(row);
+
+  mysql_free_result(result);
+
+  /* set AUTOCOMMIT to OFF */
+  rc= mysql_autocommit(mysql, FALSE);
+  myquery(rc);
+
+  rc= mysql_query(mysql, "DROP TABLE trans");
+  myquery(rc);
+
+  DBUG_VOID_RETURN;
+}
+
 /*
   Read and parse arguments and MySQL options from my.cnf
 */
@@ -18085,6 +18138,7 @@ static struct my_tests_st my_tests[]= {
 #ifdef HAVE_QUERY_CACHE
   { "test_bug36326", test_bug36326 },
 #endif
+  { "test_wl4284_1", test_wl4284_1 },
   { 0, 0 }
 };
 


Attachment: [text/bzr-bundle] bzr/mats@sun.com-20090305095710-42u7e0jrzgr9368s.bundle
Thread
bzr commit into mysql-5.1 branch (mats:2810) Bug#43362Mats Kindahl5 Mar