MySQL Bugs: #6473: mysqld restarts when using stored procedure

Bug #6473	mysqld restarts when using stored procedure
Submitted:	6 Nov 2004 3:54	Modified:	13 Dec 2004 19:57
Reporter:	Shawn Mall	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	5.0.1-alpha-stan	OS:	Linux (Linux cher 2.4.20-8smp #1 SMP Th)
Assigned to:		CPU Architecture:	Any

Description:
Create a stored procedure, try and call the stored procedure and mysqld restarts

Information:

server.err log from mysqld
========================
New value of fp=(nil) failed sanity check, terminating stack trace!
Please read http://www.mysql.com/doc/en/Using_stack_trace.html and follow instructions on how to resolve the stack trace. Resolved
stack trace is much more helpful in diagnosing the problem, so please do
resolve it
Trying to get some variables.
Some pointers may be invalid and cause the dump to abort...
thd->query at 0x867a5a8 = Call sp_InsertUpdateCustomer('shawn mall','89052','7026174589','','shawn@deluzion.net','123','2276 aria d$
thd->thread_id=3
The manual page at http://www.mysql.com/doc/en/Crashing.html contains  
information that should help you find out what is causing the crash.

Number of processes running now: 0
041105 19:03:24  mysqld restarted
041105 19:03:24  Warning: Asked for 196608 thread stack, but got 126976
041105 19:03:24  InnoDB: Database was not shut down normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files...
InnoDB: Restoring possible half-written data pages from the doublewrite
InnoDB: buffer...
041105 19:03:24  InnoDB: Starting log scan based on checkpoint at
InnoDB: log sequence number 0 46973.
InnoDB: Doing recovery: scanned up to log sequence number 0 46973
041105 19:03:24  InnoDB: Flushing modified pages from the buffer pool...
041105 19:03:24  InnoDB: Started; log sequence number 0 46973
/usr/local/mysql/bin/mysqld: ready for connections.
Version: '5.0.1-alpha-standard'  socket: '/tmp/mysql.sock'  port: 3306
==================================

Resolv Stack Output:
==================================
0x808b493 handle_segfault + 423
0x82d5e88 pthread_sighandler + 184
0x80b97c3 setup_tables__FP3THDP13st_table_listPP4Item + 59
0x80be679 prepare__4JOINPPP4ItemP13st_table_listUiP4ItemUiP8st_orderT6T4T6P13st_select_lexP18st_select_lex_unit + 161
0x80752d0 prepare__30subselect_single_select_engine + 676
0x807244f fix_fields__14Item_subselectP3THDP13st_table_listPP4Item + 87
0x805532a fix_fields__9Item_funcP3THDP13st_table_listPP4Item + 130
0x8142242 sp_eval_func_item__FP3THDP4Item16enum_field_types + 66
0x8144ab4 execute__20sp_instr_jump_if_notP3THDPUi + 32
0x8142d57 execute__7sp_headP3THD + 199
0x814339d execute_procedure__7sp_headP3THDPt4List1Z4Item + 753
0x809d83e mysql_execute_command__FP3THD + 15706
0x809ef39 mysql_parse__FP3THDPcUi + 289
0x8098c5f dispatch_command__F19enum_server_commandP3THDPcUi + 1643
0x80985ea do_command__FP3THD + 214
0x8097d1a handle_one_connection + 618
0x82d363c pthread_start_thread + 220
0x8304d3a thread_start + 4
=================================

Mysqlbug output
=================================
>C compiler:    2.95.3
>C++ compiler:  2.95.3

System: Linux cher 2.4.20-8smp #1 SMP Thu Mar 13 17:45:54 EST 2003 i686 i686 i386 GNU/Linux
Architecture: i686

Some paths:  /usr/bin/perl /usr/bin/make /usr/bin/gmake /usr/bin/gcc /usr/bin/cc
GCC: Reading specs from /usr/lib/gcc-lib/i386-redhat-linux/3.2.2/specs
Configured with: ../configure --prefix=/usr --mandir=/usr/share/man --infodir=/usr/share/info --enable-shared --enable-threads=posix --disable-checking --with-system-zlib --enable-__cxa_atexit --host=i386-redhat-linux
Thread model: posix
gcc version 3.2.2 20030222 (Red Hat Linux 3.2.2-5)
Compilation info: CC='gcc'  CFLAGS='-O2 -mcpu=pentiumpro'  CXX='gcc'  CXXFLAGS='-O2 -mcpu=pentiumpro -felide-constructors'  LDFLAGS=''  ASFLAGS=''
LIBC:
lrwxrwxrwx    1 root     root           13 Dec 24  2003 /lib/libc.so.6 -> libc-2.3.2.so
-rwxr-xr-x    1 root     root      1549556 Mar 13  2003 /lib/libc-2.3.2.so
-rw-r--r--    1 root     root      2321376 Mar 13  2003 /usr/lib/libc.a
-rw-r--r--    1 root     root          204 Mar 13  2003 /usr/lib/libc.so
Configure command: ./configure '--prefix=/usr/local/mysql' '--localstatedir=/usr/local/mysql/data' '--libexecdir=/usr/local/mysql/bin' '--with-comment=Official MySQL-standard binary' '--with-extra-charsets=complex' '--with-server-suffix=-standard' '--enable-thread-safe-client' '--enable-local-infile' '--enable-assembler' '--disable-shared' '--with-client-ldflags=-all-static' '--with-mysqld-ldflags=-all-static' '--with-readline' '--with-embedded-server' '--with-innodb' 'CFLAGS=-O2 -mcpu=pentiumpro' 'CXXFLAGS=-O2 -mcpu=pentiumpro -felide-constructors' 'CXX=gcc'
=====================================

How to repeat:
create table:
==================
CREATE TABLE `mycustomer` (
  `cus_accntnum` varchar(45) default NULL,
  `cus_id` int(11) NOT NULL auto_increment,
  `cus_addr` varchar(45) default NULL,
  `cus_city` varchar(45) default NULL,
  `cus_country` varchar(45) default NULL,
  `cus_datetimeadd` varchar(45) default NULL,
  `cus_name` varchar(150) default NULL,
  `cus_email` varchar(45) default NULL,
  `cus_hphone` varchar(45) default NULL,
  `cus_state` varchar(45) default NULL,
  `cus_wphone` varchar(45) default NULL,
  `cus_zip` varchar(45) default NULL,
  PRIMARY KEY  (`cus_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1; 
=======================

create stored procedure:
==================
DELIMITER $$

DROP PROCEDURE IF EXISTS `thomasandmack_focalbase_com`.`sp_InsertUpdateCustomer`$$
CREATE PROCEDURE "thomasandmack_focalbase_com"."sp_InsertUpdateCustomer"(
 IN customername varchar(150),
 IN customerzip varchar(45),
 IN customerhphone varchar(45),
 IN customerwphone varchar(45),
 IN customeremail varchar(45),
 IN customeraccnum varchar(45),
 IN customeraddress varchar(150),
 IN customercity varchar(45),
 IN customerstate varchar(45),
 IN customercountry varchar(45))
BEGIN
 IF (SELECT COUNT(*) FROM mycustomer  WHERE cus_name = customername AND cus_zip = customerzip) = 0 THEN
   INSERT INTO mycustomer (cus_name, cus_accntnum, cus_addr, cus_city, cus_state, cus_zip, cus_hphone, cus_wphone, cus_email, cus_country) VALUES(customername, customeraccnum, customeraddress, customercity, customerstate, customerzip, customerhphone, customerwphone, customeremail, customercountry);
 ELSE
  UPDATE mycustomer SET cus_hphone = customerhphone, cus_wphone = customerwphone, cus_email = customeremail WHERE cus_name = customername AND cus_zip = customerzip;
 END IF;
END$$

DELIMITER ;
==========================

run select statement on stored procedure:
===========================
select sp_InsertUpdateCustomer('shawn mall','89052','7026174589','','shawn@deluzion.net','123','2276 aria dr','henderson','nv','usa')
===========================

IT WORKS!! ... If I modify the stored procedure to:
=======================
DELIMITER $$

DROP PROCEDURE IF EXISTS `thomasandmack_focalbase_com`.`sp_InsertUpdateCustomer`$$
CREATE PROCEDURE "thomasandmack_focalbase_com"."sp_InsertUpdateCustomer"(IN customername varchar(150),
 IN customerzip varchar(45),
 IN customerhphone varchar(45),
 IN customerwphone varchar(45),
 IN customeremail varchar(45),
 IN customeraccnum varchar(45),
 IN customeraddress varchar(45),
 IN customercity varchar(45),
 IN customerstate varchar(45),
 IN customercountry varchar(45))
BEGIN
DECLARE customers int;
SELECT COUNT(cus_name) into customers FROM mycustomer  WHERE cus_name = customername AND cus_zip = customerzip;
 IF customers = 0 THEN
   INSERT INTO mycustomer (cus_name, cus_accntnum, cus_addr, cus_city, cus_state, cus_zip, cus_hphone, cus_wphone, cus_email, cus_country) VALUES(customername, customeraccnum, customeraddress, customercity, customerstate, customerzip, customerhphone, customerwphone, customeremail, customercountry);
 ELSE
  UPDATE mycustomer SET cus_hphone = customerhphone, cus_wphone = customerwphone, cus_email = customeremail WHERE cus_name = customername AND cus_zip = customerzip;
 END IF;
END$$

DELIMITER ;
==================================

The problem lied in having the "SELECT COUNT(cus_name) into customers FROM mycustomer  WHERE cus_name = customername AND cus_zip = customerzip;" in the conditional IF statement of the original SP that I posted earlier.

I wasn't able to repeat with latest BK source 5.0 tree.