| Bug #36981 | innodb crash when selecting for update | ||
|---|---|---|---|
| Submitted: | 26 May 2008 15:20 | Modified: | 16 Nov 2010 3:56 |
| Reporter: | Shane Bester (Platinum Quality Contributor) | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: Optimizer | Severity: | S1 (Critical) |
| Version: | 6.0.6,6.0.8,6.0.10 | OS: | Any |
| Assigned to: | Olav Sandstå | CPU Architecture: | Any |
| Tags: | index_condition_pushdown, optimizer_switch, v6 | ||
[26 May 2008 15:21]
MySQL Verification Team
bt full output
Attachment: bug36981_bt_full.txt (text/plain), 9.89 KiB.
[26 May 2008 18:28]
Sveta Smirnova
Thank you for the report. Verified as described.
[9 Jul 2008 4:13]
Calvin Sun
The crash doesn't occur on 6.0.6 if I turn off DS-MRR and Index Condition Pushdown using
set optimizer_use_mrr='disable';
set engine_condition_pushdown=off;
-------------------------------------------------------
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 6.0.6-alpha-pro-debug Source distribution
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> set engine_condition_pushdown=off;
Query OK, 0 rows affected (0.00 sec)
mysql> set optimizer_use_mrr='disable';
Query OK, 0 rows affected (0.00 sec)
mysql> use test;
Database changed
mysql> drop table if exists `t1`;
Query OK, 0 rows affected (0.05 sec)
mysql> create table `t1` (`c1` char(1) default null,`c2` char(10) default null,
key (`c1`))
-> engine=innodb default charset=latin1;
Query OK, 0 rows affected (0.11 sec)
mysql> insert into `t1` values ('3',null);
Query OK, 1 row affected (0.05 sec)
mysql> select * from `t1` where `c1`='3' for update;
+------+------+
| c1 | c2 |
+------+------+
| 3 | NULL |
+------+------+
1 row in set (0.00 sec)
-------------------------------------------------------
But it crashes if both are on:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 6.0.6-alpha-pro-debug Source distribution
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> use test;
Database changed
mysql> drop table if exists `t1`;
Query OK, 0 rows affected (0.06 sec)
mysql> create table `t1` (`c1` char(1) default null,`c2` char(10) default null,
key (`c1`))
-> engine=innodb default charset=latin1;
Query OK, 0 rows affected (0.11 sec)
mysql> insert into `t1` values ('3',null);
Query OK, 1 row affected (0.06 sec)
mysql> select * from `t1` where `c1`='3' for update;
ERROR 2013 (HY000): Lost connection to MySQL server during query
[29 Dec 2008 21:09]
MySQL Verification Team
this bug can also manifest as a memory corruption, which is detected by innodb like this:
Version: '6.0.8-alpha-community' socket: '' port: 3306 MySQL Community Ser
InnoDB: Error: Memory area size 512, next area size 0 not a power of 2!
InnoDB: Possibly a memory overrun of the buffer being freed here.
InnoDB: Apparent memory corruption: mem dump len 500; hex 000000000000000000
00000000000000000000000000000000000000000000000000000000000000200000000000030
00000000000000000000000000000000300000006000000070000000400; asc
;
InnoDB: Scanning backward trying to find previous allocated mem blocks
Mem block at - 496, file x0trx.c, line 185
Mem block at - 1008, file x0trx.c, line 171
Mem block at - 2032, file x0trx.c, line 107
Freed mem block at - 3056, file mysql.c, line 597
Mem block at - 3312, file w0ins.c, line 82
Freed mem block at - 3568, file 0pcur.c, line 29
Mem block at - 4080, file t0mem.c, line 246
Freed mem block at - 4336, file 0pcur.c, line 29
<cut>
[8 Jul 2009 6:11]
Nidhi Shrotriya
Marking as duplicate of 45029.
[8 Jul 2009 6:12]
Nidhi Shrotriya
Closing as duplicate of 45029.
[8 Jul 2009 7:59]
Timour Katchaounov
Nidhi, First, you should not close duplicate bugs, a duplicate is a duplicate, it is not resolved until the main bug is fixed. When the main bug is fixed, that fix needs to be tested against all duplicates, and only if the duplicates are fixed, one can close them. Second, I don't see any reasoning why this is a duplicate. Have you debugged this case, have you seen some other bug with exactly the same failure? If there are bugs with exactly the same stack traces, and similar queries, then one may assume they are duplicates, otherwise no. Just determining that a bug is in a certain area, and that some other bug is in the same area of functionality, doesn't make the first a duplicate. I am setting this back to Verified, so that you re-evaluate this bug. In general, if I see other bugs being dealt with in such a formal way, I will request a reevaluation of all bugs you closed. Finally, what is your role with this bug? If you were assigned to fix, why you are not set in the "Assign to" field?
[8 Oct 2009 12:09]
Guilhem Bichot
goes away / comes back if disabling/enabling Index Condition Pushdown in InnoDB.
[6 Nov 2009 12:59]
Olav Sandstå
Valgrind reports the following memory issue: ==19047== Thread 12: ==19047== Invalid write of size 8 ==19047== at 0x8F020C: build_template(row_prebuilt_struct*, THD*, TABLE*, ha_innobase*, unsigned) (ha_innodb.cc:3437) ==19047== by 0x8F3323: ha_innobase::change_active_index(unsigned) (ha_innodb.cc:4637) ==19047== by 0x8F3492: ha_innobase::index_init(unsigned, bool) (ha_innodb.cc:4309) ==19047== by 0x5812B3: handler::ha_index_init(unsigned, bool) (handler.h:1559) ==19047== by 0x702FD7: join_read_always_key(st_join_table*) (sql_select.cc:16982) ==19047== by 0x70653C: sub_select(JOIN*, st_join_table*, bool) (sql_select.cc:16311) ==19047== by 0x71421A: do_select(JOIN*, List<Item>*, TABLE*, Procedure*) (sql_select.cc:15874) ==19047== by 0x731B69: JOIN::exec() (sql_select.cc:2929) ==19047== by 0x72C183: mysql_select(THD*, Item***, TABLE_LIST*, unsigned, List<Item>&, Item*, unsigned, st_order*, st_order*, Item*, st_order*, unsigned long long, select_result*, st_select_lex_unit*, st_select_lex*) (sql_select.cc:3120)==19047== by 0x731EA2: handle_select(THD*, LEX*, select_result*, unsigned long) (sql_select.cc:307) ==19047== by 0x6868A4: execute_sqlcom_select(THD*, TABLE_LIST*) (sql_parse.cc:4965) ==19047== by 0x687905: mysql_execute_command(THD*) (sql_parse.cc:2156) ==19047== by 0x68FB0D: mysql_parse(THD*, char const*, unsigned, char const**) (sql_parse.cc:5979) ==19047== by 0x690FEA: dispatch_command(enum_server_command, THD*, char*, unsigned) (sql_parse.cc:1076) ==19047== by 0x6924B5: do_command(THD*) (sql_parse.cc:758) ==19047== by 0x67FB9C: handle_one_connection (sql_connect.cc:1164) This is caused by the same "out of array" write as in Bug#43360. See Bug#43360 for details about the issue.
[6 Nov 2009 13:18]
Olav Sandstå
By doing a "quick fix" of the first memory issue by just doubling the size of the array (as described in Bug#43360) fixes the previous memory issue. The test case still fails and a new valgrind run reveals a second memory issue where uninitialized memory is read; ==27467== Thread 12: ==27467== Conditional jump or move depends on uninitialised value(s) ==27467== at 0x95CD2D: row_sel_store_mysql_rec (row0sel.c:2614) ==27467== by 0x95F3AC: row_search_for_mysql (row0sel.c:4154) ==27467== by 0x8F36BE: ha_innobase::index_read(unsigned char*, unsigned char const*, unsigned, ha_rkey_function) (ha_innodb.cc:4515) ==27467== by 0x7E567F: handler::index_read_map(unsigned char*, unsigned char const*, unsigned long, ha_rkey_function) (handler.h:1796) ==27467== by 0x703121: join_read_always_key(st_join_table*) (sql_select.cc:16996) ==27467== by 0x70653C: sub_select(JOIN*, st_join_table*, bool) (sql_select.cc:16311) ==27467== by 0x71421A: do_select(JOIN*, List<Item>*, TABLE*, Procedure*) (sql_select.cc:15874) ==27467== by 0x731B69: JOIN::exec() (sql_select.cc:2929) ==27467== by 0x72C183: mysql_select(THD*, Item***, TABLE_LIST*, unsigned, List<Item>&, Item*, unsigned, st_order*, st_order*, Item*, st_order*, unsigned long long, select_result*, st_select_lex_unit*, st_select_lex*) (sql_select.cc:3120)==27467== by 0x731EA2: handle_select(THD*, LEX*, select_result*, unsigned long) (sql_select.cc:307) ==27467== by 0x6868A4: execute_sqlcom_select(THD*, TABLE_LIST*) (sql_parse.cc:4965) ==27467== by 0x687905: mysql_execute_command(THD*) (sql_parse.cc:2156) ==27467== by 0x68FB0D: mysql_parse(THD*, char const*, unsigned, char const**) (sql_parse.cc:5979) ==27467== by 0x690FEA: dispatch_command(enum_server_command, THD*, char*, unsigned) (sql_parse.cc:1076) ==27467== by 0x6924B5: do_command(THD*) (sql_parse.cc:758) ==27467== by 0x67FB9C: handle_one_connection (sql_connect.cc:1164) ==27467== This is the same memory issue as reported in Bug#41996.
[3 Dec 2009 11:21]
Olav Sandstå
There is a proposed fix for this problem from sca@askmonty.org on 11/23/09 in the following email: http://lists.mysql.com/internals/37545 This fix has a very good description in Igor's blog: http://igors-notes.blogspot.com/2009/08/in-defense-of-mrr.html
[3 Dec 2009 11:21]
Olav Sandstå
There is a proposed fix for this problem from sca@askmonty.org on 11/23/09 in the following email: http://lists.mysql.com/internals/37545 This fix has a very good description in Igor's blog: http://igors-notes.blogspot.com/2009/08/in-defense-of-mrr.html
[3 Dec 2009 11:46]
Olav Sandstå
The approach suggested in Igor's blog is very similar to the first solution I arrived at when working on Bug#41996. Both solutions disables support for ICP for the query in InnoDB when InnoDB has decided to use the clustered index instead of the scanning index. Both these solutions "fixes" the crash seen in this bug and several related bugs (Bug#35080, Bug#40992, Bug#41996). Unfortunately this solution seem not to be correct. It is too late to disable the ICP at this stage of execution of the query. At this point the pushed index condition has been accepted by InnoDB and the server expect it to be used. There seems to be no "second evaluation" done by the MySQL server on what is returned from InnoDB as the pushed index condition is "removed" from the server's condition evaluation when it has been accepted by InnoDB. I will attach a test case illustrating this to this bug report. This test case contains a "normal" select query that is using ICP: select a,filler from t3 where a >= 'c-1994=w' and a != 'c-1996=w'; This query returns the correct rows. The second version of this query contains a SELECT ... FOR UPDATE: select a,filler from t3 where a >= 'c-1994=w' and a != 'c-1996=w' for update; This will trigger disabling of ICP if Igor's or mine proposed solution is used. And as a result the select condition will not be evaluated neither by InnoDB or by the server - and as a result too many rows are returned. I have not tried it out yet but for this to work we need to be able to determine that we should disable ICP for a given query before the pushed down condition has been accepted by InnoDB (in ha_innobase::idx_cond_push()). Alternatives would be to extend the ICP support in InnoDB to handle that the clustered index is used instead of the scanning index or to have the server always evaluate the full select condition (do a "second evaluation").
[3 Dec 2009 14:09]
Olav Sandstå
Test case to illustrate that disabling ICP in build_template does not work
Attachment: icptest.tar.gz (application/gzip, text), 692 bytes.
[11 Dec 2009 13:48]
Bugs System
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/93690 3768 Olav Sandstaa 2009-12-11 Fix for Bug#36981 innodb crash when selecting for update This crash occurred due to a mismatch between what the ICP code in InnoDB expected to be in the InnoDB record and what actually was there. The ICP code expected the record to contain an index entry for the index for the ICP condition while the record contained an entry from the clustered index. This happened due to InnoDB changing from using the ICP index to using the clustered index if the statement should use LOCK_X type locks (which happens when doing SELECT...FOR UPDATE or DELETE operations). The ICP implementation in InnoDB does not support ICP if the index is the primary key (using the clustered index). This change fixes the crash by extending the check for whether or not to accept the pushed index to also not support the case where InnoDB will change from using the ICP index to the clustered index when the lock type is LOCK_X. The attached test case contains two tests. The first is based on the original reproduction case from the bug. The second test case is added to have a test to verify that the condition is evaluated even if it is refused by InnoDB. Note that the included test case should be included in the include/icp_tests.inc file when this have been pushed as part of the fix for Bug #43360. This will ensure it is run for both InnoDB and MyISAM. @ mysql-test/r/bug36981.result Test result file for Bug#36981 @ mysql-test/suite/optimizer_unfixed_bugs/r/bug36981.result Moved to the test case for Bug#36981 @ mysql-test/suite/optimizer_unfixed_bugs/t/bug36981.test This test is now included in the test case for Bug#36981 @ mysql-test/t/bug36981.test Tests for Bug#36981. The first test is based on the original reproduction case from the bug. The second test is added to have a test to verify that the condition is evaluated even if it is refused by InnoDB. @ storage/innobase/handler/ha_innodb.cc Do not accept a pushed index condition if the lock strategy is LOCK_X since InnoDB in this case will switch from using the ICP index to the clustered index (which is not supported by the ICP implementation).
[14 Dec 2009 9:45]
Jørgen Løland
Patch approved pending fix of comment in email (add ASSERT that index switch has not happened when ICP is in use)
[17 Dec 2009 13:15]
Bugs System
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/94737 3778 Olav Sandstaa 2009-12-17 Fix for Bug#36981 innodb crash when selecting for update This crash occurred due to a mismatch between what the ICP code in InnoDB expected to be in the InnoDB record and what actually was there. The ICP code expected the record to contain an index entry for the index for the ICP condition while the record contained an entry from the clustered index. This happened due to InnoDB changing from using the ICP index to using the clustered index if the statement should use LOCK_X type locks (which happens when doing SELECT...FOR UPDATE or DELETE operations). The ICP implementation in InnoDB does not support ICP if the index is the primary key (using the clustered index). This change fixes the crash by extending the check for whether or not to accept the pushed index to also not support the case where InnoDB will change from using the ICP index to the clustered index when the lock type is LOCK_X. The attached test case contains two tests. The first is based on the original reproduction case from the bug. The second test case is added to have a test to verify that the condition is evaluated even if it is refused by InnoDB. Note that the included test case should be included in the include/icp_tests.inc file when this have been pushed as part of the fix for Bug #43360. This will ensure it is run for both InnoDB and MyISAM. @ mysql-test/r/bug36981.result Test result file for Bug#36981. @ mysql-test/suite/optimizer_unfixed_bugs/r/bug36981.result Moved to the test case for Bug#36981. @ mysql-test/suite/optimizer_unfixed_bugs/t/bug36981.test This test is now included in the test case for Bug#36981. @ mysql-test/t/bug36981.test Tests for Bug#36981. The first test is based on the original reproduction case from the bug. The second test is added to have a test to verify that the condition is evaluated even if it is refused by InnoDB. @ storage/innobase/handler/ha_innodb.cc Do not accept a pushed index condition if the lock strategy is LOCK_X since InnoDB in this case will switch from using the ICP index to the clustered index (which is not supported by the ICP implementation). @ storage/innobase/row/row0sel.c Change assert in the ICP implementation to test that the mysql_template is generated for a request for the index record's field and not for the entire base record.
[25 Feb 2010 12:55]
Bugs System
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/101446 2963 Olav Sandstaa 2010-02-25 Fix for Bug#36981 innodb crash when selecting for update. This crash occurred due to a mismatch between what the ICP code in InnoDB expected to be in the InnoDB record and what actually was there. The ICP code expected the record to contain an index entry for the index for the ICP condition while the record contained an entry from the clustered index. This happened due to InnoDB changing from using the ICP index to using the clustered index if the statement should use LOCK_X type locks (which happens when doing SELECT...FOR UPDATE or DELETE operations). The ICP implementation in InnoDB does not support ICP if the index is the primary key (using the clustered index). This change fixes the crash by extending the check for whether or not to accept the pushed index to also not support the case where InnoDB will change from using the ICP index to the clustered index when the lock type is LOCK_X. The attached test case contains two tests. The first is based on the original reproduction case from the bug. The second test case is added to have a test to verify that the condition is evaluated even if it is refused by InnoDB. @ mysql-test/include/icp_tests.inc Test for Bug#36981 innodb crash when selecting for update. @ mysql-test/r/innodb_icp.result Result file for ICP Bug#36981 when run against InnoDB. @ mysql-test/r/myisam_icp.result Result file for ICP Bug#36981 when run against MyISAM. @ mysql-test/suite/optimizer_unfixed_bugs/r/bug36981.result Moved to the new ICP test file icp_test.inc. @ mysql-test/suite/optimizer_unfixed_bugs/t/bug36981.test Moved to the new ICP test file icp_test.inc. @ mysql-test/t/innodb_icp.test Test case for ICP code for InnoDB. Note that currently this test will only run for debug builds due to ICP being disabled for InnoDB in release builds. @ mysql-test/t/myisam_icp.test Test case for ICP code in MyISAM. @ storage/innobase/handler/ha_innodb.cc Do not accept a pushed index condition if the lock strategy is LOCK_X since InnoDB in this case will switch from using the ICP index to the clustered index (which is not supported by the ICP implementation). @ storage/innobase/row/row0sel.c Change assert in the ICP implementation to test that the mysql_template is generated for a request for the index record's field and not for the entire base record.
[25 Feb 2010 13:29]
Olav Sandstå
Patch pushed to mysql-6.0-codebase-bugfixing with revision-id: olav@sun.com-20100225125531-9249z4xxvxqya96u .
[25 Feb 2010 19:47]
Bugs System
Pushed into 6.0.14-alpha (revid:alik@sun.com-20100225194420-p60r4u90tszj8q2x) (version source revid:vvaintroub@mysql.com-20100225163940-6j4h2jgidjbz4ikm) (merge vers: 6.0.14-alpha) (pib:16)
[25 Feb 2010 20:00]
Paul DuBois
Noted in 6.0.14 changelog. With index condition pushdown enabled, InnoDB could crash due to a mismatch between what pushdown code expected to be in a record versus was was actually there.
[6 Mar 2010 10:29]
Bugs System
Pushed into 6.0.14-alpha (revid:alik@sun.com-20100306102742-yw9zzgw9ac5r65m5) (version source revid:bar@mysql.com-20100305074327-h09o5lw290s04lcf) (merge vers: 6.0.14-alpha) (pib:16)
[7 May 2010 10:19]
Bugs System
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/107725 3144 Olav Sandstaa 2010-05-07 Fix for Bug#36981 innodb crash when selecting for update. (Backporting of revid:olav@sun.com-20100225125531-9249z4xxvxqya96u) This crash occurred due to a mismatch between what the ICP code in InnoDB expected to be in the InnoDB record and what actually was there. The ICP code expected the record to contain an index entry for the index for the ICP condition while the record contained an entry from the clustered index. This happened due to InnoDB changing from using the ICP index to using the clustered index if the statement should use LOCK_X type locks (which happens when doing SELECT...FOR UPDATE or DELETE operations). The ICP implementation in InnoDB does not support ICP if the index is the primary key (using the clustered index). This change fixes the crash by extending the check for whether or not to accept the pushed index to also not support the case where InnoDB will change from using the ICP index to the clustered index when the lock type is LOCK_X. The attached test case contains two tests. The first is based on the original reproduction case from the bug. The second test case is added to have a test to verify that the condition is evaluated even if it is refused by InnoDB. @ mysql-test/include/icp_tests.inc Test for Bug#36981 innodb crash when selecting for update. @ mysql-test/r/innodb_icp.result Result file for ICP Bug#36981 when run against InnoDB. @ mysql-test/r/myisam_icp.result Result file for ICP Bug#36981 when run against MyISAM. @ mysql-test/t/innodb_icp.test Test case for ICP code for InnoDB. Note that currently this test will only run for debug builds due to ICP being disabled for InnoDB in release builds. @ mysql-test/t/myisam_icp.test Test case for ICP code in MyISAM. @ storage/innobase/handler/ha_innodb.cc Do not accept a pushed index condition if the lock strategy is LOCK_X since InnoDB in this case will switch from using the ICP index to the clustered index (which is not supported by the ICP implementation). @ storage/innobase/row/row0sel.c Change assert in the ICP implementation to test that the mysql_template is generated for a request for the index record's field and not for the entire base record.
[16 Aug 2010 6:33]
Bugs System
Pushed into mysql-next-mr (revid:alik@sun.com-20100816062819-bluwgdq8q4xysmlg) (version source revid:alik@sun.com-20100816062612-enatdwnv809iw3s9) (pib:20)
[13 Nov 2010 16:10]
Bugs System
Pushed into mysql-trunk 5.6.99-m5 (revid:alexander.nozdrin@oracle.com-20101113155825-czmva9kg4n31anmu) (version source revid:vasil.dimov@oracle.com-20100629074804-359l9m9gniauxr94) (merge vers: 5.6.99-m4) (pib:21)
[16 Nov 2010 3:56]
Paul DuBois
Noted in 5.6.1 changelog.
Description: today i built mysql-6.0 from mysql.bkbits.net and got a crash: 0x00d0081c in memcpy () from /lib/i686/nosegneg/libc.so.6 (gdb) bt #0 0x00d0081c in memcpy #1 0x0853ebc4 in row_sel_store_mysql_rec #2 0x08542077 in row_search_for_mysql #3 0x084b8b8f in ha_innobase::index_read #4 0x0832921f in handler::index_read_map #5 0x082a009f in join_read_always_key #6 0x0829f236 in sub_select #7 0x082a0baa in do_select #8 0x082a6440 in JOIN::exec #9 0x082a71df in mysql_select #10 0x082a7741 in handle_select #11 0x0822a164 in execute_sqlcom_select #12 0x0822d393 in mysql_execute_command #13 0x08234c5a in mysql_parse #14 0x08235ba8 in dispatch_command #15 0x08236cf1 in do_command #16 0x08225eda in handle_one_connection #17 0x00de0402 in start_thread #18 0x00d612ae in clone How to repeat: drop table if exists `t1`; create table `t1` (`c1` char(1) default null,`c2` char(10) default null, key (`c1`)) engine=innodb default charset=latin1; insert into `t1` values ('3',null); select * from `t1` where `c1`='3' for update;