Bug #118121 lob pages leak when mysqld crashed
Submitted: 6 May 14:40 Modified: 13 May 8:45
Reporter: huahua xu Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: InnoDB storage engine Severity:S3 (Non-critical)
Version:8.0.37, 8.0.42 OS:Any
Assigned to: CPU Architecture:Any

[6 May 14:40] huahua xu
Description:
As a result of the final step of the innodb updating the information stored in the external field reference, the lob pages could leak during both writing the lob data and the mysqld crashed.

How to repeat:
diff --git a/storage/innobase/lob/lob0impl.cc b/storage/innobase/lob/lob0impl.cc
index fc2fb99..d98201f 100644
--- a/storage/innobase/lob/lob0impl.cc
+++ b/storage/innobase/lob/lob0impl.cc
@@ -1052,11 +1052,18 @@ dberr_t insert(InsertContext *ctx, trx_t *trx, ref_t &ref,
     }
   }

+  DBUG_EXECUTE_IF("ib_lob_page_leak_crash_before_update_first_lob_page_commit",
+                  DBUG_SUICIDE(););
+
   if (ret == DB_SUCCESS) {
     ref.update(space_id, first_page_no, 1, mtr);
     ref.set_length(total_written, mtr);
   }

+  DBUG_EXECUTE_IF("ib_lob_page_leak_crash_after_update_first_lob_page_commit",
+                  ctx->check_redolog();
+                  DBUG_SUICIDE(););
+
   DBUG_EXECUTE_IF("innodb_lob_print",
                   print(trx, index, std::cerr, ref, false););

diff --git a/storage/innobase/lob/lob0lob.cc b/storage/innobase/lob/lob0lob.cc
index 031230b..9b2e412 100644
--- a/storage/innobase/lob/lob0lob.cc
+++ b/storage/innobase/lob/lob0lob.cc
@@ -118,6 +118,8 @@ void BtrContext::check_redolog_normal() {

   commit_btr_mtr();

+  log_make_latest_checkpoint();
+
   DEBUG_SYNC_C("blob_write_middle");

   log_free_check();

After applying the patch above, test the following case to repeat the bug.

Test case 1:

mysql> drop table if exists lob_test;
mysql> CREATE TABLE lob_test(id int primary key, content longtext);
The size of data file(lob_test.ibd) is 114688.

mysql> SET DEBUG = "+d,ib_lob_page_leak_crash_before_update_first_lob_page_commit";
mysql> insert into lob_test values (1, repeat('1234567', 1024*1024*4));
The mysqld crashed, The size of data file(lob_test.ibd) is 32505856. 

Restart mysqld.
mysql> SET DEBUG = "+d,ib_lob_page_leak_crash_before_update_first_lob_page_commit";
mysql> insert into lob_test values (2, repeat('1234567', 1024*1024*3));
The mysqld crashed, The size of data file(lob_test.ibd) is 54525952. 

Restart mysqld.
mysql> SET DEBUG = "+d,ib_lob_page_leak_crash_before_update_first_lob_page_commit";
mysql> insert into lob_test values (3, repeat('1234567', 1024*1024*2));
mysqld crashed, The size of data file(lob_test.ibd) is 71303168. 

Restart mysqld.
mysql> SET DEBUG = "+d,ib_lob_page_leak_crash_before_update_first_lob_page_commit";
mysql> insert into lob_test values (4, repeat('1234567', 1024*1024*1));
The mysqld crashed, The size of data file(lob_test.ibd) is 75497472. 

Test case 2:

mysql> drop table if exists lob_test;
mysql> CREATE TABLE lob_test(id int primary key, content longtext);
The size of data file(lob_test.ibd) is 114688.

mysql> SET DEBUG = "+d,ib_lob_page_leak_crash_after_update_first_lob_page_commit";
mysql> insert into lob_test values (1, repeat('1234567', 1024*1024*4));
The mysqld crashed, The size of data file(lob_test.ibd) is 32505856. 

Restart mysqld.
mysql> SET DEBUG = "+d,ib_lob_page_leak_crash_after_update_first_lob_page_commit";
mysql> insert into lob_test values (2, repeat('1234567', 1024*1024*3));
The mysqld crashed, The size of data file(lob_test.ibd) is 32505856. 

Restart mysqld.
mysql> SET DEBUG = "+d,ib_lob_page_leak_crash_after_update_first_lob_page_commit";
mysql> insert into lob_test values (3, repeat('1234567', 1024*1024*2));
The mysqld crashed, The size of data file(lob_test.ibd) is 32505856. 

Restart mysqld.
mysql> SET DEBUG = "+d,ib_lob_page_leak_crash_after_update_first_lob_page_commit";
mysql> insert into lob_test values (4, repeat('1234567', 1024*1024*1));
The mysqld crashed, The size of data file(lob_test.ibd) is 32505856.
[13 May 8:45] MySQL Verification Team
Hello huahua xu,

Thank you for the report and test case.

regards,
Umesh