error Prepared statement needs to be re-prepared

Question

I'm running a MariaDB server (10.3.27-MariaDB-0+deb10u1 Raspbian 10) for a hobby PHP website (PHP 7.3.27-1~deb10u1 (fpm-fcgi)).

Yesterday I decided it was time to start making daily backups of the database and added script in a cron job for the purpose. After the cron job ran this night the website gave me this error:

Fatal error: Uncaught mysqli_sql_exception: Prepared statement needs to be re-prepared in [a].php:180 
Stack trace: 
#0 [a].php(180): mysqli_stmt->execute() 
#1 [b].php(64): getTargetID(Object(mysqli), 'ha', 2020) 
#2 [c].php(3): require('....') 
#3 {main} thrown in funcs.php on line 180

The function getTargetID() is basically executing a query that return an ID from a view. Running the exact same query (against the same database) in TablePlus works fine.

If I restart the MariaDB server, the PHP code executes as expected again, until the backup script is ran again.

The backup script is basically this:

mysqldump -u ${USER} -h${HOST} -p${PASSWORD} --all-databases |gzip > ${SQLFILE}$

Edit: The mysqldump command is placed in a shell script (db_backup.sh) and is executed at midnight with a cron job.

The script is executed on a backup machine, which has a database user with the following privileges:

Select
Trigger
Show view
Lock tables

EDIT2:

The function that causes the error contains the following code:

function getTargetID(&$mysqli, $league, $year) {
  $sql_query = <<<SQL
  SELECT team_id
  FROM standings

  WHERE league = ? and season = ?
  ORDER BY points DESC, goals_for DESC, team_name
  LIMIT 1;
SQL;

  $stmt = $mysqli->prepare($sql_query);
  $stmt->bind_param("si", $league, $year); 
  $stmt->execute();
  $result = $stmt->get_result();

  if ($result->num_rows > 0) {
    $rows = $result->fetch_all(MYSQLI_ASSOC);
    $team_id = $rows[0]["team_id"];
    $result->free();
    return $team_id;
  }
  else { echo "<!-- Error 107 -->"; }
  $stmt->close();
}

Standings is a view that "outputs" a table for standings in a football league.

I'm a bit at a loss how to fix this, and I've so far been unable to find any solution through Google. Should something be added to the dump command?

Mysqldump is an executable, not an sql statement. You cannot execute it with mysqli. — Shadow, Mar 28 '21 at 18:35
Can you describe how you're running that `mysqldump` command in more specific detail? As Shadow says, that is **not** something you can execute via your MySQL connection. It's a shell command, as in it's a binary executable, not a MySQL command at all. — tadman, Mar 28 '21 at 18:39
The mysqldump command is placed in a shell script (db_backup.sh) and is executed at midnight with a cron job. (Added an edit) — linus, Mar 28 '21 at 18:45
This is an issue not directly with `mysqldump` but with something your php code does. I suppose `mysqldump` does something to soak up RAM in the the `mysqld` running on your rpi server: bouncing it fixes or hides the problem. See [this](https://stackoverflow.com/questions/4380813/how-to-get-rid-of-mysql-error-prepared-statement-needs-to-be-re-prepared) for more discussion. Please [edit] your question to tell us a bit more about your php program. — O. Jones, Mar 28 '21 at 18:46
I've added the code for the function in question in an edit. I'm a bit unsure what information to add to add enough clarity. — linus, Mar 28 '21 at 19:08
As recommended in the [discussion](https://stackoverflow.com/questions/4380813/how-to-get-rid-of-mysql-error-prepared-statement-needs-to-be-re-prepared) linked by O. Jones, I've modified the SQL-query in the PHP function to not use a view, and now the PHP code executes with out any errors. It seems like this solves the problem, even though I'm somewhat left with a feeling that there is something that is broken :) — linus, Mar 28 '21 at 19:32

score 0 · Answer 1 · answered Mar 28 '21 at 19:29

Look at this ....

    $stmt = $mysqli->prepare($sql_query);
    $stmt->bind_param("si", $league, $year);
    $stmt->execute();
    $result = $stmt->get_result();
    if ($result->num_rows > 0) {
      /* do some stulff
      $rows = $result->fetch_all(MYSQLI_ASSOC);
      $team_id = $rows[0]["team_id"];
      $result->free();
      return $team_id;                   /* return here */
    }
    else { echo "<!-- Error 107 -->"; }
    $stmt->close();                      /*  close here */

When your query succeeds you return from your function without first closing your prepared query object. Is it possible that causes a prepared-query leak of some sort?

Try changing the php code to something like this....

    $stmt = $mysqli->prepare($sql_query);
    $stmt->bind_param("si", $league, $year);
    $stmt->execute();
    $result = $stmt->get_result();
    if ($result->num_rows > 0) {
      /* do some stulff
      $rows = $result->fetch_all(MYSQLI_ASSOC);
      $team_id = $rows[0]["team_id"];
      $result->free();
      $stmt->close();                    /* close first */ 
      return $team_id;                   /* return here */
    }
    else { echo "<!-- Error 107 -->"; }
    $stmt->close();

You'll be wise to sudo shutdown -r now your machine before you start testing it. That way we'll be sure to start with no prepared queries lurking in no obscure caches.

And it will take a backup cycle or two to test.

If this does fix your problem, this particular mysqli edge case is throwing an incorrect (not nuanced enough) error message.

That's sloppy of me, thank you for pointing it out. However it does not fix the problem. I restarted the database server as suggested, and then ran the the backup shell command and ran the php code again. Same error. As mentioned above, rewriting the function to not use view works. — linus, Mar 28 '21 at 19:44

error Prepared statement needs to be re-prepared

1 Answers1