Olav,
The patch looks good. See one comment inline.
On 11/15/2010 01:42 PM, Olav Sandstaa wrote:
> #At file:///export/home/tmp/mysql/opt-bug56529-v2/ based on
> revid:olav.sandstaa@stripped
>
> 3281 Olav Sandstaa 2010-11-15
> Fix for Bug#56529 Crash due to long semaphore wait in InnoDB with ICP and
> subqueries
>
> This crash was caused by the server pushing down an index condition to
> InnoDB that contained a subselect. When InnoDB executed this index
> condition the subselect would open a new handler object and access
> InnoDB a second time. This caused the same transaction to have two
> active data accesses to InnoDB which triggered the assert in InnoDB.
>
> The fix for this is to not push down index conditions to the handler
> that contain a subselect.
> @ mysql-test/include/icp_tests.inc
> Test case for Bug#56529 Crash due to long semaphore wait in InnoDB
> with ICP and subqueries.
> @ mysql-test/r/innodb_icp.result
> Result file for Test case for Bug#56529 Crash due to long semaphore
> wait in InnoDB with ICP and subqueries.
> @ mysql-test/r/innodb_icp_all.result
> Result file for Test case for Bug#56529 Crash due to long semaphore
> wait in InnoDB with ICP and subqueries.
> @ mysql-test/r/innodb_icp_none.result
> Result file for Test case for Bug#56529 Crash due to long semaphore
> wait in InnoDB with ICP and subqueries.
> @ mysql-test/r/myisam_icp.result
> Result file for Test case for Bug#56529 Crash due to long semaphore
> wait in InnoDB with ICP and subqueries.
> @ mysql-test/r/myisam_icp_all.result
> Result file for Test case for Bug#56529 Crash due to long semaphore
> wait in InnoDB with ICP and subqueries.
> @ mysql-test/r/myisam_icp_none.result
> Result file for Test case for Bug#56529 Crash due to long semaphore
> wait in InnoDB with ICP and subqueries.
> @ sql/sql_select.cc
> We should not push down index conditions to the handler that contains
> a subselect. This was already handled by the default case of the
> switch in uses_index_fields_only() but due to an earlier check in
> uses_index_fields() the code for checking that the condition only
> used "other tables" could return ok early.
>
> Fix this by adding an explicite check for if the condition
> contains a subselect and if so return false.
>
> modified:
> mysql-test/include/icp_tests.inc
> mysql-test/r/innodb_icp.result
> mysql-test/r/innodb_icp_all.result
> mysql-test/r/innodb_icp_none.result
> mysql-test/r/myisam_icp.result
> mysql-test/r/myisam_icp_all.result
> mysql-test/r/myisam_icp_none.result
> sql/sql_select.cc
> === modified file 'mysql-test/include/icp_tests.inc'
>
> === modified file 'mysql-test/include/icp_tests.inc'
> --- a/mysql-test/include/icp_tests.inc 2010-11-08 08:49:16 +0000
> +++ b/mysql-test/include/icp_tests.inc 2010-11-15 12:42:32 +0000
> @@ -515,3 +515,61 @@
> EXPLAIN SELECT * FROM t1 WHERE (c1<= '6566-06-15' AND c2<> 3);
>
> DROP TABLE t1;
> +
> +--echo #
> +--echo # Bug#56529 - "Crash due to long semaphore wait in InnoDB
> +--echo # with ICP and subqueries"
> +--echo #
> +
> +CREATE TABLE t1 (
> + col_int_nokey INTEGER,
> + col_int_key INTEGER,
> + col_varchar_key VARCHAR(1),
> +
> + KEY (col_int_key),
> + KEY (col_varchar_key, col_int_key)
> +);
> +
> +INSERT INTO t1 VALUES (NULL,2,'w');
> +INSERT INTO t1 VALUES (7,9,'m');
> +INSERT INTO t1 VALUES (9,3,'m');
> +INSERT INTO t1 VALUES (7,9,'k');
> +INSERT INTO t1 VALUES (4,NULL,'r');
> +INSERT INTO t1 VALUES (2,9,'t');
> +INSERT INTO t1 VALUES (6,3,'j');
> +INSERT INTO t1 VALUES (8,8,'u');
> +INSERT INTO t1 VALUES (NULL,8,'h');
> +INSERT INTO t1 VALUES (5,53,'o');
> +INSERT INTO t1 VALUES (NULL,0,NULL);
> +INSERT INTO t1 VALUES (6,5,'k');
> +INSERT INTO t1 VALUES (188,166,'e');
> +INSERT INTO t1 VALUES (2,3,'n');
> +INSERT INTO t1 VALUES (1,0,'t');
> +INSERT INTO t1 VALUES (1,1,'c');
> +INSERT INTO t1 VALUES (0,9,'m');
> +INSERT INTO t1 VALUES (9,5,'y');
> +INSERT INTO t1 VALUES (NULL,6,'f');
> +
> +CREATE TABLE t2 (
> + c1 INTEGER NOT NULL
> +);
> +
> +let query=
> +SELECT table1.col_int_nokey
> +FROM
> + (
> + t1 AS table1 STRAIGHT_JOIN
> + (
> + (
> + t1 AS table2 INNER JOIN t1 AS table3
> + ON (table3.col_varchar_key = table2.col_varchar_key)
> + )
> + )
> + ON (table3.col_int_nokey = table1.col_int_key)
> + )
> +WHERE table3.col_int_key != SOME ( SELECT c1 FROM t2 );
This is a quite a bit more verbose than we normally do in our test files. I
suggest removing as many newlines as possible while maintaining fair readability.
--
Jørgen Løland | Senior Software Engineer | +47 73842138
Oracle MySQL
Trondheim, Norway
