MySQL Bugs: #8833: crash while calling a stored procedure

Bug #8833	crash while calling a stored procedure
Submitted:	27 Feb 2005 19:39	Modified:	15 Apr 2005 17:32
Reporter:	Steffen Reinecke	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	5.0.2 binary + source	OS:	Linux (Linux)
Assigned to:		CPU Architecture:	Any

Description:
Hello,

mysqld restarts by calling a stored procedure. Not everytime, but my chance
to repeat this crash is up to 15%. I am using the php mysql client to connect.
I have tested it with both mysqld server versions (standard binary, source).

the message in the mysqld log:
>>>
mysqld got signal 11;
This could be because you hit a bug. It is also possible that this binary
or one of the libraries it was linked against is corrupt, improperly built,
or misconfigured. This error can also be caused by malfunctioning hardware.
We will try our best to scrape up some info that will hopefully help diagnose
the problem, but since we have already crashed, something is definitely wrong
and this may fail.

key_buffer_size=16777216
read_buffer_size=131072
max_used_connections=7
max_connections=100
threads_connected=4
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 80383 K
bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

thd=0x8a08aa0
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=0xbfe3e278, backtrace may not be correct.
Stack range sanity check OK, backtrace follows:
0x808edf3
0x8301538
0x8321047
0x82d4f26
0x80a894d
0x8159ebb
0x815b01a
0x80a2631
0x80a3eb9
0x809d798
0x809d143
0x809c85a
0x82fecec
0x83287ca
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 0x8a72140 = CALL do_login('testuser', '300303', @session_id)
thd->thread_id=438
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: 1
050223 17:27:35  mysqld restarted
050223 17:27:35 [Warning] Asked for 196608 thread stack, but got 126976
050223 17:27:35  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...
050223 17:27:36  InnoDB: Starting log scan based on checkpoint at
InnoDB: log sequence number 0 3778151.
InnoDB: Doing recovery: scanned up to log sequence number 0 3778151
050223 17:27:36  InnoDB: Flushing modified pages from the buffer pool...
050223 17:27:36  InnoDB: Started; log sequence number 0 3778151
/usr/local/mysql/bin/mysqld: ready for connections.
Version: '5.0.2-alpha-standard'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  Official MySQL-standard binary
<<<

Above, the function "do_login" is listed as the problem. I have very similar traces
with other functions in the logfile.

The function "do_login" looks as follows:

CREATE PROCEDURE do_login (IN go_username CHAR, IN go_password CHAR, OUT go_success CHAR)
SQL SECURITY DEFINER
COMMENT 'user login and session start, returns the db session ID'
NOT DETERMINISTIC
BEGIN
    DECLARE success BOOLEAN DEFAULT FALSE;
    DECLARE new_session_id CHAR DEFAULT '';
    DECLARE inserted_id INT DEFAULT 0;
    SET AUTOCOMMIT=0;
    START TRANSACTION;
    SET new_session_id = MD5(CONCAT(go_username,'******'));
    SELECT 1 INTO success FROM `user` WHERE `login`=go_username AND `pass`=MD5(go_password) AND `status_id`=1;
    IF success = 1 THEN
        UPDATE `user` SET `last_login`=NOW() WHERE `login`=go_username;
        INSERT INTO session (`session_id`,`login`,`stamp`,`touched`) VALUES (new_session_id, go_username, NOW(), NOW());
        SELECT LAST_INSERT_ID() INTO inserted_id;
        IF inserted_id THEN
            SET go_success = CONCAT(inserted_id,'.',new_session_id);
        END IF;
    END IF;
    COMMIT;
END

delimiter ;

PS: I could not get this error while performing the query from command line yet,
may be i have not hit the 15%. But it could also be a conflict between the php
mysql client lib and the server.

But anyway, i have posted it as server bug becauze it crashs the server.

Best Regards,

Steffen Reinecke

How to repeat:
see bug description

Hi

I tried to reproduce the behaviour you describe without success.

Would it be possible for you to provide us with a test case that reproduces this behaviour ?

Thanks a lot

Hello,

i will try to extract a testcase within this week. It's a bit complicated
and takes some time.

Thanx in advance,

Steffen

Hello,

now i have extracted an example. I've got it crashing on both test machines.

1. (Linux *** 2.6.8-24.11-default #1 Fri Jan 14 13:01:26 UTC 2005 x86_64 x86_64 x86_64 GNU/Linux)
2. (Linux *** 2.6.8-24.11-smp #1 SMP Fri Jan 14 13:01:26 UTC 2005 i686 i686 i386 GNU/Linux)

On the first server (my workstation) my php script gives me the error "Mysql Server has gone away" or one time the mysql error "Out of memory (Needed 4276742504 bytes)". Normally the Mysql Server was goin away allready after the first run of the "for" loop. No other queries was sent to mysql within the tests. You need to stress the mysql server a bit with the testcase for a real shutdown / restart. I have done it with pressing the F5 Button multiple times in my Firefox.

On the second server the mysql server was going away between run 500 and run 1000 of the "for" loop.

On a live system with very less traffic the crashing procedures forces a shutdown / restart of the server 5 times a day without any additionally stress.

the sql dump:
http://www.pextra.de/dump.sql

the test script:
http://www.pextra.de/testcase.php.txt

For all readers ... do not test it on important machines ;-)

I'm very interested in a solution ... so please tell me if you need some more information to reproduce it.

Best Regards,

Steffen

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

reopened

Have not seen the error message in the log since i have installed 5.0.3

Thank you for the update.