How to modify the ‘max_questions’ resource value in MySQL?
Often times, the following error is happened. This is due to the MySQL resource monitor feature, the “max_questions” means “Number of queries the user can execute within one hour”.
MySQL Error – exceeded the ‘max_questions’ resource (current value: 1000)
Here i will demonstrate how to update or modify the ‘max_questions’ value in MySQL.
1) Log into MySQL console, i’m root ~
mkyong@myserver:~# mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1640
Server version: 5.0.32-Debian_7etch10-log Debian etch distribution
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql>
2) Switch to mysql database.
mysql> use mysql
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql>
3) Retrieve the user information from MySQL user’s table.
mysql> select user, max_questions from user;
+------------------+---------------+
| user | max_questions |
+------------------+---------------+
| root | 0 |
| root | 0 |
| debian-sys-maint | 0 |
| root | 0 |
| mkyong | 1000 |
+------------------+---------------+
5 rows in set (0.00 sec)
mysql>
3) Update the max_question value, 0 means unlimited
mysql> update user set max_questions = 0 where user = 'mkyong';
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1 Changed: 0 Warnings: 0
mysql>
4) Flush the privileges to make the changes take affect.
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
mysql>
5) Done. User’s mkyong has unlimited queries access to database. 🙂
Thanks Mkyong, it’s very goog
Thanks!! Nice information!!
Hi, Mkyong.
In Step 3 – Retrieve the user information from MySQL user’s table:
When I ran command “select user, max_questions from user;”
I had the error: ERROR 1226 (42000): User ‘root’ has exceeded the ‘max_questions’ resource (current value: 10)
I had tried to run another command, It always returns the same error.
Please help me!
Hi, thaotrinh,
I stopped this circular error from generating by adding to the server’s my.ini file the line
max_questions=0
immediately below the existing line
max_connections
Adding that line allowed me then to apply Mkyong’s above steps to change the current value for ‘root’ (60 in my case) to 0. Having confirmed the change – by re-performing the “select user, max_questions from user;” command – I deleted my inserted line from the my.ini All working fine now.
Hope this works for you, too.
Thanks… This was driving me crazy.
I’m logged in as root, but as i try to run the first select statement it gives me the same 1226 error saying that I’ve exceeded my questions 🙁
same here 🙁
Did you get the answer?
same here 🙁
Hi Mkyong.
Is it possible to this using phpmyadmin?
My site is hosted in athird party server and looks like the only access I have to mysql is thru phpmyadmin.
Thans in advance for your help.
Don’t think is there a option in phpmyadim, you can contact your hosting support to configure it.
Hi mkyong, where I can find the MySQL documentation that says 0 value means unlimited max_questions?