diff options
| -rw-r--r-- | mysql-test/include/commit.inc | 8 | ||||
| -rw-r--r-- | mysql-test/include/implicit_commit_helper.inc | 5 | ||||
| -rw-r--r-- | mysql-test/r/commit_1innodb.result | 8 | ||||
| -rw-r--r-- | mysql-test/r/implicit_commit.result | 1061 | ||||
| -rw-r--r-- | mysql-test/t/implicit_commit.test | 1162 | ||||
| -rw-r--r-- | sql/events.cc | 30 | ||||
| -rw-r--r-- | sql/mysql_priv.h | 2 | ||||
| -rw-r--r-- | sql/sql_class.h | 47 | ||||
| -rw-r--r-- | sql/sql_delete.cc | 13 | ||||
| -rw-r--r-- | sql/sql_parse.cc | 247 | ||||
| -rw-r--r-- | sql/sql_table.cc | 2 | ||||
| -rw-r--r-- | tests/mysql_client_test.c | 54 |
12 files changed, 2485 insertions, 154 deletions
diff --git a/mysql-test/include/commit.inc b/mysql-test/include/commit.inc index d91ba8291fd..d5f10a6d78e 100644 --- a/mysql-test/include/commit.inc +++ b/mysql-test/include/commit.inc @@ -725,15 +725,15 @@ call p_verify_status_increment(4, 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(2, 2, 2, 2); +call p_verify_status_increment(4, 4, 4, 4); rename table t4 to t3; -call p_verify_status_increment(2, 2, 2, 2); +call p_verify_status_increment(0, 0, 0, 0); truncate table t3; call p_verify_status_increment(4, 4, 4, 4); 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); diff --git a/mysql-test/include/implicit_commit_helper.inc b/mysql-test/include/implicit_commit_helper.inc new file mode 100644 index 00000000000..5e87b2db079 --- /dev/null +++ b/mysql-test/include/implicit_commit_helper.inc @@ -0,0 +1,5 @@ +INSERT INTO db1.trans (a) VALUES (1); +--disable_result_log +eval $statement; +--enable_result_log +CALL db1.test_if_commit(); diff --git a/mysql-test/r/commit_1innodb.result b/mysql-test/r/commit_1innodb.result index 51c4ac3002c..bbff677ab3f 100644 --- a/mysql-test/r/commit_1innodb.result +++ b/mysql-test/r/commit_1innodb.result @@ -841,11 +841,11 @@ call p_verify_status_increment(2, 0, 2, 0); SUCCESS alter table t3 rename t4; -call p_verify_status_increment(2, 2, 2, 2); +call p_verify_status_increment(4, 4, 4, 4); SUCCESS rename table t4 to t3; -call p_verify_status_increment(2, 2, 2, 2); +call p_verify_status_increment(0, 0, 0, 0); SUCCESS truncate table t3; @@ -853,13 +853,13 @@ call p_verify_status_increment(4, 4, 4, 4); SUCCESS 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 diff --git a/mysql-test/r/implicit_commit.result b/mysql-test/r/implicit_commit.result new file mode 100644 index 00000000000..8c330550a3b --- /dev/null +++ b/mysql-test/r/implicit_commit.result @@ -0,0 +1,1061 @@ +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 +# +# 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 +# +# 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 +# +# SQLCOM_INSERT_SELECT +# +INSERT INTO db1.trans (a) VALUES (1); +insert into t2 select * from t1; +CALL db1.test_if_commit(); +IMPLICIT COMMIT +NO +# +# 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 +# +# SQLCOM_REPLACE_SELECT +# +INSERT INTO db1.trans (a) VALUES (1); +replace t2 select * from t1; +CALL db1.test_if_commit(); +IMPLICIT COMMIT +NO +# +# 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 +# +# 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 +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 +# +INSERT INTO db1.trans (a) VALUES (1); +show status where (1) in (select * from t1); +CALL db1.test_if_commit(); +IMPLICIT COMMIT +NO +# +# 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 +# +INSERT INTO db1.trans (a) VALUES (1); +show procedure status where (1) in (select * from t1); +CALL db1.test_if_commit(); +IMPLICIT COMMIT +NO +# +# SQLCOM_SHOW_STATUS_FUNC +# +INSERT INTO db1.trans (a) VALUES (1); +show function status where (1) in (select * from t1); +CALL db1.test_if_commit(); +IMPLICIT COMMIT +NO +# +# 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; +INSERT INTO db1.trans (a) VALUES (1); +call p1((1) in (select * from t1)); +CALL db1.test_if_commit(); +IMPLICIT COMMIT +NO +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 +drop user mysqltest_2@localhost; +# +# 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_BACKUP +# +# +# SQLCOM_SHOW_ARCHIVE +# +# +# SQLCOM_RESTORE +# +# +# 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 diff --git a/mysql-test/t/implicit_commit.test b/mysql-test/t/implicit_commit.test new file mode 100644 index 00000000000..ed451877c77 --- /dev/null +++ b/mysql-test/t/implicit_commit.test @@ -0,0 +1,1162 @@ +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 # + +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 # + +let $statement= + show procedure status where (1) in (select * from t1); +source include/implicit_commit_helper.inc; + +--echo # +--echo # SQLCOM_SHOW_STATUS_FUNC +--echo # + +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; + +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; + +drop user mysqltest_2@localhost; + +--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 # + +# +# Available only on servers with debugging support. +# + +--disable_abort_on_error +let $statement= + show procedure code p1; +source include/implicit_commit_helper.inc; +--enable_abort_on_error + +--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 # + +# +# Available only on servers with debugging support. +# + +--disable_abort_on_error +let $statement= + show function code f1; +source include/implicit_commit_helper.inc; +--enable_abort_on_error + +--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_BACKUP +--echo # + +#create database backup_db; +# +#let $statement= +# backup database db1 to 'backup_db1.ba'; +#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_RESTORE +--echo # + +#let $statement= +# restore from 'backup_db1.ba'; +#source include/implicit_commit_helper.inc; + +#--remove_file $MYSQLTEST_VARDIR/master-data/backup_db1.ba +# +#drop database backup_db; + +--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 diff --git a/sql/events.cc b/sql/events.cc index f7ff2b0ccf1..8c9e60e73e8 100644 --- a/sql/events.cc +++ b/sql/events.cc @@ -391,15 +391,6 @@ Events::create_event(THD *thd, Event_parse_data *parse_data, 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); @@ -512,13 +503,6 @@ Events::update_event(THD *thd, Event_parse_data *parse_data, 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); @@ -635,20 +619,6 @@ Events::drop_event(THD *thd, LEX_STRING dbname, LEX_STRING name, bool if_exists) 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); diff --git a/sql/mysql_priv.h b/sql/mysql_priv.h index 38a55e9ec4c..72bc20238fc 100644 --- a/sql/mysql_priv.h +++ b/sql/mysql_priv.h @@ -2014,8 +2014,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 */ diff --git a/sql/sql_class.h b/sql/sql_class.h index 3a6da64ca4a..bef8dff5759 100644 --- a/sql/sql_class.h +++ b/sql/sql_class.h @@ -3152,6 +3152,36 @@ public: joins are currently prohibited in these statements. */ #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) /** Diagnostic statement. @@ -3163,6 +3193,23 @@ public: */ #define CF_DIAGNOSTIC_STMT (1U << 8) +/* 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 */ void add_to_status(STATUS_VAR *to_var, STATUS_VAR *from_var); diff --git a/sql/sql_delete.cc b/sql/sql_delete.cc index 4827d48e1c5..c0162605e2f 100644 --- a/sql/sql_delete.cc +++ b/sql/sql_delete.cc @@ -1062,9 +1062,18 @@ static bool mysql_truncate_by_delete(THD *thd, TABLE_LIST *table_list) table_list->lock_type= TL_WRITE; mysql_init_select(thd->lex); thd->clear_current_stmt_binlog_row_based(); + /* Delete all rows from table */ 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); + /* + All effects of a TRUNCATE TABLE operation are rolled back if a row by row + deletion fails. Otherwise, operation is automatically committed at the end. + */ + if (error) + { + DBUG_ASSERT(thd->stmt_da->is_error()); + ha_autocommit_or_rollback(thd, TRUE); + end_active_trans(thd); + } thd->current_stmt_binlog_row_based= save_binlog_row_based; DBUG_RETURN(error); } diff --git a/sql/sql_parse.cc b/sql/sql_parse.cc index 62046dddcbd..660241e8e2c 100644 --- a/sql/sql_parse.cc +++ b/sql/sql_parse.cc @@ -221,6 +221,50 @@ static bool some_non_temp_table_to_be_updated(THD *thd, TABLE_LIST *tables) } +/* + 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. @@ -235,26 +279,44 @@ static bool some_non_temp_table_to_be_updated(THD *thd, TABLE_LIST *tables) */ 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)); - - 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; + /* Initialize the server command flags array. */ + memset(server_command_flags, 0, sizeof(server_command_flags)); + + 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_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_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_RENAME_TABLE]= CF_CHANGES_DATA | CF_AUTO_COMMIT_TRANS; + 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_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_ALTER_DB_UPGRADE]= 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_UPDATE]= CF_CHANGES_DATA | CF_HAS_ROW_COUNT | CF_REEXECUTION_FRAGILE; @@ -273,7 +335,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; @@ -337,9 +400,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; } @@ -903,28 +986,14 @@ bool dispatch_command(enum enum_server_command command, THD *thd, thd->set_time(); 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 */ 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 beginning of each command. @@ -1277,6 +1346,8 @@ bool dispatch_command(enum enum_server_command command, THD *thd, 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); @@ -1296,13 +1367,16 @@ bool dispatch_command(enum enum_server_command command, THD *thd, res= reload_acl_and_cache(NULL, options | REFRESH_FAST, NULL, ¬_used); my_pthread_setspecific_ptr(THR_THD, thd); - if (!res) - my_ok(thd); - break; + if (res) + break; } + else #endif - if (!reload_acl_and_cache(thd, options, NULL, ¬_used)) - my_ok(thd); + if (reload_acl_and_cache(thd, options, (TABLE_LIST*) 0, ¬_used)) + break; + if (end_active_trans(thd)) + break; + my_ok(thd); break; } #ifndef EMBEDDED_LIBRARY @@ -2037,10 +2111,20 @@ mysql_execute_command(THD *thd) #ifdef HAVE_REPLICATION } /* endif unlikely slave */ #endif + status_var_increment(thd->status_var.com_stat[lex->sql_command]); 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: @@ -2315,15 +2399,6 @@ case SQLCOM_PREPARE: } 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 @@ -2576,8 +2651,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 @@ -2690,9 +2763,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_mode && !(need_start_waiting= !wait_if_global_read_lock(thd, 0, 1))) @@ -2738,7 +2808,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; } @@ -3168,11 +3238,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; @@ -3280,8 +3345,6 @@ end_with_restore_list: { if (check_table_access(thd, DROP_ACL, all_tables, FALSE, UINT_MAX, FALSE)) goto error; /* purecov: inspected */ - if (end_active_trans(thd)) - goto error; } else { @@ -3380,9 +3443,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, FALSE, UINT_MAX, FALSE) || open_and_lock_tables(thd, all_tables))) goto error; @@ -3485,11 +3545,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)) @@ -3522,11 +3577,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); @@ -3563,11 +3613,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) || @@ -3730,8 +3775,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); @@ -3742,8 +3785,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); @@ -3754,8 +3795,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); @@ -3763,8 +3802,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; @@ -3776,9 +3813,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, @@ -4134,9 +4168,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) @@ -4360,8 +4391,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() && @@ -4566,16 +4595,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, FALSE, UINT_MAX, FALSE) - || end_active_trans(thd)) + if (check_table_access(thd, DROP_ACL, all_tables, FALSE, UINT_MAX, FALSE)) goto error; /* Conditionally writes to binlog. */ res= mysql_drop_view(thd, first_table, thd->lex->drop_mode); @@ -4583,9 +4608,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); @@ -4593,9 +4615,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; @@ -4922,6 +4941,12 @@ finish: */ start_waiting_global_read_lock(thd); } + + /* If commit fails, we should be able to reset the OK status. */ + thd->stmt_da->can_overwrite_status= TRUE; + opt_implicit_commit(thd, CF_IMPLICIT_COMMIT_END); + thd->stmt_da->can_overwrite_status= FALSE; + DBUG_RETURN(res || thd->is_error()); } diff --git a/sql/sql_table.cc b/sql/sql_table.cc index 18605916789..9f486768043 100644 --- a/sql/sql_table.cc +++ b/sql/sql_table.cc @@ -4565,8 +4565,6 @@ static bool mysql_admin_table(THD* thd, TABLE_LIST* tables, 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)); diff --git a/tests/mysql_client_test.c b/tests/mysql_client_test.c index 02241dd92f6..c9edd210c32 100644 --- a/tests/mysql_client_test.c +++ b/tests/mysql_client_test.c @@ -18436,6 +18436,59 @@ static void test_bug36004() DBUG_VOID_RETURN; } +/** + 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; +} + static void test_bug38486(void) { @@ -19197,6 +19250,7 @@ static struct my_tests_st my_tests[]= { { "test_wl4166_3", test_wl4166_3 }, { "test_wl4166_4", test_wl4166_4 }, { "test_bug36004", test_bug36004 }, + { "test_wl4284_1", test_wl4284_1 }, { "test_wl4435", test_wl4435 }, { "test_wl4435_2", test_wl4435_2 }, { "test_bug38486", test_bug38486 }, |