Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have this trigger:

DELIMITER $$
CREATE TRIGGER geometrytrigger BEFORE INSERT ON users
FOR EACH ROW BEGIN
  SET NEW.user_geometry=GeomFromText(NEW.user_geometry);
END$$
DELIMITER ;

when I insert a row with this query:

INSERT INTO `users` (`id`, `name`, `email`, `password`, `user_geometry`) VALUES (NULL, 'arash', 'arash@host.com', 'sssssss', 'POINT(-0.23 169.93)');

this error has occurred:

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

edit:

show create table users;

result:

Table   Create Table    
users   CREATE TABLE `users` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `name` varchar(127) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
 `email` varchar(127) CHARACTER SET ascii COLLATE ascii_bin DEFAULT NULL,
 `password` binary(40) NOT NULL,
 `user_geometry` geometry DEFAULT NULL,
 PRIMARY KEY (`id`),
 UNIQUE KEY `name` (`name`),
 UNIQUE KEY `email` (`email`),
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

Also I use Percona 5.6.12 or MySQL 5.6.12

share|improve this question
add comment

1 Answer

The error isn't coming from the trigger. You could confirm this by dropping the trigger and you will see the same error.

ERROR 1416 (22003): Cannot get geometry object from data you send to the GEOMETRY field

You didn't mention it in the question, but it looks like your user_geometry column is defined with a GEOMETRY data type. To use a GEOMETRY data type, you have to insert a GEOMETRY object. You can't coerce it later with a trigger because it's validated very early, before the BEFORE UPDATE trigger.

Remove the quotes and add a comma and you have a query that would work and not need the trigger:

INSERT INTO `users` (`id`, `name`, `email`, `password`, `user_geometry`) 
VALUES (NULL, 'arash', 'arash@host.com', 'sssssss', POINT(-0.23,169.93));

The POINT() function takes 2 arguments and returns a geometry object.

share|improve this answer
    
I can remove quotes, because the insert statement produced by ORM , and ORM put quotes around strings, this ORM hasn't support for MySQL expressions,SO I can only send string value in query. because of this situation write this trigger. any other approach? –  Arash Mousavi Jul 15 '13 at 11:12
    
Ah. Yes, there are some possible workarounds. Please edit your question to add the SHOW CREATE TABLE output for the table, so that I can see column types, nullability, and indexes, and I'll suggest a solution. Also please provide your exact MySQL version. –  Michael - sqlbot Jul 15 '13 at 12:10
    
I edited my question. –  Arash Mousavi Jul 15 '13 at 16:30
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.