[MDEV-3680] LP:1006164 - Multi-table DELETE that uses innodb + index_merge/intersect may fail to delete rows Created: 2012-05-29  Updated: 2015-02-02  Resolved: 2012-10-04

Status: Closed
Project: MariaDB Server
Component/s: None
Affects Version/s: None
Fix Version/s: None

Type: Bug Priority: Major
Reporter: Sergei Petrunia Assignee: Sergei Petrunia
Resolution: Fixed Votes: 0
Labels: Launchpad

Attachments: XML File LPexportBug1006164.xml    

 Description   

Based on customer case, csc#250:

CREATE TABLE tbl (
id PRIMARY KEY,
key1 INT,
key2 INT,
KEY (key1),
KEY (key2) ) engine=innodb;
...

DELETE tbl FROM tbl WHERE key1=const1 AND key2=const2;

^^ this will delete fewer rows than it actually matches.

  • Problem happens only with InnoDB (not with MyISAM)
  • Setting "index_merge=off" fixes the problem.
  • Using a non-multi-table-table DELETE statement fixes the problem.

 Comments   
Comment by Sergei Petrunia [ 2012-05-29 ]

Re: Multi-table DELETE that uses innodb + index_merge/intersect may fail to delete rows
Doesn't occur in MariaDB 5.2, occurs in 5.3 and 5.5

Comment by Sergei Petrunia [ 2012-05-29 ]

Re: Multi-table DELETE that uses innodb + index_merge/intersect may fail to delete rows
The cause of the problem is

  • a multi-table DELETE attempts to use an index_merge/intersect +"Using index" to read rows.
    = (for single-table DELETE, the problem doesnt show up, because index_merge/intersect is disabled)
  • "Using index" means that index_merge will read each column value from its index, and try to assemble a record from them (the record is needed to check the WHERE clause).
  • the table is used in DELETE, so table->no_keyread==TRUE. opt_range.cc has this [ancient] code:

int QUICK_RANGE_SELECT::init_ror_merged_scan(bool reuse_handler)
{
...
if (!head->no_keyread)

{ doing_key_read= 1; head->mark_columns_used_by_index(index); }

....

I don't understand the reasoning behind it, but its result is that we will fail to call head->mark_columns_used_by_index(index). This didn't cause the problem up to version 5.2, because opt_range also used old "useless-stub" MRR, which had handler::read_multi_range_first(), which had a call to
table->mark_columns_used_by_index_no_reset(), which fixed things.
However, in 5.3's new MRR we have removed that (because it's generally not needed), and hence this bug.

Comment by Sergei Petrunia [ 2012-05-30 ]

Re: Multi-table DELETE that uses innodb + index_merge/intersect may fail to delete rows
However, if I just move the mark_columns_used_by_index() outside of the if-statement, I get this error with tests:

At line 287: query 'DELETE t1 FROM t1,t2 WHERE t1.c1=0 AND t1.c2=0' failed: 126: Incorrect key file for table './test/t1.MYI'; try to repair it

will need to investigate.

Comment by Sergei Petrunia [ 2012-06-04 ]

Re: Multi-table DELETE that uses innodb + index_merge/intersect may fail to delete rows
Fix pushed into MariaDB 5.3

Comment by Elena Stepanova [ 2012-06-24 ]

Re: Multi-table DELETE that uses innodb + index_merge/intersect may fail to delete rows
Fix released in 5.5.25 and will be in 5.3.8 when it is out

Comment by Rasmus Johansson (Inactive) [ 2012-06-24 ]

Launchpad bug id: 1006164

Generated at Thu Feb 08 06:50:25 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.