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

          Activity

            Some other JSON functions which don't respect max_statement_time (which most likely means they don't obey KILL QUERY either):

            10.6 63e9a054

            MariaDB [test]> set @@max_statement_time= 1;
            Query OK, 0 rows affected (0.000 sec)
             
            MariaDB [test]> select length(@json), length(@json_arr);
            +---------------+-------------------+
            | length(@json) | length(@json_arr) |
            +---------------+-------------------+
            |      21941555 |          30888897 |
            +---------------+-------------------+
            1 row in set (0.021 sec)
            

            MariaDB [test]> select JSON_ARRAY_APPEND(@json_arr, '$[0]', 1) INTO @a;
            Query OK, 1 row affected (6.499 sec)
            

            MariaDB [test]> select JSON_ARRAY_INSERT(@json_arr, '$[0]', 1) INTO @a;
            Query OK, 1 row affected (6.509 sec)
            

            MariaDB [test]> select JSON_INSERT(@json, '$.meta', 1) into @a;
            Query OK, 1 row affected (4.391 sec)
            

            MariaDB [test]> select JSON_COMPACT(@json_arr) INTO @a;
            Query OK, 1 row affected (14.652 sec)
            

            MariaDB [test]> select JSON_DETAILED(@json_arr) into @a;
            Query OK, 1 row affected (43.032 sec)
            

            MariaDB [test]> select JSON_INSERT(@json, '$.meta', 1) into @a;
            Query OK, 1 row affected (4.858 sec)
            

            MariaDB [test]> select JSON_LOOSE(@json_arr) into @a;
            Query OK, 1 row affected (19.196 sec)
            

            # This one is deprecated
            MariaDB [test]> select JSON_MERGE(@json, @json_arr) into @a;
            Query OK, 1 row affected (1 min 2.378 sec)
            

            MariaDB [test]> select JSON_MERGE_PRESERVE(@json, @json_arr) into @a;
            Query OK, 1 row affected (49.576 sec)
            

            MariaDB [test]> select JSON_REMOVE(@json,'$.foo') into @a;
            Query OK, 1 row affected (5.030 sec)
            

            MariaDB [test]> select JSON_REPLACE(@json,'$.foo',1) into @a;
            Query OK, 1 row affected (4.418 sec)
            

            MariaDB [test]> select JSON_SET(@json_arr,'$[1000]',1) into @a;
            Query OK, 1 row affected (5.014 sec)
            

            elenst Elena Stepanova added a comment - Some other JSON functions which don't respect max_statement_time (which most likely means they don't obey KILL QUERY either): 10.6 63e9a054 MariaDB [test]> set @@max_statement_time= 1; Query OK, 0 rows affected (0.000 sec)   MariaDB [test]> select length(@json), length(@json_arr); + ---------------+-------------------+ | length(@json) | length(@json_arr) | + ---------------+-------------------+ | 21941555 | 30888897 | + ---------------+-------------------+ 1 row in set (0.021 sec) MariaDB [test]> select JSON_ARRAY_APPEND(@json_arr, '$[0]' , 1) INTO @a; Query OK, 1 row affected (6.499 sec) MariaDB [test]> select JSON_ARRAY_INSERT(@json_arr, '$[0]' , 1) INTO @a; Query OK, 1 row affected (6.509 sec) MariaDB [test]> select JSON_INSERT(@json, '$.meta' , 1) into @a; Query OK, 1 row affected (4.391 sec) MariaDB [test]> select JSON_COMPACT(@json_arr) INTO @a; Query OK, 1 row affected (14.652 sec) MariaDB [test]> select JSON_DETAILED(@json_arr) into @a; Query OK, 1 row affected (43.032 sec) MariaDB [test]> select JSON_INSERT(@json, '$.meta' , 1) into @a; Query OK, 1 row affected (4.858 sec) MariaDB [test]> select JSON_LOOSE(@json_arr) into @a; Query OK, 1 row affected (19.196 sec) # This one is deprecated MariaDB [test]> select JSON_MERGE(@json, @json_arr) into @a; Query OK, 1 row affected (1 min 2.378 sec) MariaDB [test]> select JSON_MERGE_PRESERVE(@json, @json_arr) into @a; Query OK, 1 row affected (49.576 sec) MariaDB [test]> select JSON_REMOVE(@json, '$.foo' ) into @a; Query OK, 1 row affected (5.030 sec) MariaDB [test]> select JSON_REPLACE(@json, '$.foo' ,1) into @a; Query OK, 1 row affected (4.418 sec) MariaDB [test]> select JSON_SET(@json_arr, '$[1000]' ,1) into @a; Query OK, 1 row affected (5.014 sec)
            serg Sergei Golubchik added a comment - https://github.com/MariaDB/server/commit/d194dbe434562dfe0619c474c0b5be37a51751fb https://github.com/MariaDB/server/commit/8cb1a5d431fcfb1cc87c915f8e41137ab234b894 https://github.com/MariaDB/server/commit/7808cf9a8b4430262df23b6410de61c499ee740d
            brad0 Brad Smith added a comment - - edited

            This commit... https://github.com/MariaDB/server/commit/add782a13e58c027c14d548fa705f48fc25cc3e1 does not build.

            /home/ports/pobj/mariadb-10.6.3/mariadb-10.6.3/sql/sql_select.cc:18423:12: error: cannot initialize return object of type 'bool' with an rvalue of type 'nullptr_t'
                return NULL;
                       ^~~~
            /usr/include/sys/_null.h:12:14: note: expanded from macro 'NULL'
            #define NULL    nullptr
                            ^~~~~~~
            1 error generated.
            

            Which is this chunk of code..

            static bool make_json_valid_expr(TABLE *table, Field *field)
            {
              THD *thd= table->in_use;
              Query_arena backup_arena;
              Item *expr, *item_field;
             
              if (!table->expr_arena && table->init_expr_arena(thd->mem_root))
                return NULL;
            

            brad0 Brad Smith added a comment - - edited This commit... https://github.com/MariaDB/server/commit/add782a13e58c027c14d548fa705f48fc25cc3e1 does not build. /home/ports/pobj/mariadb-10.6.3/mariadb-10.6.3/sql/sql_select.cc:18423:12: error: cannot initialize return object of type 'bool' with an rvalue of type 'nullptr_t' return NULL; ^~~~ /usr/include/sys/_null.h:12:14: note: expanded from macro 'NULL' #define NULL nullptr ^~~~~~~ 1 error generated. Which is this chunk of code.. static bool make_json_valid_expr(TABLE *table, Field *field) { THD *thd= table->in_use; Query_arena backup_arena; Item *expr, *item_field;   if (!table->expr_arena && table->init_expr_arena(thd->mem_root)) return NULL;

            It's a wrong MDEV for this. But anyway, what compiler was it? It was in the latest 10.6.3 release, so it has definitely compiled with a bunch of different gcc's and few clang's. With -Werror too.

            Although the line is wrong and should be fixed, I'd still rather prefer our CI's to catch compilation errors before the release.

            serg Sergei Golubchik added a comment - It's a wrong MDEV for this. But anyway, what compiler was it? It was in the latest 10.6.3 release, so it has definitely compiled with a bunch of different gcc's and few clang's. With -Werror too. Although the line is wrong and should be fixed, I'd still rather prefer our CI's to catch compilation errors before the release.
            brad0 Brad Smith added a comment - - edited

            Oh sorry I saw an MDEV in a commit branch at the bottom so I figured that was associated with the commit.

            Clang 11.1.0.

            brad0 Brad Smith added a comment - - edited Oh sorry I saw an MDEV in a commit branch at the bottom so I figured that was associated with the commit. Clang 11.1.0.

            The 'code cleanup' patches are ok.
            Comments added for the fixing one
            https://github.com/MariaDB/server/commit/7808cf9a8b4430262df23b6410de61c499ee740d

            holyfoot Alexey Botchkov added a comment - The 'code cleanup' patches are ok. Comments added for the fixing one https://github.com/MariaDB/server/commit/7808cf9a8b4430262df23b6410de61c499ee740d

            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.