MySQL Bugs: #57372: Multi-table updates and deletes fail when running with ICP against InnoDB

Bug #57372	Multi-table updates and deletes fail when running with ICP against InnoDB
Submitted:	11 Oct 2010 13:53	Modified:	23 Nov 2010 3:33
Reporter:	Olav Sandstå	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S3 (Non-critical)
Version:	5.6.99	OS:	Any
Assigned to:	Olav Sandstå	CPU Architecture:	Any
Tags:	index_condition_pushdown, optimizer_switch

Description:
The current implementation of ICP in InnoDB has some restrictions for
the index condtions InnoDB will accept. The code for these checks are found
in ha_innobase::idx_cond_push:

	if (keyno_arg != primary_key
	    && prebuilt->select_lock_type != LOCK_X) {

With this check InnoDB will reject index conditions that are either
done on the primary key or for update statements.

There is on-going work to lift these restrictions in order to make
InnoDB accept all index conditions that the optimizer attempts to push
down.

Using an early version of InnoDB where these restrictions have been
removed shows that several test cases now fail when running
multi-table update or multi-table delete statements where an index
condition is being pushed down (failing tests: main.ps_3innodb,
innodb.innodb_mysql, innodb.innodb_multi_update).

A typical error message looks like:

  Warnings from just before the error:
  Error 1032 Can't find record in 't4' 
  Error 1105 An error occured in multi-table update

and is caused by the "update part" of the statement not finding the
records that should be updated.

How to repeat:
In order to repeat these test failures you need an updated version of
InnoDB that have removed these restrictions. With this patch applied
and ICP enabled (eg. using the source from mysql-next-mr-opt-backporting) the following test case will fail:

CREATE TABLE t1 (
  a INT KEY, 
  b INT
) ENGINE = INNODB;

CREATE TABLE t2 (
  a INT KEY, 
  b INT
) ENGINE = INNODB;

INSERT INTO t1 VALUES (1, 101), (2, 102), (3, 103), (4, 104), (5, 105);
INSERT INTO t2 VALUES (1, 1), (2, 2), (3, 3), (4, 4), (5, 5);

UPDATE t1, t2 SET t1.a = t1.a + 100, t2.b = t1.a + 10 
WHERE t1.a BETWEEN 2 AND 4 AND t2.a = t1.b - 100;

with an error message saying:

  Warnings from just before the error:
  Error 1032 Can't find record in 't2' 
  Error 1105 An error occured in multi-table update

In the multi-table update query given in the bug report the following
condition will be pushed down to InnoDB when executing the "query"
part of the statement for retrieving records from table t2:

  `test`.`t2`.`a` = (`test`.`t1`.`b` - 100)

The results from the query part is (in this case) stored into two
temporary tables. When the update part of the statement is done the
content of these tempory tables are used for updating t1 and t2. The
update of t1 is done correctly. But when trying to update t2 InnoDB
reports that there are no records to update.

The update is done in the function multi_update::do_updates() (in
sql_update.cc). This function does the following to position on the
first row to be updated:

      /* call ha_rnd_pos() using rowids from temporary table */
      check_opt_it.rewind();
      TABLE *tbl= table;
      uint field_num= 0;
      do
      {
        if((local_error=
              tbl->file->ha_rnd_pos(tbl->record[0],
                                    (uchar *) tmp_table->field[field_num]->ptr)))
          goto err;
        field_num++;
      } while((tbl= check_opt_it++));

In this case the call to tbl->file->ha_rnd_pos() returns an error that
there are no records found. The cause for this is that when InnoDB is
executing the tbl->file->ha_rnd_pos() call the handler object still
contains the pushed index condition and this is evaluated. In this
case it evaluates to FALSE and InnoDB skip this record and returns
that the record is not found.

Some ideas for alternatives for fixing this:

1. Disable use of ICP if the query is either a multi-table update or delete

2. Disable the pushed condition before starting updating the table

   2a: "faking it": push a new index condition function to the storage 
       engine that always evaluates to true.

   2b: "proper": extend the handler interface with a new method to 
       inform the storage engine that ICP should no longer being used

3. Create a separate handler object for doing the updates

Out of these alternative 1 seems to be the easiest and
safest. Alternative 2 is doable but it might not solve all
issues. There are cases where the updates to the table can be done "on
the fly" without going through a temporary table (see the criteria in
the safe_update_on_fly() function in sql_update.cc). Alternativ 3 is
the one that likely would require most code changes and require
setting up multiple handler objects for the same table.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

http://lists.mysql.com/commits/120629

3262 Olav Sandstaa 2010-10-13
Fix for Bug#57372 Multi-table updates and deletes fail when running with ICP against InnoDB

For multi-table update and delete statements the same handler object
is used for both doing the query part and the update part of the
statement. When ICP is used a select condition will be pushed down to
the storage engine. If this is accepted by the storage engine it will
be used during execution of the query part of the statement but it
might also be evaluated during the update part of the statement. This
can lead to either not finding the record to update or result in wrong
record being updated.

Using ICP with InnoDB this has not given any issues this far as
InnoDB has not accepted ICP for statements that updates the
table. This restriction is about to be removed from InnoDB. After this change
using ICP with InnoDB might cause wrong results if we have pushed an
index condition for multi-table update and delete statements.

