"max_questions" limit not respected when running MySQL query via PHP

Question

I'm using MySQL Community Server 5.5 with PHP 5.3.3 on a Windows Server 2008 platform. I have set up per user resources limits, particularly a limit related to the queries run by a DB user within one hour. I have read this document and it is very interesting but always it doesn't work for me.

http://dev.mysql.com/doc/refman/5.5/en/user-resources.html

When I run a query via mysql command line tool (mysql.exe) the limit works properly and if the queries limit per hour was 7, after 7 queries within one hour I receive the error:

ERROR 1226 (42000): User 'user' has exceeded the 'max_questions' resource (current value: 7)

This above is the wanted behavior.

When I run a query via PHP (The user I have used to connect to the DB is the same above), the queries limit doesn't work: the same user via php can run all the queries it wants and without any limit. And if I come back on the mysql command line above the counter seems to be reset (even if the hour hasn't elapsed) : I can run 7 queries within an hour too. I think this isn't the wanted behaviour. It seems that PHP resets the counter and doesn't trigger the queries limit.

PS: The 'user' owns only SELECT,INSERT,UPDATE,DELETE privileges and in the PHP code there isn't any SQL code as FLUSH USER_RESOURCES that the user 'user' couldn't have run (because RELOAD privilege isn't assigned to the user)

Thanks in advance

Answer 1

I have solved (partially) the problem.

The problem occurs only when the query including the MAX_QUERIES_PER_HOUR component includes also one of the following elements different from 0:

MAX_CONNECTIONS_PER_HOUR !=0
MAX_USER_CONNECTIONS !=0;

e.g. the following grant query won't ever cause the error message related to the queries limit achievement in PHP:

GRANT USAGE ON *.* TO 'user'@'localhost'
WITH MAX_QUERIES_PER_HOUR 20
MAX_UPDATES_PER_HOUR 10
MAX_CONNECTIONS_PER_HOUR 5
MAX_USER_CONNECTIONS 2;

Instead the following grant works correctly and MySQL applies the limits correctly.

GRANT USAGE ON *.* TO 'user'@'localhost'
    WITH MAX_QUERIES_PER_HOUR 20
    MAX_UPDATES_PER_HOUR 10
    MAX_CONNECTIONS_PER_HOUR 0
    MAX_USER_CONNECTIONS 0;

In short, when the query setting queries limit includes one of the components, MAX_CONNECTIONS_PER_HOUR and MAX_USER_CONNECTIONS different from 0, the limits related to the MAX_QUERIES_PER_HOUR and MAX_UPDATES_PER_HOUR are ignored: my PHP pages can perform all the queries they want. Otherwise the limit is understood and the message

has exceeded the 'max_questions' resource

correctly is showed when the queries limit achievement event occurs.

As I said above the problem doesn't occurs when I use the mysql command line tool. It occurs only when queries are run through PHP pages.

Answer 2

I think you are doing little mistake,

Please check for the host in your mysql_connect function into php. if your host is IP address then you need to assign the resource limit to username@yourip or username@%

If you are running through the command line then you are login from username@localhost and you have assign the limit to the same user.

Please try my above tricks and let me know if you are having problem still ?

Happy coding....

Answer 3

Check this tip,

how many users do you have in your mysql with the same name..(remember not only the name but the host where is permited to connect the user is specified in mysql ). **VERY IMPORTANT.

user@%
[email protected]
user@localhost
[email protected]

each one of them are not the same, if you have any limit rule do you like to use, so, you must assign limitations to the user@wherever you want the limintation. (all of them, if you have problems **think is your case).

Recomendation: create a specific user for WEB PHP (only one user@host must be created) , instead using a generic user with may have several users@hosts assigned. like phpuser@'localhost' typically is enough.