Bug #3988 FETCH cur1 INTO vars... crashes server
Submitted: 3 Jun 2004 20:10 Modified: 20 Jun 2004 10:31
Reporter: KARL KASPER Email Updates:
Status: Can't repeat Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:5.0.0-alpha-standard OS:Linux (RH9 (Linux 2.4.20-30.9smp))
Assigned to: Bugs System CPU Architecture:Any

[3 Jun 2004 20:10] KARL KASPER
Description:
CREATEd a PROCEDURE called sp_import_format_scan.  By adding "debug" type statements at each step in the PROCEDURE (INSERT INTO dbg(msg) VALUES ('Entering Repeat') type messages), I have determined that:

1) DECLARE of cursor seems to work
2) OPEN of cursor seems to work
3) FETCH cursor INTO <csv list of 6 vars> crashes the server with...

"...
Version: '5.0.0-alpha-standard'  socket: '/var/lib/mysql/mysql.sock'  port: 3306mysqld got signal 11;
This could be because you hit a bug..." written to /var/log/mysqld.log.

Client receives, "ERROR 2013 (HY000): Lost connection to MySQL server during query".

Server spits out (stdout or stderr), "Number of processes running now: 0
040603 13:35:06  mysqld restarted".

resolve_stack_trace with the mysql.stack from /var/log/mysqld.log AND symbols from the distribution's mysql.sym.gz, produces the following:

"resolve_stack_dump -s /tmp/mysqld.sym -n ./mysqld.stack
0x80880d3 handle_segfault + 423
0x82df1d8 pthread_sighandler + 184
0x8130b31 fetch__9sp_cursorP3THDPt4List1Z7sp_pvar + 501
0x812fd85 execute__15sp_instr_cfetchP3THDPUi + 53
0x812e0e7 execute__7sp_headP3THD + 143
0x812e6b0 execute_procedure__7sp_headP3THDPt4List1Z4Item + 708
0x8099aa3 mysql_execute_command__FP3THD + 17055
0x809afc9 mysql_parse__FP3THDPcUi + 185
0x8094a1f dispatch_command__F19enum_server_commandP3THDPcUi + 1659
0x809439a do_command__FP3THD + 218
0x8093aea handle_one_connection + 602
0x82dc98c pthread_start_thread + 220
0x83124ca thread_start + 4"

And, just for good measure, though already entered under the "Operating system" field in the bug report...
 
"[root@mehoe mysql]# uname -a
Linux mehoe 2.4.20-30.9smp #1 SMP Wed Feb 4 20:36:46 EST 2004 i686 i686 i386 GNU/Linux"

How to repeat:
To repeat this, I have gathered up a bunch of information.  In the process, I discovered that curdemo() has the same problems (and may be a simpler way to reproduce).  Whether my example is used or the curdemo() example, use the 5.0.0 alpha binary distribution for Linux.

If the mysql.com example is used, create tables t1 and t2 in test and then create and call the SPROC curdemo().  Boom!

Assuming my example is to be used, create the following tables (defined below under "REQUIRED TABLES") then put some test data into the format_scan table - for instance:

+------------+-------------+----------+--------------+------+--------+---------------+
| ip_address | port_number | protocol | country_code | OS   | banner | record_status |
+------------+-------------+----------+--------------+------+--------+---------------+
| 10.1.69.1  |        8080 | TCP      |              | None | None   | Imported      |
| 10.1.69.1  |        6000 | TCP      |              | None | None   | Imported      |
+------------+-------------+----------+--------------+------+--------+---------------+

It is not necessary to populate hosts, services or address_space tables.

Next, execute the SQL to CREATE PROCEDURE import_format_scan (included below under "STORED PROCEDURE").  That will successfully create a SPROC that is supposed to push data from the format_scan table into the hosts and services tables while noting each step of the SPROC into the dbg table's msg field.  Once the SPROC has successfully been created (I can SHOW CREATE PROCEDURE import_format_scan), call import_format_scan(); and the server will crash, returning "ERROR 2013 (HY000): Lost connection to MySQL server during query" along with the messages cited above from mysqld's stderr or stdout and /var/log/mysqld.log.

