Debian Codegraph @

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

raw
1 ############# mysql-test\t\max_prepared_stmt_count_fn.test #####################
2 # #
3 # Variable Name: max_prepared_stmt_count #
4 # Scope: SESSION #
5 # Access Type: Dynamic #
6 # Data Type: NUMERIC #
7 # Default Value: 16382 #
8 # Values: 0-1048576 #
9 # #
10 # #
11 # Creation Date: 2008-03-02 #
12 # Author: Sharique Abdullah #
13 # #
14 # Description: Test Cases of Dynamic System Variable "max_prepared_stmt_count" #
15 # that checks behavior of this variable in the following ways #
16 # * Functionality based on different values #
17 # #
18 #Reference: http://dev.mysql.com/doc/refman/5.1/en/server-system-variables.html#
19 # option_mysqld_max_prepared_stmt_count #
20 # #
21 ################################################################################
22
23
24 --echo ** Setup **
25 --echo
26 #
27 # Setup
28 #
29
30 SET @global_max_prepared_stmt_count = @@global.max_prepared_stmt_count;
31
32
33 --echo '#---------------------FN_DYNVARS_031_01----------------------#'
34 #################################################################################
35 # Check if prepared stmt Can be created more then max_prepared_stmt_count value#
36 #################################################################################
37
38 SET GLOBAL max_prepared_stmt_count=2;
39 --echo ** Prepare statements **
40 #preparing stmts
41 PREPARE stmt from "SELECT * FROM information_schema.CHARACTER_SETS C";
42 PREPARE stmt1 from "SELECT * FROM information_schema.CHARACTER_SETS C";
43 --Error ER_MAX_PREPARED_STMT_COUNT_REACHED
44 PREPARE stmt2 from "SELECT * FROM information_schema.CHARACTER_SETS C";
45 --echo Expected error "Max prepared statements count reached"
46
47 SHOW STATUS like 'Prepared_stmt_count';
48 --echo 2 Expected
49
50
51 --echo '#---------------------FN_DYNVARS_031_02----------------------#'
52 ################################################################################
53 # Check if prepared stmt Can be created more then max_prepared_stmt_count value
54 ################################################################################
55
56 SET GLOBAL max_prepared_stmt_count=0;
57
58 --Error ER_MAX_PREPARED_STMT_COUNT_REACHED
59 PREPARE stmt3 from "SELECT * FROM information_schema.CHARACTER_SETS C";
60 --echo Expected error "Max prepared statements count reached"
61
62 SHOW STATUS like 'Prepared_stmt_count';
63 --echo 2 Expected
64
65 --Error ER_MAX_PREPARED_STMT_COUNT_REACHED
66 PREPARE stmt from "SELECT * FROM information_schema.CHARACTER_SETS C";
67 --echo Expected error "Max prepared statements count reached"
68
69 SHOW STATUS like 'Prepared_stmt_count';
70 --echo 2 Expected
71 --echo 'Bug#35389 A pre existing valid prepared statement DROPS if a PREPARE'
72 --echo 'STATEMENT command is issued with the same name that'
73 --echo 'causes ER_MAX_PREPARED_STMT_COUNT_REACHED error'
74
75
76 --echo '#---------------------FN_DYNVARS_031_03----------------------#'
77 ##############################################################################
78 # check the status of prepared_max stmt after setting max_prepared_stmt_count
79 ##############################################################################
80
81
82 SHOW STATUS like 'Prepared_stmt_count';
83 SET GLOBAL max_prepared_stmt_count=4;
84 PREPARE stmt from "SELECT * FROM information_schema.CHARACTER_SETS C";
85 PREPARE stmt1 from "SELECT * FROM information_schema.CHARACTER_SETS C";
86 PREPARE stmt2 from "SELECT * FROM information_schema.CHARACTER_SETS C";
87 PREPARE stmt3 from "SELECT * FROM information_schema.CHARACTER_SETS C";
88 --echo ** Value of prepared stmt'
89 SHOW STATUS LIKE 'Prepared_stmt_count';
90 --echo 4 Expected
91
92
93 --echo '#---------------------FN_DYNVARS_031_04----------------------#'
94 ######################################################################
95 # Setting value lower then number of prepared stmt #
96 ######################################################################
97
98 --echo ** preparing stmts **
99 #preparing stmts
100
101 PREPARE stmt from "SELECT * FROM information_schema.CHARACTER_SETS C";
102 PREPARE stmt1 from "SELECT * FROM information_schema.CHARACTER_SETS C";
103 PREPARE stmt2 from "SELECT * FROM information_schema.CHARACTER_SETS C";
104
105 --echo ** setting value **
106 SET GLOBAL max_prepared_stmt_count=3;
107 --echo ** Check wether any more statements can be prepared **
108
109 --Error ER_MAX_PREPARED_STMT_COUNT_REACHED
110 PREPARE stmt5 from "SELECT * FROM information_schema.CHARACTER_SETS C";
111 --echo Expected error "Max prepared statements count reached"
112
113 SHOW STATUS LIKE 'Prepared_stmt_count';
114 --echo 4 Expected
115
116
117 --echo '#---------------------FN_DYNVARS_031_05----------------------#'
118 ###########################################################################
119 # Checking in stored procedure#
120 ###########################################################################
121
122 SET GLOBAL max_prepared_stmt_count=3;
123
124 --echo ** Creating procedure **
125 # create procedure to add rows
126 DROP PROCEDURE IF EXISTS sp_checkstmts;
127
128 DELIMITER //;
129
130 CREATE PROCEDURE sp_checkstmts ()
131 BEGIN
132 PREPARE newstmt from "SELECT * FROM information_schema.CHARACTER_SETS C";
133 END //
134
135 DELIMITER ;//
136
137 -- Error ER_MAX_PREPARED_STMT_COUNT_REACHED
138 CALL sp_checkstmts();
139 --echo Expected error "Max prepared statements count reached"
140
141 #
142 # Cleanup
143 #
144
145 DROP PREPARE stmt;
146 DROP PREPARE stmt1;
147 DROP PREPARE stmt2;
148 DROP PREPARE stmt3;
149
150 DROP PROCEDURE sp_checkstmts;
151
152 SET @@global.max_prepared_stmt_count = @global_max_prepared_stmt_count;