MySQL Bugs: #6516: Server crash loading spatial data

Bug #6516	Server crash loading spatial data
Submitted:	9 Nov 2004 10:29	Modified:	2 Jan 2005 15:11
Reporter:	[ name withheld ] (Basic Quality Contributor)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	4.1.8 max nt	OS:	Windows (windows 2000 server sp4)
Assigned to:	Alexey Botchkov	CPU Architecture:	Any

Description:
The server crashes while inserting data into a spatially enabled table, the message is:

mysqld-max-nt.exe Application Error

The instruction at "0x004fa3d9" referenced memory at "0x00000004". The memory could not be read ...

And the server goes down.

How to repeat:
The behaviour can be reproduced as:
1. Download zip codes file at http://spatialnews.geocomm.com/newsletter/2000/jan/cenzuszipcodes.zip

2. Create staging table:
CREATE TABLE `ST_ZIP_CODES` (
  `STATE_FIPS_CODE` VARCHAR(50),
  `ZIP_CODE` VARCHAR(5),
  `STATE_ABBREVIATION` VARCHAR(45),
  `ZIP_CODE_NAME` VARCHAR(45),
  `LONGITUDE` FLOAT,
  `LATITUDE` FLOAT,
  `1990_POPULATION` INTEGER,
  `ALLOCATION_FACTOR` FLOAT
)
TYPE = InnoDB;

3. Load it with data coming from the file

load data local infile 'c:/zips.txt'
replace
into table st_zip_codes
fields terminated by ','
optionally enclosed  by '"'
lines
terminated by '\n'

4. Create final destination table:

create table ZIP_CODES (
STATE_FIPS_CODE CHAR(2),
ZIP_CODE CHAR(5),
STATE_ABBREVIATION CHAR(2),
ZIP_CODE_NAME VARCHAR(50),
ZIP_LOC POINT NOT NULL,
PRIMARY KEY (ZIP_CODE),
SPATIAL KEY (ZIP_LOC)
)
TYPE = MyISAM;

ALTER TABLE `test`.`zip_codes`
, ADD COLUMN `1990_POPULATION` INTEGER UNSIGNED AFTER `ZIP_LOC`
, ADD COLUMN `ALLOCATION_FACTOR` FLOAT AFTER `1990_POPULATION`;

5. Load it with an insert .. select 

INSERT INTO
zip_codes
(
STATE_FIPS_CODE,
ZIP_CODE,
STATE_ABBREVIATION,
ZIP_CODE_NAME,
ZIP_LOC,
1990_POPULATION,
ALLOCATION_FACTOR
)
SELECT
STATE_FIPS_CODE,
ZIP_CODE,
STATE_ABBREVIATION,
ZIP_CODE_NAME,
GeomFromText('POINT(' + LONGITUDE + ' ' + LATITUDE + ')'),
1990_POPULATION,
ALLOCATION_FACTOR FROM ST_ZIP_CODES

and BANG!!! the server crashes.

Verified with 4.1.8-debug-log
OS: Windows, Linux

Back trace:

(gdb) bt
#0  0x083d0475 in sp_get_geometry_mbr (wkb=0xbdbfeef4, end=0x0, n_dims=2, mbr=0xbdbfef3c, top=1)
    at sp_key.c:203
#1  0x083d02d0 in sp_mbr_from_wkb (wkb=0x4 <Address 0x4 out of bounds>, size=4294967292,
    n_dims=2, mbr=0xbdbfef3c) at sp_key.c:118
#2  0x083d017c in sp_make_key (info=0x8cf1fe8, keynr=1, key=0x8cf2a78 "\00535004",
    record=0x8ce5bb8 "&#1070;01    35004          AL    ACMAR", ' ' <repeats 145 times>, filepos=0)
    at sp_key.c:53
#3  0x083a60e2 in _mi_make_key (info=0x8cf1fe8, keynr=1, key=0x8cf2a78 "\00535004",
    record=0x8ce5bb8 "&#1070;01    35004          AL    ACMAR", ' ' <repeats 145 times>, filepos=0)
    at mi_key.c:57
#4  0x083af0b6 in mi_write (info=0x8cf1fe8,
    record=0x8ce5bb8 "&#1070;01    35004          AL    ACMAR", ' ' <repeats 145 times>)
    at mi_write.c:122
#5  0x081dbc57 in ha_myisam::write_row (this=0x8ce5898,
    buf=0x8ce5bb8 "&#1070;01    35004          AL    ACMAR", ' ' <repeats 145 times>)
    at ha_myisam.cc:263
