使用PHP PDO在读取副本上存储MySQL过程

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

我已经在MySQL http://bugs.mysql.com/bug.php?id= 70793&感恩节4中打开了一个bug报告。这里有一个代码示例演示了这个错误。我还发现了bug报告中包含的一个解决方案。这个解决方案适用于PHP和控制台

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

对于存储过程和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.
它工作正常在只读副本从控制台访问相同的用户时,PHP PDO股票——编辑:这是我最初的报告部分不正确,存储过程将工作如果临时表存在,将会失败如果临时表不存在控制台和PDO环境。有关详细信息，请参见链接到MySQL的bug报告。
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
主副本和副本都运行MySQL 5.5.27
The MySQL servers are managed on AWS RDS; I have a single parameter group with a standard configuration.
MySQL服务器在AWS RDS上进行管理;我有一个带有标准配置的参数组。

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

我的问题是我不能从PHP PDO调用这个存储过程，我得到了这个错误

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

SQLSTATE[HY000]:一般错误:1290 MySQL服务器使用-只读选项运行，因此无法执行此语句

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.

这完全没有意义，因为我可以在只读副本上调用它，只要我不是在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

我可以让控制台会话失败，但我也可以使它成功。这取决于存储的proc使用的临时表是否已经创建。让我解释一下我的工作和失败用例。

Fail

失败

Login to the server on console
在控制台登录到服务器
Try to call the stored proc
尝试调用存储的proc
Fail The MySQL server is running with the --read-only option so it cannot execute this statement
MySQL服务器正在使用只读选项运行，因此无法执行此语句

Pass

通过

Login to the server on console
在控制台登录到服务器
Create the temp table
创建临时表
Try to call the stored proc
尝试调用存储的proc
Success
成功

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

更奇怪的是，我肯定会将那个临时表放在存储的proc中，如果它存在，就重新创建它。

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

我很确定，现在我们正在研究一个MySQL错误

1 个解决方案

#1

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

您是否尝试向DROP TABLE命令添加临时关键字?

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.)
不检查访问权限。(临时表只对创建它的会话可见，因此不需要检查。)

#1