Debian Codegraph @

mysql-test/suite/sys_vars/t/innodb_max_dirty_pages_pct_basic.test

raw
1 ################# mysql-test\t\innodb_max_dirty_pages_pct_basic.test ##########
2 # #
3 # Variable Name: innodb_max_dirty_pages_pct #
4 # Scope: GLOBAL #
5 # Access Type: Dynamic #
6 # Data Type: Numeric #
7 # Default Value: 75 #
8 # Range: 0-99 #
9 # #
10 # #
11 # Creation Date: 2008-02-07 #
12 # Author: Rizwan #
13 # #
14 #Description: Test Cases of Dynamic System Variable #
15 # innodb_max_dirty_pages_pct that checks the behavior of #
16 # this variable in the following ways #
17 # * Default Value #
18 # * Valid & Invalid values #
19 # * Scope & Access method #
20 # * Data Integrity #
21 # #
22 # Reference: http://dev.mysql.com/doc/refman/5.1/en/ #
23 # server-system-variables.html #
24 # #
25 ###############################################################################
26 --source include/load_sysvars.inc
27
28 ######################################################################
29 # START OF innodb_max_dirty_pages_pct TESTS #
30 ######################################################################
31
32
33 ################################################################################
34 # Saving initial value of innodb_max_dirty_pages_pct in a temporary variable #
35 ################################################################################
36
37 SET @global_start_value = @@global.innodb_max_dirty_pages_pct;
38 SELECT @global_start_value;
39
40 # need this because setting innodb_max_dirty_pages_pct to lower than this
41 # should cause a warning
42 SET @global_start_max_dirty_lwm_value = @@global.innodb_max_dirty_pages_pct_lwm;
43 SELECT @global_start_max_dirty_lwm_value;
44
45
46 SET @@global.innodb_max_dirty_pages_pct_lwm = 0;
47 SELECT @@global.innodb_max_dirty_pages_pct_lwm;
48
49 --echo '#--------------------FN_DYNVARS_046_01------------------------#'
50 ########################################################################
51 # Display the DEFAULT value of innodb_max_dirty_pages_pct #
52 ########################################################################
53
54 SET @@global.innodb_max_dirty_pages_pct = 0;
55 SET @@global.innodb_max_dirty_pages_pct = DEFAULT;
56 SELECT @@global.innodb_max_dirty_pages_pct;
57
58 --echo '#---------------------FN_DYNVARS_046_02-------------------------#'
59 ################################################################################
60 # check if innodb_max_dirty_pages_pct can be accessed with and without @@ sign #
61 ################################################################################
62
63 --Error ER_GLOBAL_VARIABLE
64 SET innodb_max_dirty_pages_pct = 1;
65 SELECT @@innodb_max_dirty_pages_pct;
66
67 --Error ER_UNKNOWN_TABLE
68 SELECT local.innodb_max_dirty_pages_pct;
69
70 SET global innodb_max_dirty_pages_pct = 0;
71 SELECT @@global.innodb_max_dirty_pages_pct;
72
73 --echo '#--------------------FN_DYNVARS_046_03------------------------#'
74 ##########################################################################
75 # change the value of innodb_max_dirty_pages_pct to a valid value #
76 ##########################################################################
77
78 SET @@global.innodb_max_dirty_pages_pct = 0.0;
79 SELECT @@global.innodb_max_dirty_pages_pct;
80
81 SET @@global.innodb_max_dirty_pages_pct = 1;
82 SELECT @@global.innodb_max_dirty_pages_pct;
83 SET @@global.innodb_max_dirty_pages_pct = 99;
84 SELECT @@global.innodb_max_dirty_pages_pct;
85
86 --echo '#--------------------FN_DYNVARS_046_04------------------------#'
87 ##########################################################################
88 # change value of based on innodb_max_dirty_pages_pct_lwm #
89 ##########################################################################
90 SET @@global.innodb_max_dirty_pages_pct_lwm = @global_start_value - 1;
91 SELECT @@global.innodb_max_dirty_pages_pct_lwm;
92
93 # this should cause warning
94 SET @@global.innodb_max_dirty_pages_pct = @global_start_value - 2;
95 SELECT @@global.innodb_max_dirty_pages_pct;
96
97 --echo '#--------------------FN_DYNVARS_046_05-------------------------#'
98 ###########################################################################
99 # Change the value of innodb_max_dirty_pages_pct to invalid value #
100 ###########################################################################
101
102 SET @@global.innodb_max_dirty_pages_pct = -1;
103 SELECT @@global.innodb_max_dirty_pages_pct;
104 SET @@global.innodb_max_dirty_pages_pct = -1024;
105 SELECT @@global.innodb_max_dirty_pages_pct;
106 --Error ER_WRONG_TYPE_FOR_VAR
107 SET @@global.innodb_max_dirty_pages_pct = "T";
108 SELECT @@global.innodb_max_dirty_pages_pct;
109
110 --Error ER_WRONG_TYPE_FOR_VAR
111 SET @@global.innodb_max_dirty_pages_pct = "Y";
112 SELECT @@global.innodb_max_dirty_pages_pct;
113
114 SET @@global.innodb_max_dirty_pages_pct = 100;
115 SELECT @@global.innodb_max_dirty_pages_pct;
116 SET @@global.innodb_max_dirty_pages_pct = 1001;
117 SELECT @@global.innodb_max_dirty_pages_pct;
118 SET @@global.innodb_max_dirty_pages_pct = 100000;
119 SELECT @@global.innodb_max_dirty_pages_pct;
120 --Error ER_WRONG_TYPE_FOR_VAR
121 SET @@global.innodb_max_dirty_pages_pct = ' ';
122 SELECT @@global.innodb_max_dirty_pages_pct;
123 --Error ER_WRONG_TYPE_FOR_VAR
124 SET @@global.innodb_max_dirty_pages_pct = " ";
125 SELECT @@global.innodb_max_dirty_pages_pct;
126 SET @@global.innodb_max_dirty_pages_pct = 1.1;
127 SELECT @@global.innodb_max_dirty_pages_pct;
128 set global innodb_max_dirty_pages_pct = 0.1;
129 SELECT @@global.innodb_max_dirty_pages_pct;
130 set global innodb_max_dirty_pages_pct = 31.34;
131 SELECT @@global.innodb_max_dirty_pages_pct;
132 set global innodb_max_dirty_pages_pct = 100;
133 SELECT @@global.innodb_max_dirty_pages_pct;
134 set global innodb_max_dirty_pages_pct = 99.999;
135 SELECT @@global.innodb_max_dirty_pages_pct;
136
137 --echo '#----------------------FN_DYNVARS_046_06------------------------#'
138 #########################################################################
139 # Check if the value in GLOBAL Table matches value in variable #
140 #########################################################################
141
142 --disable_warnings
143 SELECT @@global.innodb_max_dirty_pages_pct =
144 VARIABLE_VALUE FROM performance_schema.global_variables
145 WHERE VARIABLE_NAME='innodb_max_dirty_pages_pct';
146 --enable_warnings
147 SELECT @@global.innodb_max_dirty_pages_pct;
148 --disable_warnings
149 SELECT VARIABLE_VALUE FROM performance_schema.global_variables
150 WHERE VARIABLE_NAME='innodb_max_dirty_pages_pct';
151 --enable_warnings
152
153 --echo '#---------------------FN_DYNVARS_046_07-------------------------#'
154 ###################################################################
155 # Check if ON and OFF values can be used on variable #
156 ###################################################################
157
158 --ERROR ER_WRONG_TYPE_FOR_VAR
159 SET @@global.innodb_max_dirty_pages_pct = OFF;
160 SELECT @@global.innodb_max_dirty_pages_pct;
161
162 --ERROR ER_WRONG_TYPE_FOR_VAR
163 SET @@global.innodb_max_dirty_pages_pct = ON;
164 SELECT @@global.innodb_max_dirty_pages_pct;
165
166 --echo '#---------------------FN_DYNVARS_046_08----------------------#'
167 ###################################################################
168 # Check if TRUE and FALSE values can be used on variable #
169 ###################################################################
170
171 SET @@global.innodb_max_dirty_pages_pct = TRUE;
172 SELECT @@global.innodb_max_dirty_pages_pct;
173 SET @@global.innodb_max_dirty_pages_pct = FALSE;
174 SELECT @@global.innodb_max_dirty_pages_pct;
175
176 ##############################
177 # Restore initial value #
178 ##############################
179
180 SET @@global.innodb_max_dirty_pages_pct = @global_start_value;
181 SELECT @@global.innodb_max_dirty_pages_pct;
182
183 SET @@global.innodb_max_dirty_pages_pct_lwm = @global_start_max_dirty_lwm_value;
184 SELECT @@global.innodb_max_dirty_pages_pct_lwm;
185
186 ###############################################################
187 # END OF innodb_max_dirty_pages_pct TESTS #
188 ###############################################################