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