1############## mysql-test\t\max_insert_delayed_threads_basic.test ############### 2# # 3# Variable Name: max_insert_delayed_threads # 4# Scope: GLOBAL | SESSION # 5# Access Type: Dynamic # 6# Data Type: numeric # 7# Default Value: 20 # 8# Range: 0-16384 # 9# # 10# # 11# Creation Date: 2008-02-07 # 12# Author: Salman # 13# # 14# Description: Test Cases of Dynamic System Variable max_insert_delayed_threads # 15# that checks the behavior of this variable in the following ways# 16# * Default Value # 17# * Valid & Invalid values # 18# * Scope & Access method # 19# * Data Integrity # 20# # 21# Reference: http://dev.mysql.com/doc/refman/5.1/en/ # 22# server-system-variables.html # 23# # 24############################################################################### 25 26--source include/load_sysvars.inc 27 28################################################################## 29# START OF max_insert_delayed_threads TESTS # 30################################################################## 31 32 33############################################################# 34# Save initial value # 35############################################################# 36 37SET @start_global_value = @@global.max_insert_delayed_threads; 38SELECT @start_global_value; 39SET @start_session_value = @@session.max_insert_delayed_threads; 40SELECT @start_session_value; 41 42 43--echo '#--------------------FN_DYNVARS_075_01-------------------------#' 44################################################################## 45# Display the DEFAULT value of max_insert_delayed_threads # 46################################################################## 47 48SET @@global.max_insert_delayed_threads = 1000; 49SET @@global.max_insert_delayed_threads = DEFAULT; 50SELECT @@global.max_insert_delayed_threads; 51 52--Error ER_WRONG_VALUE_FOR_VAR 53SET @@session.max_insert_delayed_threads = 1000; 54SET @@session.max_insert_delayed_threads = DEFAULT; 55SELECT @@session.max_insert_delayed_threads; 56 57 58--echo '#--------------------FN_DYNVARS_075_02-------------------------#' 59################################################################## 60# Check the DEFAULT value of max_insert_delayed_threads # 61################################################################## 62 63SET @@global.max_insert_delayed_threads = DEFAULT; 64SELECT @@global.max_insert_delayed_threads = 20; 65 66SET @@session.max_insert_delayed_threads = DEFAULT; 67SELECT @@session.max_insert_delayed_threads = 20; 68 69 70--echo '#--------------------FN_DYNVARS_075_03-------------------------#' 71############################################################################ 72# Change the value of max_insert_delayed_threads to a valid value for GLOBAL Scope # 73############################################################################ 74 75SET @@global.max_insert_delayed_threads = 1; 76SELECT @@global.max_insert_delayed_threads; 77SET @@global.max_insert_delayed_threads = 1025; 78SELECT @@global.max_insert_delayed_threads; 79SET @@global.max_insert_delayed_threads = 0; 80SELECT @@global.max_insert_delayed_threads; 81SET @@global.max_insert_delayed_threads = 16384; 82SELECT @@global.max_insert_delayed_threads; 83SET @@global.max_insert_delayed_threads = 16383; 84SELECT @@global.max_insert_delayed_threads; 85 86--echo '#--------------------FN_DYNVARS_075_04-------------------------#' 87############################################################################# 88# Change the value of max_insert_delayed_threads to a valid value for SESSION Scope # 89############################################################################# 90 91--Error ER_WRONG_VALUE_FOR_VAR 92SET @@session.max_insert_delayed_threads = 1; 93SELECT @@session.max_insert_delayed_threads; 94--Error ER_WRONG_VALUE_FOR_VAR 95SET @@session.max_insert_delayed_threads = 1025; 96SELECT @@session.max_insert_delayed_threads; 97 98SET @@session.max_insert_delayed_threads = 0; 99SELECT @@session.max_insert_delayed_threads; 100--Error ER_WRONG_VALUE_FOR_VAR 101SET @@session.max_insert_delayed_threads = 16384; 102SELECT @@session.max_insert_delayed_threads; 103 104SET @@session.max_insert_delayed_threads = 16383; 105SELECT @@session.max_insert_delayed_threads; 106 107 108--echo '#------------------FN_DYNVARS_075_05-----------------------#' 109############################################################## 110# Change the value of max_insert_delayed_threads to an invalid value # 111############################################################## 112 113SET @@global.max_insert_delayed_threads = -1024; 114SELECT @@global.max_insert_delayed_threads; 115SET @@global.max_insert_delayed_threads = -1; 116SELECT @@global.max_insert_delayed_threads; 117SET @@global.max_insert_delayed_threads = 16385; 118SELECT @@global.max_insert_delayed_threads; 119--Error ER_WRONG_TYPE_FOR_VAR 120SET @@global.max_insert_delayed_threads = 65530.34; 121SELECT @@global.max_insert_delayed_threads; 122--Error ER_WRONG_TYPE_FOR_VAR 123SET @@global.max_insert_delayed_threads = test; 124SELECT @@global.max_insert_delayed_threads; 125SET @@session.max_insert_delayed_threads = 16385; 126SELECT @@session.max_insert_delayed_threads; 127SET @@session.max_insert_delayed_threads = -1; 128SELECT @@session.max_insert_delayed_threads; 129SET @@session.max_insert_delayed_threads = -2; 130SELECT @@session.max_insert_delayed_threads; 131--Error ER_WRONG_TYPE_FOR_VAR 132SET @@session.max_insert_delayed_threads = 65530.34; 133SET @@session.max_insert_delayed_threads = 10737418241; 134SELECT @@session.max_insert_delayed_threads; 135 136 137--Error ER_WRONG_TYPE_FOR_VAR 138SET @@session.max_insert_delayed_threads = test; 139SELECT @@session.max_insert_delayed_threads; 140 141 142--echo '#------------------FN_DYNVARS_075_06-----------------------#' 143#################################################################### 144# Check if the value in GLOBAL Table matches value in variable # 145#################################################################### 146 147 148SELECT @@global.max_insert_delayed_threads = VARIABLE_VALUE 149FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES 150WHERE VARIABLE_NAME='max_insert_delayed_threads'; 151 152--echo '#------------------FN_DYNVARS_075_07-----------------------#' 153#################################################################### 154# Check if the value in SESSION Table matches value in variable # 155#################################################################### 156 157SELECT @@session.max_insert_delayed_threads = VARIABLE_VALUE 158FROM INFORMATION_SCHEMA.SESSION_VARIABLES 159WHERE VARIABLE_NAME='max_insert_delayed_threads'; 160 161 162--echo '#------------------FN_DYNVARS_075_08-----------------------#' 163#################################################################### 164# Check if TRUE and FALSE values can be used on variable # 165#################################################################### 166 167SET @@global.max_insert_delayed_threads = TRUE; 168SELECT @@global.max_insert_delayed_threads; 169SET @@global.max_insert_delayed_threads = FALSE; 170SELECT @@global.max_insert_delayed_threads; 171 172 173--echo '#---------------------FN_DYNVARS_001_09----------------------#' 174################################################################################# 175# Check if accessing variable with and without GLOBAL point to same variable # 176################################################################################# 177 178SET @@global.max_insert_delayed_threads = 2048; 179SELECT @@max_insert_delayed_threads = @@global.max_insert_delayed_threads; 180 181 182--echo '#---------------------FN_DYNVARS_001_10----------------------#' 183######################################################################################################## 184# Check if accessing variable with SESSION,LOCAL and without SCOPE points to same session variable # 185######################################################################################################## 186 187--Error ER_WRONG_VALUE_FOR_VAR 188SET @@max_insert_delayed_threads = 100000; 189SELECT @@max_insert_delayed_threads = @@local.max_insert_delayed_threads; 190SELECT @@local.max_insert_delayed_threads = @@session.max_insert_delayed_threads; 191 192 193--echo '#---------------------FN_DYNVARS_001_11----------------------#' 194############################################################################# 195# Check if max_insert_delayed_threads can be accessed with and without @@ sign # 196############################################################################# 197 198--Error ER_WRONG_VALUE_FOR_VAR 199SET max_insert_delayed_threads = 1024; 200SELECT @@max_insert_delayed_threads; 201--Error ER_UNKNOWN_TABLE 202SELECT local.max_insert_delayed_threads; 203--Error ER_UNKNOWN_TABLE 204SELECT session.max_insert_delayed_threads; 205--Error ER_BAD_FIELD_ERROR 206SELECT max_insert_delayed_threads = @@session.max_insert_delayed_threads; 207 208 209#################################### 210# Restore initial value # 211#################################### 212 213SET @@global.max_insert_delayed_threads = @start_global_value; 214SELECT @@global.max_insert_delayed_threads; 215SET @@session.max_insert_delayed_threads = @start_session_value; 216SELECT @@session.max_insert_delayed_threads; 217 218 219####################################################### 220# END OF max_insert_delayed_threads TESTS # 221####################################################### 222 223