List:General Discussion« Previous MessageNext Message »
From:Gleb Paharenko Date:June 15 2005 2:26pm
Subject:Re: Prepared Statements in Stored Procedures.
View as plain text  
Hello.

I suggest you to check your procedure on the latest bk source, and
if the server keeps crashing report a bug.


Rob Hall <robslists@stripped> wrote:
> I've been playing around with stored procedures and I seem to be able to=20
> consistantly crash msqld but I'm unsure wether it's because I'm doing=20
> 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=
> =20
> interest more than anything.
> 
> =46or reference the new table is being created by the SP but mysqld then=20
> crashes.  I'm running 5.0.6 installed from the Redhat 9 RPMS under Fedora=20
> Core 3.
> 
> DELIMITER $$
> 
> DROP PROCEDURE IF EXISTS `userauth`.`DeleteUser`$$
> 
> CREATE PROCEDURE `userauth`.`DeleteUser` (IN i_uid int, IN sz_username=20
> char(100), IN sz_deletedby char(100))
> BEGIN DECLARE tablename char(120);
> SET tablename=3DCONCAT('DEL_',REPLACE(sz_deletedby,'-','_'));
> SET @SQLcmd=3DCONCAT('CREATE TABLE ', tablename, ' SELECT * FROM usertest W=
> HERE=20
> uid =3D "',i_uid,'" AND username =3D "',sz_username,'"');
> prepare stmt from @SQLcmd;
> execute stmt;
> deallocate prepare stmt;
> SET @SQLcmd=3DCONCAT('ALTER TABLE ', tablename, ' ADD COLUMN datedeleted=20
> TIMESTAMP FIRST');
> prepare stmt from @SQLcmd;
> execute stmt;
> deallocate prepare stmt;
> SET @SQLcmd=3DCONCAT('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=3DMyISAM DEFAULT CHARSET=3Dlatin1;
> 
> 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=3D0x46cf74, 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=3D(nil) failed sanity check, terminating stack trace!
> Please read http://dev.mysql.com/doc/mysql/en/Using_stack_trace.html and=20
> 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 =3D SET @SQLcmd=3DCONCAT('ALTER TABLE ', tablename,=
> ' ADD=20
> COLUMN datedeleted TIMESTAMP FIRST')
> thd->thread_id=3D1
> 
> 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 (?)
> 
> =2D-=20
> Best regards,
> =A0Rob Hall - Red Hat Certified Engineer
> =A0Technical Team Leader
> =A0Newsquest Digital Media
> 


-- 
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.NET http://www.ensita.net/
   __  ___     ___ ____  __
  /  |/  /_ __/ __/ __ \/ /    Gleb Paharenko
 / /|_/ / // /\ \/ /_/ / /__   Gleb.Paharenko@stripped
/_/  /_/\_, /___/\___\_\___/   MySQL AB / Ensita.NET
       <___/   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