List:General Discussion« Previous MessageNext Message »
From:Paul DuBois Date:June 15 2005 4:03pm
Subject:Re: Prepared Statements in Stored Procedures.
View as plain text  
At 14:57 +0100 6/15/05, Rob Hall wrote:
>I've been playing around with stored procedures and I seem to be able to
>consistantly crash msqld but I'm unsure wether it's because I'm doing
>something I shouldn't (SP newbie :]) or wether the issue's a bug in mysql.
>
>I know I'm 'pushing my luck' with the code below but I was trying it out of
>interest more than anything.
>
>For reference the new table is being created by the SP but mysqld then
>crashes.  I'm running 5.0.6 installed from the Redhat 9 RPMS under Fedora
>Core 3.

Several problems have been found with SQL prepared statements within
stored routines, so use of prepared statements within stored routines
has been disabled for now.  See the notes for bugs #10975, #7115, and
#10605 here:


http://dev.mysql.com/doc/mysql/en/news-5-0-8.html

>
>DELIMITER $$
>
>DROP PROCEDURE IF EXISTS `userauth`.`DeleteUser`$$
>
>CREATE PROCEDURE `userauth`.`DeleteUser` (IN i_uid int, IN sz_username
>char(100), IN sz_deletedby char(100))
>BEGIN DECLARE tablename char(120);
>SET tablename=CONCAT('DEL_',REPLACE(sz_deletedby,'-','_'));
>SET @SQLcmd=CONCAT('CREATE TABLE ', tablename, ' SELECT * FROM usertest WHERE
>uid = "',i_uid,'" AND username = "',sz_username,'"');
>prepare stmt from @SQLcmd;
>execute stmt;
>deallocate prepare stmt;
>SET @SQLcmd=CONCAT('ALTER TABLE ', tablename, ' ADD COLUMN datedeleted
>TIMESTAMP FIRST');
>prepare stmt from @SQLcmd;
>execute stmt;
>deallocate prepare stmt;
>SET @SQLcmd=CONCAT('DROP TABLE ',tablename);
>prepare stmt from @SQLcmd;
>execute stmt;
>deallocate prepare stmt;
>END$$
>
>DELIMITER ;
>
>CREATE TABLE `usertest` (
>   `uid` int(11) NOT NULL auto_increment,
>   `username` varchar(100) NOT NULL,
>   PRIMARY KEY  (`uid`)
>) ENGINE=MyISAM DEFAULT CHARSET=latin1;
>
>INSERT INTO usertest VALUES('502','dvs');
>
>CALL DeleteUser('502','dvs','rhall-superuser');
>
>mysqld.log:-
>
>Attempting backtrace. You can use the following information to find out
>where mysqld died. If you see no messages after this, something went
>terribly wrong...
>Cannot determine thread, fp=0x46cf74, backtrace may not be correct.
>Stack range sanity check OK, backtrace follows:
>0x815f807
>0x4d47c8
>(nil)
>0x84d4a28
>0x84d4a4d
>0x84d46cb
>0x811071d
>0x8131552
>0x812631a
>0x8167cc1
>0x81678f1
>0x81731ae
>0x824a5aa
>0x824a421
>0x824a4fa
>0x82482b4
>0x8248a96
>0x8174dfa
>0x81771a9
>0x816f8b4
>0x816f1e4
>0x816e7a4
>0x4ce341
>0x1ddfee
>New value of fp=(nil) failed sanity check, terminating stack trace!
>Please read http://dev.mysql.com/doc/mysql/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 0x8c99520 = SET @SQLcmd=CONCAT('ALTER TABLE ', tablename, ' ADD
>COLUMN datedeleted TIMESTAMP FIRST')
>thd->thread_id=1
>
>resolve_stack_dump -s /usr/lib/mysql/mysqld-max.sym -n /tmp/mysqld.stack
>0x815f807 handle_segfault + 423
>0x4d47c8 (?)
>(nil)
>0x84d4a28 __default_terminate + 24
>0x84d4a4d __terminate + 29
>0x84d46cb __pure_virtual + 43
>0x811071d val_str__12Item_splocalP6String + 45
>0x8131552 val_str__16Item_func_concatP6String + 178
>0x812631a check__22Item_func_set_user_var + 154
>0x8167cc1 check__12set_var_userP3THD + 49
>0x81678f1 sql_set_variables__FP3THDPt4List1Z12set_var_base + 49
>0x81731ae mysql_execute_command__FP3THD + 9390
>0x824a5aa exec_core__13sp_instr_stmtP3THDPUi + 26
>0x824a421 reset_lex_and_exec_core__13sp_lex_keeperP3THDPUibP8sp_instr + 209
>0x824a4fa execute__13sp_instr_stmtP3THDPUi + 122
>0x82482b4 execute__7sp_headP3THD + 644
>0x8248a96 execute_procedure__7sp_headP3THDPt4List1Z4Item + 806
>0x8174dfa mysql_execute_command__FP3THD + 16634
>0x81771a9 mysql_parse__FP3THDPcUi + 249
>0x816f8b4 dispatch_command__F19enum_server_commandP3THDPcUi + 1732
>0x816f1e4 do_command__FP3THD + 196
>0x816e7a4 handle_one_connection + 740
>0x4ce341 (?)
>0x1ddfee (?)
>
>--
>Best regards,
>  Rob Hall - Red Hat Certified Engineer
>  Technical Team Leader
>  Newsquest Digital Media

-- 
Paul DuBois, MySQL Documentation Team
Madison, Wisconsin, USA
MySQL AB, www.mysql.com
Thread
Prepared Statements in Stored Procedures.Rob Hall15 Jun
  • Re: Prepared Statements in Stored Procedures.Martijn Tonies15 Jun
    • Re: Prepared Statements in Stored Procedures.Rob Hall15 Jun
  • Re: Prepared Statements in Stored Procedures.Gleb Paharenko15 Jun
  • Re: Prepared Statements in Stored Procedures.Paul DuBois15 Jun