[#MXS-3952] SQL editor Don't Limit option Issue, So implement Offset Feature Like Range Option

[MXS-3952] SQL editor Don't Limit option Issue, So implement Offset Feature Like Range Option Created: 2022-01-20 Updated: 2023-12-15
Status:	Open
Project:	MariaDB MaxScale
Component/s:	maxgui
Affects Version/s:	6.2.1
Fix Version/s:	Icebox

Type:

New Feature

Priority:

Major

Reporter:

Naresh Chandra

Assignee:

Joe Cotellese

Resolution:

Unresolved

Votes:

Labels:

None

Attachments:

image-2022-01-20-11-51-22-604.png

image-2022-01-20-11-54-15-191.png

Description

Hi Team,

After the SQL LIMIT issue[ ~~MXS-3841~~ ] is fixed then we have upgraded to 6.2.1 version. But observed the below issue.

1. Remove the Max Rows option(drop down lis) in the GUI or change it like below SQL yog screenshot option. Can you please change the Max Rows UI to range, like first row and then last row number but default is 0 to 1000/10000. And keep the no limit check box separately if possible.
NOTE: But don't remove the max_rows=Don't Limit option, please keep it as a separate check box like SQLyog.
So that user can enter the start value and end value like SQL yog option below.

Comments

Comment by markus makela [ 2022-01-20 ]

Does the MaxScale log report any errors? If it does, please include the relevant parts here.

Comment by Naresh Chandra [ 2022-01-20 ]

Hi Markus,

I did not get any errors in maxscale log.
But I can see the OOM errors in the system log file.

Jan 19 17:51:26 test123 maxscale: Accept authentication from 'admin', using password. Request: /auth
Jan 19 17:54:01 test123 systemd-logind: Removed session 3626.
Jan 19 17:58:06 test123 kernel: maxscale invoked oom-killer: gfp_mask=0x201da, order=0, oom_score_adj=0
Jan 19 17:58:06 test123 kernel: maxscale cpuset=/ mems_allowed=0
Jan 19 17:58:06 test123 kernel: CPU: 3 PID: 33019 Comm: maxscale Kdump: loaded Tainted: P E ------------ 3.10.0-1160.36.2.el7.x86_64 #1
Jan 19 17:58:06 test123 kernel: Hardware name: VMware, Inc. VMware Virtual Platform/440BX Desktop Reference Platform, BIOS 6.00 11/12/2020
Jan 19 17:58:06 test123 kernel: Call Trace:
Jan 19 17:58:06 test123 kernel: [<ffffffffa1b83559>] dump_stack+0x19/0x1b
Jan 19 17:58:06 test123 kernel: [<ffffffffa1b7e5f8>] dump_header+0x90/0x229
Jan 19 17:58:06 test123 kernel: [<ffffffffa1506972>] ? ktime_get_ts64+0x52/0xf0
Jan 19 17:58:06 test123 kernel: [<ffffffffa155dfff>] ? delayacct_end+0x8f/0xb0
Jan 19 17:58:06 test123 kernel: [<ffffffffa15c252d>] oom_kill_process+0x2cd/0x490
Jan 19 17:58:06 test123 kernel: [<ffffffffa15c1f1d>] ? oom_unkillable_task+0xcd/0x120
Jan 19 17:58:06 test123 kernel: [<ffffffffa15c2c1a>] out_of_memory+0x31a/0x500
Jan 19 17:58:06 test123 kernel: [<ffffffffa15c9834>] __alloc_pages_nodemask+0xad4/0xbe0
Jan 19 17:58:06 test123 kernel: [<ffffffffa1619388>] alloc_pages_current+0x98/0x110
Jan 19 17:58:06 test123 kernel: [<ffffffffa15bdfe7>] __page_cache_alloc+0x97/0xb0
Jan 19 17:58:06 test123 kernel: [<ffffffffa15c0f80>] filemap_fault+0x270/0x420
Jan 19 17:58:06 test123 kernel: [<ffffffffc048791e>] __xfs_filemap_fault+0x7e/0x1d0 [xfs]
Jan 19 17:58:06 test123 kernel: [<ffffffffc0487b1c>] xfs_filemap_fault+0x2c/0x30 [xfs]
Jan 19 17:58:06 test123 kernel: [<ffffffffa15ee78a>] __do_fault.isra.61+0x8a/0x100
Jan 19 17:58:06 test123 kernel: [<ffffffffa15eed3c>] do_read_fault.isra.63+0x4c/0x1b0
Jan 19 17:58:06 test123 kernel: [<ffffffffa15f6580>] handle_mm_fault+0xa20/0xfb0
Jan 19 17:58:06 test123 kernel: [<ffffffffa1b90653>] __do_page_fault+0x213/0x500
Jan 19 17:58:06 test123 kernel: [<ffffffffa1b90975>] do_page_fault+0x35/0x90
Jan 19 17:58:06 test123 kernel: [<ffffffffa1b8c778>] page_fault+0x28/0x30
Jan 19 17:58:06 test123 kernel: Mem-Info:
Jan 19 17:58:06 test123 kernel: active_anon:1585725 inactive_anon:310693 isolated_anon:0#012 active_file:0 inactive_file:0 isolated_file:43#012 unevictable:1 dirty:0 writeback:0 unstable:0#012 slab_reclaimable:8213 slab_unreclaimable:15236#012 mapped:631 shmem:595 pagetables:7832 bounce:0#012 free:28254 free_pcp:491 free_cma:0
Jan 19 17:58:06 test123 kernel: Node 0 DMA free:15872kB min:132kB low:164kB high:196kB active_anon:0kB inactive_anon:0kB active_file:0kB inactive_file:0kB unevictable:0kB isolated(anon):0kB isolated(file):0kB present:15988kB managed:15904kB mlocked:0kB dirty:0kB writeback:0kB mapped:0kB shmem:0kB slab_reclaimable:0kB slab_unreclaimable:32kB kernel_stack:0kB pagetables:0kB unstable:0kB bounce:0kB free_pcp:0kB local_pcp:0kB free_cma:0kB writeback_tmp:0kB pages_scanned:0 all_unreclaimable? yes
Jan 19 17:58:06 test123 kernel: lowmem_reserve[]: 0 2830 7787 7787
Jan 19 17:58:06 test123 kernel: Node 0 DMA32 free:50436kB min:24524kB low:30652kB high:36784kB active_anon:2230216kB inactive_anon:557536kB active_file:0kB inactive_file:0kB unevictable:4kB isolated(anon):0kB isolated(file):0kB present:3129152kB managed:2898720kB mlocked:4kB dirty:0kB writeback:0kB mapped:1496kB shmem:1420kB slab_reclaimable:8696kB slab_unreclaimable:14656kB kernel_stack:1200kB pagetables:10060kB unstable:0kB bounce:0kB free_pcp:1032kB local_pcp:696kB free_cma:0kB writeback_tmp:0kB pages_scanned:19 all_unreclaimable? yes
Jan 19 17:58:06 test123 kernel: lowmem_reserve[]: 0 0 4956 4956
Jan 19 17:58:06 test123 kernel: Node 0 Normal free:46708kB min:42920kB low:53648kB high:64380kB active_anon:4112684kB inactive_anon:685236kB active_file:0kB inactive_file:0kB unevictable:0kB isolated(anon):0kB isolated(file):172kB present:5242880kB managed:5075424kB mlocked:0kB dirty:0kB writeback:0kB mapped:1028kB shmem:960kB slab_reclaimable:24156kB slab_unreclaimable:46256kB kernel_stack:6160kB pagetables:21268kB unstable:0kB bounce:0kB free_pcp:932kB local_pcp:632kB free_cma:0kB writeback_tmp:0kB pages_scanned:0 all_unreclaimable? yes
Jan 19 17:58:06 test123 kernel: lowmem_reserve[]: 0 0 0 0
Jan 19 17:58:06 test123 kernel: Node 0 DMA: 0*4kB 0*8kB 0*16kB 0*32kB 2*64kB (U) 1*128kB (U) 1*256kB (U) 0*512kB 1*1024kB (U) 1*2048kB (M) 3*4096kB (M) = 15872kB

Comment by markus makela [ 2022-01-20 ]

Can you clarify what you mean by this statement: If we use Don't Limit option in the SQL editor then Maxscale is getting restarted , seems memory error

If MaxScale is indeed restarted, it means there is either a corresponding entry in /var/log/maxscale/maxscale.log or in the SystemD journal. To get the last 300 lines of the journal, something like this should work:

journalctl -n 300 -u maxscale

Comment by Naresh Chandra [ 2022-01-20 ]

Hi Markus,
>> Can you clarify what you mean by this statement: If we use Don't Limit option in the SQL editor then Maxscale is getting restarted , seems memory error
– If select Don't Limit in the SQL editor then if we do selects on the large tables so maxscale is restarting due to the memory issue.

I executed the above command but I don't see any error there. but i found error in the system log.

Comment by markus makela [ 2022-01-20 ]

OK, now the comment makes sense to me. The reason is that the server where MaxScale is runs out of memory and proceeds to kill MaxScale. This does reveal that it it might be pretty easy to cause MaxScale to run out of memory if you do things that you shouldn't. Having a hard cap on the result size that's only modifiable with admin credentials would fix that.

As for issue 2, can you describe that problem a bit more? I'm not sure if I understand what the exact problem is. When you execute the query with the limit enabled, does it return a result to the GUI?

Comment by Naresh Chandra [ 2022-01-20 ]

>> OK, now the comment makes sense to me. The reason is that the server where MaxScale is runs out of memory and proceeds to kill MaxScale. This does reveal that it it might be pretty easy to cause MaxScale to run out of memory if you do things that you shouldn't. Having a hard cap on the result size that's only modifiable with admin credentials would fix that.
– Yes, if we allow only for admin and restrict for read_only users then it would fix our issue.

>> As for issue 2, can you describe that problem a bit more? I'm not sure if I understand what the exact problem is. When you execute the query with the limit enabled, does it return a result to the GUI?
– For, example if we run select * from table; statement then login to DB there we are unable to see what query is running in the DB process list. In the previous version(6.2.0) we were able to see what query is running in the DB process list. But in the 6.2.1 version we are seeing null query in the process list, which we have executed from the Maxscale Query editor.

Comment by markus makela [ 2022-01-20 ]

What does that query return in the GUI? Does it return an error or a resultset?
How many rows are in that table and what is the limit you have set?
Does MaxScale or the database log any errors when this happens?

Comment by Naresh Chandra [ 2022-01-20 ]

>>What does that query return in the GUI? Does it return an error or a resultset?
– No, Its not giving any error and the query is like Select * from t1; simple query only. But we are able to get the rows in the GUI.

>>How many rows are in that table and what is the limit you have set?
– Around 10 Millions, even 1000 rows tables also we are not seeing the query in the show processlist;

>> Does MaxScale or the database log any errors when this happens?
– No, we don't see any errors, seems query is hiding but when we select Don't Limit option then we are able to see the select query in the DB process list.

Comment by markus makela [ 2022-01-20 ]

How many rows are in the result that is in the GUI? Is it the same amount that you selected for the row limit?

Comment by Naresh Chandra [ 2022-01-20 ]

CASE1; If we select max_rows option to 10000, its giving output only 10000 rows. but we cant see query in the DB process list.

CASE1; If we select max_rows option to 10000 or any number, its giving output only 10000 rows or give number. but we cant see query in the DB process list which we written on the Query Editor.
NOTE: If select max_rows with any number , we are unable to see the query in the DB process list but we are getting the result set in the query editor.

CASE2: If select max_rows option to Don't Limit option then its giving the total rows if its a small table. but we can see the query in the DB process list which we written on the Query Editor.

CASE3: If select max_rows option to Don't Limit option then Maxscale is crashing if its a big table. but we can see the query in the DB process list which we written on the Query Editor.

Comment by markus makela [ 2022-01-20 ]

How long does it take for the query to complete when you have the row limit set? If it takes a short time, how do you know if it was executed or not?

Comment by Naresh Chandra [ 2022-01-20 ]

>> How long does it take for the query to complete when you have the row limit set? If it takes a short time, how do you know if it was executed or not?
– To check this, I am selecting some big table and I am monitoring the Query editor and DB process list at the same time. So, that query editor still loading and in the DB process list showing null query.

Comment by markus makela [ 2022-01-20 ]

You can more accurately measure the time it takes by using the command line client (mariadb or mysql) and then executing SET sql_select_limit=10000 followed by your query. This is what MaxScale does and there might be some processing lag in the GUI as well as MaxScale itself.

Another option would be to enable logging in the database that logs queries and how long they took.

If the resultset arrives in the GUI and it contains the correct data then we can only accept that it was executed on the database. The fact that it doesn't show up in the processlist must mean that it was too fast to be seen there. You can slow down the query by adding SLEEP(5) as one of the values in the query.

Comment by Naresh Chandra [ 2022-01-20 ]

sure, let me check it with your inputs.

Comment by Naresh Chandra [ 2022-01-20 ]

Hi Markus,

Seems my assumption was wrong, so we can ignore the 2nd point.

Comment by Naresh Chandra [ 2022-01-20 ]

Hi Markus,

I have changed the points in the description, now can we check on the below two points.
NOTE: Now don't remove the max_rows=Don't Limit option, possible keep it a separate check box like SQLyog.

1. Remove the Max Rows option(drop down lis) in the GUI or change it like below SQL yog screenshot option. Can you please change the Max Rows UI to range, like first row and then last row number but default is 0 to 1000/10000. And keep the no limit check box separately if possible.
So that user can enter the start value and end value like SQL yog option below.

2. If we change the Max_Rows to Don't Limit option instead of the number then after logout and login also its still in Don't Limit, as expected after logout it should automatically change the max_rows to default 10000 limit. When user session is closed/logout then automatically it should set the max_rows value to 10000 in the GUI.

Comment by markus makela [ 2022-01-20 ]

As the row limit is implemented using sql_select_limit, it can only limit the total number of rows and not the offset at which it starts. For the time being, you're better off explicitly adding the LIMIT clause into the SQL as it serves a functional purpose.

Since there is no real bug and this is expected behavior, I'll change this to a feature request.

Comment by Naresh Chandra [ 2022-01-20 ]

Yeah sure, Thanks for the update.

Comment by markus makela [ 2022-01-20 ]

We can add a new parameter that always sets sql_select_limit for all queries unless the user asks for a lower limit. This should make it harder for users to cause problems that might affect MaxScale.

Generated at Thu Feb 08 12:54:53 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.

[MXS-3952] SQL editor Don't Limit option Issue, So implement Offset Feature Like Range Option Created: 2022-01-20 Updated: 2023-12-15