1. 

   shem, Member

   We run mydbr reports from within our PHP application.
   Once in a while, a random report, even one whose code has not been changed generates the following error message when we try to run it.

   There was an error in the report execution.
   Prepared statement needs to be re-prepared.

   It seems to happen (once in a while) on our production server after we execute a "Synchronization" in the SQL Editor of the server.
   Usually all it takes to do away with the error is to click on our PHP code's "GENERATE REPORT" button one or two more times and then the report displays properly.
   Any ideas as to why this happens, and how I could prevent it from happening?
2. 

   myDBR Team, Key Master

   This is a database server configuration issue. What is the database version you are running?

   One possible fix is to increase the table_definition_cache in your MySQL/MariaDB configuration. Also check if you have large group_concat-calls in your report.

   --
   myDBR Team
3. 

   shem, Member

   MySQL version 8.0
   possibly
   8.0.41-0ubuntu0.24.04.1

   My table_definition_cache is currently set to 2000

   The reports where the errors occur do not have any group_concat-calls

   Can you please explain in more detail how the error is a database server configuration issue?
   And why increasing the cache will help?
4. 

   myDBR Team, Key Master

   MySQL maintains a cache for prepared statements to so the statements do not need to be re-prepared. The cache being used is the table_definition_cache

   The error comes when the prepared statement can no longer be found in the cache therefore the need for recompile.. This may be caused by several reasons (cache being to small, table changes, memory starvation etc).

   Try increasing the table_definition_cache (double it) and check also the table_open_cache value.

   --
   myDBR Team
5. 

   shem, Member

   You wrote:
   The error comes when the prepared statement can no longer be found in the cache therefore the need for recompile.

   But shouldn't mydbr be smart enough to recompile by itself when the prepared statement can no longer be found in the cache?
   Why should the customer have to first see the error and then attempt to regenerate the report himself?
6. 

   myDBR Team, Key Master

   This is not a problem with the application using MySQL (whether it's myDBR or any other); it is a MySQL configuration issue.

   myDBR executes queries against MySQL, and MySQL is responsible for managing its internal caches. If your server configuration leads to a lack of resources, this error may occur, and myDBR will simply display the error returned by MySQL.

   --
   myDBR Team
7. 

   shem, Member

   IF its simply a mysql configuration issue then why would it happen that I try to run the report from our application and I get the
   "Prepared statement needs to be re-prepared."
   error, and then I all I have to do is simply login to the MyDBR portal on that server, and then Refresh the application page and then the error goes away?
   How do you explain that?
8. 

   myDBR Team, Key Master

   The error "Prepared statement needs to be re-prepared" originates directly from the MySQL server. It typically occurs when the MySQL server’s internal statement cache (specifically, the table definition cache) is too small to handle the number of concurrently prepared statements or queries involving many tables. When that cache gets full or invalidated, MySQL may discard and try to recompile statements. If it can't do that properly (often due to resource limits or cache settings), you get this error.

   Why the error does not happen every time, the server reinitializes or recompile some internal objects or metadata related to the queries, clearing/updateting the relevant statement or table definition in the cache. The root cause still lies in how MySQL is managing its prepared statement cache.

   The fix for this is typically increasing the values of certain MySQL configuration settings — like table_definition_cache, table_open_cache, or table_open_cache_instances, depending on the exact environment.

   So while the application is surfacing the error (as it must), the underlying cause is server-side — specifically, how MySQL manages prepared statement caching.

   --
   myDBR Team

Reply

You must log in to post.