[MDEV-15108] "Prepared statement needs to be re-prepared" error Created: 2018-01-29  Updated: 2020-08-25  Resolved: 2018-07-19

Status: Closed
Project: MariaDB Server
Component/s: Prepared Statements
Affects Version/s: 10.1
Fix Version/s: N/A

Type: Bug Priority: Major
Reporter: Hartmut Holzgraefe Assignee: Oleksandr Byelkin
Resolution: Incomplete Votes: 1
Labels: need_feedback

Issue Links:
Relates
relates to MDEV-7753 Regression on executing prepare state... Closed

 Description   

As far as I understand prepared statement reprepares are tried three times, and only if things fail three times in a row the ER_NEED_REPREPARE error will actually be passed on to the client. During each reprepare attempt, successful or not, the Com_stmt_reprepare counter should be incremented.

I' having a case now where a java client gets "Prepared statement needs to be re-prepared" exceptions, but

SHOW GLOBAL STATUS LIKE "Com_stmt_%prepare";
 
| Com_stmt_prepare | 1753 |
| Com_stmt_reprepare | 0 |

Raising the table_definition_cache size originally made the problem go away for a while, but it has returned now.



 Comments   
Comment by Hartmut Holzgraefe [ 2018-02-01 ]

There may have been a "FLUSH STATUS;" between the time the warning has been seen, and the time SHOW STATUS values were retrieved ... I'll add a further comment once that's been confirmed.

Comment by Hartmut Holzgraefe [ 2018-02-01 ]

No FLUSH STATUS; but there had been a reboot between getting the error message, and retrieving the STATUS counters.

Comment by Hartmut Holzgraefe [ 2018-02-01 ]

I removed the status counter part of the bug title.

The actual question now is why raising the table_definition_cache seems to fix, or at least reduce the frequency of, re-prepare warnings.

Comment by Michael Als [ 2018-02-06 ]

We're experiencing the same issue when using prepared statements on a view. Restarting mysql makes it go away.

I'm not sure how to reproduce. It appears a few times a week on our development server.
One theory is that it has something to do with the nightly reload of databases from sqldumps.

Would love to supply more data the next time error occurs, though I'm not sure what would be helpful?

Comment by Michael Als [ 2018-02-07 ]

Problem occurred again today.
Running FLUSH TABLES made it go away without restarting the service.

Comment by Oleksandr Byelkin [ 2018-06-12 ]

Actually there is a lot of cases where it will not try to rexecute:

  if ((sql_command_flags[lex->sql_command] & CF_REEXECUTION_FRAGILE) &&
      error && !thd->is_fatal_error && !thd->killed &&
      reprepare_observer.is_invalidated() &&
      reprepare_attempt++ < MAX_REPREPARE_ATTEMPTS)
  {
    DBUG_ASSERT(thd->get_stmt_da()->sql_errno() == ER_NEED_REPREPARE);
    thd->clear_error();
 
    error= reprepare();
 
    if (! error)                                /* Success */
      goto reexecute;
  }
  reset_stmt_params(this);

As you can see among other it will not to try to reprepare command marked as CF_REEXECUTION_FRAGILE and it is almost all commands.

So to solve the problem it should be repeated.

Comment by Oleksandr Byelkin [ 2018-06-12 ]

hmmm actually it will reexecute CF_REEXECUTION_FRAGILE... need to check more...

Comment by Oleksandr Byelkin [ 2018-06-12 ]

OK, the name is not really the best, but there is a fact that we have commands which will never be reprepared automatically but just return ER_NEED_REPREPARE (for example ALTER ... )

So IMHO it is not a bug.

Comment by Hartmut Holzgraefe [ 2018-07-05 ]

In the case I reported this for it happens on SELECT though. As raising the table_definition_cache size seems to make it happen less often I assume it may actually be a duplicate of MDEV-4621 after all? Still doesn't explain why it becomes user visible though, and does not get caught by internal re-prepare logic (Com_stmt_reprepare being zero)

Comment by Oleksandr Byelkin [ 2018-07-05 ]

if there are some certainty about commands, test suite should be created.

I do not see how SHOW GLOBAL STATUS are connected with SELECT from information schema with charset complications in the request, do you see?

Comment by Oleksandr Byelkin [ 2018-07-10 ]

Information schema was mentioned in the bug you put reference https://jira.mariadb.org/browse/MDEV-4621 . And I stated that it can not be duplicate.

Comment by Oleksandr Byelkin [ 2018-07-10 ]

"As far as I understand the user facing "needs to be reprepared" error should only be raised after three failed internal attempts to re-prepare the statement?" - NO. There are queries which do not try to re-prepare and that what was I talking in the my comment.

Generated at Thu Feb 08 08:18:44 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.