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/have_innodb.inc
27 --source include/load_sysvars.inc
28
29 # set default value for test
30 SET @save_innodb_max_dirty_pages_pct=@@global.innodb_max_dirty_pages_pct;
31 set @@global.innodb_max_dirty_pages_pct=75;
32
33 ######################################################################
34 # START OF innodb_max_dirty_pages_pct TESTS #
35 ######################################################################
36
37
38 ################################################################################
39 # Saving initial value of innodb_max_dirty_pages_pct in a temporary variable #
40 ################################################################################
41
42 SET @global_start_value = @@global.innodb_max_dirty_pages_pct;
43 SELECT @global_start_value;
44
45 # need this because setting innodb_max_dirty_pages_pct to lower than this
46 # should cause a warning
47 SET @global_start_max_dirty_lwm_value = @@global.innodb_max_dirty_pages_pct_lwm;
48 SELECT @global_start_max_dirty_lwm_value;
49
50
51 SET @@global.innodb_max_dirty_pages_pct_lwm = 0;
52 SELECT @@global.innodb_max_dirty_pages_pct_lwm;
53
54 --echo '#--------------------FN_DYNVARS_046_01------------------------#'
55 ########################################################################
56 # Display the DEFAULT value of innodb_max_dirty_pages_pct #
57 ########################################################################
58
59 SET @@global.innodb_max_dirty_pages_pct = 0;
60 SET @@global.innodb_max_dirty_pages_pct = DEFAULT;
61 SELECT @@global.innodb_max_dirty_pages_pct;
62
63 --echo '#---------------------FN_DYNVARS_046_02-------------------------#'
64 ################################################################################
65 # check if innodb_max_dirty_pages_pct can be accessed with and without @@ sign #
66 ################################################################################
67
68 --Error ER_GLOBAL_VARIABLE
69 SET innodb_max_dirty_pages_pct = 1;
70 SELECT @@innodb_max_dirty_pages_pct;
71
72 --Error ER_UNKNOWN_TABLE
73 SELECT local.innodb_max_dirty_pages_pct;
74
75 SET global innodb_max_dirty_pages_pct = 0;
76 SELECT @@global.innodb_max_dirty_pages_pct;
77
78 --echo '#--------------------FN_DYNVARS_046_03------------------------#'
79 ##########################################################################
80 # change the value of innodb_max_dirty_pages_pct to a valid value #
81 ##########################################################################
82
83 SET @@global.innodb_max_dirty_pages_pct = 0.0;
84 SELECT @@global.innodb_max_dirty_pages_pct;
85
86 SET @@global.innodb_max_dirty_pages_pct = 1;
87 SELECT @@global.innodb_max_dirty_pages_pct;
88 SET @@global.innodb_max_dirty_pages_pct = 99;
89 SELECT @@global.innodb_max_dirty_pages_pct;
90
91 --echo '#--------------------FN_DYNVARS_046_04------------------------#'
92 ##########################################################################
93 # change value of based on innodb_max_dirty_pages_pct_lwm #
94 ##########################################################################
95 SET @@global.innodb_max_dirty_pages_pct_lwm = @global_start_value - 1;
96 SELECT @@global.innodb_max_dirty_pages_pct_lwm;
97
98 # this should cause warning
99 SET @@global.innodb_max_dirty_pages_pct = @global_start_value - 2;
100 SELECT @@global.innodb_max_dirty_pages_pct;
101
102 --echo '#--------------------FN_DYNVARS_046_05-------------------------#'
103 ###########################################################################
104 # Change the value of innodb_max_dirty_pages_pct to invalid value #
105 ###########################################################################
106
107 SET @@global.innodb_max_dirty_pages_pct = -1;
108 SELECT @@global.innodb_max_dirty_pages_pct;
109 SET @@global.innodb_max_dirty_pages_pct = -1024;
110 SELECT @@global.innodb_max_dirty_pages_pct;
111 --Error ER_WRONG_TYPE_FOR_VAR
112 SET @@global.innodb_max_dirty_pages_pct = "T";
113 SELECT @@global.innodb_max_dirty_pages_pct;
114
115 --Error ER_WRONG_TYPE_FOR_VAR
116 SET @@global.innodb_max_dirty_pages_pct = "Y";
117 SELECT @@global.innodb_max_dirty_pages_pct;
118
119 SET @@global.innodb_max_dirty_pages_pct = 100;
120 SELECT @@global.innodb_max_dirty_pages_pct;
121 SET @@global.innodb_max_dirty_pages_pct = 1001;
122 SELECT @@global.innodb_max_dirty_pages_pct;
123 SET @@global.innodb_max_dirty_pages_pct = 100000;
124 SELECT @@global.innodb_max_dirty_pages_pct;
125 --Error ER_WRONG_TYPE_FOR_VAR
126 SET @@global.innodb_max_dirty_pages_pct = ' ';
127 SELECT @@global.innodb_max_dirty_pages_pct;
128 --Error ER_WRONG_TYPE_FOR_VAR
129 SET @@global.innodb_max_dirty_pages_pct = " ";
130 SELECT @@global.innodb_max_dirty_pages_pct;
131 SET @@global.innodb_max_dirty_pages_pct = 1.1;
132 SELECT @@global.innodb_max_dirty_pages_pct;
133 set global innodb_max_dirty_pages_pct = 0.1;
134 SELECT @@global.innodb_max_dirty_pages_pct;
135 set global innodb_max_dirty_pages_pct = 31.34;
136 SELECT @@global.innodb_max_dirty_pages_pct;
137 set global innodb_max_dirty_pages_pct = 100;
138 SELECT @@global.innodb_max_dirty_pages_pct;
139 set global innodb_max_dirty_pages_pct = 99.999;
140 SELECT @@global.innodb_max_dirty_pages_pct;
141
142 --echo '#----------------------FN_DYNVARS_046_06------------------------#'
143 #########################################################################
144 # Check if the value in GLOBAL Table matches value in variable #
145 #########################################################################
146
147 --disable_warnings
148 SELECT @@global.innodb_max_dirty_pages_pct =
149 VARIABLE_VALUE FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES
150 WHERE VARIABLE_NAME='innodb_max_dirty_pages_pct';
151 --enable_warnings
152 SELECT @@global.innodb_max_dirty_pages_pct;
153 --disable_warnings
154 SELECT VARIABLE_VALUE FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES
155 WHERE VARIABLE_NAME='innodb_max_dirty_pages_pct';
156 --enable_warnings
157
158 --echo '#---------------------FN_DYNVARS_046_07-------------------------#'
159 ###################################################################
160 # Check if ON and OFF values can be used on variable #
161 ###################################################################
162
163 --ERROR ER_WRONG_TYPE_FOR_VAR
164 SET @@global.innodb_max_dirty_pages_pct = OFF;
165 SELECT @@global.innodb_max_dirty_pages_pct;
166
167 --ERROR ER_WRONG_TYPE_FOR_VAR
168 SET @@global.innodb_max_dirty_pages_pct = ON;
169 SELECT @@global.innodb_max_dirty_pages_pct;
170
171 --echo '#---------------------FN_DYNVARS_046_08----------------------#'
172 ###################################################################
173 # Check if TRUE and FALSE values can be used on variable #
174 ###################################################################
175
176 SET @@global.innodb_max_dirty_pages_pct = TRUE;
177 SELECT @@global.innodb_max_dirty_pages_pct;
178 SET @@global.innodb_max_dirty_pages_pct = FALSE;
179 SELECT @@global.innodb_max_dirty_pages_pct;
180
181 ##############################
182 # Restore initial value #
183 ##############################
184
185 SET @@global.innodb_max_dirty_pages_pct = @global_start_value;
186 SELECT @@global.innodb_max_dirty_pages_pct;
187
188 SET @@global.innodb_max_dirty_pages_pct_lwm = @global_start_max_dirty_lwm_value;
189 SELECT @@global.innodb_max_dirty_pages_pct_lwm;
190
191 SET @@global.innodb_max_dirty_pages_pct=@save_innodb_max_dirty_pages_pct;
192
193 ###############################################################
194 # END OF innodb_max_dirty_pages_pct TESTS #
195 ###############################################################