Bug #34590 Innodb crash in build_template() on multi-table delete
Submitted: 15 Feb 2008 11:51 Modified: 20 Jan 2010 11:45
Reporter: Philip Stoev Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S3 (Non-critical)
Version:6.0.4 OS:Any
Assigned to: Assigned Account
Tags: index_condition_pushdown, optimizer_switch, v6
Triage: Triaged: D1 (Critical)

[15 Feb 2008 11:51] Philip Stoev
Description:
Innodb crashes when the queries provided in "how to repeat" is executed in a mysql client.

Backtrace is:

#0  0x00110402 in __kernel_vsyscall ()
#1  0x00bdc617 in pthread_kill () from /lib/libpthread.so.0
#2  0x083f46fb in write_core (sig=11) at stacktrace.c:240
#3  0x0828dc05 in handle_segfault (sig=11) at mysqld.cc:2313
#4  <signal handler called>
#5  0x08559bbe in build_template (prebuilt=0xb7082e68, thd=<value optimized out>, table=0x96f7bb8, file=0x9706740, templ_type=1) at handler/ha_innodb.cc:3236
#6  0x0855a04b in ha_innobase::change_active_index (this=0x9706740, keynr=1) at handler/ha_innodb.cc:4307
#7  0x0855a193 in ha_innobase::index_init (this=0x9706740, keynr=1, sorted=false) at handler/ha_innodb.cc:3979
#8  0x083096c8 in join_read_always_key (tab=0x971efc4) at handler.h:1413
#9  0x08300268 in sub_select (join=0x971a8c8, join_tab=0x971efc4, end_of_records=false) at sql_select.cc:13280
#10 0x083000cb in evaluate_join_record (join=0x971a8c8, join_tab=0x971ee10, error=<value optimized out>) at sql_select.cc:13505
#11 0x08300273 in sub_select (join=0x971a8c8, join_tab=0x971ee10, end_of_records=false) at sql_select.cc:13281
#12 0x0830a939 in do_select (join=0x971a8c8, fields=0x970a9b0, table=0x0, procedure=0x0) at sql_select.cc:13038
#13 0x0830ff97 in JOIN::exec (this=0x971a8c8) at sql_select.cc:2713
#14 0x08310cac in mysql_select (thd=0x96a2cb8, rref_pointer_array=0x970aa20, tables=0x970b3d0, wild_num=0, fields=@0x970a9b0, conds=0x9716788, og_num=0,
    order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=3491516032, result=0x971a888, unit=0x970a694, select_lex=0x970a91c)
    at sql_select.cc:2901
#15 0x0829adce in mysql_execute_command (thd=0x96a2cb8) at sql_parse.cc:2834
#16 0x08405f17 in sp_instr_stmt::exec_core (this=0x970bea8, thd=0x96a2cb8, nextp=0xb2797760) at sp_head.cc:2807
#17 0x0840618a in sp_lex_keeper::reset_lex_and_exec_core (this=0x970bed0, thd=0x96a2cb8, nextp=0xb2797760, open_tables=false, instr=0x970bea8)
    at sp_head.cc:2649
#18 0x0840cff2 in sp_instr_stmt::execute (this=0x970bea8, thd=0x96a2cb8, nextp=0xb2797760) at sp_head.cc:2758
#19 0x08409d52 in sp_head::execute (this=0x9709f68, thd=0x96a2cb8) at sp_head.cc:1195
#20 0x0840a786 in sp_head::execute_procedure (this=0x9709f68, thd=0x96a2cb8, args=0x96a3fbc) at sp_head.cc:1904
#21 0x0829d718 in mysql_execute_command (thd=0x96a2cb8) at sql_parse.cc:3858
#22 0x0829ff5c in mysql_parse (thd=0x96a2cb8, inBuf=0x96ff2d8 "CALL delete_multi(@uuid)", length=24, found_semicolon=0xb27982e4) at sql_parse.cc:5410
#23 0x082a0ed9 in dispatch_command (command=COM_QUERY, thd=0x96a2cb8, packet=0x96f06f9 "CALL delete_multi(@uuid)", packet_length=24) at sql_parse.cc:921
#24 0x082a1f73 in do_command (thd=0x96a2cb8) at sql_parse.cc:697
#25 0x08291b1a in handle_one_connection (arg=0x96a2cb8) at sql_connect.cc:1146
#26 0x00bd750b in start_thread () from /lib/libpthread.so.0
#27 0x00b18b2e in clone () from /lib/libc.so.6

