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
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
