There are many reasons why you usually wouldn’t allow a user to access or even process a raw query directly within a web app – even administrators. Recently I had a need to do just this.
In my particular case, my need for accessing the database directly was for ease of use of creating and saving reports. This allowed those who know a little SQL to write there own reports with some ‘magic’ tools to automatically replace up to four placeholder values with input variables. No sensitive information is stored in this particular database, or at least, no sensitive information that an administrator of my app should not be able to access… this simplifies permissions for me. I opted to create a single MySQL read only user for this purpose. This at least stops someone from accidentally truncating a table or being tempted to insert and delete records directly (me included).
The second major hurdle I had was inefficient queries. It doesn’t take much for even an experienced person to create a really bad inefficient query that grinds your web application to a halt. I wanted MySQL to automatically kill a query within 1 second and simply return a timeout to the user.
I first looked at this possibility a few years ago for a different project – but didn’t find anything built out of the box – sure there are alternative solutions, but they were solutions that you had to build and manage yourself. When I started searching the interweb for this particular project I found this exact feature had been added in MySQL 5.7.
From the looks of it, this feature has evolved since it’s inception. Originally, in it’s early form from MySQL 5.7.4 and up until 5.7.7 you could set a timeout using MAX_STATEMENT_TIME and setting a value in milliseconds.
# MySQL 5.7.4 - 5.7.7 (Now Deprecated)
# Set timeout globally
SET GLOBAL MAX_STATEMENT_TIME=1000;
# Set timeout for current MySQL connection
SET SESSION MAX_STATEMENT_TIME=1000;
# Set timeout within a Select statement
SELECT MAX_STATEMENT_TIME=1000 {*} FROM {table}
Timeout using MySQL Optimizer Hints (version 5.7.8+)
Since MySQL 7.7.8 this setting was changed from MAX_STATEMENT_TIME to MAX_EXECUTION_TIME. It also appears to be now apart of MySQL’s Optimizer Hints. I’m not overly familiar yet with Optimizer hints, they were introduced into MySQL in version 5.7.7. In summary, the name says it all. Optimizer Hints allow you to provide hints to the MySQL optimizer using the /*+ … */ syntax. You can read more about Optimizer Hints from MySQL’s manual here.
# From MySQL 5.7.8
# Set timeout globally
SET GLOBAL MAX_EXECUTION_TIME=1000;
# Set timeout for current MySQL connection
SET SESSION MAX_EXECUTION_TIME=1000;
# Set timeout within a Select statement using MySQL Optimizer Hint
SELECT /*+ MAX_EXECUTION_TIME(1000) */ {*} FROM {table}
