############# 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;