Bug #21790 geometry field with spatial index returns ERROR 1105 (HY000): Unknown error
Submitted: 23 Aug 2006 0:52 Modified: 28 Nov 2006 20:40
Reporter: Peter Brodersen (Candidate Quality Contributor) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Data Types Severity:S3 (Non-critical)
Version:5.0.25-BK, 5.0.24-Debian_0.dotdeb.0-log OS:Linux (Linux 2.4 debian stable)
Assigned to: Alexey Botchkov CPU Architecture:Any

[23 Aug 2006 0:52] Peter Brodersen
Description:
A geometry field with a spatial index returns ERROR 1105 (HY000): Unknown error when inserting a default value or other invalid value instead of a more helpful error message.

How to repeat:
mysql> CREATE TABLE geom (foo GEOMETRY NOT NULL, SPATIAL INDEX(foo) );
Query OK, 0 rows affected (0.02 sec)

mysql> INSERT INTO geom (foo) VALUES (NULL);
ERROR 1048 (23000): Column 'foo' cannot be null

mysql> INSERT INTO geom (foo) VALUES (1);
ERROR 1416 (22003): Cannot get geometry object from data you send to the GEOMETRY field

mysql> INSERT INTO geom (foo) VALUES ('x');
ERROR 1416 (22003): Cannot get geometry object from data you send to the GEOMETRY field

mysql> INSERT INTO geom () VALUES ();
ERROR 1105 (HY000): Unknown error

mysql> INSERT INTO geom (foo) VALUES ('');
ERROR 1105 (HY000): Unknown error

Suggested fix:
All insert statements are correctly dismissed as errors. 

The first three error messages are fine and make sense as we have the NOT NULL constraint (as required by the spatial index) and we can't insert values directly.

But the last two error messages aren't quite clear ("Unknown error") and should be changed.
[28 Aug 2006 9:45] Valeriy Kravchuk
Thank you for a bug report. Verified just as described with 5.0.25-BK on Linux:

openxs@suse:~/dbs/5.0> bin/mysql -uroot test
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mys
ql.sock' (2)
openxs@suse:~/dbs/5.0> bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 5.0.25-debug

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

mysql> CREATE TABLE geom (foo GEOMETRY NOT NULL, SPATIAL INDEX(foo) );
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO geom (foo) VALUES (NULL);
ERROR 1048 (23000): Column 'foo' cannot be null
mysql> INSERT INTO geom (foo) VALUES (1);
ERROR 1416 (22003): Cannot get geometry object from data you send to the GEOMETRY field
mysql> INSERT INTO geom (foo) VALUES ('');
ERROR 1105 (HY000): Unknown error

This "Unknown" error message is a bug.
[1 Oct 2006 11:09] Bugs System
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/12916

ChangeSet@1.2293, 2006-10-01 16:36:26+05:00, holyfoot@mysql.com +3 -0
  bug #21790 (UNKNOWN ERROR message in geometry)
  
  We issued UNKNOWN ERROR initially in this place and forgot to
  fix it when we implemented informative error message for this
[25 Oct 2006 11:31] Alexander Barkov
The patch "bk commit into 5.0 tree (holyfoot:1.2293) BUG#21790" is ok to push.
[27 Nov 2006 17:14] Georgi Kodinov
Pushed in 5.0.32/5.1.14-beta
[28 Nov 2006 20:40] Paul DuBois
Noted in 5.0.32, 5.1.14 changelogs.

Inserting a default or invalid value into a spatial column could fail
with Unknown error rather than a more appropriate error.