Bug #13653 Crash on stored procedure with large dataset
Submitted: 30 Sep 2005 12:59 Modified: 18 Nov 2005 20:40
Reporter: David Fuess Email Updates:
Status: Can't repeat Impact on me:
None 
Category:MySQL Server: Stored Routines Severity:S2 (Serious)
Version:5.0.13 RC/BK source OS:Microsoft Windows (Windows 2000/Linux)
Assigned to: Konstantin Osipov CPU Architecture:Any

[30 Sep 2005 12:59] David Fuess
Description:
Mysqld-nt crashes when using a cursor in a stored procedure to process a large number of records. In my experimentation the number of records returned int he cursor to produce the error for the particular database I am using is between 600,000 and 750,000 records.

Records int the main table consist of:
2 INT(10)
17 VARCHAR(50)
11 DOUBLE(14,10)
1 DATETIME

The error message is:
The instruction at "0x0047be5f" referenced memory at "0x0000008c". The memory could not be read.

I would provide the actual code but it is on an offnet system and I have no connectivity that permits transfer.

How to repeat:
Create a stored procedure that iterates over a cursor and performs a simple operation. In my case I am using a cursor in an outer loop that iterates over a large table that calculates and stores a result (>4M records) and a cursor in the inner loop that returns a single record from a smaller table used in the calculation.
[30 Sep 2005 13:05] Miguel Solorzano
Could you please provide the dump file of the database
for to test on our side ? If yes you can upload the
the zip file with a name like bug13653.zip at:

ftp://ftp.mysql.com:/pub/mysql/upload

Thanks in advance.
[30 Sep 2005 13:44] David Fuess
Unfortunately I cannot provide a dump. The database is on a classified system and there is no transport from it to the external net. I can be your eyes but that's the best I can do with this table.
[30 Sep 2005 14:14] Miguel Solorzano
If you can provide at least the table/stored procedure create script
would be nice, following the description of the issue sometimes we
are not able to reproduce the issue. Thanks.
[30 Sep 2005 15:28] David Fuess
Uploaded a sample database with all necessary tables, procedures, and functions. File: film.zip

The procedure is "UpdateLoc" it uses function"Dist." You can probably dummy up Dist or just not use it in your tests because it will require proper Latitudes and Longitudes in both tables.
[30 Sep 2005 22:32] David Fuess
I have reproduced the problem using the cities database. Here is the code to build the database and procedures. All you have to do is set the proper location to load cities data and "Call UpdateLocs()". I will upload cities.tar.gz to the ftp site.

DROP TABLE IF EXISTS cities;
CREATE TABLE cities (
  country char(2) NOT NULL default '',
  city varchar(100) NOT NULL default '',
  city_accented varchar(100) NOT NULL default '',
  region char(2) NOT NULL default '',
  latitude decimal(12,7) NOT NULL default '0.0000000',
  longitude decimal(12,7) NOT NULL default '0.0000000'
) ENGINE=InnoDB;

DROP TABLE IF EXISTS locs;
CREATE TABLE locs (
  locID INT(10) NOT NULL AUTO_INCREMENT,
  country char(2) NOT NULL default '',
  city varchar(100) NOT NULL default '',
  city_accented varchar(100) NOT NULL default '',
  region char(2) NOT NULL default '',
  latitude decimal(12,7) NOT NULL default '0.0000000',
  longitude decimal(12,7) NOT NULL default '0.0000000',
  KEY (locID)
) ENGINE=InnoDB;

LOAD DATA
INFILE 'D:\\Programs\\MySQL\\Cities.txt'
INTO TABLE cities
IGNORE 1 LINES;

ALTER TABLE `cities`.`cities` ADD COLUMN `cityID` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT AFTER `longitude`,
  ADD COLUMN `locID` INTEGER UNSIGNED AFTER `cityID`,
  ADD COLUMN `locDST` DECIMAL(12,3) AFTER `locID`,
  ADD PRIMARY KEY(`cityID`);

INSERT INTO locs (country, city, city_accented, region, latitude, longitude)
  SELECT country, city, city_accented, region, latitude, longitude
  FROM cities WHERE city='livermore';

DELIMITER $$

DROP FUNCTION IF EXISTS `cities`.`Dist`$$
CREATE FUNCTION `Dist`(lat1 DOUBLE, lon1 DOUBLE, lat2 DOUBLE, lon2 DOUBLE) RETURNS double
BEGIN
  RETURN 3963.0 * arccos(sin(lat1/57.2958) * sin(lat2/57.2958) + cos(lat1/57.2958) * cos(lat2/57.2958) *  cos(lon2/57.2958 -lon1/57.2958));
