Description:
InnoDB may silently omit a visible row during a secondary-index lookup when
row_sel_sec_rec_is_for_clust_rec() validates a full-column secondary-index
field against an externally stored clustered field.
The testcase provides an explicit READ UNCOMMITTED versus READ COMMITTED
control using the same table, row, index, selected columns, and predicate:
- READ UNCOMMITTED, current clustered version: row is incorrectly omitted.
- READ COMMITTED, current clustered version: row is returned.
- REPEATABLE READ, old clustered version: row is incorrectly omitted.
- READ COMMITTED, old clustered version: row is incorrectly omitted.
READ UNCOMMITTED forces the secondary-to-clustered consistency check even
when the current clustered version is used. READ COMMITTED does not require
that check for the current version, so the row is returned.
When READ COMMITTED or REPEATABLE READ must reconstruct an old clustered
version, the same consistency check is executed and the row is incorrectly
omitted.
This contrast shows that the stored data and search predicate are valid.
The wrong result occurs specifically when
row_sel_sec_rec_is_for_clust_rec() is executed.
Physical layout:
The table uses ROW_FORMAT=DYNAMIC. The indexed VARCHAR column is actually
stored externally in the clustered record.
For a full-column secondary index:
dict_field_t::prefix_len == 0
The records contain:
clustered record:
local field data plus a 20-byte external-field reference
secondary index record:
complete 2000-byte indexed value
row_sel_sec_rec_is_for_clust_rec() materializes an externally stored
clustered value only inside the ifield->prefix_len > 0 branch.
Because a full-column index has prefix_len == 0, it skips external-value
materialization and calls cmp_data_data() with the clustered local/reference
bytes and the complete secondary-index value.
The comparison reports a false mismatch. The caller then discards the
otherwise visible row.
This is a silent wrong-result issue. I have not observed persistent table or
index corruption.
Environment:
- MySQL 8.0.46-debug
- Source commit: 9fc8d60003b4eee32767c1e30942f222c3508c31
(one commit after mysql-8.0.46)
- Linux x86_64
- Default 16KB InnoDB page size
Runtime reproduction was confirmed on the version above.
Source inspection shows the same missing full-column external-field
materialization in:
- mysql-8.4.11
- mysql-9.7.2
- mysql-26.7.0
- current upstream trunk
I have not run the testcase against binaries of those later versions.
How to repeat:
How to repeat：
1. Copy the attached full_column_external_sec_index.test to:
mysql-test/suite/innodb/t/full_column_external_sec_index.test
2. Copy the attached expected result to:
mysql-test/suite/innodb/r/full_column_external_sec_index.result
3. Build an unmodified MySQL server and run:
./build/mysql-test/mtr --suite=innodb \
full_column_external_sec_index
4. Observe "Result content mismatch".
All queries use the same predicate:
WHERE c = REPEAT('a', 2000)
Expected and actual results:
Isolation level Clustered version Expected Actual
----------------------------------------------------------------
REPEATABLE READ current 1 b 1 b
READ UNCOMMITTED current 1 b empty
READ COMMITTED current 1 b 1 b
REPEATABLE READ old 1 b empty
READ COMMITTED old 1 b empty
There are no concurrent writes during the READ UNCOMMITTED and
READ COMMITTED current-version controls. Both queries read the same committed
row. Therefore, READ UNCOMMITTED omitting the row is not permitted by its
weaker visibility rules.
The REPEATABLE READ old-version section establishes its snapshot before
another connection commits an update to an unrelated column.
The READ COMMITTED old-version section reads while another transaction has
an uncommitted update to an unrelated column.
Suggested fix:
Preserve the existing prefix-index, spatial-index, and multi-value-index
handling.
For an ordinary, non-virtual, full-column secondary-index field
(ifield->prefix_len == 0), when the corresponding clustered field is
externally stored, materialize the complete clustered value before calling
cmp_data_data().
For example, use lob::btr_rec_copy_externally_stored_field() with the
clustered record, clustered offsets, and clustered field position.
If materialization fails, keep the existing non-match behavior.
The existing row_sel_sec_rec_is_for_blob() helper should not be reused for
this case because it is prefix-specific and asserts prefix_len > 0.
The attached full_col_index.patch contains my proposed fix for this issue.
Description: InnoDB may silently omit a visible row during a secondary-index lookup when row_sel_sec_rec_is_for_clust_rec() validates a full-column secondary-index field against an externally stored clustered field. The testcase provides an explicit READ UNCOMMITTED versus READ COMMITTED control using the same table, row, index, selected columns, and predicate: - READ UNCOMMITTED, current clustered version: row is incorrectly omitted. - READ COMMITTED, current clustered version: row is returned. - REPEATABLE READ, old clustered version: row is incorrectly omitted. - READ COMMITTED, old clustered version: row is incorrectly omitted. READ UNCOMMITTED forces the secondary-to-clustered consistency check even when the current clustered version is used. READ COMMITTED does not require that check for the current version, so the row is returned. When READ COMMITTED or REPEATABLE READ must reconstruct an old clustered version, the same consistency check is executed and the row is incorrectly omitted. This contrast shows that the stored data and search predicate are valid. The wrong result occurs specifically when row_sel_sec_rec_is_for_clust_rec() is executed. Physical layout: The table uses ROW_FORMAT=DYNAMIC. The indexed VARCHAR column is actually stored externally in the clustered record. For a full-column secondary index: dict_field_t::prefix_len == 0 The records contain: clustered record: local field data plus a 20-byte external-field reference secondary index record: complete 2000-byte indexed value row_sel_sec_rec_is_for_clust_rec() materializes an externally stored clustered value only inside the ifield->prefix_len > 0 branch. Because a full-column index has prefix_len == 0, it skips external-value materialization and calls cmp_data_data() with the clustered local/reference bytes and the complete secondary-index value. The comparison reports a false mismatch. The caller then discards the otherwise visible row. This is a silent wrong-result issue. I have not observed persistent table or index corruption. Environment: - MySQL 8.0.46-debug - Source commit: 9fc8d60003b4eee32767c1e30942f222c3508c31 (one commit after mysql-8.0.46) - Linux x86_64 - Default 16KB InnoDB page size Runtime reproduction was confirmed on the version above. Source inspection shows the same missing full-column external-field materialization in: - mysql-8.4.11 - mysql-9.7.2 - mysql-26.7.0 - current upstream trunk I have not run the testcase against binaries of those later versions. How to repeat: How to repeat： 1. Copy the attached full_column_external_sec_index.test to: mysql-test/suite/innodb/t/full_column_external_sec_index.test 2. Copy the attached expected result to: mysql-test/suite/innodb/r/full_column_external_sec_index.result 3. Build an unmodified MySQL server and run: ./build/mysql-test/mtr --suite=innodb \ full_column_external_sec_index 4. Observe "Result content mismatch". All queries use the same predicate: WHERE c = REPEAT('a', 2000) Expected and actual results: Isolation level Clustered version Expected Actual ---------------------------------------------------------------- REPEATABLE READ current 1 b 1 b READ UNCOMMITTED current 1 b empty READ COMMITTED current 1 b 1 b REPEATABLE READ old 1 b empty READ COMMITTED old 1 b empty There are no concurrent writes during the READ UNCOMMITTED and READ COMMITTED current-version controls. Both queries read the same committed row. Therefore, READ UNCOMMITTED omitting the row is not permitted by its weaker visibility rules. The REPEATABLE READ old-version section establishes its snapshot before another connection commits an update to an unrelated column. The READ COMMITTED old-version section reads while another transaction has an uncommitted update to an unrelated column. Suggested fix: Preserve the existing prefix-index, spatial-index, and multi-value-index handling. For an ordinary, non-virtual, full-column secondary-index field (ifield->prefix_len == 0), when the corresponding clustered field is externally stored, materialize the complete clustered value before calling cmp_data_data(). For example, use lob::btr_rec_copy_externally_stored_field() with the clustered record, clustered offsets, and clustered field position. If materialization fails, keep the existing non-match behavior. The existing row_sel_sec_rec_is_for_blob() helper should not be reused for this case because it is prefix-specific and asserts prefix_len > 0. The attached full_col_index.patch contains my proposed fix for this issue.