Query Timeout, A Gopher Perspective
MySQL Query Timeout
I once wondered if I can put a timeout constraint on my SQL queries and YES, here is the process if you want to try it too.
Table of Content
- Setting Up a MySQL Database in Docker
- Fill Database with Data
- Set Constraint on the Query
- Set Constraint on the Database
Setting Up a MySQL Database in Docker
First of all, we need a mySQL database to put our constraint on. I prefer to set up a database in docker, so we don't care what your os is.
inside the bash of the container, run below commands
Fill Database with Data
You can manually create a table inside the database, and fill it in many ways but preferred to use `migrations` to create
the table and write a simple Golang code to fill it.
inside the file with the extension up. SQL just wrote
It's a good idea to have even more columns. Below code run migration to create table
fill the table with data
I decided to put 5000 records to the table, but you can do what you want.
Set Constraint on the Query
If you are coding in Golang like me one the approaches I can suggest is using contexts:
If the query takes longer than the time you set, this error will be returned.
context deadline exceeded
Another way is to specify the constraint inside the query:
After getting the bash of the container and connecting to MySQL server run below query
If the time you set is not enough, you'll get this error
ERROR 3024 (HY000): Query execution was interrupted, maximum statement execution time exceeded
Now let's try the same query inside our Golang code. What do we expect??!! I think it's reasonable if it returns an error
and no rows.
Surprisingly, there were no errors
0
1
.
.
.
2826
2827
As you can see it just gave us as many records as it could in the specified time and didn't return any errors 😐😐😐.
WHY? That's because whey you write a query in MySQL client it gives you the response for example in our case the whole table at the same time OR an error but when you use libraries in your code to connect to database and write a query what it gives you back is a CURSOR, and then it uses the cursor to iterate over the records of the table you want and gives you the result, when the time limit exceeds it just finishes its job and doesn't get any error to return to you. Not knowing the point may cause bug in the behavioral of your code.
Set Constraint on the Database
We saw how to put constraint on the query but more commonly you like to put constraint on the database. You can easily use
below commands to do this.
SET SESSION MAX_EXECUTION_TIME=[time]; sets the constraint only on the specific session and
SET GLOBAL MAX_EXECUTION_TIME=[time]; sets the constraint on all the sessions.
Note: The results after setting these constraints are completely predictable, I just want to mention a point which may confuse you.Suppose you have a session to your mysql server and want to put a `GLOBAL` constraint on it after setting the constraint, it is not applied to the current session if you close the current connection and open a new one you see that everything works fine and as expected.
Update
Now let's see how we can put timeout constraint on database query when we're using sqlx or gorm(both slqx and gorm are popular libraries for communication with database in golang)
SQLX
Everything I said about SQL **remains exactly the same** for this part. So you can both put `/*+ MAX_EXECUTION_TIME([time]) */` inside the query or use `QueryxContext`.
Gorm
In gorm version 2 you can put timeout constraint on queries using `WithContext` but in Version 1 the only way you can do it is by using transactions