END$$

DELIMITER ;

DELIMITER $$

DROP PROCEDURE IF EXISTS `cities`.`UpdateLocs`$$
CREATE PROCEDURE `UpdateLocs`()
BEGIN
  DECLARE LA1, LO1, DST DOUBLE;
  DECLARE FID, TID INT;
  DECLARE LoopEnd INT DEFAULT 0;

  DECLARE C1 CURSOR FOR
    SELECT cityID, latitude, longitude
    FROM cities
    WHERE locID IS NULL;

  DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET LoopEnd=1;

  OPEN C1;

  REPEAT
    FETCH C1 into FID, LA1, LO1;
    SELECT locID, Dist(LA1, LO1, locs.latitude, locs.longitude) AS Distance
      INTO TID, DST
      FROM locs
      ORDER BY Distance
      LIMIT 1;
    UPDATE cities SET locID=TID, locDist=DST WHERE cityID=FID;
  UNTIL LoopEnd END REPEAT;
  CLOSE C1;

END$$

DELIMITER ;
[30 Sep 2005 22:42] David Fuess
Oops, it's "cities.txt.gz" and the upload is complete.
[1 Oct 2005 0:58] Miguel Solorzano
Thank you for the feedback and test case. I was able to reproduce
on Linux too:

miguel@hegel:~/dbs/5.0> bin/mysql -uroot
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 5.0.14-rc-debug

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> create database cities;
Query OK, 1 row affected (0.01 sec)

mysql> use cities;
Database changed
mysql> DROP TABLE IF EXISTS cities;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> CREATE TABLE cities (
    ->   country char(2) NOT NULL default '',
    ->   city varchar(100) NOT NULL default '',
    ->   city_accented varchar(100) NOT NULL default '',
    ->   region char(2) NOT NULL default '',
    ->   latitude decimal(12,7) NOT NULL default '0.0000000',
    ->   longitude decimal(12,7) NOT NULL default '0.0000000'
    -> ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.15 sec)

mysql> 
mysql> DROP TABLE IF EXISTS locs;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> CREATE TABLE locs (
    ->   locID INT(10) NOT NULL AUTO_INCREMENT,
    ->   country char(2) NOT NULL default '',
    ->   city varchar(100) NOT NULL default '',
    ->   city_accented varchar(100) NOT NULL default '',
    ->   region char(2) NOT NULL default '',
    ->   latitude decimal(12,7) NOT NULL default '0.0000000',
    ->   longitude decimal(12,7) NOT NULL default '0.0000000',
    ->   KEY (locID)
    -> ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.07 sec)

mysql> LOAD DATA
    -> INFILE '/home/miguel/v/cities.txt'
    -> INTO TABLE cities
    -> IGNORE 1 LINES;
Query OK, 3047082 rows affected (4 min 9.80 sec)
Records: 3047082  Deleted: 0  Skipped: 0  Warnings: 0

mysql> ALTER TABLE `cities`.`cities` ADD COLUMN `cityID` INTEGER UNSIGNED NOT NULL
    -> AUTO_INCREMENT AFTER `longitude`,
    ->   ADD COLUMN `locID` INTEGER UNSIGNED AFTER `cityID`,
    ->   ADD COLUMN `locDST` DECIMAL(12,3) AFTER `locID`,
    ->   ADD PRIMARY KEY(`cityID`);
Query OK, 3047082 rows affected (4 min 31.76 sec)
Records: 3047082  Duplicates: 0  Warnings: 0

mysql> INSERT INTO locs (country, city, city_accented, region, latitude, longitude)
    ->   SELECT country, city, city_accented, region, latitude, longitude
    ->   FROM cities WHERE city='livermore';
Query OK, 7 rows affected (45.37 sec)
Records: 7  Duplicates: 0  Warnings: 0

mysql> DELIMITER $$
mysql> 
mysql> DROP FUNCTION IF EXISTS `cities`.`Dist`$$
Query OK, 0 rows affected, 1 warning (0.15 sec)

mysql> CREATE FUNCTION `Dist`(lat1 DOUBLE, lon1 DOUBLE, lat2 DOUBLE, lon2 DOUBLE)
    -> RETURNS double
    -> BEGIN
    ->   RETURN 3963.0 * arccos(sin(lat1/57.2958) * sin(lat2/57.2958) +
    -> cos(lat1/57.2958) * cos(lat2/57.2958) *  cos(lon2/57.2958 -lon1/57.2958));
    -> END$$
