Details
-
Bug
-
Status: Closed (View Workflow)
-
Critical
-
Resolution: Fixed
-
10.5, 10.6, 10.2(EOL), 10.3(EOL), 10.4(EOL)
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
-
MDEV-27955 main.func_json_notembedded test fails on out-of-memory on MariaDB 10.6.7
- Closed
- relates to
-
MDEV-25333 Debug server is extremely slow in some JSON functions
- Open
-
MDEV-28404 JSON functions don't respect KILL QUERY / max_statement_time limit #2
- Open
-
MDEV-28714 JSON functions still don't respect KILL on centos73-ppc64, main.func_json_notembedded fails
- Open
-
MDEV-25332 Debug server is extremely slow upon ANALYZE FORMAT=JSON with long literals
- Open