Bug #114138 MVCC is broken for small partial update of blob

• Submitted: 27 Feb 9:44
• Modified: 27 Feb 10:35
• Reporter: Fungo Wang (OCA)
• Status: Verified
• Category: MySQL Server: InnoDB storage engine
• Severity: S1 (Critical)
• Version: 8.0.36, 8.3.0
• OS: Any
• CPU Architecture: Any
• Tags: BLOB, MVCC, partial update, small partial update

[27 Feb 9:44] Fungo Wang

Description:
MVCC of blob object is broken, and selecting blob gives wrong result, all 8.0 versions are affected.

How to repeat:
a simplified mtr testcase

```
CREATE TABLE t1(pkey int not null auto_increment, j json, primary key (pkey)) engine=innodb row_format=dynamic;
SHOW CREATE TABLE t1;
INSERT INTO t1(j) values (JSON_ARRAY(REPEAT("MySQL", 40), REPEAT("BlobPartialUpdate", 512)));

--echo # Connection con0:
connect (con0,localhost,root,,);
set transaction_isolation = 'REPEATABLE-READ';
start transaction;
select json_extract(j, '$[0]') from t1 where pkey = 1;
--disable_result_log
select @var_0 := json_extract(j, '$[0]') from t1;
--enable_result_log

--echo # Doing partial update of length=60: (MVCC in undo)
--echo # Connection default:
connection default;
select json_extract(j, '$[0]') from t1 where pkey = 1;
start transaction;
update t1 set j = json_set(j, '$[0]', REPEAT("InnoDB", 10)) where pkey = 1;
select json_extract(j, '$[0]') from t1 where pkey = 1;
commit;

--echo # Connection con1:
connect (con1,localhost,root,,);
set transaction_isolation = 'REPEATABLE-READ';
start transaction;
select json_extract(j, '$[0]') from t1 where pkey = 1;
--disable_result_log
select @var_1 := json_extract(j, '$[0]') from t1;
--enable_result_log

--echo # Doing partial update of length=140: (MVCC in blob)
--echo # Connection default:
connection default;
start transaction;
update t1 set j = json_set(j, '$[0]', REPEAT("PolarDB", 20)) where pkey = 1;
select json_extract(j, '$[0]') from t1 where pkey = 1;
commit;

--echo # Doing partial update of length=70: (MVCC in undo)
--echo # Connection default:
connection default;
start transaction;
update t1 set j = json_set(j, '$[0]', REPEAT("abcde", 14)) where pkey = 1;
select json_extract(j, '$[0]') from t1 where pkey = 1;
commit;

--echo # Doing partial update of length=200: (MVCC in blob)
--echo # Connection default:
connection default;
select json_extract(j, '$[0]') from t1 where pkey = 1;
start transaction;
update t1 set j = json_set(j, '$[0]', REPEAT("fungo", 40)) where pkey = 1;
select json_extract(j, '$[0]') from t1 where pkey = 1;
commit;

--echo # Doing partial update of length=90: (MVCC in undo)
--echo # Connection default:
connection default;
select json_extract(j, '$[0]') from t1 where pkey = 1;
start transaction;
update t1 set j = json_set(j, '$[0]', REPEAT("abcde", 18)) where pkey = 1;
select json_extract(j, '$[0]') from t1 where pkey = 1;
commit;

--echo # Connection con0:
connection con0;
--echo ## Result should be
select @var_0;
--echo ## but in actual, it is
select json_extract(j, '$[0]') from t1 where pkey = 1;
commit;

--echo # Connection con1:
connection con1;
--echo ## Result should be
select @var_1;
--echo ## but in actual, it is
select json_extract(j, '$[0]') from t1 where pkey = 1;
commit;

--echo # Connection default:
connection default;
drop table t1;
disconnect con0;
disconnect con1;
```

Check the result of mtr testcase for the 2nd select of con0 and con1

