Can I set a row-limit for query results that throws an ERROR if the result set exceeds that limit?

Question

I'm looking for a way to protect my database against runaway queries issued by an application. I have control over both the application and the database. It occurs to me that I can protect against two specific things relatively easily:

1. Set a max_statement using GRANT ... MAX_STATEMENT_TIME for the application user.
2. Set sql_select_limit for each connection.

When testing, I can see that if I set a max_statement_time of 5 (seconds), if I execute a SELECT SLEEP(10), I end up with an error like this:

Query execution was interrupted (max_statement_time exceeded)

But if I perform a SELECT which should return a number of rows that exceeds some value (e.g. 1000), I get this result:

mysql> set sql_select_limit = 1000;
Query OK, 0 rows affected (0.000 sec)

mysql> SELECT * FROM largetable;

....
....
1000 rows in set (0.010 sec)

mysql> show warnings;
Empty set (0.000 sec)

So while I get the "protection" of limiting my query results to 1000 rows, I think what I really want is an error to be thrown in these cases, so we can identify them in the application and fix them.

Is there anything in MariaDB that is like sql_select_limit but throws an error rather than simply limiting the size of the result set?

Answer 1

The purpose of MAX_STATEMENT_TIME is to protect the system, because MySQL does not know how much time it will take to execute this statement, and the system may be in an unknown state at this time, so it is reasonable to design it as “return error”.

And sql_select_limit is a clear query statement, and it is safe to return directly to the client when the number of rows (such as 1000) is met, so returning is more reasonable than reporting an error.

Of course, in practice, this parameter setting is not friendly to the business code. Imagine that another programmer takes over this system and may think that 1000 is the actual correct return result, which may cause problems with the subsequent processing logic.

If you have a business logic that is to achieve “report an error when exceeding sql_select_limit”, it is recommended to use "select id from largetable limit sql_select_limit+1;"

Then judge whether the "returned row_num" > sql_select_limit, and the business layer code reports an error by itself.