' Root ' has exceeded the ' max_questions ' resource (current value:100) (1226)

Colleague encounters a problem

Mysqldump:couldn ' t execute ' SELECT @ @collation_database ': User ' root ' has exceeded the ' max_questions ' resource (current value:100) (1226)

Follow the hint it is easy to think that max_questions value is too big, at present only 100, so error.

First think of show variables like ' R ' actually did not find, this should not be a global parameter, then where? Should be in the system table.

Then go in the database inside, want to see how many connections, show full processlist, report the same error, any SELECT statement is reported this error.

Fortunately it's just a development test library, so restarting DB directly should solve this problem. Sure enough, after restarting everything OK.

Go to MySQL library, view the user table and find the following fields

Max_questions          | int (one) unsigned                  | NO   |     | 0       |       

The value of this library setting is indeed 100, changed to 0, and the refresh permission.

In general, the user will not modify this value, 0 words is no limit, do not know who hand owes, modified this value.

As such, it should be a limit for individual users.

To resolve this problem, you should log in with another super-privileged user, modify this value directly, and then refresh the permissions without restarting the DB.

Check the official document also found that the 5.6 version of the newly added field password_expired to control password expiration.

Look at the official documents.

Mysql> Grant Usage on *. * to ' Francis ' @ ' localhost ' with  max_queries_per_hour 100;mysql> grant usage on * * to ' F Rancis ' @ ' localhost ' with  max_connections_per_hour 0;

The server stores resource limits is the account of the user table row corresponding to the account. The Max_questions, max_updates, and max_connections columns store the Per-hour limits, and the Max_user_connections column Stores the max_user_connections limit.

' Root ' has exceeded the ' max_questions ' resource (current value:100) (1226)