# +-----------------+
# | REQUIRED TABLES |
# +-----------------+
#
# Host: localhost
# Database: whohas
# Table: 'address_space'
# 
CREATE TABLE `address_space` (
  `class` varchar(100) NOT NULL default '',
  `start_ip` varchar(100) NOT NULL default '',
  `end_ip` varchar(100) NOT NULL default '',
  `assigned_date` datetime default '0000-00-00 00:00:00',
  `submit_date` datetime default '0000-00-00 00:00:00',
  `record_status` set('Unassigned','Assigned','Completed','Submitted') NOT NULL default 'Unassigned',
  `netname` varchar(100) NOT NULL default '',
  `country_code` varchar(100) NOT NULL default '',
  `ip_count` bigint(20) unsigned NOT NULL default '0'
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COMMENT='U:Table to hold address blocks to scan';
#
# Host: localhost
# Database: whohas
# Table: 'dbg'
# 
CREATE TABLE `dbg` (
  `msg` varchar(100) NOT NULL default ''
) ENGINE=MyISAM DEFAULT CHARSET=latin1; 
#
# Host: localhost
# Database: whohas
# Table: 'format_scan'
# 
CREATE TABLE `format_scan` (
  `ip_address` varchar(100) NOT NULL default '',
  `port_number` mediumint(9) NOT NULL default '0',
  `protocol` varchar(100) NOT NULL default '',
  `country_code` varchar(100) NOT NULL default '',
  `OS` varchar(100) NOT NULL default '',
  `banner` mediumtext NOT NULL,
  `record_status` set('Imported','Migrating','Delete') NOT NULL default 'Imported'
) ENGINE=MyISAM DEFAULT CHARSET=latin1; 
#
# Host: localhost
# Database: whohas
# Table: 'hosts'
# 
CREATE TABLE `hosts` (
  `ip_address` varchar(100) NOT NULL default '',
  `host_name` varchar(100) NOT NULL default 'UNRESOLVED',
  `OS` varchar(100) default 'UNKNOWN',
  `netname` varchar(100) default '',
  `country_code` varchar(100) NOT NULL default '',
  `date_added` datetime NOT NULL default '0000-00-00 00:00:00',
  `last_update` datetime default '0000-00-00 00:00:00',
  `record_status` set('Migrating','Active','Delete') NOT NULL default 'Active',
  `Notes` mediumtext
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COMMENT='P:Table of scanned hosts'; 
#
# Host: localhost
# Database: whohas
# Table: 'services'
# 
CREATE TABLE `services` (
  `ip_address` varchar(100) NOT NULL default '',
  `port_number` mediumint(9) NOT NULL default '0',
  `protocol` varchar(100) NOT NULL default '',
  `banner` mediumtext,
  `date_added` datetime NOT NULL default '0000-00-00 00:00:00',
  `record_status` set('Open','Delete','Migrating') NOT NULL default ''
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COMMENT='P:Table of open ports';
#
#
# +------------------+
# | STORED PROCEDURE |
# +------------------+
#
delimiter //
 
CREATE PROCEDURE import_format_scan()
 BEGIN
 
  # declare and initialize variables
  DECLARE done INT DEFAULT 0;
  DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
  DECLARE fs_cursor CURSOR FOR SELECT ip_address,port_number,protocol,country_code,os,banner FROM whohas.format_scan;
  DECLARE fs_ip varchar(100);
  DECLARE fs_port mediumint(9);
  DECLARE fs_proto varchar(100);
  DECLARE fs_cc varchar(100);
  DECLARE fs_os varchar(100);
  DECLARE fs_banner mediumtext;
 
  # clear out any stuff from failed runs...
  DELETE from whohas.format_scan where record_status in ("Delete","Migrating");
  DELETE from whohas.hosts where record_status in ("Delete","Migrating");
  DELETE from whohas.services where record_status in ("Delete","Migrating");
 
  # DEBUG:
  INSERT INTO dbg(msg) VALUES ('Done with Deletes');
 
  # open the table cursor on format_scan
  OPEN fs_cursor;
 
  # DEBUG:
  INSERT INTO dbg(msg) VALUES ('fs_cursor OPENED');
 
  # read in format_scan table record by record
  REPEAT
    # DEBUG:
    INSERT INTO dbg(msg) VALUES ('Entered REPEAT LOOP');
 
    FETCH fs_cursor INTO fs_ip, fs_port, fs_proto, fs_cc, fs_os, fs_banner;
 
    # DEBUG:
    INSERT INTO dbg(msg) VALUES ('Values FETCHed');
 
    IF NOT done THEN
      # DEBUG:
      INSERT INTO dbg(msg) VALUES ('Ok, we are not done');
 
      # first handle the host data
      IF EXISTS (select ip_address from whohas.hosts where ip_address = fs_ip) THEN
        # DEBUG:
        INSERT INTO dbg(msg) VALUES ('Host exists');
 
        UPDATE whohas.hosts set record_status = "Active", last_update=NOW() where hosts.ip_address = fs_ip;
      ELSE
        # DEBUG:
        INSERT INTO dbg(msg) VALUES ('Host is new');
                                                                                
        INSERT INTO whohas.hosts(ip_address,country_code,OS,date_added,record_status)
         values(fs_ip,fs_cc,fs_os,NOW(),"Active");
        IF EXISTS (select netblock from whohas.address_space where start_ip < fs_ip and end_ip > fs_ip) THEN
          UPDATE whohas.hosts SET netname = (select netname from whohas.address_space where
            start_ip < fs_ip and end_ip > fs_ip limit 1);
        END IF;
      END IF;
      # now handle the port level data
      IF EXISTS (select ip_address, port_number, protocol from whohas.services where ip_address=fs_ip and port_number=fs_port  and protocol=fs_proto) THEN
        UPDATE whohas.services SET record_status='Active' WHERE ip_address=fs_ip and port_number=fs_port and protocol=fs_proto;
        IF EXISTS (select banner from whohas.services where ip_address=fs_ip and port_number=fs_port and protocol=fs_proto and banner = '') THEN
          UPDATE whohas.services SET banner=fs_banner where ip_address=fs_ip and port_number=fs_port and protocol=fs_proto;
        END IF;
      ELSE
        INSERT INTO whohas.services(ip_address,port_number,protocol,banner,date_
added,record_status)
         values(fs_ip,fs_port,fs_proto,fs_banner,NOW(),"Open");
      END IF;
      # any post processing on the record goes here before we move on...
      # 1. If we have netname in address_space
      IF EXISTS (SELECT netname FROM whohas.address_space where start_ip < fs_ip
 and end_ip > fs_ip) THEN
        # 1.1 and we dont have it in hosts
        IF EXISTS (SELECT netname FROM whohas.hosts where ip_address = fs_ip and
 netname = '') THEN
          # grab netname from address_space into hosts
          UPDATE whohas.hosts SET netname = (select netname from whohas.address_
space where start_ip < fs_ip and end_ip > fs_ip limit 1);
        END IF;
      END IF;
      # any more post-processing goes here
    END IF;
  UNTIL done END REPEAT;
  CLOSE fs_cursor;
END//
delimiter ;

Along with the error messages listed above, the dbg table's msg field is being populated as such:

use whohas;
select * from dbg;

+---------------------+
| msg                 |
+---------------------+
| Done with Deletes   |
| fs_cursor OPENED    |
| Entered REPEAT LOOP |
| Done with Deletes   |
| fs_cursor OPENED    |
| Entered REPEAT LOOP |
| Done with Deletes   |
| fs_cursor OPENED    |
| Entered REPEAT LOOP |
| Done with Deletes   |
| fs_cursor OPENED    |
| Entered REPEAT LOOP |
+---------------------+

This is from 4 runs of CALLing import_format_scan(), (I'm not deleting any records before executing the procedure so this is 4 attempts at call import_format_scan()).  As you can see, we make it to the point just before, but not just after, the FETCH.  At first, I thought there might be too many (6) vars but again, curdemo() also crashes the server in a similar way.  The stack trace for curdemo()'s crash looks like this:

resolve_stack_dump -s /tmp/mysqld.sym -n ./mysqld.stack
0x80880d3 handle_segfault + 423
0x82df1d8 pthread_sighandler + 184
0x81308fe destroy__9sp_cursor + 18
0x837ccd9 _._9sp_cursor + 25
0x8130792 pop_cursors__11sp_rcontextUi + 58
0x812e8c6 execute_procedure__7sp_headP3THDPt4List1Z4Item + 1242
0x8099aa3 mysql_execute_command__FP3THD + 17055
0x809afc9 mysql_parse__FP3THDPcUi + 185
0x8094a1f dispatch_command__F19enum_server_commandP3THDPcUi + 1659
0x809439a do_command__FP3THD + 218
0x8093aea handle_one_connection + 602
0x82dc98c pthread_start_thread + 220
0x83124ca thread_start + 4

Thus, the similarities diverge with the param/value for execute_procedure__7sp..., and re-converge with pthread_sighandler + 184.  

Any assistance is appreciated greatly.
[20 Jun 2004 5:20] MySQL Verification Team
I tried your test case and the Curdemo and both now return the
below error message:

    -> //
ERROR 1331 (42000): Cursor declaration after handler declaration
mysql>

The test I did against latest BK 5.0 source tree.

Pem what happens ?
[20 Jun 2004 10:31] Per-Erik Martin
The binary build from late 2003 did not enforce the SQL-99/2003 standard stricly. The current
version does. The order of declarations must be: variables, conditions, cursors, handlers.

After correcting this in the procedure, it seems to work in the current source tree. (I.e. it doesn't
crash.)