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