Query OK, 0 rows affected (0.04 sec)

mysql> DROP PROCEDURE IF EXISTS `cities`.`UpdateLocs`$$
Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> CREATE PROCEDURE `UpdateLocs`()
    -> BEGIN
    ->   DECLARE LA1, LO1, DST DOUBLE;
    ->   DECLARE FID, TID INT;
    ->   DECLARE LoopEnd INT DEFAULT 0;
    -> 
    ->   DECLARE C1 CURSOR FOR
    ->     SELECT cityID, latitude, longitude
    ->     FROM cities
    ->     WHERE locID IS NULL;
    -> 
    ->   DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET LoopEnd=1;
    -> 
    ->   OPEN C1;
    -> 
    ->   REPEAT
    ->     FETCH C1 into FID, LA1, LO1;
    ->     SELECT locID, Dist(LA1, LO1, locs.latitude, locs.longitude) AS Distance
    ->       INTO TID, DST
    ->       FROM locs
    ->       ORDER BY Distance
    ->       LIMIT 1;
    ->     UPDATE cities SET locID=TID, locDist=DST WHERE cityID=FID;
    ->   UNTIL LoopEnd END REPEAT;
    ->   CLOSE C1;
    -> 
    -> END$$
Query OK, 0 rows affected (0.01 sec)

mysql> DELIMITER ;
mysql> Call UpdateLocs();
ERROR 2013 (HY000): Lost connection to MySQL server during query

[New Thread 1132256176 (LWP 29355)]
050930 21:34:44 [Note] /home/miguel/dbs/5.0/libexec/mysqld: ready for connections.
Version: '5.0.14-rc-debug'  socket: '/tmp/mysql.sock'  port: 3306  Source distribution
[New Thread 1132456880 (LWP 29598)]

Program received signal SIGSEGV, Segmentation fault.
[Switching to Thread 1132456880 (LWP 29598)]
0x00000000 in ?? ()
(gdb) bt full
#0  0x00000000 in ?? ()
No symbol table info available.
#1  0x0832ddcc in Materialized_cursor::fetch (this=0x8e746c8, num_rows=1) at sql_cursor.cc:589
        thd = (class THD *) 0x8e382a0
        res = 0
#2  0x0833c864 in sp_cursor::fetch (this=0x8e63ea0, thd=0x8e382a0, vars=0x8e76584) at sp_rcontext.cc:246
No locals.
#3  0x083399a0 in sp_instr_cfetch::execute (this=0x8e76560, thd=0x8e382a0, nextp=0x437fd940) at sp_head.cc:2582
        c = (class sp_cursor *) 0x8e63ea0
        res = 11
        backup_arena = {_vptr.Query_arena = 0x85d97b8, free_list = 0x8e6b794, mem_root = 0x4, is_backup_arena = false, state = 149127840}
        _db_func_ = 0x857523a "\203Ä\020ÉÃU\211å\203ì\b\213E\b\2038"
        _db_file_ = 0x437fd858 "èÙ\177C._3\b`eç\b \202ã\b@Ù\177C"
        _db_level_ = 149359768
        _db_framep_ = (char **) 0x8690d21
#4  0x08335f2e in sp_head::execute (this=0x8e75660, thd=0x8e382a0) at sp_head.cc:967
        i = (sp_instr *) 0x8e76560
        hip = 149369704
        _db_func_ = 0x0
        _db_file_ = 0x437fda98 "èß\177CÑ8\037\b`Vç\b \202ã\bL\207ã\b"
        _db_level_ = 149129036
        _db_framep_ = (char **) 0x437fda40
<cut>
[1 Oct 2005 3:40] David Fuess
Update on the code ... noted a few problems with the previous one. This one is running ok with 750k records but crashed with 3M. You can set the limit in the UpdateLocs procedure for testing.

DROP TABLE IF EXISTS cities;
CREATE TABLE cities (
  country char(2) NOT NULL default '',
  city varchar(100) NOT NULL default '',
  city_accented varchar(100) NOT NULL default '',
  region char(2) NOT NULL default '',
  latitude decimal(12,7) NOT NULL default '0.0000000',
  longitude decimal(12,7) NOT NULL default '0.0000000'
) ENGINE=InnoDB;

