| Bug #92114 | Partial Updates of JSON Values | ||
|---|---|---|---|
| Submitted: | 22 Aug 2018 1:22 | Modified: | 10 Jun 2021 4:05 |
| Reporter: | chen chen (OCA) | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: JSON | Severity: | S3 (Non-critical) |
| Version: | 8.0.12 | OS: | Any |
| Assigned to: | Jon Stephens | CPU Architecture: | Any |
[5 Sep 2018 10:18]
MySQL Verification Team
Hello Chen, Thank you for the report and test case. Thanks, Umesh
[30 Nov 2018 14:07]
Knut Anders Hatlen
Posted by developer: I don't think this is a code bug. Since there is not enough space for the updated value inside the existing JSON document, a full update is performed when storing the updated document in the table. This can be seen by the fact that JSON_STORAGE_SIZE(c1) increases from 29 to 51 and JSON_STORAGE_FREE(c1) is 0 after the update. This is in agreement with the documentation. The reporter mentions that mysqlbinlog shows a partial update despite this. This is because the binlog will contain logical diffs instead of physical row images when binlog_row_value_options is set to 'PARTIAL_JSON', in order to reduce the amount of log to send to the slaves. This is done regardless of whether there is enough space in the original document to do a partial update when writing the updated column value to the table. This could be clarified in the documentation: https://dev.mysql.com/doc/refman/8.0/en/replication-options-binary-log.html#sysvar_binlog_... currently says: "This works for an UPDATE statement which modifies a JSON column using any sequence of JSON_SET(), JSON_REPLACE(), and JSON_REMOVE(). If the modification requires more space than the full document, or if the server is unable to generate a partial update, the full document is used instead." The part about "requires more space" should be removed, because the size is not taken into consideration when determining if the binlog should use partial or full format. https://dev.mysql.com/doc/refman/8.0/en/json.html#json-partial-updates - The last two bullets ("All changes replace existing array or object values with new ones, and do not add any new elements to the parent object or array" and "The value being replaced must be at least as large as the replacement value") do not apply to the partial binlog format. They are requirement for doing partial update of the physical column values in the table, but the partial binlog format can be used even if those two requirements are not met.
[10 Jun 2021 4:05]
Jon Stephens
Fixed in mysqldoc rev 69996. Closed.
Description: Here comes from the official document In MySQL 8.0, the optimizer can perform a partial, in-place update of a JSON column instead of removing the old document and writing the new document in its entirety to the column. This optimization can be performed for an update that meets the following conditions: All changes replace existing array or object values with new ones, and do not add any new elements to the parent object or array. The value being replaced must be at least as large as the replacement value. In other words, the new value cannot be any larger than the old one. but i found it is not true. Here it is create table t (id int auto_increment primary key, c1 json); insert into t (c1) values ('{"id": "1", "name": "a"}'), ('{"id": "2", "name": "b"}'), ('{"id": "3", "name": "c"}'), ('{"id": "4", "name": "d"}'); set session binlog_row_value_options='PARTIAL_JSON'; reset master; mysql> select id,c1,JSON_STORAGE_SIZE(c1),JSON_STORAGE_FREE(c1) from t; +----+--------------------------+-----------------------+-----------------------+ | id | c1 | JSON_STORAGE_SIZE(c1) | JSON_STORAGE_FREE(c1) | +----+--------------------------+-----------------------+-----------------------+ | 1 | {"id": "1", "name": "a"} | 29 | 0 | | 2 | {"id": "2", "name": "b"} | 29 | 0 | | 3 | {"id": "3", "name": "c"} | 29 | 0 | | 4 | {"id": "4", "name": "d"} | 29 | 0 | +----+--------------------------+-----------------------+-----------------------+ 4 rows in set (0.00 sec) mysql> update t set c1=json_set(c1,"$.age",'{"123",456}') where id=1; Query OK, 1 row affected (0.06 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> select id,c1,JSON_STORAGE_SIZE(c1),JSON_STORAGE_FREE(c1) from t; +----+--------------------------------------------------+-----------------------+-----------------------+ | id | c1 | JSON_STORAGE_SIZE(c1) | JSON_STORAGE_FREE(c1) | +----+--------------------------------------------------+-----------------------+-----------------------+ | 1 | {"id": "1", "age": "{\"123\",456}", "name": "a"} | 51 | 0 | | 2 | {"id": "2", "name": "b"} | 29 | 0 | | 3 | {"id": "3", "name": "c"} | 29 | 0 | | 4 | {"id": "4", "name": "d"} | 29 | 0 | +----+--------------------------------------------------+-----------------------+-----------------------+ 4 rows in set (0.00 sec) then i check the binlog output # mysqlbinlog binlog.000001 -vv BINLOG ' t7h8WxMBAAAANQAAAHMBAAAAAL0BAAAAAAEACHNsb3d0ZWNoAAF0AAID9QEEAgEBAEkoAks= t7h8WycBAAAAagAAAN0BAAAAAL0BAAAAAAEAAgAC//8AAQAAAB0AAAAAAgAcABIAAgAUAAQADBgA DBoAaWRuYW1lATEBYQEBAAEAAAAVAAAAAQUkLmFnZQ0MC3siMTIzIiw0NTZ97jnTrg== '/*!*/; ### UPDATE `slowtech`.`t` ### WHERE ### @1=1 /* INT meta=0 nullable=0 is_null=0 */ ### @2='{"id": "1", "name": "a"}' /* JSON meta=4 nullable=1 is_null=0 */ ### SET ### @1=1 /* INT meta=0 nullable=0 is_null=0 */ ### @2=JSON_INSERT(@2, '$.age', '{"123",456}') /* JSON meta=4 nullable=1 is_null=0 */ # at 477 #180822 9:13:27 server id 1 end_log_pos 508 CRC32 0x06a53e59 Xid = 3909 COMMIT/*!*/; it still use the JSON_INSERT function to perform the update,not the full image. so i wondered is there anything wrong with the document saying. And what is the true precondition for the Partial Updates of JSON Values? How to repeat: As stated above