App throws 500 due to max_prepared_stmt_count

Hello,

I've been developing my Laravel app used together with Backpack. Since today, all endpoints throw 500 SQLSTATE[42000]: Syntax error or access violation: 1461 Can't create more than max_prepared_stmt_count statements (current value: 16382).

This is in my main app as well as /admin/ endpoint where it e.g. redirects me to login page and says

SQLSTATE[42000]: Syntax error or access violation: 1461 Can't create more than max_prepared_stmt_count statements (current value: 16382) (SQL: select column_name as column_name from information_schema.columns where table_schema = and table_name = users)

Everything was working well for several months. I have a very basic table setup.

When I ran SHOW GLOBAL STATUS LIKE '%comstmt%'; on MySQL, I got

Com_stmt_execute 2387953 Com_stmt_close 2217228 Com_stmt_fetch 0 Com_stmt_prepare 2846723 Com_stmt_reset 0 Com_stmt_send_long_data 25094 Com_stmt_reprepare 458707

Can this be MySQL server issue or what can possibly have caused so many prepared statements to hang unclosed? I was under impression they should be tied to a session and I don't have any long-running job active, just basic views and Backpack.

Thanks for any help

jlrdw

3 years ago

Level 75

Are you running code in a loop and check if you are incurring the n+1 problem.

mixerx

3 years ago

Level 1

@jlrdw I added a DB logger to AppServiceProvider. The DB queries on application startup look perfectly normal, both in my front-end and Backpack.

[previous exception] [object] (PDOException(code: 42000): SQLSTATE[42000]: Syntax error or access violation: 1461 Can't create more than max_prepared_stmt_count statements (current value: 16382) at apppath/vendor/laravel/framework/src/Illuminate/Database/Connectors/MySqlConnector.php:76) [stacktrace] #0 apppath/vendor/laravel/framework/src/Illuminate/Database/Connectors/MySqlConnector.php(76): PDO->prepare('set names 'utf8...') #1 apppath/vendor/laravel/framework/src/Illuminate/Database/Connectors/MySqlConnector.php(32): Illuminate\Database\Connectors\MySqlConnector->configureEncoding(Object(PDO), Array) #2 apppath/vendor/laravel/framework/src/Illuminate/Database/Connectors/ConnectionFactory.php(184): Illuminate\Database\Connectors\MySqlConnector->connect(Array) [2022-12-12 16:28:14] production.ERROR: SQLSTATE[42000]: Syntax error or access violation: 1461 Can't create more than max_prepared_stmt_count statements (current value: 16382) (SQL: select * from users where id = 1 limit 1) {"view":{"view":"/apppath/resources/views/errors/layout.blade.php"

My suspicion is whether Backpack properly closes all the prepared statements it uses. I don't see any other explanation as how the app could possibly have exceeded the limit. I have used Backpack heavily in the past days and if all the used prepared insert statements remained cached, then that would be the issue.

Has anyone come across a similar problem? Is there any way how to force cleanup of this in MySQL? For now, I had to use PDO::ATTR_EMULATE_PREPARES => true which is not optimal.

mixerx

3 years ago

Level 1

To be more specific, these are the queries from Backpack startup:

[2022-12-12 22:36:45] production.DEBUG: select * from users where id = ? limit 1 [2022-12-12 22:36:45] production.DEBUG: array ( 0 => 1, ) [2022-12-12 22:36:45] production.DEBUG: select column_name as column_name from information_schema.columns where table_schema = ? and table_name = ? [2022-12-12 22:36:45] production.DEBUG: array ( 0 => 'x8lytlkf', 1 => 'users', )

Which in my case now only work when I set PDO::ATTR_EMULATE_PREPARES => true. Otherwise I receive max_prepared_stmt_count exceeded error. Any direction to investigate would be much appreciated.

Please or to participate in this conversation.