Description:
On a ROW_FORMAT=COMPRESSED table, if the server crashes while an UPDATE is storing a new value for an externally stored BLOB column, recovery rollback frees the OLD LOB and then restores the row to point at the pages it has just freed.
Nothing looks wrong at first: recovery completes, and the row still reads back correctly, because the freed pages have not been reused yet and still hold the old data. The corruption surfaces later, once those pages are handed out to another row:
- SELECT on the affected row returns another row's data.
- Deleting the row, or purge reaching it, aborts the server with
"Assertion failure: ut_a(page_type == FIL_PAGE_TYPE_LOB_FIRST)" in
lob0purge.cc, or with "InnoDB is trying to free page ... though it is
already marked as free in the tablespace" from fsp_free_page().
Conditions:
- ROW_FORMAT=COMPRESSED.
- The value being replaced is externally stored and is at most 128K, so it
is held in the old single z-stream format (FIL_PAGE_TYPE_ZBLOB). A larger
old value is not affected.
- The server crashes during the UPDATE. The size and format of the NEW
value do not matter.
How to repeat:
Let me provide an mtr test. Needs a debug build (uses DEBUG_SYNC). The server aborts at the SELECT right after recovery.
===
--source include/have_debug.inc
--source include/have_debug_sync.inc
--source include/have_innodb_16k.inc
--source include/not_valgrind.inc
--source include/not_crashrep.inc
CREATE TABLE t1 (id INT PRIMARY KEY, c LONGBLOB)
ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8 ENGINE=InnoDB;
# 80000 is <= 128K, so this value is stored in the old format.
INSERT INTO t1 VALUES (1, REPEAT('a', 80000));
--connect (con1, localhost, root,,)
# The first hit of blob_write_middle is inside the store of the new value,
# before any of it has been written.
SET DEBUG_SYNC = 'blob_write_middle SIGNAL lob_half_written WAIT_FOR go_never';
--send UPDATE t1 SET c = REPEAT('b', 4000000)
--connection default
SET DEBUG_SYNC = 'now WAIT_FOR lob_half_written';
--source include/kill_and_restart_mysqld.inc
--error CR_SERVER_LOST
--reap
--disconnect con1
--connection default
# Recovery rolled the UPDATE back and the row reads back fine, because the
# freed pages still hold the old data.
SELECT id, LENGTH(c) FROM t1;
# Hand the freed LOB pages out to other rows.
--disable_query_log
let $i = 60;
while ($i)
{
eval INSERT INTO t1 VALUES (100 + $i, REPEAT('c', 80000));
dec $i;
}
--enable_query_log
--let $lob_intact = `SELECT c = REPEAT('a', 80000) FROM t1 WHERE id = 1`
--let $assert_text = row 1 must still own its original LOB after recovery
--let $assert_cond = $lob_intact = 1
--source include/assert.inc
DELETE FROM t1 WHERE id = 1;
DROP TABLE t1;
SET DEBUG_SYNC = 'RESET';
Suggested fix:
When rolling back, lob::purge() must not destroy a LOB that the reference still designates because the interrupted store never got to redirect it.
I will submit a PR on GitHub later.
Description: On a ROW_FORMAT=COMPRESSED table, if the server crashes while an UPDATE is storing a new value for an externally stored BLOB column, recovery rollback frees the OLD LOB and then restores the row to point at the pages it has just freed. Nothing looks wrong at first: recovery completes, and the row still reads back correctly, because the freed pages have not been reused yet and still hold the old data. The corruption surfaces later, once those pages are handed out to another row: - SELECT on the affected row returns another row's data. - Deleting the row, or purge reaching it, aborts the server with "Assertion failure: ut_a(page_type == FIL_PAGE_TYPE_LOB_FIRST)" in lob0purge.cc, or with "InnoDB is trying to free page ... though it is already marked as free in the tablespace" from fsp_free_page(). Conditions: - ROW_FORMAT=COMPRESSED. - The value being replaced is externally stored and is at most 128K, so it is held in the old single z-stream format (FIL_PAGE_TYPE_ZBLOB). A larger old value is not affected. - The server crashes during the UPDATE. The size and format of the NEW value do not matter. How to repeat: Let me provide an mtr test. Needs a debug build (uses DEBUG_SYNC). The server aborts at the SELECT right after recovery. === --source include/have_debug.inc --source include/have_debug_sync.inc --source include/have_innodb_16k.inc --source include/not_valgrind.inc --source include/not_crashrep.inc CREATE TABLE t1 (id INT PRIMARY KEY, c LONGBLOB) ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8 ENGINE=InnoDB; # 80000 is <= 128K, so this value is stored in the old format. INSERT INTO t1 VALUES (1, REPEAT('a', 80000)); --connect (con1, localhost, root,,) # The first hit of blob_write_middle is inside the store of the new value, # before any of it has been written. SET DEBUG_SYNC = 'blob_write_middle SIGNAL lob_half_written WAIT_FOR go_never'; --send UPDATE t1 SET c = REPEAT('b', 4000000) --connection default SET DEBUG_SYNC = 'now WAIT_FOR lob_half_written'; --source include/kill_and_restart_mysqld.inc --error CR_SERVER_LOST --reap --disconnect con1 --connection default # Recovery rolled the UPDATE back and the row reads back fine, because the # freed pages still hold the old data. SELECT id, LENGTH(c) FROM t1; # Hand the freed LOB pages out to other rows. --disable_query_log let $i = 60; while ($i) { eval INSERT INTO t1 VALUES (100 + $i, REPEAT('c', 80000)); dec $i; } --enable_query_log --let $lob_intact = `SELECT c = REPEAT('a', 80000) FROM t1 WHERE id = 1` --let $assert_text = row 1 must still own its original LOB after recovery --let $assert_cond = $lob_intact = 1 --source include/assert.inc DELETE FROM t1 WHERE id = 1; DROP TABLE t1; SET DEBUG_SYNC = 'RESET'; Suggested fix: When rolling back, lob::purge() must not destroy a LOB that the reference still designates because the interrupted store never got to redirect it. I will submit a PR on GitHub later.