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

JSON functions don't respect KILL QUERY / max_statement_time limit

Details

    • Bug
    • Status: Closed (View Workflow)
    • Critical
    • Resolution: Fixed
    • 10.2(EOL), 10.3(EOL), 10.4(EOL), 10.5, 10.6
    • 10.6.6
    • JSON

    Description

      Some JSON functions, like JSON_MERGE, can be very lengthy if they are applied to long documents with many keys/values. Currently, if it happens, it is impossible to interrupt them – even killed or timed out, they still proceed to the end. The example below imitates merging two JSON documents, with 2000 key/value pairs in each. It takes ~25 seconds on a debug build, ~8 seconds on a release build. One can calculate how long it would take with 10,000 pairs in each of 2 documents, or with more documents to merge.

      The effect of a non-killable statement is that the server cannot be shut down normally.

      set group_concat_max_len= 4294967295;
      		 
      create or replace table t (f1 JSON, f2 JSON) ENGINE=MyISAM AS SELECT CONCAT('{',GROUP_CONCAT('"a":"',REPEAT('b',1000),'"'),'}') AS f1, CONCAT('{',GROUP_CONCAT('"c":"d"'),'}') AS f2 FROM seq_1_to_2000;
      		 
       
      		 
      # Selecting into a variable just so that the long value doesn't flood the output
      		 
      SELECT JSON_MERGE_PATCH(f1, f2) INTO @a FROM t;
      		 
       
      		 
      set max_statement_time= 3;
      		 
      SELECT JSON_MERGE_PATCH(f1, f2) INTO @b FROM t;
      		 
      select length(@b);

      10.2 067465cd

      		 
      MariaDB [test]> # Selecting into a variable just so that the long value doesn't flood the output
      		 
      MariaDB [test]> SELECT JSON_MERGE_PATCH(f1, f2) INTO @a FROM t;
      		 
      Query OK, 1 row affected (7.754 sec)
      		 
       
      		 
      MariaDB [test]> 
      MariaDB [test]> set max_statement_time= 3;
      		 
      Query OK, 0 rows affected (0.000 sec)
      		 
       
      		 
      MariaDB [test]> SELECT JSON_MERGE_PATCH(f1, f2) INTO @b FROM t;
      		 
      Query OK, 1 row affected (7.756 sec)

      From a different connection while SELECT continues running after max_statement_time:

      | 11 | root        | localhost:53558 | test | Killed  |    5 | Sending data             | SELECT JSON_MERGE_PATCH(f1, f2) INTO @b FROM t |    0.000 |

      Attachments

        Issue Links

          causes

          Bug - A problem which impairs or prevents the functions of the product. MDEV-27955 main.func_json_notembedded test fails on out-of-memory on MariaDB 10.6.7

          • Minor - Minor loss of function, or other problem where easy workaround is present.
          • Closed
          relates to

          Bug - A problem which impairs or prevents the functions of the product. MDEV-25333 Debug server is extremely slow in some JSON functions

          • Minor - Minor loss of function, or other problem where easy workaround is present.
          • Open

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

          • Major - Major loss of function.
          • Open

          Bug - A problem which impairs or prevents the functions of the product. MDEV-28714 JSON functions still don't respect KILL on centos73-ppc64, main.func_json_notembedded fails

          • Major - Major loss of function.
          • Open

          Bug - A problem which impairs or prevents the functions of the product. MDEV-25332 Debug server is extremely slow upon ANALYZE FORMAT=JSON with long literals

          • Minor - Minor loss of function, or other problem where easy workaround is present.
          • Open

          Activity

            People

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

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

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