Solved

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

Posted on 2016-07-18
4
345 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
  • 3
4 Comments
 
LVL 50

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 50

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 50

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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Connect MySql database to wordpress site 3 67
MySQL left join performance 4 38
Insert data into database 2 44
How do uses indexes to maximize MySQL Searches 14 33
Fore-Foreword Today (2016) Maxmind has a new approach to the distribution of its data sets.  This article may be obsolete.  Instead of using the examples here, have a look at the MaxMind API (https://www.maxmind.com/en/geolite2-developer-package). …
As a database administrator, you may need to audit your table(s) to determine whether the data types are optimal for your real-world data needs.  This Article is intended to be a resource for such a task. Preface The other day, I was involved …
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

810 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