Description:
This bug was reported as a MariaDB bug at first in:
https://mariadb.atlassian.net/browse/MDEV-4922
There turns out to be a valgrind issue (Invalid read), which probably causes the problem. The valgrind warning is reproducible on MariaDB 5.3, 5.5 and on MySQL 5.5, 5.6; but not on MariaDB 10.0.4.
The rest is apparently the matter of luck, depending on what the invalid read has read.
A stored procedure, SP(data text, out g geometry), does mess up the geomtery content, when used inside a trigger.
Try the attached test case with mysql / mariadb.
Note that the column data get messed up, as illustrated with the attach image (see MDEV-4922, test case runned a couple of times)
How to repeat:
####
# Testcase for geometry bug
#
# Procedure: TestDataGeometry
# - simple snippet from more complex procedure to show bug
#
# Table: testg
# - simple table to show trigger problem
#
# Trigger: testg_before_insert
# - to demonstrate the bug
#
# NOTE! The gemotry column is filled with junk, this is probably at least not good...
USE test;
DELIMITER $$
DROP PROCEDURE IF EXISTS TestDataGeometry$$
CREATE PROCEDURE TestDataGeometry(_data text, out g_ geometrycollection) COMMENT 'Sets Geometry from data string...'
BEGIN
SET g_=GeomFromText(concat('POLYGON((',_data,'))'));
IF g_ is null THEN SET g_=GeomFromText(concat('LINESTRING(',_data,')')); END IF;
IF g_ is null THEN SET g_=GeomFromText(concat('MULTILINESTRING((',_data,'))')); END IF;
IF g_ is null THEN SET g_=GeomFromText(concat('MULTIPOLYGON(((',_data,')))')); END IF;
END;$$
DELIMITER ;
#- DROP TABLE IF EXISTS testg;
CREATE TABLE IF NOT EXISTS testg (
id int not null auto_increment,
data text,
g geometry,
PRIMARY KEY (id)
) ENGINE=MyISAM COMMENT='Test Case Trigger/Geometry function';
##
# Trigger: testg_before_insert
DELIMITER $$
DROP TRIGGER IF EXISTS testg_before_insert$$
CREATE TRIGGER testg_before_insert BEFORE INSERT ON testg FOR EACH ROW BEGIN
# Below line works on all mysql version I tested, but not in mariadb
CALL TestDataGeometry(NEW.data, NEW.g);
# This is a workaround, that worked both mysql and mariabd?
# SET @g=null;
# CALL TestDataGeometry(NEW.data, @g);
# SET NEW.g=@g;
END;$$
DELIMITER ;
##
# Test by inserting some data
INSERT INTO testg (data) VALUES ('11.960453 57.565838,11.925262 57.567955,11.921486 57.555248,11.960882 57.554189,11.960453 57.565838');
##
# Look! AsText(g) is sometimes null??
SELECT version();
SELECT id,data,AsText(g) FROM testg;
Suggested fix:
More info
https://mariadb.atlassian.net/browse/MDEV-4922
