MySQL Bugs: #24243: max_questions resourse limit does not have effect on "select * from tbl

Bug #24243	max_questions resourse limit does not have effect on "select * from tbl_name"
Submitted:	13 Nov 2006 8:51	Modified:	15 Nov 2006 18:57
Reporter:	Rumen Telbizov	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S1 (Critical)
Version:	MySQL 5.0.27, 4.0.27	OS:	FreeBSD (FreeBSD 6.2 , Linux 2.6 kernel)
Assigned to:	Paul DuBois	CPU Architecture:	Any

Description:
As the synopsis says:

The max_questions resourse limit does not have effect on "select * from tbl_name"  ...  and probably others.

Here is the problem:

I have a user with limited number of queries per hour:

(as mysql root user)

mysql> select user, max_questions from user where user='altares';
+---------+---------------+
| user    | max_questions |
+---------+---------------+
| altares |             2 |
+---------+---------------+

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql> use altares;
Database changed

mysql> select * from test;
+----+-------------+
| id | txt         |
+----+-------------+
|  1 | First line  |
|  2 | Second line |
|  3 | Third line  |
+----+-------------+

(as user altares)

mysql> select 1+1;
+-----+
| 1+1 |
+-----+
|   2 |
+-----+
(first query)

mysql> select 2+2;
+-----+
| 2+2 |
+-----+
|   4 |
+-----+
(second query)

mysql> select 3+3;
ERROR 1226 (42000): User 'altares' has exceeded the 'max_questions' resource (current value: 2)

Ok, so far so good.

... user altares keeps issuing commands ...

mysql> select * from test;
+----+-------------+
| id | txt         |
+----+-------------+
|  1 | First line  |
|  2 | Second line |
|  3 | Third line  |
+----+-------------+

A query was allowed _after_ User 'altares' has exceeded the 'max_questions' !!!

mysql> select * from test where id=1;
ERROR 1226 (42000): User 'altares' has exceeded the 'max_questions' resource (current value: 2)

Where clause is good ...

mysql> select * from test;
+----+-------------+
| id | txt         |
+----+-------------+
|  1 | First line  |
|  2 | Second line |
|  3 | Third line  |
+----+-------------+

Again success!

mysql> select id, txt from test;
ERROR 1226 (42000): User 'altares' has exceeded the 'max_questions' resource (current value: 2)

Aside from the "select * from tbl_name" not catched withing the limit,
I have also found that the following statements also succeed from time to time:

select count(*) from tbl_name;
select * from tbl_name where 1;

And sometimes they don't.

I do not have MySQL 4.1.x server nearby so I did not test on 4.1 version
but I suppose that this version is also affected!

If you have any additional questions for me - let me know.

Best regards
Rumen Telbizov

How to repeat:
Please see description

Suggested fix:
Add "select * from tbl_name" into the resourse limit count and also
do a review of the resourse limit code because something is wrong!

Hi again,

Thanks to Nikolay Bachiyski for pointing the following out.

It seems that the limit is not taken into account if the query is
already in the cache. In this case the result is simply returned to the
client and the counter is not checked at all.

Is this supposed to be the correct behaviour?
Shouldn't it be noted in the documentation?

--- quote --

if (query_cache_send_result_to_client(thd, inBuf, length) <= 0)
  {
    LEX *lex=lex_start(thd, (uchar*) inBuf, length);
    if (!yyparse() && ! thd->fatal_error)
    {
      if (mqh_used && thd->user_connect &&
          check_mqh(thd, thd->lex.sql_command))

--- quote --

Best regards
Rumen Telbizov

I believe it's the intended behaviour. Note that when a result of a select is taken from the cache, Com_select status variable is not increased either.

Ok,

Still it would be nice have this in the documentation.

Just one final thought:
Are you sure that 'max_questions' is supposed to limit the number
of non-cached queries only?

Consider this:
A broken script or a malicious user starts issuing the same (cached)
query in a loop without sleep or any delay. This still causes some load
on the server. If you multiply this by as many processes that this user
might spawn and you have a DoS scenario that you cannot limit using
the max_questions!

Best regards
Rumen Telbizov

Thank you for the bug report.

Thank you for your bug report. This issue has been addressed in the documentation. The updated documentation will appear on our website shortly, and will be included in the next release of the relevant products.

I've added a note to the sections on user resources and the
GRANT statement:

Queries for which results are served from the query cache do not
count against the MAX_QUERIES_PER_HOUR limit.