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. :)

About the Author

author image
mkyong
Founder of Mkyong.com, love Java and open source stuff. Follow him on Twitter, or befriend him on Facebook or Google Plus. If you like my tutorials, consider make a donation to these charities.

Comments

avatar
6 Comment threads
5 Thread replies
1 Followers
 
Most reacted comment
Hottest comment thread
9 Comment authors
tonyRichard H TraubthaotrinhFahima MokhtariAvery Horton Recent comment authors
newest oldest most voted
trackback
MySQL Error – exceeded the ‘max_questions’ resource (current value: 1000) | MySQL

[…] How to modify the ‘max_questions’ resource value in MySQL? […]

tony
Guest
tony

Thanks!! Nice information!!

thaotrinh
Guest
thaotrinh

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!

Richard H Traub
Guest
Richard H Traub

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.

Avery Horton
Guest
Avery Horton

Thanks… This was driving me crazy.

al
Guest
al

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 :(

Fahima Mokhtari
Guest
Fahima Mokhtari

same here :(

thaotrinh
Guest
thaotrinh

same here :(

Carlos Ramos
Guest
Carlos Ramos

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.