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

[11 May 2005 11:02] Per-Erik Martin
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.]
[12 May 2005 4:22] MySQL Verification Team
Verified on Linux.
[13 May 2005 17:07] Peter Gulutzan
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
[17 May 2005 16:11] Heikki Tuuri
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
[17 May 2005 16:52] Per-Erik Martin
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)
[17 May 2005 17:00] Heikki Tuuri
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
[19 May 2005 12:41] Per-Erik Martin
Reassigned on request.
"It is either duplicate of 10015 or very close to it."
[7 Jun 2005 12:27] Dmitry Lenev
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).