All geek questions in one place

MySQL stored procedure on a read replica with PHP PDO

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

I ran into a fancy problem with stored procedure and PHP PDO.

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

  • It only works correctly in a read-only replica when accessing from a console with the same user as PHP PDO. - Edit: my initial report here is partially incorrect, the stored procedure will work if the temporary table exists and will fail if the temporary table does not exist in the console and pdo environments. See the MySQL Error Report for more details.
  • I verified that I am using the same user in both places.
  • The only record it performs is inside the temp table
  • He uses a cursor
  • The master and replica work MySQL 5.5.27
  • MySQL servers are managed on AWS RDS; I have one group of parameters with a standard configuration.

My problem 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 no sense because I can call it read-only if I don't do this with PHP.

Can anyone shed light on what can happen here?

Edit More Bizarre Information

I can end the console session, but I can also succeed. This depends on whether a temporary table has been created that the stored proc process uses. So let me explain my working and unsuccessful use cases.

Renouncement

  • Log in to the server on the console
  • Try calling the stored proc procedure
  • Error The MySQL server is running with the --read-only option so it cannot execute this statement

Pass

  • Log in to the server on the console
  • Create temporary table
  • Try calling the stored proc procedure
  • Success

Even the stranger is that I will definitely discard this temporary table inside the stored procedure and recreate it if it exists.

I'm pretty sure about this, we are looking at a MySQL error

+6
source share
2 answers

Have you tried adding the TEMPORARY keyword to the DROP TABLE command?

The TEMPORARY keyword has the following effects:

  • A statement cancels only TEMPORARY tables.
  • The application does not terminate the current transaction.
  • No permissions. (TEMPORARILY the table is visible only to the session that created it, so no verification is necessary.)
+1
source

--read-only only for users without --read-only root or non-replicas. That way, ROOT from the console can still do anything, but not a PHP user.

-1
source

Source: https://habr.com/ru/post/957080/

More articles:


All Articles