Solved

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

Posted on 2016-07-18
4
748 Views
Last Modified: 2016-07-19
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?
0
Comment
Question by:Panos
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
4 Comments
 
LVL 51

Expert Comment

by:Steve Bink
ID: 41718173
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
 
LVL 51

Accepted Solution

by:
Steve Bink earned 500 total points
ID: 41718174
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
 
LVL 2

Author Comment

by:Panos
ID: 41719403
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
 
LVL 51

Expert Comment

by:Steve Bink
ID: 41719430
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

Featured Post

Webinar: Choosing a MySQL HA Solution

Join Percona’s Principal Technical Services Engineer, Marcos Albe as he presents Choosing a MySQL High Availability Solution on Thursday, June 29, 2017 at 10:00 am PDT / 2:00 pm EDT (UTC-7).

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Foreword This article was written many years ago, in the days when PHP supported the MySQL extension (http://php.net/manual/en/function.mysql-connect.php).  Today (http://php.net/manual/en/migration70.removed-exts-sapis.php) you would not use MySQL…
Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…

690 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question