#6  0x081a7628 in write_record (table=0x8ceda78, info=0x8cfdf20) at sql_insert.cc:588
#7  0x081a9dd8 in select_insert::send_data (this=0x8cfdf00, values=@0x8cee510)
    at sql_insert.cc:1496
#8  0x0819dbf5 in end_send (join=0x8cfdf48, join_tab=0x8cfeea0, end_of_records=false)
    at sql_select.cc:6366
#9  0x0819ca5b in sub_select (join=0x8cfdf48, join_tab=0x8cfed60, end_of_records=false)
    at sql_select.cc:5790
#10 0x0819c656 in do_select (join=0x8cfdf48, fields=0x8cee510, table=0x0, procedure=0x0)
    at sql_select.cc:5674
#11 0x08192471 in JOIN::exec (this=0x8cfdf48) at sql_select.cc:1464
#12 0x08192a29 in mysql_select (thd=0x8cee378, rref_pointer_array=0x8cee5ac, tables=0x8cfde98,
    wild_num=0, fields=@0x8cee510, conds=0x0, og_num=0, order=0x0, group=0x0, having=0x0,
    proc_param=0x0, select_options=277105152, result=0x8cfdf00, unit=0x8cee3c0,
    select_lex=0x8cee4a8) at sql_select.cc:1584
#13 0x0818f0fc in handle_select (thd=0x8cee378, lex=0x8cee3b4, result=0x8cfdf00)
    at sql_select.cc:193
#14 0x0816e6b7 in mysql_execute_command (thd=0x8cee378) at sql_parse.cc:2748
#15 0x08171711 in mysql_parse (thd=0x8cee378,
    inBuf=0x8cfd088 "INSERT INTO\nZIP_CODES\n(\nSTATE_FIPS_CODE,\nZIP_CODE,\nSTATE_ABBREVIATION,\nZIP_CODE_NAME,\nZIP_LOC,\n1990_POPULATION,\nALLOCATION_FACTOR\n)\nSELECT\nSTATE_FIPS_CODE,\nZIP_CODE,\nSTATE_ABBREVIATION,\nZIP_CODE_NAME,"..., length=312) at sql_parse.cc:4063
#16 0x0816b8c2 in dispatch_command (command=COM_QUERY, thd=0x8cee378,
    packet=0x8ceffc1 "INSERT INTO\nZIP_CODES\n(\nSTATE_FIPS_CODE,\nZIP_CODE,\nSTATE_ABBREVIATION,\nZIP_CODE_NAME,\nZIP_LOC,\n1990_POPULATION,\nALLOCATION_FACTOR\n)\nSELECT\nSTATE_FIPS_CODE,\nZIP_CODE,\nSTATE_ABBREVIATION,\nZIP_CODE_NAME,"..., packet_length=313) at sql_parse.cc:1494
#17 0x0816b1e2 in do_command (thd=0x8cee378) at sql_parse.cc:1280
#18 0x0816a7bb in handle_one_connection (arg=0x8cee378) at sql_parse.cc:1024
#19 0xb7e4714b in pthread_start_thread () from /lib/libpthread.so.0
#20 0xb7e471df in pthread_start_thread_event () from /lib/libpthread.so.0
#21 0xb7d7a50a in clone () from /lib/libc.so.6

bk commit - 4.1 tree (hf:1.2104) BUG#6516

I have perhaps a similar problem with 4.1.7 server.

Perhaps the better place would be to try bug http://bugs.mysql.com/bug.php?id=6701 (more similar behaviour of mysql server), but this one seems to be closer when we come to the cause (INSERT query).

Server crashes on some SQL statements (apparently - INSERTs only, as far as I was able to detect for now), but now dialog appears. It just begins to use 99% of CPU time and only task manager allows to kill it.

Computer runs Windows XP Prof. with SP 2.
MySQL Server: 4.1.7, I tried -nt, -max-nt and -debug, they all crash in the same way on the same statements.

Now, what queries are these?

Well, I just happened to install eZ publish (first encountered problem of this kind) and then Post Nuke. PostNuke's installatin doesn't finish properly (detailed description below) due to the problem with MySQL. In eZ publish I use UTF-8 and polish language setting, I also use UTF-8 encoding in PostNuke.

