MySQL stored procedure on a read replica with PHP PDO

Question

I've opened a bug report against MySQL http://bugs.mysql.com/bug.php?id=70793&thanks=4. There is a code example here that demonstrates this on this bug. There is also a workaround that I found that is included in the bug report. This workaround works for PHP and console

I'm running into a bizarre issue with a Stored Procedure and PHP PDO.

I am not allowed to post the body of the stored procedure, but I can provide the following information.

• It works correctly on a read only replica when accessed from console with the same user that PHP PDO shares -- Edit: My initial report here is partially incorrect, the stored procedure will work if the temp table exists and will fail if the temp table doesn't exist in both console and pdo environments. See the linked bug report to MySQL for details.
• I have verified that I am using the same user in both places.
• The only write activity it performs is inside a temp table
• It does utilize a cursor
• The master and replica are both running MySQL 5.5.27
• The MySQL servers are managed on AWS RDS; I have a single parameter group with a standard configuration.

My issue is that I cannot call this stored procedure from PHP PDO, I get this error

SQLSTATE[HY000]: General error: 1290 The MySQL server is running with the --read-only option so it cannot execute this statement

This makes absolutely no sense because I can call this on the read only replica as long as I'm not doing it from PHP.

Can anyone shed any light on what might be going on here?

Edit More bizarre information

I can get a console session to fail, but I can also make it succeed. It depends on if the temporary table that the stored proc uses has already been created. So let me explain my working and failing use cases

Fail

1. Login to the server on console
2. Try to call the stored proc
3. Fail The MySQL server is running with the --read-only option so it cannot execute this statement

Pass

1. Login to the server on console
2. Create the temp table
3. Try to call the stored proc
4. Success

Even stranger is that I most definitely drop that temp table inside the the stored proc and recreate it if it exists.

I'm reasonably certain at this point we are looking at a MySQL bug

Answer 1

Did you try adding the TEMPORARY keyword to the DROP TABLE command?

The TEMPORARY keyword has the following effects:

• The statement drops only TEMPORARY tables.
• The statement does not end an ongoing transaction.
• No access rights are checked. (A TEMPORARY table is visible only to the session that created it, so no check is necessary.)