Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-28404

JSON functions don't respect KILL QUERY / max_statement_time limit #2

Details

    • Bug
    • Status: Open (View Workflow)
    • Major
    • Resolution: Unresolved
    • 10.2(EOL), 10.3(EOL), 10.4(EOL), 10.5, 10.6, 10.7(EOL), 10.8(EOL)
    • 10.6
    • JSON
    • None

    Description

      The fix for MDEV-24909 is in the main branches now, however same problem still affects various JSON-related queries/functions.

      For example,

      --source include/have_sequence.inc
      		 
       
      		 
      --connect (con1,localhost,root,,)
      		 
       
      		 
      set max_statement_time= 5;
      		 
      select concat('{',group_concat(concat('"seq',seq,'":',seq)),'}') into @json from seq_1_to_50000;
      		 
      --let $conid= `select connection_id()`
      		 
       
      		 
      send
      		 
      SELECT JSON_CONTAINS(@json, @json);
      		 
       
      		 
      --connection default
      		 
      sleep 2;
      		 
      show processlist;
      		 
      sleep 5;
      		 
      show processlist;
      		 
      sleep 5;
      		 
      show processlist;
      		 
       
      		 
      --connection con1
      		 
      --reap

      Shows this

      10.6 fae0ccad non-debug

      		 
      show processlist;
      		 
      Id	User	Host	db	Command	Time	State	Info	Progress
      		 
      4	root	localhost	test	Query	0	starting	show processlist	0.000
      		 
      5	root	localhost	test	Query	2	Executing	SELECT JSON_CONTAINS(@json, @json)	0.000
      		 
      show processlist;
      		 
      Id	User	Host	db	Command	Time	State	Info	Progress
      		 
      4	root	localhost	test	Query	0	starting	show processlist	0.000
      		 
      5	root	localhost	test	Killed	7	Executing	SELECT JSON_CONTAINS(@json, @json)	0.000
      		 
      show processlist;
      		 
      Id	User	Host	db	Command	Time	State	Info	Progress
      		 
      4	root	localhost	test	Query	0	starting	show processlist	0.000
      		 
      5	root	localhost	test	Killed	12	Executing	SELECT JSON_CONTAINS(@json, @json)	0.000

      Same with explicit KILL instead of max_statement_time.

      It doesn't hang, the server keeps processing the query at 100% CPU and eventually responds, but it takes the same time as it does if the query is executed without interruption (on my machine, it's ~1.5 min on a non-debug 10.6 build and ~3.5 min on debug).

      It's scalable, depending on the size of the JSON documents involved. Increase the number of rows selected from the sequence table if it's too fast on your machine. Watch for group_concat_max_len, you may need to set it to a non-default value if the number of rows is increased.

      Set fix version to 10.6 because MDEV-24909 was fixed in 10.6.

      Attachments

        Issue Links

          relates to

          Bug - A problem which impairs or prevents the functions of the product. MDEV-24909 JSON functions don't respect KILL QUERY / max_statement_time limit

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Activity

            People

              serg Sergei Golubchik
              elenst Elena Stepanova
              Votes:
              0 Vote for this issue
              Watchers:
              1 Start watching this issue

              Dates

                Created:
                Updated:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.