How to repeat:
CREATE TABLE `inter1` (
  `t1_autoinc` int(11) NOT NULL AUTO_INCREMENT,
  `t1_uuid` char(36) DEFAULT NULL,
  PRIMARY KEY (`t1_autoinc`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `inter2` (
  `t2_autoinc` int(11) NOT NULL AUTO_INCREMENT,
  `t1_uuid` char(36) DEFAULT NULL,
  `t2_date` datetime DEFAULT NULL,
  PRIMARY KEY (`t2_autoinc`),
  KEY `t1_uuid` (`t1_uuid`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE VIEW view1 AS SELECT * FROM inter1 WHERE t1_uuid IS NULL or t1_uuid LIKE '%-%';
CREATE VIEW view2 AS SELECT * FROM inter2 WHERE t1_uuid IS NULL or LENGTH(t1_uuid) = 36;

CREATE PROCEDURE delete_multi (IN uuid CHAR(36))
        DELETE view1, view2 FROM view1 INNER JOIN view2
        ON view1.t1_uuid = view2.t1_uuid
        WHERE view1.t1_uuid = @uuid;

SET @uuid = UUID();

SET AUTOCOMMIT=OFF;
START TRANSACTION;

INSERT INTO view1 (t1_uuid) VALUES (@uuid);

SELECT t1_autoinc FROM view1 WHERE t1_uuid = @uuid INTO @t1_autoinc;
INSERT INTO view2 (t1_uuid, t2_date) VALUES (@uuid, '2009-09-09');

CALL delete_multi(@uuid);
[15 Feb 2008 12:04] Philip Stoev
May be related to bug #34591 - same queries are involved, stack trace is different.
[15 Feb 2008 12:57] Susanne Ebrecht
Verified as described.

mysql> INSERT INTO view2 (t1_uuid, t2_date) VALUES (@uuid, '2009-09-09');
Query OK, 1 row affected (0.00 sec)

mysql> CALL delete_multi(@uuid);
ERROR 2013 (HY000): Lost connection to MySQL server during query

mysql> select version()\G
*************************** 1. row ***************************
version(): 6.0.5-alpha-debug
[19 Feb 2008 18:02] Valerii Kravchuk
We should check if 5.x versions are also affected.
[19 Feb 2008 18:56] Valerii Kravchuk
This bug is NOT repeatable with 5.0.54.
[19 Feb 2008 19:03] Valerii Kravchuk
The bug is not repeatable with 5.1.23-rc also. So, this is indeed a 6.0.x-only bug.
[4 Mar 2008 17:34] Heikki Tuuri
Calvin, when you build 6.0, please look at this crash. MySQL may be calling handler functions in a wrong order.
--Heikki
[2 Jul 2008 14:38] Philip Stoev
This crash just happened on a single-table DELETE:

DELETE K FROM `B` AS K WHERE K . int_key < 118

int_key is an integer column with a key on it.

stack trace is :

#0  0x00110416 in __kernel_vsyscall ()
#1  0x00581c78 in pthread_kill () from /lib/libpthread.so.0
#2  0x085b0dc4 in my_write_core (sig=11) at stacktrace.c:307
#3  0x08213e88 in handle_segfault (sig=11) at mysqld.cc:2638
#4  <signal handler called>
#5  0x084cbaa7 in build_template (prebuilt=0xadbdb068, thd=<value optimized out>, table=0x9fadf70, file=0x9fa3320, templ_type=0) at handler/ha_innodb.cc:3265
#6  0x084cc543 in ha_innobase::index_read (this=0x9fa3320, buf=0x9fa34b8 "Ъ", key_ptr=0x9f9df28 "\001", key_len=5, find_flag=HA_READ_AFTER_KEY)
    at handler/ha_innodb.cc:4203
#7  0x0830bd93 in handler::index_read_map (this=0x9fa3320, buf=0x9fa34b8 "Ъ", key=0x9f9df28 "\001", keypart_map=1, find_flag=HA_READ_AFTER_KEY)
    at ../../sql/handler.h:1664
#8  0x0830436f in handler::read_range_first (this=0x9fa3320, start_key=0x9fa33a8, end_key=0x9fa33b8, eq_range_arg=false, sorted=false) at handler.cc:4907
#9  0x084c7fdf in ha_innobase::read_range_first (this=0x9fa3320, start_key=0x9fa33a8, end_key=0x9fa33b8, eq_range_arg=<value optimized out>,
    sorted=<value optimized out>) at handler/ha_innodb.cc:8394
#10 0x0830337d in handler::multi_range_read_next (this=0x9fa3320, range_info=0xa896b58c) at handler.cc:4262
#11 0x08304d40 in DsMrr_impl::dsmrr_next (this=0x9fa3494, h=0x9fa3320, range_info=0xa896b58c) at handler.cc:4464
#12 0x084c80af in ha_innobase::multi_range_read_next (this=0x9fa3320, range_info=0xa896b58c) at handler/ha_innodb.cc:8327
#13 0x082ea15a in QUICK_RANGE_SELECT::get_next (this=0x9fc8030) at opt_range.cc:8518
#14 0x082fe857 in rr_quick (info=0x9f940cc) at records.cc:298
#15 0x0826e88c in join_init_read_record (tab=0x9f94088) at sql_select.cc:14481
#16 0x08281615 in sub_select (join=0x9fc4ca8, join_tab=0x9f94088, end_of_records=false) at sql_select.cc:13636
#17 0x0828b5b9 in do_select (join=0x9fc4ca8, fields=0x9fb404c, table=0x0, procedure=0x0) at sql_select.cc:13387
#18 0x0828d761 in JOIN::exec (this=0x9fc4ca8) at sql_select.cc:2811
#19 0x0828e1fa in mysql_select (thd=0x9fb2bd8, rref_pointer_array=0x9fb40bc, tables=0x9f93570, wild_num=0, fields=@0x9fb404c, conds=0x9f93ab8, og_num=0,
    order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=3489942144, result=0x9f93c08, unit=0x9fb3d28, select_lex=0x9fb3fb8)
    at sql_select.cc:3001
#20 0x0822350f in mysql_execute_command (thd=0x9fb2bd8) at sql_parse.cc:3127
#21 0x0822a02e in mysql_parse (thd=0x9fb2bd8, inBuf=0x9f92e08 "DELETE K FROM `B` AS K WHERE K . int_key < 118", length=46, found_semicolon=0xa896c314)
    at sql_parse.cc:5811
#22 0x0822a921 in dispatch_command (command=COM_QUERY, thd=0x9fb2bd8, packet=0x9fa3711 "DELETE K FROM `B` AS K WHERE K . int_key < 118  ", packet_length=48)
    at sql_parse.cc:1051
#23 0x0822ba49 in do_command (thd=0x9fb2bd8) at sql_parse.cc:724
#24 0x0821bef0 in handle_one_connection (arg=0x9fb2bd8) at sql_connect.cc:1153
#25 0x0057d32f in start_thread () from /lib/libpthread.so.0
#26 0x0049a27e in clone () from /lib/libc.so.6

The crash is here:

3260                            templ->mysql_length_bytes = (ulint)
3261                                    (((Field_varstring*)field)->length_bytes);
3262                    }
3263
3264                    templ->charset = dtype_get_charset_coll_noninline(
3265                                    index->table->cols[i].prtype); <<<< HERE
3266                    templ->mbminlen = index->table->cols[i].mbminlen;
3267                    templ->mbmaxlen = index->table->cols[i].mbmaxlen;
3268                    templ->is_unsigned = index->table->cols[i].prtype
3269                                                            & DATA_UNSIGNED;

(gdb) print index
$1 = (dict_index_t *) 0xadd3ba68
(gdb) print index->table
$2 = (dict_table_t *) 0xadd35068
(gdb) print index->table->cols
$3 = (dict_col_t *) 0x6
[8 Jul 2008 18:55] Sergey Petrunya
The crash doesn't occur on 6.0 if I turn off DS-MRR and Index Condition Pushdown using

set optimizer_use_mrr='disable';
set engine_condition_pushdown=off;
[8 Jul 2008 18:57] Sergey Petrunya
... which means the problem is on MySQL side, not on InnoDB's. 

Changing category and re-assigning accordingly.
[21 Aug 2008 22:23] Sergey Petrunya
* Repeatable on the latest bzr tree
* Repeatable when fix for BUG#37842 is applied

* Not repeatable if one does SET engine_condition_pushdown=off
* The testcase does not use DS-MRR. 
This seems to be an index-condition-pushdown problem.
[26 Aug 2008 9:44] Sergey Petrunya
After initial investigation it seems that the problem is that build_template() doesn't work correctly when IndexConditionPushdown is used lock_type==LOCK_X which causes template_type=ROW_MYSQL_WHOLE_ROW.

The part of ICP code inside row_search_for_mysql() expects that the first columns in the template are the index components. When that isn't the case (and the columns are of different length), it unpacks index tuple to wrong location, causing memory overrun and all kinds of funny effects.
[8 Jul 2009 6:04] Nidhi Shrotriya
Marking as duplicate of 45029.
[8 Jul 2009 6:06] Nidhi Shrotriya
Closing it as duplicate of 45029.
[4 Dec 2009 7:16] Sveta Smirnova
Not repeatable for me with latest 6.0-codebase.
[9 Dec 2009 10:09] Olav Sandstå
Support for ICP in InnoDB has been disabled (see Bug#45029). In order to enable it this requires adding back the HA_DO_INDEX_COND_PUSHDOWN flag in index_flags() in storage/innobase/handler/ha_innodb.h. Setting it back to verified.
[20 Jan 2010 11:43] Olav Sandstå
The crash reported in this bug is the same as several other bug reports caused by Index Condition Pushdown for InnoDB. These bugs are fixed by the fixes committed for Bug#43360 and Bug#36981. 

I have verified that the initial test case in this bug report still creates the crash if the fix is not applied but runs correctly after applying the patches.

The test case is almost identical to the test case committed for Bug#35080 so I will not add this as a separate test case.

Closing this bug as duplicate of Bug#43360 and Bug#36981.