```
# Connection con0:
## Result should be
select @var_0;
@var_0
"MySQLMySQLMySQLMySQLMySQLMySQLMySQLMySQLMySQLMySQLMySQLMySQLMySQLMySQLMySQLMySQLMySQLMySQLMySQLMySQLMySQLMySQLMySQLMySQLMySQLMySQLMySQLMySQLMySQLMySQLMySQLMySQLMySQLMySQLMySQLMySQLMySQLMySQLMySQLMySQL"
## but in actual, it is
select json_extract(j, '$[0]') from t1 where pkey = 1;
json_extract(j, '$[0]')
"MySQLMySQLMySQLMySQLMySQLMySQLMySQLMySQLMySQLMySQLMySQLMySQarDBPolarDofungofungofungofungLMySQLMySQLMySQLMySQLMySQLMySQLMySQLMySQLMySQLMySQLMySQLMySQLMySQLMySQLMySQLMySQLMySQLMySQLMySQLMySQLMySQLMySQL"
commit;
# Connection con1:
## Result should be
select @var_1;
@var_1
"InnoDBInnoDBInnoDBInnoDBInnoDBInnoDBInnoDBInnoDBInnoDBInnoDB"
## but in actual, it is
select json_extract(j, '$[0]') from t1 where pkey = 1;
json_extract(j, '$[0]')
"PolarDBPolarDBPolarDBPolarDBPolarDBPolarDBPolarDBPolarDBPolarDBPolarDofungofungofungofungLMySQLMySQLMySQLMySQLMySQLMySQLMySQLMySQLMySQLMySQL"
commit;
# Connection default:
drop table t1;
```

for con0, the result of 2nd select is partially different with expected
for con1, the result of 2nd select is totally different with expected (even the length is different).

Suggested fix:
MVCC of blob is introduced in 8.0, and these worklogs and blogs give a general explaination of partial update(not small) and small partial upate.
https://dev.mysql.com/worklog/task/?id=8960
https://dev.mysql.com/worklog/task/?id=11328

https://dev.mysql.com/blog-archive/mysql-8-0-mvcc-of-large-objects-in-innodb/
https://dev.mysql.com/blog-archive/mysql-8-0-optimizing-small-partial-update-of-lob-in-inn...

The bug is caused by the **small partial update** feature, when old version of blob is built to satisfy the read visibility, the wrong undo rec is applied to blob.

A simple fix is to filter out the wrong undo log rec when applied to blob.

```
diff --git a/storage/innobase/lob/lob0impl.cc b/storage/innobase/lob/lob0impl.cc
index 74f2fa15742..bd9b1c99800 100644
--- a/storage/innobase/lob/lob0impl.cc
+++ b/storage/innobase/lob/lob0impl.cc
@@ -1129,7 +1129,7 @@ ulint read(ReadContext *ctx, ref_t ref, ulint offset, ulint len, byte *buf) {
   cached_blocks.insert(
       std::pair<page_no_t, buf_block_t *>(page_no, first_page.get_block()));

-  ctx->m_lob_version = first_page.get_lob_version();
+  ctx->m_lob_version = lob_version;

   page_no_t first_page_no = first_page.get_page_no();

diff --git a/storage/innobase/lob/lob0undo.cc b/storage/innobase/lob/lob0undo.cc
index 7138ab2d2de..4e70c0f2a05 100644
--- a/storage/innobase/lob/lob0undo.cc
+++ b/storage/innobase/lob/lob0undo.cc
@@ -52,8 +52,9 @@ void undo_data_t::apply(dict_index_t *index, byte *lob_mem, size_t len,
   }
 #endif /* UNIV_DEBUG */

-  /* Ensure that the undo log applied on the LOB is matching. */
-  if (first_page_no == m_page_no) {
+  /* Ensure that the undo log applied on the LOB is matching with the
+  lob object and version. */
+  if (first_page_no == m_page_no && lob_version == m_version) {
     byte *ptr = lob_mem + m_offset;
     ut_ad((m_offset + m_length) <= len);
     memcpy(ptr, m_old_data, m_length);
```

[27 Feb 10:35] MySQL Verification Team

Hello Fungo Wang,

Thank you for the report and testcase.

regards,
Umesh