Sample Header Ad - 728x90

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

-1 votes
1 answer
814 views
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](https://mariadb.com/kb/en/server-system-variables/#max_statement_time) using GRANT ... MAX_STATEMENT_TIME for the application user. 2. Set [sql_select_limit](https://mariadb.com/kb/en/server-system-variables/#max_statement_time) 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?
Asked by Christopher Schultz (327 rep)
Jul 24, 2023, 04:43 PM
Last activity: Mar 22, 2025, 10:04 AM