How to set `max_prepared_stmt_count` permanently on mysql server?

Question

I need to increase max_prepared_stmt_count, and I can do that easily by running

set global max_prepared_stmt_count=50000;

The problem, though, is that number gets reset to the default everytime after a reboot. I've tried adding in lines like

max_prepared_stmt_count=50000;

into various config files, but they spit out the same error:

mysql: [ERROR] unknown variable 'max_prepared_stmt_count=50000'

Anybody know what I'm doing wrong?

In what conf files do you set it and what MySQL version do you use? It should be set in /etc/my.cnf — esoroka, Nov 6 '19 at 5:06
Make sure you add the setting to the [mysqld] section. And don't add the semicolon. — wurtel, Nov 6 '19 at 7:52

Romeo Ninov · Accepted Answer · 2019-11-06 10:09:54Z

1

You should add it in /etc/my.cnf on this way:

[mysqld]
max_prepared_stmt_count = 50000

answered Nov 6 '19 at 10:09

Romeo Ninov

1,9241 gold badge8 silver badges14 bronze badges

I don't have that file. Should I create it? I do have /etc/mysq/my.cnf and /etc/mysql/mysql.cnf. – Taylor Nov 6 '19 at 13:12
edit it in /etc/mysql/mysql.cnf (if this is the config of your mysql instance) – Romeo Ninov Nov 6 '19 at 13:13
Didn't work. Still shows default variable like 16382 when I type show variables like 'max_prepared_stmt_count'; – Taylor Nov 6 '19 at 13:17
@Taylor, did you restart mysql after config file change? – Romeo Ninov Nov 6 '19 at 13:18
1

Yay, works. I typed sudo /etc/init.d/mysql restart and checked. Thanks very much – Taylor Nov 6 '19 at 13:22

| show 1 more comment

1 Answer 1