This patch solves this issue by not pushing down index conditions to
the storage engine when the statement is either a multi-table delete
or update statement.
@ mysql-test/include/icp_tests.inc
Test case for Bug#57372 "Multi-table updates and deletes fail when
running with ICP gainst InnoDB.
@ mysql-test/r/innodb_icp.result
Result file for test case for Bug#57372 "Multi-table updates and
deletes fail when running with ICP gainst InnoDB.
@ mysql-test/r/innodb_icp_all.result
Result file for test case for Bug#57372 "Multi-table updates and
deletes fail when running with ICP gainst InnoDB.
@ mysql-test/r/innodb_icp_none.result
Result file for test case for Bug#57372 "Multi-table updates and
deletes fail when running with ICP gainst InnoDB.
@ mysql-test/r/myisam_icp.result
Result file for test case for Bug#57372 "Multi-table updates and
deletes fail when running with ICP gainst InnoDB.
@ mysql-test/r/myisam_icp_all.result
Result file for test case for Bug#57372 "Multi-table updates and
deletes fail when running with ICP gainst InnoDB.
@ mysql-test/r/myisam_icp_none.result
Result file for test case for Bug#57372 "Multi-table updates and
deletes fail when running with ICP gainst InnoDB.
@ sql/sql_select.cc
For multi-table delete and update statements the same handler is used
for both doing the query part and the update part. If an index
condition has been pushed down to the storage engine it will be
evaluated during execution of the query part but it might also be
executed during the update part. This can lead to wrong result.
This patch solves this problem by restricting the server to not push
down index conditions if the statement is either a multi-table update or
delete statement.

The patch disables index condition pushdown for multi-table deletes and updates. Looks OK from the InnoDB point of view. What about multi-table replace or other multi-table operations, can ICP be attempted on them?

I have looked at "multi-table" INSERT/REPLACE (note that insert/replace can only update one table) and it seems like this is not an issue for these operations. A statement like:

  REPLACE/INSERT INTO t1 SELECT ... FROM t1,t2 WHERE ...

can use ICP for execution of the SELECT part. The reason this does not cause issues when doing the REPLACE/INSERT part is that the replace/insert is done using a separate handler object instead of doing it using the same handler object as used for the select.

For the above statement the server will create three table objects for "t1", "t1", "t2" each with a separate handler object. One of the "t1" table objects will be used for the select part and the other "t1" table object for the replace/insert part.

For a multi-table update statement like:

  UPDATE t1, t2 SET ... WHERE... 

the server will create only two table objects "t1", "t2" which will be used both for the "select" part and the "update" part (and caused this problem).

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

http://lists.mysql.com/commits/122180

3270 Olav Sandstaa 2010-10-28
Fix for Bug#57372 Multi-table updates and deletes fail when running with ICP against InnoDB

For multi-table update and delete statements the same handler object
is used for both doing the query part and the update part of the
statement. When ICP is used a select condition will be pushed down to
the storage engine. If this is accepted by the storage engine it will
be used during execution of the query part of the statement but it
might also be evaluated during the update part of the statement. This
can lead to either not finding the record to update or result in wrong
record being updated.

Using ICP with InnoDB this has not given any issues this far as
InnoDB has not accepted ICP for statements that updates the
table. This restriction is about to be removed from InnoDB. After this change
using ICP with InnoDB might cause wrong results if we have pushed an
index condition for multi-table update and delete statements.

This patch solves this issue by not pushing down index conditions to
the storage engine when the statement is either a multi-table delete
or update statement.
@ mysql-test/include/icp_tests.inc
Test case for Bug#57372 "Multi-table updates and deletes fail when
running with ICP gainst InnoDB.
@ mysql-test/r/innodb_icp.result
Result file for test case for Bug#57372 "Multi-table updates and
deletes fail when running with ICP gainst InnoDB.
@ mysql-test/r/innodb_icp_all.result
Result file for test case for Bug#57372 "Multi-table updates and
deletes fail when running with ICP gainst InnoDB.
@ mysql-test/r/innodb_icp_none.result
Result file for test case for Bug#57372 "Multi-table updates and
deletes fail when running with ICP gainst InnoDB.
@ mysql-test/r/myisam_icp.result
Result file for test case for Bug#57372 "Multi-table updates and
deletes fail when running with ICP gainst InnoDB.
@ mysql-test/r/myisam_icp_all.result
Result file for test case for Bug#57372 "Multi-table updates and
deletes fail when running with ICP gainst InnoDB.
@ mysql-test/r/myisam_icp_none.result
Result file for test case for Bug#57372 "Multi-table updates and
deletes fail when running with ICP gainst InnoDB.
@ sql/sql_select.cc
For multi-table delete and update statements the same handler is used
for both doing the query part and the update part. If an index
condition has been pushed down to the storage engine it will be
evaluated during execution of the query part but it might also be
executed during the update part. This can lead to wrong result.
This patch solves this problem by restricting the server to not push
down index conditions if the statement is either a multi-table update or
delete statement.

Patch pushed to mysql-next-mr-opt-backporting with revision id:
olav.sandstaa@oracle.com-20101028080834-ghs007fm0geomvw3

Pushed into mysql-trunk 5.6.99-m5 (revid:alexander.nozdrin@oracle.com-20101113155825-czmva9kg4n31anmu) (version source revid:alexander.nozdrin@oracle.com-20101113155825-czmva9kg4n31anmu) (merge vers: 5.6.99-m5) (pib:21)

Pushed into mysql-next-mr (revid:alexander.nozdrin@oracle.com-20101113160336-atmtmfb3mzm4pz4i) (version source revid:alexander.nozdrin@oracle.com-20101113152540-gxro4g0v29l27f5x) (pib:21)

Bug does not appear in any released 5.6.x version. No 5.6.1 changelog entry needed.