MySQL Bugs: #15849: Executing a stored procedure kills the server.

Bug #15849	Executing a stored procedure kills the server.
Submitted:	19 Dec 2005 9:12	Modified:	9 Feb 2006 12:53
Reporter:	Steinar Gravem	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server	Severity:	S1 (Critical)
Version:	5.0.15-nt-log	OS:	Windows (Windows server 2003)
Assigned to:		CPU Architecture:	Any

Description:
Hi,
when calling the procedure below from MySQL Query Browser located on the server, it looks like the job is done, but it results in a error message : Lost connection to MySQL server during Query.errornr 2013. The reason is that MySQL service has stopped! Have to restart the Service.
Starting the same job from Navicat located on a client, results the same error message, but now is it impossible to restart the service. We have to restart the Server.

CREATE PROCEDURE `Opdater_firma`()
BEGIN
declare a, b INT;
declare cur_1 CURSOR for select bedrnr from firma ;
declare continue handler for not found
set b=1;
open cur_1;
repeat
fetch cur_1 into a;
update firma
set antanskat1=(select count(*) from person p where (p.bedrnr=a) and (p.kategori=1) and (p.ugyldig is null ) and (p.sluttetdato is null)),
antanskat2=(select count(*) from person p where (p.bedrnr=a) and (p.kategori=2) and (p.ugyldig is null ) and (p.sluttetdato is null)),
antanskat3=(select count(*) from person p where (p.bedrnr=a) and (p.kategori=3) and (p.ugyldig is null ) and (p.sluttetdato is null))
where bedrnr=a;
until b=1
end repeat;
close cur_1;
END

How to repeat:
Just by running the procedure.
The database consists of 3 tables and contain above 6 GB of data.

Thank you for a problem report. Please, send the SHOW CREATE TABLE results for (2) tables mentioned in your storage procedure.

Show create Table for Person and Firma

Attachment: person_firma.txt (text/plain), 1.53 KiB.

A file person_firma.txt containing the result of Show Create Table for the two involved tables is attached to the bug description

Thank you for the additional information. Looks like you'll have to provide some sample data, because on empty tables your procedure works OK for me:

mysql> delimiter //
mysql> CREATE PROCEDURE `Opdater_firma`()
    -> BEGIN
    -> declare a, b INT;
    -> declare cur_1 CURSOR for select bedrnr from firma ;
    -> declare continue handler for not found
    -> set b=1;
    -> open cur_1;
    -> repeat
    -> fetch cur_1 into a;
    -> update firma
    -> set antanskat1=(select count(*) from person p where (p.bedrnr=a) and
    -> (p.kategori=1) and (p.ugyldig is null ) and (p.sluttetdato is null)),
    -> antanskat2=(select count(*) from person p where (p.bedrnr=a) and (p.kategori=2)
    -> and (p.ugyldig is null ) and (p.sluttetdato is null)),
    -> antanskat3=(select count(*) from person p where (p.bedrnr=a) and (p.kategori=3)
    -> and (p.ugyldig is null ) and (p.sluttetdato is null))
    -> where bedrnr=a;
    -> until b=1
    -> end repeat;
    -> close cur_1;
    -> END//
Query OK, 0 rows affected (0.23 sec)

mysql> call `Opdater_firma`()//
Query OK, 0 rows affected (0.06 sec)

mysql> select version()//
+-----------+
| version() |
+-----------+
| 5.0.15-nt |
+-----------+
1 row in set (0.00 sec)

I'm sorry to tell you that this is confidental data, and we can't bring them out of the house. Can I help you with testing, bringing you the results?

Thank you for the clarification.

Can you try to repeat the behaviour with just some data similar to real? How many rows are there in your tables? 

We'll need a repeatable test case to call this a bug, anyway.

Does it happen with a smaller data set? (10s of rows, 100, 1000? etc?)

Does it happen when using the command line client, 'mysql'?

If it does, does it still happen if you move the subselects out of the update, as shown below? (There's a potential bug in the example btw, it will call update even when fetch has no more data, before it exits the loop. The example below shows an alternative way to handle the "not found" condition. But this might be completely unrelated to the crash of course.)

create procedure `Opdater_firma`()
begin
  declare cur_1 cursor for select bedrnr from firma;

  open cur_1;
  begin
    declare exit handler for not found close cur_1;

    loop
    begin
      declare a int;
      declare c1, c2, c3 bigint;

      fetch cur_1 into a;
      select count(*) into c1
        from person p
       where p.bedrnr=a and
             p.kategori=1 and
             p.ugyldig is null and
             p.sluttetdato is null;
      select count(*) into c2
        from person p
       where p.bedrnr=a and
             p.kategori=2 and
             p.ugyldig is null and
             p.sluttetdato is null;
      select count(*) into c3
        from person p
       where p.bedrnr=a and
             p.kategori=3 and
             p.ugyldig is null and
             p.sluttetdato is null;
      update firma
         set antanskat1=c1,
             antanskat2=c2,
             antanskat3=c3
       where bedrnr=a;
     end;
     end loop;
  end;
end//

I forgot:
It might be worth trying a newer version of the server too. (I think 5.0.17 has just been released.)

Happy new year!

The number of rows in the Firma table is 2 413 059 and in the Person table there are 19.921.984 rows.

By inserting a Commit statement after the line with the where clause ( where bedrnr=a), the procedure runs without causing any problems! A buffer problem?
However, should not MySQL give some a warning in this situation?

Thank you for the additional information. Can you, please, send the my.ini content and *.err file (it is in the data subdirectory usually) content for the period when this procedure crashed your server.

My.ini

Attachment: my.ini (application/octet-stream, text), 9.41 KiB.

Error file

Attachment: hjelpeserver.err (application/octet-stream, text), 125.58 KiB.

Attached you wil find the files you asked for.

I had found a lot of messages similar to the following in you error log:

"051110  9:45:43051110  9:45:43 [ERROR] Cannot find table aareg/firma from the internal data dictionary of InnoDB though the .frm file for the table exists."

where firma is the table you are trying to update in your SP. 

What happened with that table? Have you tried to ANALIZE or CHECK it?

I also recommend you to try to reproduce this problem on 5.0.18, now available.

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".