[MDEV-26866] FOREIGN KEY…SET NULL corrupts an index on a virtual column Created: 2021-10-20  Updated: 2021-11-01  Resolved: 2021-10-28

Status: Closed
Project: MariaDB Server
Component/s: Storage Engine - InnoDB, Virtual Columns
Affects Version/s: 10.2, 10.3, 10.4, 10.5, 10.6, 10.7
Fix Version/s: 10.2.41, 10.3.32, 10.4.22, 10.5.13, 10.6.5, 10.7.1

Type: Bug Priority: Blocker
Reporter: Marko Mäkelä Assignee: Nikita Malyavin
Resolution: Fixed Votes: 0
Labels: corruption, upstream

Attachments: File foreign_key.patch    
Issue Links:
Blocks
blocks MDEV-26867 Merge new release of InnoDB 5.7.36 to... Closed

 Description   

MySQL 5.7.36 includes a bug fix with a test case:
Bug #33053297 VIRTUAL INDEX CORRUPTED DURING CASCADE UPDATE ON CHILD TABLE
The test case resembles MDEV-24041. A quick attempt to apply the patch (with some rewriting in row_ins_foreign_fill_virtual()) did not affect the outcome. There is a result difference:

@@ -918,7 +918,6 @@
 
 1	NULL	2020-10-22 13:32:41	2020- 
 SELECT `date_sent` FROM `email_stats` WHERE `generated_sent_email` = '2020-';
 
 date_sent
 
-2020-10-22 13:32:41
 
 DROP TABLE `email_stats`;
 
 DROP TABLE `emails`;
 
 # test-case 4
 
@@ -956,7 +955,6 @@
 
 1	NULL	2020-10-22 13:32:41	2020- 
 SELECT `date_sent` FROM `email_stats` WHERE `generated_sent_email` = '2020-';
 
 date_sent
 
-2020-10-22 13:32:41
 
 DROP TABLE `email_stats`;
 
 DROP TABLE `emails`;
 
 # End of 10.2 tests

Curiously, if I add CHECK TABLE to the test, it will not report any corruption.

 Comments   
Comment by Marko Mäkelä [ 2021-10-20 ]

nikitamalyavin, I applied the test from the patch to MariaDB as foreign_key.patch. (The final desired location would be gcol.innodb_virtual_fk where we already have a similar test.)

This is otherwise the same as the original test, but there now is a trailing space in two WHERE conditions in the .test file. I wanted to see if it matters.

With that, and with or without my attempted crude port of the fix, the test still fails like this:

10.2 69b3de830d531e5cbc57c1a43c7bd55b31f7197e

 
innodb.foreign_key 'innodb'              [ fail ]
 
        Test ended at 2021-10-20 21:20:51
 
 
 
CURRENT_TEST: innodb.foreign_key
 
--- /mariadb/10.2o/mysql-test/suite/innodb/r/foreign_key.result	2021-10-20 17:54:42.173723268 +0300
 
+++ /mariadb/10.2o/mysql-test/suite/innodb/r/foreign_key.reject	2021-10-20 21:20:51.146557129 +0300
 
@@ -916,9 +916,8 @@
 
 SELECT * FROM `email_stats`;
 
 id	email_id	date_sent	generated_sent_email
 
 1	NULL	2020-10-22 13:32:41	2020- 
-SELECT `date_sent` FROM `email_stats` WHERE `generated_sent_email` = '2020-';
 
+SELECT `date_sent` FROM `email_stats` WHERE `generated_sent_email` = '2020- ';
 
 date_sent
 
-2020-10-22 13:32:41
 
 DROP TABLE `email_stats`;
 
 DROP TABLE `emails`;
 
 # test-case 4
 
@@ -954,9 +953,8 @@
 
 SELECT * FROM `email_stats`;
 
 id	email_id	date_sent	generated_sent_email
 
 1	NULL	2020-10-22 13:32:41	2020- 
-SELECT `date_sent` FROM `email_stats` WHERE `generated_sent_email` = '2020-';
 
+SELECT `date_sent` FROM `email_stats` WHERE `generated_sent_email` = '2020- ';
 
 date_sent
 
-2020-10-22 13:32:41
 
 DROP TABLE `email_stats`;
 
 DROP TABLE `emails`;
 
 # End of 10.2 tests
 
 
 
mysqltest: Result length mismatch

Comment by Nikita Malyavin [ 2021-10-22 ]

By most, the problem was in the following call:

		dfield_t* new_vfield = innobase_get_computed_value(
 
				update->old_vrow, col, index,
 
				&vc.heap, update->heap, NULL, thd,
 
				mysql_table, record, NULL,
 
				set_null ? update : node->update, foreign);

We want new_vfield, but fetch it from update->old_vrow, where actually some 'new row' should be used. Unfortunately, in case of DELETE, cascade->row corresponding the new row, is NULL

Then Sachin Agarwal's trick was to NULL the old fields in place:

for (uint32_t j = 0; j < col->num_base; j++) {
 
  dict_col_t *base_col = col->base_col[j];
 
  uint32_t col_no = base_col->ind;
 
  dfield_t *row_field =
 
      innobase_get_field_from_update_vector(
 
          foreign, node->update, col_no);
 
  if (row_field != NULL) {
 
    dfield_set_null(row_field);
 
  }
 
}

The direct patch application could have failed, but it works fine with a little bit more context applied.

please review the following commits:
f359747a prettify
0d235615 MDEV-26866 FOREIGN KEY…SET NULL corrupts an index on a virtual column

Comment by Marko Mäkelä [ 2021-10-25 ]

Please address my review comments and prepare also a 10.6 version for additional stress testing.

Comment by Marko Mäkelä [ 2021-10-27 ]

This looks like very welcome cleanup. OK to push if the stress tests pass.

Comment by Matthias Leich [ 2021-10-27 ] 

origin/bb-10.2-nikita e83e82780d75ee91ea817378646963c0a41b08cf 2021-10-27T11:42:20+03:00
 
origin/bb-10.6-nikita-MDEV-26866 3c93637a294ba061dc44c5a9ddeed1be65910a3f 2021-10-27T12:50:51+03:00
 
behaved well in RQG testing.

Generated at Thu Feb 08 09:48:33 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.