DROP TABLE IF EXISTS locs;
CREATE TABLE locs (
  locID INT(10) NOT NULL AUTO_INCREMENT,
  country char(2) NOT NULL default '',
  city varchar(100) NOT NULL default '',
  city_accented varchar(100) NOT NULL default '',
  region char(2) NOT NULL default '',
  latitude decimal(12,7) NOT NULL default '0.0000000',
  longitude decimal(12,7) NOT NULL default '0.0000000',
  KEY (locID)
) ENGINE=InnoDB;

LOAD DATA
INFILE 'D:\\Programs\\MySQL\\Cities.txt'
INTO TABLE cities
IGNORE 1 LINES;

ALTER TABLE `cities`.`cities` ADD COLUMN `cityID` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT AFTER `longitude`,
  ADD COLUMN `locID` INTEGER UNSIGNED AFTER `cityID`,
  ADD COLUMN `locDST` DECIMAL(12,3) AFTER `locID`,
  ADD PRIMARY KEY(`cityID`);

INSERT INTO locs (country, city, city_accented, region, latitude, longitude)
  SELECT country, city, city_accented, region, latitude, longitude
  FROM cities WHERE city='livermore';

DDELIMITER $$

DROP FUNCTION IF EXISTS `cities`.`Dist`$$
CREATE FUNCTION `Dist`(lat1 DOUBLE, lon1 DOUBLE, lat2 DOUBLE, lon2 DOUBLE) RETURNS double (20,10)
BEGIN
  RETURN ROUND(3963.0 + acos(sin(lat1/57.2958) * sin(lat2/57.2958) + cos(lat1/57.2958) * cos(lat2/57.2958) *  cos(lon2/57.2958 - lon1/57.2958)), 3);
END$$

DROP PROCEDURE IF EXISTS `cities`.`UpdateLocs`$$
CREATE PROCEDURE `UpdateLocs`()
BEGIN
  DECLARE LA1, LO1, DST DOUBLE (20,10);
  DECLARE FID, TID INT;
  DECLARE LoopEnd INT DEFAULT 0;

  DECLARE C1 CURSOR FOR
    SELECT cityID, latitude, longitude
    FROM cities
    WHERE locID IS NULL
    LIMIT 750000;

  DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET LoopEnd=1;

  OPEN C1;

  REPEAT
    FETCH C1 into FID, LA1, LO1;
    SELECT locID, Dist(LA1, LO1, locs.latitude, locs.longitude) AS Distance
      INTO TID, DST
      FROM locs
      ORDER BY Distance
      LIMIT 1;
    UPDATE cities SET locID=TID, locDST=DST WHERE cityID=FID;
  UNTIL LoopEnd END REPEAT;
  CLOSE C1;

END$$

DELIMITER ;
[2 Oct 2005 2:07] David Fuess
BTW, it is interesting to note that when I define the Dist function under Windows XP both the MySQL Administrator 1.1.3 and the Query Browser 1.1.13 start to encounter "msvcrt.dll" read fault errors. The errors occur only only on the schema containing the Dist stored procedure (the cities schema in this case). In both cases neither of the programs nor DreamWeaver are able to get the schema details from MySQL.
[18 Nov 2005 15:18] Konstantin Osipov
This is most likely a duplicate of a closed Bug#14143.
Could you please verify that the bug is still present in the latest source tree?
[18 Nov 2005 19:35] David Fuess
Verified. I ran the sample code and data I sent with the original report under 5.0.15 and the bug is still there.
[18 Nov 2005 19:50] Konstantin Osipov
Bug#14143/Bug#14210 were fixed in 5.0.16.
Could you please check the latest version?
[18 Nov 2005 20:40] Miguel Solorzano
I was unable to repeat with server built from source, instead I got
an error with a function called from original script which not exists:

miguel@hegel:~/dbs/5.0> bin/mysqladmin -uroot create cities
miguel@hegel:~/dbs/5.0> bin/mysql -uroot cities
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2 to server version: 5.0.17-debug

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> DROP TABLE IF EXISTS cities;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> CREATE TABLE cities (
    ->   country char(2) NOT NULL default '',
    ->   city varchar(100) NOT NULL default '',
    ->   city_accented varchar(100) NOT NULL default '',
    ->   region char(2) NOT NULL default '',
    ->   latitude decimal(12,7) NOT NULL default '0.0000000',
    ->   longitude decimal(12,7) NOT NULL default '0.0000000'
    -> ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.06 sec)

