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. 🙂