toddstoffel I assign it to you, so you can rank priority and determine which version it should be.

If we run a select query without limit option, against to big table is causing DB slowness, so please make sure that auto limit option should be added before hitting to the DB.

Can we have a quick fix for this issue, at least in the next release? We are planning to give it to the other development teams but since its a production DB, without fixing this bug, we can't give it to them. Can you please check on this bug and make it forward to the next release would be good? So that we will plan accordingly.

Thanks in advance.

Can we fix this in the next release please ?

Hi Naresh, the bug for preview data without specifying LIMIT is fixed in 6.2.0 which would probably be released soon. However, to automatically inject the LIMIT clause to every statement that is written by the user in the SQL editor, that required us to refactor the way we implemented and that takes time to consider.
Thank you for your patience.

Hi Duong,

Thanks for the update.

Seems this is not fixed in the 6.2.0 version, can we take-up this issue in the next release version would be very helpful. So, that we can plan for the Production environment.

toddstoffel, I think we can have this for the 6.3 release. Though this's not trivial, to enforce the LIMIT clause to every statement, we need to split the query into statements which is the hardest part as we need to handle the use of DELIMITER.

Hi Todd,

Can you please take up this as a priority would be very helpful for us, as long as its not fixed then we cant use the SQL editor till it get fixed for Production. Please give priority to this issue.

Hi Todd,

Can you please take up this as a priority, as long as its not fixed then we cant use the SQL editor till it get fixed for Production. Please give priority to this issue.

naresh.chandra@copart.com We have https://mariadb.com/docs/reference/mdb/system-variables/sql_select_limit/, would this solve your issue?
Alternatively, we can still add the Limit dropdown with some default value and customize ability in the GUI.

Hi Duong,

Thanks for the update.
We have already checked from DB end, but it was not solving our problems and App users and few internal users affecting if we enable this option.

Please implement in the Maxscale would be good for us.

Would it be okay if we internally implement current "Max rows" value to behave like `sql_select_limit` i.e. when `Max rows` value changes, it internally execute `SET sql_select_limit= new_max_rows` ?

Hi Duong,

Not sure, but can you confirm whether this option automatically add limit to the select query? or after fetching the total rows then it will filter with this option? can you confirm on this?

For example:
sql_select_limit = 10
Actual table count is 100 Million rows.

Then If I use this option then, will it scan/read all the 100 Million rows then return 10 rows or will it directly scan/read first 10 rows and then return 10 rows.

I have below queries
1. If we join with multiple tables, then how it behaves we don't know?
2. If its stored procedure how it works?
3. If we specify limit option then how we can pass the query?
4. If its view then how it works we don't know?

Can you please test all the above scenarios, if I miss anything then add other scenarios test it and if works fine then we are good.
NOTE:
1. This option should run in the session level not global level.
EX: set session sql_select_limit =1000;

2. It should work with read only user and should be work with PAM user(AD authentication).

3. it should work pagination like first page 1 - 1000, next pagination 1000 - 2000 something like that. so that first time it will hit and fetch 1 to 1000 records and 2nd time it will hit and fetch 1000 to 2000 records like that.

naresh.chandra@copart.com, since `sql_select_limit` is already implemented in the server, can you open a support ticket for the usage of the `sql_select_limit`? Some of our support engineers will help you clarify it. Thank you

Hi Duong,

I will request to MariaDB Team but below things needs to taken care by you, can you please check on the below points.?

1. If user specify limit option in the Query editor then how we can pass the query?
2. This option should run in the session level not global level.
EX: set session sql_select_limit =1000;
3. It should work with read only user and should be work with PAM user(AD authentication).
4. it should work pagination like first page 1 - 1000, next pagination 1000 - 2000 something like that. so that first time it will hit and fetch 1 to 1000 records and 2nd time it will hit and fetch 1000 to 2000 records like that.
5. Please test with Stored procedure, joins with multiple tables and and views, this needs to be testing after implementation of the above.

naresh.chandra@copart.com,
"sql_select_limit acts as an automatic LIMIT row_count to any SELECT query." as it's stated here: https://mariadb.com/kb/en/query-limits-and-timeouts/#sql_select_limit.

1. If the user uses the LIMIT clause with a value smaller than the value of `sql_select_limit`, it works just fine. But if the value is larger than that of `sql_select_limit`, it uses the value of `sql_select_limit` and this is expected behavior. The user can then increase the value of `sql_select_limit`.
2. Yes, we can do that
3. Yes, it works for read-only users. For PAM users, I haven't checked but believe it should be fine.
4. yes, since it acts as an automatic LIMIT clause.
5. yes, since it acts as an automatic LIMIT clause. `sql_select_limit` actually works better than attempting to inject `LIMIT` as it's impossible for the GUI to inject LIMIT to a created stored procedure.

Hi Duong,

Thanks for the update.
We got the reply from MariaDB and we are good with this option as per their suggestion.

Please go ahead and implement this option.

If anything need then please refer the CASE(CS0371064) which we raised to MariaDB Team.

Except SP, everything looks good.

> 2. If its stored procedure how it works?

sql_select_limit will not work for procedures (it is call() not select),.
also not for the select in the the SP, you have to define it in the SP.

*******************************
DELIMITER //

CREATE PROCEDURE p2 (

)
BEGIN
set sql_select_limit = 2;
SELECT * from t1;
END;
//

DELIMITER ;
************************************

Since we're going to make `max_rows` behave likes `sql_select_limit`, this seems to be a bug fix. So I'll change its type and the fix version.

Hi Duong,

Thanks for the update. Any ETA for 6.2.1 Release ? can we expect this 6.2.1 release in this month?

naresh.chandra@copart.com As far as I know, no ETA at the moment.

Hi Duong,

Thanks for the update.