MySQL Bugs: #10540: Stored function with innodb table crashes server

Bug #10540	Stored function with innodb table crashes server
Submitted:	11 May 2005 11:02	Modified:	7 Jun 2005 12:27
Reporter:	Per-Erik Martin	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	5.0 bk	OS:	Any (Any)
Assigned to:	Assigned Account	CPU Architecture:	Any

Description:
(Moved from BUG#10054)

It works when the engine type is myisam, but not with innodb.
With innodb it crashes regardless of default charset (latin1 and utf8 tested).

How to repeat:
  [11 May 3:37] Markus Popp

I have following table (in MySQL 5.0.4-beta, Windows 2000): 

CREATE TABLE `emails` ( 
`email` varchar(50) NOT NULL, 
PRIMARY KEY (`email`) 
) ENGINE=InnoDB DEFAULT CHARSET=utf8; 

INSERT INTO `emails` VALUES ('abc@def.gh'); 
INSERT INTO `emails` VALUES ('bcd@efg.hi'); 
INSERT INTO `emails` VALUES ('cde@fgh.ij'); 
INSERT INTO `emails` VALUES ('def@ghi.jk'); 
INSERT INTO `emails` VALUES ('efg@hij.kl'); 
INSERT INTO `emails` VALUES ('fgh@ijk.lm'); 

and following function: 

CREATE FUNCTION domain(email varchar(100)) RETURNS varchar(100) 
begin 
return lower( right( email, length( email ) - locate( '@', email ) ) ); 
end // 

Doing the query ... 

SELECT email, domain(email) from emails 

... the server crashes :(. 

However, if I query ... 

SELECT domain('xyz@abc.de') 

... the function works.
[PEM: Actually, it crashes in this case too. Run with valgrind to make sure the error is caught.]

Verified on Linux.

Perhaps the following is a simpler test case (SUSE 9.2, 5.0.6-beta-debug):

mysql> create table tx (s1 char(5)) engine=innodb;
Query OK, 0 rows affected (0.01 sec)

mysql> insert into tx values ('a'),('a');
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> create function fx () returns char(5) return '';
Query OK, 0 rows affected (0.00 sec)

mysql> select fx() from tx;
ERROR 2013 (HY000): Lost connection to MySQL server during query

Hi!

Please post the gdb stack dump. In bugs like this you should always put the information that you have to the bugs database.

Regards,

Heikki

Here's the backtrace for the first test case. (I couldn't repeat the error with the simple
SELECT domain('xyz@abc.de') this time, nor the added simpler testcase).
This is with a valgrind-max build.

Program received signal SIGSEGV, Segmentation fault.
[Switching to Thread 1024 (LWP 4361)]
0x0838ff16 in read_view_sees_trx_id (view=0x0, trx_id={high = 0, low = 777})
    at ../include/read0read.ic:53
Current language:  auto; currently c
(gdb) where
#0  0x0838ff16 in read_view_sees_trx_id (view=0x0, trx_id={high = 0, low = 777})
    at ../include/read0read.ic:53
#1  0x083b8933 in lock_clust_rec_cons_read_sees (rec=0x4142009b "bcd@efg.hi", 
    index=0x41011568, offsets=0xbfffe710, view=0x0) at lock0lock.c:495
#2  0x083644ec in row_search_for_mysql (
    buf=0x8c062d0 "\nabc@def.gh", ' ' <repeats 140 times>, mode=1, 
    prebuilt=0x4101bc68, match_mode=0, direction=1) at row0sel.c:3734
#3  0x082452bc in ha_innobase::general_fetch (this=0x8bea7a0, 
    buf=0x8c062d0 "\nabc@def.gh", ' ' <repeats 140 times>, direction=1, 
    match_mode=0) at ha_innodb.cc:3885
#4  0x0824538d in ha_innobase::index_next (this=0x8bea7a0, 
    buf=0x8c062d0 "\nabc@def.gh", ' ' <repeats 140 times>) at ha_innodb.cc:3922
#5  0x081e0338 in join_read_next (info=0x8c0854c) at sql_select.cc:9815
#6  0x081df0e7 in sub_select (join=0x8c07348, join_tab=0x8c08510, 
    end_of_records=false) at sql_select.cc:9182
#7  0x081dec2d in do_select (join=0x8c07348, fields=0x8becf08, table=0x0, 
    procedure=0x0) at sql_select.cc:8940
#8  0x081cf905 in JOIN::exec (this=0x8c07348) at sql_select.cc:1658
#9  0x081d0526 in mysql_select (thd=0x8becc60, rref_pointer_array=0x8becfc8, 
    tables=0x8c071c0, wild_num=0, fields=@0x8becf08, conds=0x0, og_num=0, 
    order=0x0, group=0x0, having=0x0, proc_param=0x0, 
    select_options=2158250496, result=0x8c0f1e0, unit=0x8beccac, 
    select_lex=0x8bece98) at sql_select.cc:2008
#10 0x081cbd18 in handle_select (thd=0x8becc60, lex=0x8becca0, 
    result=0x8c0f1e0, setup_tables_done_option=0) at sql_select.cc:254
#11 0x0819edc3 in mysql_execute_command (thd=0x8becc60) at sql_parse.cc:2393
#12 0x081a54b2 in mysql_parse (thd=0x8becc60, 
    inBuf=0x8c0fe98 "SELECT email, domain(email) from emails", length=39)
    at sql_parse.cc:5243
#13 0x0819d503 in dispatch_command (command=COM_QUERY, thd=0x8becc60, 
    packet=0x8be1c21 "", packet_length=40) at sql_parse.cc:1651
#14 0x0819cef8 in do_command (thd=0x8becc60) at sql_parse.cc:1454
#15 0x0819c2cb in handle_one_connection (arg=0x8becc60) at sql_parse.cc:1114
#16 0x0818c9e5 in create_new_thread (thd=0x8becc60) at mysqld.cc:3520
#17 0x0818d02a in handle_connections_sockets (arg=0x0) at mysqld.cc:3792
#18 0x0818c51b in main (argc=4, argv=0xbffffa34) at mysqld.cc:3191
#19 0x42017499 in __libc_start_main () from /lib/i686/libc.so.6
(gdb)

PEM,

in the stack trace, the consistent read view is NULL. Probably MySQL has released the read lock on the table too early, which makes InnoDB to think that the SQL statement has ended, and InnoDB frees the consistent read view.

Please check that MySQL does not release table locks too early.

Regards,

Heikki

Reassigned on request.
"It is either duplicate of 10015 or very close to it."

Hi!

This bug is turned out to be duplicate of bug #10015 " Crash in InnoDB if SPs and Triggers are used" which was fixed in 5.0.7 (See http://bugs.mysql.com/bug.php?id=10015 for more info).