| 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: | |
| Category: | MySQL Server: Stored Routines | Severity: | S2 (Serious) |
| Version: | 5.0.13 RC/BK source | OS: | Windows (Windows 2000/Linux) |
| Assigned to: | Konstantin Osipov | CPU Architecture: | Any |
[30 Sep 2005 12:59]
David Fuess
[30 Sep 2005 13:05]
MySQL Verification Team
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]
MySQL Verification Team
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]
MySQL Verification Team
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]
MySQL Verification Team
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) +
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