In eZ publish creating new article with any of my national (polish) characters, like o with ' over it or c with ' over it (can't use it here since bugs.mysql.com uses ISO-8859-1 :( ) and saving it to database, mySQL crashes and kills itself after PHP's 30 sec timeout. When creating the very same article without national characters, no problem occurs.

It repeats every time I try to do it, by due to the complication of eZ publish I don't know what query exactly does that.

Now, back to PostNuke.

I proceed with installation, tables are crated smoothly, then PN's install asks for some user data (administartor's email, password and such). I enter the values and PN's installation procedure attempts to fill the tables with basic data (like categories etc.). 

However, this part doesn't succeed completely - in the third "part" of the newdata.php script, among many INSERTs, on line 494 there is a statement shown below:

$result = $dbconn->Execute("INSERT INTO ".$prefix."_module_vars VALUES ('98', 'Wiki','FieldSeparator','\263')") or die ("<strong>"._NOTUPDATED.$prefix."_module_vars</strong>");

The problem seems to be with the escape sign \ in the beginning of the last field's value ('\263'). Removing it "solves" the problem. I have no idea (yet) what this whole record means and why is there a backslash before the number, but that seems to be exactly what's causing the problem.

The very same query ran under QueryBrowser works fine. (My PostNuke runs under PHP 4.3.x and Apache 1.3.x)

To reproduce my bug - if it's different and you want to test it (I hope you do) -  download PostNuke .750 (newest version) and try to install it on 4.1.7 server running on Windows (XP SP2, if possible), Apache 1.3.x and PHP 4.3.x with old passwords so that there are no problems with php's old client.

If more information is needed or if I should report a distinct bug - contact me please at   petrykowski @ people.pl.

Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.

If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information 
about accessing the source trees is available at
    http://www.mysql.com/doc/en/Installing_source_tree.html

This bug is still present in MySQL 4.1.8-nt, repeating exactly the same steps leads to a more graceful stop:

mysql> INSERT INTO
    -> zip_codes
    -> (
    -> STATE_FIPS_CODE,
    -> ZIP_CODE,
    -> STATE_ABBREVIATION,
    -> ZIP_CODE_NAME,
    -> ZIP_LOC,
    -> 1990_POPULATION,
    -> ALLOCATION_FACTOR
    -> )
    -> SELECT
    -> STATE_FIPS_CODE,
    -> ZIP_CODE,
    -> STATE_ABBREVIATION,
    -> ZIP_CODE_NAME,
    -> GeomFromText('POINT(' + LONGITUDE + ' ' + LATITUDE + ')'), 1990_POPULATIO
N, ALLOCATION_FACTOR FROM ST_ZIP_CODES;
ERROR 1105 (HY000): Unknown error
mysql>

The same for 4.1.8-nt-max

Thank you for the update.
Tested on Linux Slackware BK source tree:

mysql> INSERT INTO
    -> ZIP_CODES
    -> (
    -> STATE_FIPS_CODE,
    -> ZIP_CODE,
    -> STATE_ABBREVIATION,
    -> ZIP_CODE_NAME,
    -> ZIP_LOC,
    -> 1990_POPULATION,
    -> ALLOCATION_FACTOR
    -> )
    -> SELECT
    -> STATE_FIPS_CODE,
    -> ZIP_CODE,
    -> STATE_ABBREVIATION,
    -> ZIP_CODE_NAME,
    -> GeomFromText('POINT(' + LONGITUDE + ' ' + LATITUDE + ')'),
    -> 1990_POPULATION,
    -> ALLOCATION_FACTOR FROM ST_ZIP_CODES;
ERROR 1105 (HY000): Unknown error
mysql> select version();
+-----------------+
| version()       |
+-----------------+
| 4.1.9-debug-log |
+-----------------+
1 row in set (0.03 sec)

mysql>

Changed server version from 4.1.7 to 4.1.8

Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.

If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information 
about accessing the source trees is available at
    http://www.mysql.com/doc/en/Installing_source_tree.html

Additional info:

Returning error here is the expected behaviour, not a bug.

You see, operation '+' in MySQL isn't equivalent for the CONCAT() function.
So ' select 'POINT(' + 10.1 + ' ' + 20.2 + ')';'
returns 
+------------------------------------+
| 'POINT(' + 10.1 + ' ' + 20.2 + ')' |
+------------------------------------+
|                               30.3 |
+------------------------------------+
1 row in set (0.00 sec)

To get proper result you should change the expression in your query like that:
GeomFromText(CONCAT('POINT(', LONGITUDE ,' ', LATITUDE, ')'));