mariadb/mysql-test/main/implicit_commit.test

# Tests will be skipped for the view protocol because the view protocol creates 
# an additional util connection and other statistics data
-- source include/no_view_protocol.inc

source include/have_innodb.inc;
source include/not_embedded.inc;
source include/have_profiling.inc;

SET SQL_MODE="";

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 #

let $statement=
  reset query cache;
source include/implicit_commit_helper.inc;

--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_BACKUP_LOCK
--echo #

INSERT INTO db1.trans VALUES (1);
BACKUP LOCK t1;
ROLLBACK;
BACKUP UNLOCK;
CALL db1.test_if_commit();

--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