Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

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

Posted on 2016-07-18
4
Medium Priority
?
1,160 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 2000 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

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

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

By, Vadim Tkachenko. In this article we’ll look at ClickHouse on its one year anniversary.
In this article, we’ll look at how to deploy ProxySQL.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

636 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