Bug #18787 Server crashed when calling a stored procedure containing a misnamed function
Submitted: 5 Apr 2006 2:03 Modified: 27 Apr 2006 13:40
Reporter: Iain Hosking Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Stored Routines Severity:S1 (Critical)
Version:5.0.19/5.0BK/5.1BK OS:Windows (Windows XP/Linux)
Assigned to: Bugs System CPU Architecture:Any

[5 Apr 2006 2:03] Iain Hosking
Description:
While developing a MySQL 5.0 database for a new project I called a stored procedure which in turn called function that didn't exist, whereupon the server immediately crashed (the client reported ERROR 2013 (HY000): Lost connection to MySQL server during query).

I recreated the stored procedure using the correct function name (making no other changes), and it ran without problems.

If it helps, the function names were twc_date_add (correct) and twc_add_date (incorrect). There never was a function twc_add_date().

How to repeat:
When I tried to create a test case, using a very simple stored procedure, calling it simply returned ERROR 1305 (42000): FUNCTION test.notthere does not exist.

However, the error is completely repeatable (ie the server crashes) if I try to call the original procedure. When I can find time I will try to create a test case I can send to you, but in the meantime I'm reporting the error in case anyone else has experienced similar problems. I'd be happier if a syntax error in a stored procedure didn't crash the entire server.
[5 Apr 2006 2:13] MySQL Verification Team
Thank you for the bug report. Could you please paste here the test case
if possible to be public for to make possible others users can identify if
their case is a duplicate of this, otherwise you can paste as private
comment for only developers can see.

Thanks in advance.
[5 Apr 2006 10:29] Iain Hosking
-- Create tables and stored procedure to recreate server crash
-- Run this script to create products and users tables and the crash_server 
-- procedure in the test database. Then connect as user test and run:

-- mysql> call crash_server( 1, 2, @status, @err_code );

-- It crashes because missing_function() doesn't exist, and it's in a subquery.

-- System: Windows XP SP 2; MySQL 5.0.19.
-- On my laptop MySQL 5.0 is running on port 3307, not that this should make any difference.

USE test;

GRANT ALL ON test.* TO 'test'@'localhost' IDENTIFIED BY 'test';

DROP PROCEDURE IF EXISTS crash_server;

DROP TABLE IF EXISTS users;

DROP TABLE IF EXISTS products;

