max_prepared_stmt_count_func.test 6.01 KB
Newer Older
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
############# 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;

SET @@global.max_prepared_stmt_count = @global_max_prepared_stmt_count;