#1416 - Cannot get geometry object from data you send to the GEOMETRY field

Hello experts.
I m trying to install and configure php mysql and apache on a new 64bit machine with Windows 10.
I  have installed all programs successfully.
I have a problem uploading my backup database from my old machine.
The old mysql version was 5.0.8
the new 5.0.11

The error i get is :
#1416 - Cannot get geometry object from data you send to the GEOMETRY field

CREATE TABLE IF NOT EXISTS `regions` (
  `CategoryID` int(10) NOT NULL AUTO_INCREMENT,
  `uuid` varchar(55) DEFAULT NULL,
  `CountryCode` char(2) DEFAULT NULL,
  `SubCategoryOf` int(11) DEFAULT NULL,
  `OrderValue` int(10) DEFAULT '1',
  `ShowSubCats` tinyint(1) NOT NULL DEFAULT '1',
  `CategoryActive` tinyint(3) unsigned DEFAULT NULL,
  `CategoryDateIn` datetime DEFAULT NULL,
  `lat` float(10,6) DEFAULT NULL,
  `lng` float(10,6) DEFAULT NULL,
  `location` point DEFAULT NULL,
  `postalcode` char(5) DEFAULT NULL,
  `oldid` int(11) NOT NULL,
  `zoom` tinyint(4) DEFAULT NULL,
  `css` text NOT NULL,
  PRIMARY KEY (`CategoryID`),
  UNIQUE KEY `uuid` (`uuid`),
  KEY `CountryCode` (`CountryCode`,`SubCategoryOf`),
  KEY `SubCategoryOf` (`SubCategoryOf`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC AUTO_INCREMENT=14379 ;

Open in new window

sample code:
INSERT INTO `regions` (`CategoryID`, `uuid`, `CountryCode`, `SubCategoryOf`, `OrderValue`, `ShowSubCats`, `CategoryActive`, `CategoryDateIn`, `lat`, `lng`, `location`, `postalcode`, `oldid`, `zoom`, `css`) VALUES
(1, 'B821EA6D-90F5-80DD-4B42DC35FC0956E0', 'GR', NULL, 1, 1, 1, '2013-03-07 18:16:28', 37.458130, 22.253296, '', NULL, 105050, 8, 'test');

Open in new window


Any help?
LVL 2
PanosAsked:
Who is Participating?
 
Steve BinkCommented:
I miscounted your INSERT values...  you are not inserting a NULL, you are inserting a blank string.  Given the information I've found so far, I think that falls under the same restriction.  It looks like MySQL enforces data validity for GEOMETRY types.  This doc page implies GEOMETRY columns only accept spatial data.
0
 
Steve BinkCommented:
I have not been able to find information in the docs explicitly stating this, but I'm not sure GEOMETRY data types can have NULL values.  I did find this question quoting a line from the MySQL manual, but I've been unable to locate the actual statement.

The docs DO say that GEOMETRY type data is implemented as with the OpenGIS model, understands WKT and WKB formats, and cannot have default values.  All of the examples I've found in my research include a NOT NULL in the column definition.
0
 
PanosAuthor Commented:
Thank you for your help.
I did delete the location column from my old database to go on.
I could not find a way to set a default value in my old table and after that export and inport in my new database.
0
 
Steve BinkCommented:
For anyone else coming back here, some quick testing verified the blank string, and demonstrates the validity of NULL:
mysql> select @@version;
+-------------------+
| @@version         |
+-------------------+
| 5.7.12-0ubuntu1.1 |
+-------------------+
1 row in set (0.00 sec)

mysql> create table geo (id int auto_increment primary key, geop point);
Query OK, 0 rows affected (0.01 sec)

mysql> insert into geo (geop) values (null);
Query OK, 1 row affected (0.01 sec)

mysql> select * from geo;
+----+------+
| id | geop |
+----+------+
|  1 | NULL |
+----+------+
1 row in set (0.00 sec)

mysql> insert into geo (geop) values ('');
ERROR 1416 (22003): Cannot get geometry object from data you send to the GEOMETRY field

Open in new window


So there you have it: GEOMETRY types (or..POINT types, at least) can receive NULL values, but blank strings are a no-no.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.