CREATE TABLE products (
    product_id              int             NOT NULL AUTO_INCREMENT,
    product_name            varchar(50)     NOT NULL,
    period                  int             NOT NULL,
    period_units            varchar(10)     NOT NULL,
    create_time             datetime        NOT NULL,
    last_update             timestamp       NOT NULL,
    PRIMARY KEY  (product_id)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO products ( product_name, period, period_units, create_time )
VALUES ( 'Widget 1', 0, 'D', NOW() );

INSERT INTO products ( product_name, period, period_units, create_time )
VALUES ( 'Widget 2', 5, 'D', NOW() );

CREATE TABLE users (
    user_id                 int             NOT NULL AUTO_INCREMENT,
    first_name              varchar(50)     NULL,
    last_name               varchar(50)     NULL,
    product_id              int             NOT NULL,
    product_activated       datetime        NOT NULL,
    product_expires         datetime        NULL,
    create_time             datetime        NOT NULL,
    last_update             timestamp       NOT NULL,
    PRIMARY KEY (user_id),
    KEY (product_id),
    CONSTRAINT fk_user_product_id FOREIGN KEY (product_id) REFERENCES products (product_id)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO users ( first_name, last_name, product_id, product_activated, product_expires, create_time )
VALUES ( 'Jack', 'Spratt', 1, NOW(), NULL, NOW() );

delimiter $$
CREATE PROCEDURE crash_server (
    IN in_user_id                   int,
    IN in_product_id                int,
    OUT out_status                  int,            -- Procedure return status (1=successful; 0=unsuccessful)
    OUT out_error_code              varchar(30)
) DETERMINISTIC BEGIN

    -- Default output values
    SET out_status = 0;
    SET out_error_code = NULL;

    START TRANSACTION;

    BEGIN

        -- SQLEXCEPTION is shorthand for all SQLSTATE codes not caught by SQLWARNING or NOT FOUND.
        DECLARE CONTINUE HANDLER FOR SQLEXCEPTION BEGIN
            SET out_error_code = 'SQLEXCEPTION';
        END;

        -- Update the user's product. This is what causes the server crash.
        -- missing_function() doesn't exist (it was originally a typo). If you
        -- replace it with NULL the procedure will run without problems.
        IF out_error_code IS NULL THEN
            UPDATE users
            SET product_id = in_product_id,
            product_activated = NOW(),
            product_expires = (SELECT missing_function() FROM products WHERE product_id = in_product_id)
            WHERE user_id = in_user_id;
        END IF;

    END;

    -- If the SQL statement executed without errors, issue a COMMIT; otherwise a ROLLBACK.
    IF out_error_code IS NULL THEN
        COMMIT;
        SET out_status = 1;
    ELSE
        ROLLBACK;
    END IF;

END;
$$

delimiter ;

GRANT EXECUTE ON PROCEDURE crash_server TO test@localhost;
[5 Apr 2006 12:55] MySQL Verification Team
Thank you for the feedback and test case. I was able to repeat with
current server source:

060405  9:29:06 [Note] /home/miguel/dbs/5.0/libexec/mysqld: ready for connections.
Version: '5.0.21-debug'  socket: '/tmp/mysql.sock'  port: 3306  Source distribution
[New Thread 1131862960 (LWP 5901)]
[Thread 1131862960 (zombie) exited]
[New Thread 1131862960 (LWP 5903)]

Program received signal SIGSEGV, Segmentation fault.
[Switching to Thread 1131862960 (LWP 5903)]
0x081608ef in Field::val_str (this=0x0, str=0x4376b1f4) at field.h:106
106       inline String *val_str(String *str) { return val_str(str, str); }
(gdb) bt full
#0  0x081608ef in Field::val_str (this=0x0, str=0x4376b1f4) at field.h:106
No locals.
#1  0x0818398a in Item_func_sp::val_str (this=0x8ea0d48, str=0x8ec1d78) at item_func.h:1436
        buf = {Ptr = 0x4376b1e0 "\\%ì\b\b²vC\004²vC", str_length = 0, Alloced_length = 20, alloced = false,
  str_charset = 0x87e2880}
        buff = "\\%ì\b\b²vC\004²vC\000²vC\\%ì\b"
#2  0x081619db in Item::str_result (this=0x8ea0d48, tmp=0x8ec1d78) at item.h:583
No locals.
#3  0x0815e47f in Item_cache_str::store (this=0x8ec1cd8, item=0x8ea0d48) at item.cc:5702
No locals.
#4  0x081b140c in Item_singlerow_subselect::store (this=0x8eb3e88, i=0, item=0x8ea0d48) at item_subselect.cc:385
No locals.
#5  0x081d5677 in select_singlerow_subselect::send_data (this=0x8eb3f00, items=@0x8ea09c8) at sql_class.cc:1359
        i = 0
        _db_func_ = 0x4376b338 "¨³vCÖ»$\bà\016ì\bè#ì\b"
        _db_file_ = 0x0
        _db_level_ = 2
        _db_framep_ = (char **) 0x8e88fb8
        it = (class Item_singlerow_subselect *) 0x8eb3e88
        li = {<base_list_iterator> = {list = 0x8ea09c8, el = 0x8ea14b8, prev = 0x0, current = 0x0}, <No data fields>}
        val_item = (class Item *) 0x8ea0d48
<cut>

060405  9:52:42 [Note] /home/miguel/dbs/5.1/libexec/mysqld: ready for connections.
Version: '5.1.9-beta-debug'  socket: '/tmp/mysql.sock'  port: 3306  Source distribution
[New Thread 1119902640 (LWP 5946)]
[Thread 1119902640 (zombie) exited]
[New Thread 1119902640 (LWP 5949)]

Program received signal SIGSEGV, Segmentation fault.
[Switching to Thread 1119902640 (LWP 5949)]
0x081a584b in Item_func_sp::execute (this=0x9258960, flp=0x92589dc) at item_func.cc:4756
4756                      sql_alloc(f->pack_length()) : result_buf);
(gdb)
[11 Apr 2006 10:18] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/4777
[13 Apr 2006 20:43] Konstantin Osipov
Approved by email.
[18 Apr 2006 9:16] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/5068
[21 Apr 2006 9:51] Per-Erik Martin
Pushed to mysql-5.0-runtime.

The problem was that when attempting to use a non-existing function in the precense of a CONTINUE handler, it proceeded to execute as if a useful result had been returned.
[25 Apr 2006 21:31] Dmitry Lenev
Fixed in 5.0.21 and 5.1.10.
[27 Apr 2006 13:40] Paul DuBois
Noted in 5.0.21, 5.1.10 changelogs.