mirror of
https://github.com/MariaDB/server.git
synced 2025-10-26 01:18:31 +02:00
159 lines
5.5 KiB
Text
159 lines
5.5 KiB
Text
############## mysql-test\t\auto_commit_func.test #############################
|
|
# #
|
|
# Variable Name: autocommit #
|
|
# Scope: SESSION #
|
|
# Access Type: Dynamic #
|
|
# Data Type: Boolean #
|
|
# Default Value: NA #
|
|
# Range: NA #
|
|
# #
|
|
# #
|
|
# Creation Date: 2008-03-07 #
|
|
# Author: Salman Rawala #
|
|
# #
|
|
# Description: Test Cases of Dynamic System Variable "autocommit" #
|
|
# that checks functionality of this variable #
|
|
# #
|
|
# Reference: (Not Mentioned on website) #
|
|
# #
|
|
################################################################################
|
|
|
|
--source include/have_innodb.inc
|
|
|
|
--disable_warnings
|
|
drop table if exists t1;
|
|
--enable_warnings
|
|
|
|
#########################
|
|
# Creating new table #
|
|
#########################
|
|
|
|
--echo ## Creating new table ##
|
|
CREATE TABLE t1
|
|
(
|
|
id INT NOT NULL auto_increment,
|
|
PRIMARY KEY (id),
|
|
name varchar(30)
|
|
) ENGINE = INNODB;
|
|
|
|
--echo '#--------------------FN_DYNVARS_003_01-------------------------#'
|
|
#####################################################
|
|
# Setting initial value of auto_commit to zero #
|
|
#####################################################
|
|
|
|
--echo ## Setting variable's value to 0 i.e false ##
|
|
SET @@autocommit = 0;
|
|
|
|
|
|
--echo '#--------------------FN_DYNVARS_003_02-------------------------#'
|
|
######################################################################
|
|
# Creating 2 different connections & testing behavior of autocommit
|
|
# after updating record in 1st connection
|
|
######################################################################
|
|
|
|
CONNECT (test_con1,localhost,root,,);
|
|
CONNECTION test_con1;
|
|
|
|
--echo ## Checking value of variable after opening new connection ##
|
|
|
|
SELECT @@autocommit;
|
|
|
|
--echo ## Setting value of variable to zero and inserting some rows ##
|
|
SET @@autocommit = 0;
|
|
|
|
INSERT into t1(name) values('Record_1');
|
|
INSERT into t1(name) values('Record_2');
|
|
SELECT * from t1;
|
|
|
|
--echo ## Creating another connection and verifying records in table ##
|
|
|
|
CONNECT (test_con2,localhost,root,,);
|
|
CONNECTION test_con2;
|
|
SELECT * from t1;
|
|
|
|
|
|
--echo '#--------------------FN_DYNVARS_003_03-------------------------#'
|
|
######################################################################
|
|
# Creating 2 different connections & testing behavior of autocommit
|
|
# after updating record in 1st connection and using COMMIT in first
|
|
# connection
|
|
######################################################################
|
|
|
|
--echo ## Verifying behavior of variable by commiting rows in test_con1 ##
|
|
CONNECTION test_con1;
|
|
SELECT * from t1;
|
|
COMMIT;
|
|
|
|
|
|
--echo ## Now verifying records in table from connection # 02 ##
|
|
CONNECTION test_con2;
|
|
SELECT * from t1;
|
|
|
|
--echo '#--------------------FN_DYNVARS_003_04-------------------------#'
|
|
######################################################################
|
|
# Creating 2 different connections & testing behavior of autocommit
|
|
# after updating record in 1st connection and using ROLLBACK in
|
|
# first connection
|
|
######################################################################
|
|
|
|
CONNECTION test_con1;
|
|
SELECT * from t1;
|
|
|
|
--echo ## Updating value of first row ##
|
|
UPDATE t1 set name = 'Record_12' where name = 'Record_1';
|
|
SELECT * from t1;
|
|
|
|
--echo ## Connecting to connecting # 02 and verifying effect of update query ##
|
|
CONNECTION test_con2;
|
|
SELECT * from t1;
|
|
|
|
--echo ## Now connecting with connection # 01 and using ROLLBACK after it ##
|
|
CONNECTION test_con1;
|
|
ROLLBACK;
|
|
SELECT * from t1;
|
|
|
|
|
|
|
|
--echo '#--------------------FN_DYNVARS_003_05-------------------------#'
|
|
######################################################################
|
|
# Creating 2 different connections & testing behavior of autocommit
|
|
# after updating records in 1st connection and setting AUTOCOMMIT
|
|
# to 1 in second connection
|
|
######################################################################
|
|
|
|
|
|
CONNECTION test_con1;
|
|
INSERT into t1(name) values('Record_3');
|
|
|
|
|
|
--echo ## Now verifying records in table from connection # 02 and changing value ##
|
|
--echo ## of autocommit to true ##
|
|
CONNECTION test_con2;
|
|
SELECT * from t1;
|
|
SET @@autocommit = 1;
|
|
INSERT into t1(name) values('Record_4');
|
|
INSERT into t1(name) values('Record_5');
|
|
SELECT * from t1;
|
|
|
|
--echo ## Connecting with connection # 01 and inserting few records ##
|
|
CONNECTION test_con1;
|
|
SELECT * from t1;
|
|
INSERT into t1(name) values('Record_6');
|
|
SELECT * from t1;
|
|
|
|
--echo ## Now verifying the effect of these new records in second connection ##
|
|
CONNECTION test_con2;
|
|
SELECT * from t1;
|
|
|
|
--echo ## Commit changes
|
|
CONNECTION test_con1;
|
|
COMMIT;
|
|
|
|
--echo ## Dropping table t1 ##
|
|
DROP table t1;
|
|
|
|
DISCONNECT test_con1;
|
|
DISCONNECT test_con2;
|
|
|
|
|
|
|