InnoDB: Error: row_search_for_mysql() is called without ha_innobase::external_lock() in maria-5.3

[Philip Stoev]

The following query causes the following Innodb errors to be printed in the log:

InnoDB: Error: row_search_for_mysql() is called without ha_innobase::external_lock()
110415 13:32:28 [ERROR] trx->active_flag & TRX_ACTIVE_IN_MYSQL== 0, but trx->conc_state != TRX_NOT_STARTED

The query then crashes with bug https://bugs.launchpad.net/maria/+bug/751439

IF the innodb error is converted to an assertion, the backtrace is as follows:

#8 0x006b0d98 in __assert_fail () from /lib/libc.so.6
#9 0x086c8f8d in row_search_for_mysql (buf=0xb6c2ba70 "\377", mode=1, prebuilt=0xb6c2bf60, match_mode=0, direction=0) at row/row0sel.c:3801
#10 0x0866d4eb in ha_innobase::index_read (this=0xb6c2b540, buf=0xb6c2ba70 "\377", key_ptr=0x0, key_len=0, find_flag=HA_READ_AFTER_KEY)
    at handler/ha_innodb.cc:5905
#11 0x0866e008 in ha_innobase::index_first (this=0xb6c2b540, buf=0xb6c2ba70 "\377") at handler/ha_innodb.cc:6229
#12 0x0866e247 in ha_innobase::rnd_next (this=0xb6c2b540, buf=0xb6c2ba70 "\377") at handler/ha_innodb.cc:6326
#13 0x081b41ae in handler::ha_rnd_next (this=0xb6c2b540, buf=0xb6c2ba70 "\377") at sql_class.h:3606
#14 0x083cbfa5 in rr_sequential (info=0xb6c3694c) at records.cc:399
#15 0x0832ad47 in join_init_read_record (tab=0xb6c368f4) at sql_select.cc:15052
#16 0x0832923e in sub_select (join=0xb6c31470, join_tab=0xb6c368f4, end_of_records=false) at sql_select.cc:14274
#17 0x083298fe in evaluate_join_record (join=0xb6c31470, join_tab=0xb6c366f8, error=0) at sql_select.cc:14472
#18 0x08329291 in sub_select (join=0xb6c31470, join_tab=0xb6c366f8, end_of_records=false) at sql_select.cc:14277
#19 0x08328b38 in do_select (join=0xb6c31470, fields=0xb6c15eac, table=0x0, procedure=0x0) at sql_select.cc:13945
#20 0x0830ea2b in JOIN::exec (this=0xb6c31470) at sql_select.cc:2486
#21 0x0823f451 in subselect_single_select_engine::exec (this=0xb6c169b0) at item_subselect.cc:2738
#22 0x082399ed in Item_subselect::exec (this=0xb6c168b8) at item_subselect.cc:468
#23 0x08239c24 in Item_in_subselect::exec (this=0xb6c168b8) at item_subselect.cc:571
#24 0x0823b672 in Item_in_subselect::val_bool (this=0xb6c168b8) at item_subselect.cc:1244
#25 0x081d861a in Item::val_bool_result (this=0xb6c168b8) at item.h:802
#26 0x082046f0 in Item_in_optimizer::val_int (this=0xb6c242f8) at item_cmpfunc.cc:1980
#27 0x08328aff in do_select (join=0xb6c2c5e0, fields=0xb6c3137c, table=0x0, procedure=0x0) at sql_select.cc:13942
#28 0x0830ea2b in JOIN::exec (this=0xb6c2c5e0) at sql_select.cc:2486
#29 0x0830f258 in mysql_select (thd=0xb34e5b0, rref_pointer_array=0xb350050, tables=0xb6c14db8, wild_num=0, fields=..., conds=0xb6c168b8, og_num=1,
    order=0x0, group=0xb6c24078, having=0x0, proc_param=0x0, select_options=2147764736, result=0xb6c24128, unit=0xb34fcac, select_lex=0xb34ff4c)
    at sql_select.cc:2707
#30 0x083078a7 in handle_select (thd=0xb34e5b0, lex=0xb34fc50, result=0xb6c24128, setup_tables_done_option=0) at sql_select.cc:280
#31 0x082a4cc8 in execute_sqlcom_select (thd=0xb34e5b0, all_tables=0xb6c14db8) at sql_parse.cc:5090
#32 0x0829bb3a in mysql_execute_command (thd=0xb34e5b0) at sql_parse.cc:2235
#33 0x082a726e in mysql_parse (thd=0xb34e5b0,
    rawbuf=0xb6c14a90 "SELECT alias1.f10 AS field2\nFROM t2 AS alias1\nJOIN (\nt3 AS alias2\nJOIN t1 AS alias3\nON alias3.f10\n) ON alias3.f1\nWHERE alias2.f11 IN (\nSELECT SQ4_alias1.f10\nFROM t1 AS SQ4_alias1\nLEFT JOIN t2 AS SQ4_"..., length=242, found_semicolon=0xa28b6228) at sql_parse.cc:6090
#34 0x082997c0 in dispatch_command (command=COM_QUERY, thd=0xb34e5b0, packet=0xb3a4281 "", packet_length=243) at sql_parse.cc:1211
#35 0x08298c6d in do_command (thd=0xb34e5b0) at sql_parse.cc:904
#36 0x08295d20 in handle_one_connection (arg=0xb34e5b0) at sql_connect.cc:1154
#37 0x00821919 in start_thread () from /lib/libpthread.so.0
#38 0x0076acce in clone () from /lib/libc.so.6

explain:

id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY alias2 system NULL NULL NULL NULL 1 Using temporary; Using filesort
1 PRIMARY alias1 ALL NULL NULL NULL NULL 1
1 PRIMARY alias3 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (flat, BNL join)
2 DEPENDENT SUBQUERY SQ4_alias1 ALL NULL NULL NULL NULL 2 Using where
2 DEPENDENT SUBQUERY SQ4_alias3 ALL NULL NULL NULL NULL 1 Using where

test case:

--source include/have_innodb.inc

CREATE TABLE t1 ( f1 int NOT NULL , f10 int) ;
INSERT IGNORE INTO t1 VALUES (25,0),(29,0);

CREATE TABLE t2 ( f10 int) ENGINE=InnoDB;

CREATE TABLE t3 ( f11 int) ;
INSERT IGNORE INTO t3 VALUES (0);

SELECT alias1.f10 AS field2
FROM t2 AS alias1
JOIN (
        t3 AS alias2
        JOIN t1 AS alias3
        ON alias3.f10
) ON alias3.f1
WHERE alias2.f11 IN (
        SELECT SQ4_alias1.f10
        FROM t1 AS SQ4_alias1
        LEFT JOIN t2 AS SQ4_alias3 ON SQ4_alias3.f10
)
GROUP BY field2 ;

[Timour Katchaounov (timour)]

No longer reproducible with the latest 5.3 as of 14-06-2011, however
this bug is possibly related to semijoin+outer join, thus assigning to
SergeyP so he can add the test case where appropriate.

[Sergey Petrunia (sergefp)]

This is not the kind of problem that we have in outer join + semi-join handling. Added a testcase to the testsuite. Changing status to Invalid.