mysql> 
mysql> DROP TABLE IF EXISTS locs;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> CREATE TABLE locs (
    ->   locID INT(10) NOT NULL AUTO_INCREMENT,
    ->   country char(2) NOT NULL default '',
    ->   city varchar(100) NOT NULL default '',
    ->   city_accented varchar(100) NOT NULL default '',
    ->   region char(2) NOT NULL default '',
    ->   latitude decimal(12,7) NOT NULL default '0.0000000',
    ->   longitude decimal(12,7) NOT NULL default '0.0000000',
    ->   KEY (locID)
    -> ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.05 sec)

mysql> 
mysql> LOAD DATA
    -> INFILE '/home/miguel/v/cities.txt'
    -> INTO TABLE cities
    -> IGNORE 1 LINES;
Query OK, 3047082 rows affected (3 min 50.18 sec)
Records: 3047082  Deleted: 0  Skipped: 0  Warnings: 0

mysql> ALTER TABLE `cities`.`cities` ADD COLUMN `cityID` INTEGER UNSIGNED NOT NULL
    -> AUTO_INCREMENT AFTER `longitude`,
    ->   ADD COLUMN `locID` INTEGER UNSIGNED AFTER `cityID`,
    ->   ADD COLUMN `locDST` DECIMAL(12,3) AFTER `locID`,
    ->   ADD PRIMARY KEY(`cityID`);
Query OK, 3047082 rows affected (4 min 3.53 sec)
Records: 3047082  Duplicates: 0  Warnings: 0

mysql> INSERT INTO locs (country, city, city_accented, region, latitude, longitude)
    ->   SELECT country, city, city_accented, region, latitude, longitude
    ->   FROM cities WHERE city='livermore';
Query OK, 7 rows affected (38.37 sec)
Records: 7  Duplicates: 0  Warnings: 0

mysql> 
mysql> DELIMITER $$
mysql> 
mysql> DROP FUNCTION IF EXISTS `cities`.`Dist`$$
Query OK, 0 rows affected, 1 warning (0.08 sec)

mysql> CREATE FUNCTION `Dist`(lat1 DOUBLE, lon1 DOUBLE, lat2 DOUBLE, lon2 DOUBLE)
    -> RETURNS double
    -> BEGIN
    ->   RETURN 3963.0 * arccos(sin(lat1/57.2958) * sin(lat2/57.2958) +
    -> cos(lat1/57.2958) * cos(lat2/57.2958) *  cos(lon2/57.2958 -lon1/57.2958));
    -> END$$
Query OK, 0 rows affected (0.03 sec)

mysql> 
mysql> DELIMITER ;
mysql> DELIMITER $$
mysql> 
mysql> DROP PROCEDURE IF EXISTS `cities`.`UpdateLocs`$$
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> CREATE PROCEDURE `UpdateLocs`()
    -> BEGIN
    ->   DECLARE LA1, LO1, DST DOUBLE;
    ->   DECLARE FID, TID INT;
    ->   DECLARE LoopEnd INT DEFAULT 0;
    -> 
    ->   DECLARE C1 CURSOR FOR
    ->     SELECT cityID, latitude, longitude
    ->     FROM cities
    ->     WHERE locID IS NULL;
    -> 
    ->   DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET LoopEnd=1;
    -> 
    ->   OPEN C1;
    -> 
    ->   REPEAT
    ->     FETCH C1 into FID, LA1, LO1;
    ->     SELECT locID, Dist(LA1, LO1, locs.latitude, locs.longitude) AS Distance
    ->       INTO TID, DST
    ->       FROM locs
    ->       ORDER BY Distance
    ->       LIMIT 1;
    ->     UPDATE cities SET locID=TID, locDist=DST WHERE cityID=FID;
    ->   UNTIL LoopEnd END REPEAT;
    ->   CLOSE C1;
    -> 
    -> END$$
Query OK, 0 rows affected (0.00 sec)

mysql> DELIMITER ;
mysql> Call UpdateLocs();
ERROR 1305 (42000): FUNCTION cities.arccos does not exist

mysql> CREATE FUNCTION `Dist`(lat1 DOUBLE, lon1 DOUBLE, lat2 DOUBLE, lon2 DOUBLE)
    -> RETURNS double
    -> BEGIN
    ->   RETURN 3963.0 * arccos(sin(lat1/57.2958) * sin(lat2/57.2958) +
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^