--source include/load_sysvars.inc ##################################################################### # START OF select_into_disk_sync_delay TESTS # ##################################################################### ############################################################# # Save initial value # ############################################################# SET @start_global_value = @@global.select_into_disk_sync_delay; SELECT @start_global_value; SET @start_session_value = @@session.select_into_disk_sync_delay; SELECT @start_session_value; --echo '#--------------------FN_DYNVARS_002_01-------------------------#' ##################################################################### # Display the DEFAULT value of select_into_disk_sync_delay # ##################################################################### SET @@global.select_into_disk_sync_delay = 1000; SET @@global.select_into_disk_sync_delay = DEFAULT; SELECT @@global.select_into_disk_sync_delay; SET @@session.select_into_disk_sync_delay = 3000; SET @@session.select_into_disk_sync_delay = DEFAULT; SELECT @@session.select_into_disk_sync_delay; --echo '#--------------------FN_DYNVARS_002_02-------------------------#' ##################################################################### # Check the DEFAULT value of select_into_disk_sync_delay # ##################################################################### SET @@global.select_into_disk_sync_delay = @start_global_value; SELECT @@global.select_into_disk_sync_delay = 10; SET @@session.select_into_disk_sync_delay = @start_session_value; SELECT @@session.select_into_disk_sync_delay = 20; --echo '#--------------------FN_DYNVARS_002_03-------------------------#' ############################################################################### # Change the value of select_into_disk_sync_delay to a valid value for GLOBAL Scope # ############################################################################### SET @@global.select_into_disk_sync_delay = 5; SELECT @@global.select_into_disk_sync_delay; SET @@global.select_into_disk_sync_delay = 12; SELECT @@global.select_into_disk_sync_delay; SET @@global.select_into_disk_sync_delay = 100; SELECT @@global.select_into_disk_sync_delay; --echo '#--------------------FN_DYNVARS_002_04-------------------------#' ############################################################################### # Change the value of select_into_disk_sync_delay to a valid value for SESSION Scope# ############################################################################### SET @@session.select_into_disk_sync_delay = 4; SELECT @@session.select_into_disk_sync_delay; SET @@session.select_into_disk_sync_delay = 8; SELECT @@session.select_into_disk_sync_delay; SET @@session.select_into_disk_sync_delay = 17; SELECT @@session.select_into_disk_sync_delay; --echo '#------------------FN_DYNVARS_002_05-----------------------#' ################################################################# # Change the value of select_into_disk_sync_delay to an invalid value # ################################################################# # for global scope SET @@global.select_into_disk_sync_delay = -1; SELECT @@global.select_into_disk_sync_delay; SET @@global.select_into_disk_sync_delay = -1024; SELECT @@global.select_into_disk_sync_delay; SET @@global.select_into_disk_sync_delay = 3747474747474747474; SELECT @@global.select_into_disk_sync_delay; --Error ER_WRONG_TYPE_FOR_VAR SET @@global.select_into_disk_sync_delay = ON; SELECT @@global.select_into_disk_sync_delay; --Error ER_WRONG_TYPE_FOR_VAR SET @@global.select_into_disk_sync_delay = OFF; SELECT @@global.select_into_disk_sync_delay; --Error ER_WRONG_TYPE_FOR_VAR SET @@global.select_into_disk_sync_delay = test; SELECT @@global.select_into_disk_sync_delay; # for session scope SET @@session.select_into_disk_sync_delay = -1; SELECT @@session.select_into_disk_sync_delay; SET @@session.select_into_disk_sync_delay = -2; SELECT @@session.select_into_disk_sync_delay; --Error ER_WRONG_TYPE_FOR_VAR SET @@session.select_into_disk_sync_delay = ON; SELECT @@session.select_into_disk_sync_delay; --Error ER_WRONG_TYPE_FOR_VAR SET @@session.select_into_disk_sync_delay = OFF; SELECT @@session.select_into_disk_sync_delay; --Error ER_WRONG_TYPE_FOR_VAR SET @@session.select_into_disk_sync_delay = test; SELECT @@session.select_into_disk_sync_delay; --Error ER_WRONG_TYPE_FOR_VAR SET @@session.select_into_disk_sync_delay = 85858585858585858585; SELECT @@session.select_into_disk_sync_delay; --Error ER_WRONG_VALUE_FOR_VAR --echo '#------------------FN_DYNVARS_002_06-----------------------#' #################################################################### # Check if the value in GLOBAL Table matches value in variable # #################################################################### SELECT @@global.select_into_disk_sync_delay = VARIABLE_VALUE FROM performance_schema.global_variables WHERE VARIABLE_NAME='select_into_disk_sync_delay'; --echo '#------------------FN_DYNVARS_002_07-----------------------#' #################################################################### # Check if the value in SESSION Table matches value in variable # #################################################################### SELECT @@session.select_into_disk_sync_delay = VARIABLE_VALUE FROM performance_schema.session_variables WHERE VARIABLE_NAME='select_into_disk_sync_delay'; --echo '#---------------------FN_DYNVARS_001_09----------------------#' ############################################################################### # Check if global and session variables are independant of each other # ############################################################################### SET @@global.select_into_disk_sync_delay = 10; SET @@session.select_into_disk_sync_delay = 1000; SELECT @@select_into_disk_sync_delay = @@global.select_into_disk_sync_delay; --echo '#---------------------FN_DYNVARS_001_10----------------------#' ############################################################################## # Check if accessing variable with SESSION,LOCAL and without SCOPE points # # to same session variable # ############################################################################## SET @@select_into_disk_sync_delay = 1000; SELECT @@select_into_disk_sync_delay = @@local.select_into_disk_sync_delay; SELECT @@local.select_into_disk_sync_delay = @@session.select_into_disk_sync_delay; --echo '#---------------------FN_DYNVARS_001_11----------------------#' ############################################################################### # Check if select_into_disk_sync_delay can be accessed with and without @@ sign # ############################################################################### SET select_into_disk_sync_delay = 1000; SELECT @@select_into_disk_sync_delay; --Error ER_UNKNOWN_TABLE SELECT local.select_into_disk_sync_delay; --Error ER_UNKNOWN_TABLE SELECT session.select_into_disk_sync_delay; --Error ER_BAD_FIELD_ERROR SELECT select_into_disk_sync_delay = @@session.select_into_disk_sync_delay; --echo '#---------------------FN_DYNVARS_001_12----------------------#' ###################################################################################### # Check if changing session variable throws error without sufficient privileges # ###################################################################################### CREATE USER foo, bar; GRANT SESSION_VARIABLES_ADMIN ON *.* TO bar; --let $conn_name = foo_connection --connect($conn_name, localhost, foo,,,) SELECT @@session.select_into_disk_sync_delay; --error ER_SPECIFIC_ACCESS_DENIED_ERROR SET @@session.select_into_disk_sync_delay = 2000; --connection default disconnect $conn_name; --let $conn_name = bar_connection --connect($conn_name, localhost, bar,,,) SELECT @@session.select_into_disk_sync_delay; SET @@session.select_into_disk_sync_delay = 2000; --error ER_SPECIFIC_ACCESS_DENIED_ERROR SET @@global.select_into_disk_sync_delay = 2000; SELECT @@session.select_into_disk_sync_delay; --connection default disconnect $conn_name; DROP USER foo, bar; #################################### # Restore initial value # #################################### SET @@global.select_into_disk_sync_delay = @start_global_value; SELECT @@global.select_into_disk_sync_delay; SET @@session.select_into_disk_sync_delay = @start_session_value; SELECT @@session.select_into_disk_sync_delay; ################################################### # END OF select_into_disk_sync_delay TESTS # ###################################################