MySqli Error: Can't create more than max_prepared_stmt_count statements (current value: 16382)

Question

I'm getting this error when I'm using normal prepared query which is working on one server and on another is not working.

$this->PreparedQuery("UPDATE user_login SET end_time = '?' WHERE id = ?", 692233, 1);

wow. that's quite a lot. do you really need all of them 16k prepared? — Your Common Sense, Sep 20 '16 at 07:40
@YourCommonSense - Take a look at my query, there are only 2 prepared statements. This is probably mysql bug. — iWizard, Sep 20 '16 at 07:42
in fact there is only one prepared statement with only one parameter. So I suppose that there is a severe bug is not in mysqli but in that PreparedQuery thing. so you better get rid of it. — Your Common Sense, Sep 20 '16 at 07:47
perhaps you ought to post your db class &/or explain how/where you are using the above code — Professor Abronsius, Sep 20 '16 at 08:09
Can you show us the source code of `PreparedQuery` method and a little bit of context of how you use it? — Filip Halaxa, Apr 30 '20 at 17:33

score 8 · Answer 1 · answered Sep 10 '18 at 07:22

As the question is not clear.

I will post some ways to avoid this mysql error:

Check your client code, you should close the prepared stmt object after you do not need it. Please do not leak resources.
If your client code use prepared statement as cache, please close all of them after the program see this error. You can also try not use prepared statement as cache at all, just create one, use it, and delete it.
Use SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST find all your client code, then repeat step 1.
You can also restart mysql server to solve this problem temporary.

score 2 · Answer 2 · answered Apr 30 '20 at 17:23

2

I recently run into this problem too. Make sure you're disposing PreparedQuery or any other prepared SQL Command after execution. Disposing calls ClosePreparedStatement.

answered Apr 30 '20 at 17:23

ReneF

21
1

score 0 · Answer 3 · answered Sep 20 '16 at 07:29

0

Increase:

set global max_prepared_stmt_count = 30000;

** Do what you have to do **

Decrease again to default:

set global max_prepared_stmt_count = 16382;

answered Sep 20 '16 at 07:29

iWizard

6,816
19
67
103

you cannot set global variables from the regular (not superuser) account. thus this suggestion could be of no help in the real environment. – Your Common Sense Sep 20 '16 at 07:48
This solution will not work on real environment, as we do not have super privilages . – Amit Agarwal Jul 11 '18 at 10:46

MySqli Error: Can't create more than max_prepared_stmt_count statements (current value: 16382)

3 Answers3