0

I have a little large database application using PHP and MariaDB 10.3.

I have about 100 tables, and then about 3,000 views.

When exceeding something like 1,000 views, the database schema breaks down and stops being accessible. The tables are stil fine, but all the views break down. When query any of the views it gives different error messages like "prepared statements need to be re-prepared" or "lost connection".

The primary problem is not about table cache as extending this does not solve the problem. It is not either about how to emulate the prepared statements. I have tried those strategies already.

Someone knows this problem and how to solve it?

Henrik Hansen
  • 109
  • 1
  • 8
  • Possible duplicate of [How to get rid of MySQL error 'Prepared statement needs to be re-prepared'](https://stackoverflow.com/questions/4380813/how-to-get-rid-of-mysql-error-prepared-statement-needs-to-be-re-prepared) – sh1hab Oct 22 '19 at 06:57
  • This does not solve this problem - even one of the error messages is same. – Henrik Hansen Oct 22 '19 at 06:59
  • 1
    the duplicate suggest that the cause might be anything from: 1) bug, 2) configuration, 3) an actual need to re-prepare the query, 4) hosting. we cant exactly help the problem as its pretty wide.. – Bagus Tesa Oct 22 '19 at 07:05
  • While I can't speak for MariaDB specifically, I would observe that views in MySQL offer remarkably limited benefit, given their inability to utilise underlying indexes. Also, 100 tables is quite a lot - possibly large enough to be indicative of poor design. – Strawberry Oct 22 '19 at 07:22
  • 100 tables is not a lot for a large comprehensive application. Professional systems will easily have thousands. InnoDB has a limit of 4 billion tables. But again, the problem is not the tables but the views. And views in MariaDB are able to utilise the underlying indexes if you do not "force" MariaDB to create a temporary table. This is actually the reason for the large number of views as views on views will easily create this situation. – Henrik Hansen Oct 22 '19 at 07:27
  • Which version? I think there is a setting for number of prepared statements to cache. – Rick James Oct 24 '19 at 05:57

1 Answers1

1

Check the value of max_prepared_stmt_count It defaults to about 16K, so I don't think it is the problem.

Do you do DEALLOCATE PREPARED STATEMENT when finished with a statement?

Do the VIEWs call Stored Routines?

Do you have the Query cache turned on?

What is the value of table_open_cache?

SHOW GLOBAL STATUS LIKE 'Subquery_cache%';

Rick James
  • 135,179
  • 13
  • 127
  • 222