MySQL Bugs: #22372: Disable spatial key, load data, enable spatial key, crashes table

Bug #22372	Disable spatial key, load data, enable spatial key, crashes table
Submitted:	15 Sep 2006 0:12	Modified:	13 Dec 2006 18:57
Reporter:	Peter Brodersen (Candidate Quality Contributor)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	5.1.12-beta-log, 5.0.24a-Debian_2.dotdeb, 5.0 BK, 4.1 BK	OS:	Linux (linux)
Assigned to:	Alexey Botchkov	CPU Architecture:	Any

Description:
A table will crash after a series of circumstances:

Create a table with a (non-important) ordinary field and a geometry field (with not null constraint and spatial index)
Disable keys
Load data from file where data does not populate the geometry field
Enable keys

How to repeat:
Create a file named simple.csv that just contains one word as data, e.g.:
echo foo >simple.csv

DROP TABLE IF EXISTS k;
CREATE TABLE k (a TEXT, b GEOMETRY NOT NULL, SPATIAL KEY(b) );
ALTER TABLE k DISABLE KEYS;
LOAD DATA LOCAL INFILE 'simple.csv' INTO TABLE k;
ALTER TABLE k ENABLE KEYS;

The ENABLE KEYS results in:
ERROR 1034 (HY000): 158 for record at pos 0

The table will be marked as crashed from that point on:
SELECT * FROM k;
ERROR 144 (HY000): Table './test/k' is marked as crashed and last (automatic?) repair failed

If the DISABLE KEYS query is omitted the LOAD DATA query will simply fail (with an "Unknown error", as mentioned in bug #21790 )

Thank you for the report.

Verified as described on Linux. All versions are affected.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/13293

ChangeSet@1.2574, 2006-10-07 21:39:10+05:00, holyfoot@mysql.com +5 -0
  bug #22372 (LOAD DATA crashes the table with the GEOMETRY field)
  
  The problem is that the GEOMETRY field doesn't have and default value
  that is usually set by Field::reset method, if the field can't be NULL.
  So if the loaded file doesn't have all the data it can't be loaded.
  
  I propose to check if the 'reset' operation is possible for the field
  and return an error if it's not.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/14424

ChangeSet@1.2574, 2006-10-26 17:23:14+05:00, holyfoot@mysql.com +4 -0
  bug #22372 (LOAD DATA crashes the table with the GEOMETRY field)
  
  The problem is that the GEOMETRY field doesn't have and default value
  that is usually set by Field::reset method, if the field can't be NULL.
  So if the loaded file doesn't have all the data it can't be loaded.
  
  I propose to check if the 'reset' operation is possible for the field
  and return an error if it's not.

The patch "bk commit into 4.1 tree (holyfoot:1.2574) BUG#22372" is ok to push.

P.S.

Consider extending the test to load a file with multiple records,
with good records in the beginning and a bad record in the middle.

Sorry, previous patch was incomplete. We not should change
behaviour for non-GEOMETRY fields. 

Please add condition:

(field->type() == FIELD_TYPE_GEOMETRY)

and post a new patch.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/14428

ChangeSet@1.2574, 2006-10-26 18:12:53+05:00, holyfoot@mysql.com +4 -0
  bug #22372 (LOAD DATA crashes the table with the GEOMETRY field)
  
  The problem is that the GEOMETRY field doesn't have and default value
  that is usually set by Field::reset method, if the field can't be NULL.
  So if the loaded file doesn't have all the data it can't be loaded.
  
  I propose to check if the 'reset' operation is possible for the field
  and return an error if it's not.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/14430

ChangeSet@1.2574, 2006-10-26 19:25:45+05:00, holyfoot@mysql.com +4 -0
  bug #22372 (LOAD DATA crashes the table with the GEOMETRY field)
  
  The problem is that the GEOMETRY field doesn't have and default value
  that is usually set by Field::reset method, if the field can't be NULL.
  So if the loaded file doesn't have all the data it can't be loaded.
  
  I propose to check if the 'reset' operation is possible for the field
  and return an error if it's not.

The patch "bk commit into 4.1 tree (holyfoot:1.2574) BUG#22372" is ok.

One thing:

I'd still recommend to extend tests to load a file with several lines,
with a bad line in the middle.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/16352

ChangeSet@1.2557, 2006-12-02 19:22:15+04:00, holyfoot@mysql.com +5 -0
  bug #22372 (LOAD DATA crashes the table with the geometry field)
   The problem is that the GEOMETRY NOT NULL can't automatically
  set any value as a default one. Now we always complete LOAD DATA command,
  even if we don't have all the data in file. That doesn't work for the
  GEOMETRY field. Setting the 'default' value for the field  ordinarily is 
  done by Field::reset method. Now it returns an error if Field can't
  be reset.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/16353

ChangeSet@1.2558, 2006-12-02 19:23:56+04:00, holyfoot@mysql.com +1 -0
  bug #22372 (LOAD DATA on GEOMETRY crashes server)
  Required bad_gis_data.dat added to be used in test

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/16531

ChangeSet@1.2563, 2006-12-06 21:45:57+04:00, holyfoot@mysql.com +5 -0
  bug #22372 (LOAD DATA crashes the table with the geometry field)
  The problem is that the GEOMETRY NOT NULL can't automatically set
  any value as a default one. We always tried to complete LOAD DATA
  command even if there's not enough data in file. That doesn't work
  for GEOMETRY NOT NULL. Now Field_*::reset() returns an error sign
  and it's checked in mysql_load()

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/16532

ChangeSet@1.2564, 2006-12-06 21:47:29+04:00, holyfoot@mysql.com +1 -0
  bug #22372
  datafile added to be used in gis.test

Fixed in 4.1.23, merged to 5.0.32, 5.1.15-beta

Noted in 4.1.23, 5.0.32, 5.1.15 changelogs.

A server crash occurred when using LOAD DATA to load a table
containing a NOT NULL spatial column, when the statement did not load
the spatial column. Now a NULL supplied to NOT NULL column error
occurs.