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

Reply

You must log in to post.