max_statement_time per Query

[NiroDeveloper]

I think there is no way at the moment to set max_statement_time per query.
(Except using raw SQL statements)

SET STATEMENT max_statement_time=100 FOR SELECT field1 FROM table_name ORDER BY field1;

https://mariadb.com/kb/en/aborting-statements/

My current use case for this:
I count all rows of my database tables for Prometheus metrics, my tables are very huge and under high load the count query needs very long. (I think because of gap locking or something like that)
If the query on the big table would be canceled after some seconds, it could still query the other tables ... now the endpoint just times out.

[juanparati]

I had just released a package that can provide max statement time per query.

See: https://github.com/juanparati/laravel-timeout

[macropay-solutions]

There is a way of handling it also via Eloquent without core changes but we did not disclose it publicly in laravel-crud-wizard-free.

[juanparati]

The laravel-timeout doesn't change the Laravel core libraries. It just inject the timeout method using a macro.

[macropay-solutions]

Your lib handles also the usage via Eloquent builder or just the query builder?

[juanparati]

Both, you can add eloquent code inside.

You can use like DB::transaction.

Example:

\DB::timeout(
    5, 
    fn() => $users = User::where('name', 'like', 'john%')->get()   
);

[macropay-solutions]

@juanparati it sets the timeout per session not per sql statement, right?

[juanparati]

Yes the timeout is set per session but after that query runs the session variable is restored. It's restored even in case that an error is raised because I throw all the exceptions after that session variable is restored.

The reason why is done at session level it's because do it at query level may require changes on the MySQL and MariaDB Laravel grammar or query builder.

The good thing of my library is that unified the same method for MariaDB and MySQL, however MariaDB is more reliable because it raise an real exception when max_statement_time is reached.