Main Tutorials

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 Author

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

Comments

Subscribe
Notify of
12 Comments
Most Voted
Newest Oldest
Inline Feedbacks
View all comments
Louis
4 years ago

Thanks Mkyong, it’s very goog

tony
4 years ago

Thanks!! Nice information!!

thaotrinh
6 years ago

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
5 years ago
Reply to  thaotrinh

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
8 years ago

Thanks… This was driving me crazy.

al
9 years ago

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 🙁

thaotrinh
6 years ago
Reply to  al

same here 🙁

Ary
4 years ago
Reply to  thaotrinh

Did you get the answer?

Fahima Mokhtari
7 years ago
Reply to  al

same here 🙁

Carlos Ramos
12 years ago

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.

Rain
10 years ago
Reply to  mkyong

Hi mkyong, where I can find the MySQL documentation that says 0 value means unlimited max_questions?