MySQL Bugs: #70793: Temp table in stored proc on read replica behaves erratically

Bug #70793	Temp table in stored proc on read replica behaves erratically
Submitted:	31 Oct 2013 16:25	Modified:	1 Nov 2013 18:51
Reporter:	Whit Marbut	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	5.5.27	OS:	Linux (Ubuntu 12.04 LTS)
Assigned to:		CPU Architecture:	Any
Tags:	linux, readonly, stored procedure, temporary table, ubuntu

Description:
I'm seeing strange behavior related to temporary tables and stored procedures on a read-only server. The user has all privileges on the database, but not server wide. The user does not have super.

Calling a stored procedure that creates a temp table on a read-only server in a session where the table has not been created will fail. However if you create the temporary table outside of the stored procedure and then call it, it will succeed. The stored procedure drops the temp table if it exists and then re-creates it. 

Here is a simple stored proc to demonstrate the issue

DROP PROCEDURE IF EXISTS `simpleProc`;
DELIMITER #
CREATE PROCEDURE `simpleProc` ()
COMMENT 'Demonstrate potential bugs'
BEGIN

DROP TABLE IF EXISTS my_temp;
CREATE TEMPORARY TABLE my_temp (id INT(11), person_name VARCHAR(255));
INSERT INTO my_temp (id, person_name) VALUES (2, 'testing_a_bug');
SELECT * FROM my_temp;

END #
DELIMITER ;

How to repeat:
FAIL USE CASE

1) Start a fresh session
2) `CALL simpleProc()`

PASS USE CASE

1) Start a fresh session
2) Create temp table manually `INSERT INTO my_temp (id, person_name) VALUES (2, 'testing_a_bug');`
3) `CALL simpleProc()`

Meant to paste

CREATE TEMPORARY TABLE my_temp (id INT(11), person_name VARCHAR(255)) 

into the PASS example and not

INSERT INTO my_temp (id, person_name) VALUES (2, 'testing_a_bug');

This problem can be abated by using the `TEMPORARY` keyword in the `DROP TABLE` command. See http://stackoverflow.com/a/19719174/701368

Thank you for taking the time to write to us, but this is not a bug. Please double-check the documentation available at http://dev.mysql.com/doc/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

DROP TABLE without word "TEMPORARY" fails even if used without stored procedure. This is not a bug.