Details
Major Description
server returns random column type
describe geozone;
|
polygon | polygon | NO | | NULL | | |
describe geozone;
|
polygon | geometry | NO | | NULL | | |
CREATE TABLE `geozone` ( |
`id` int(11) NOT NULL, |
`name` varchar(255) NOT NULL, |
`user_id` int(11) NOT NULL COMMENT 'creator', |
`active` tinyint(4) DEFAULT '1', |
`ride_end` tinyint(1) DEFAULT NULL COMMENT 'Jestli se v teto oblasti ma ukoncit jizda', |
`min_lat` double DEFAULT NULL, |
`min_lon` double DEFAULT NULL, |
`max_lat` double DEFAULT NULL, |
`max_lon` double DEFAULT NULL, |
`polygon` geometry NOT NULL, |
`polygonBackup` polygon DEFAULT NULL, |
`ride_type` tinyint(1) NOT NULL DEFAULT '0' COMMENT '1 soukroma/2 sluzebni' |
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='geozone'; |
Attachments
Issue Links
- relates to
-
MDEV-12078 Using spatial index changes type from point to geometry
-
- Closed
-
Activity
Do you happen to remember which version of MySQL it was created with?
I hoped to figure it out from the frm file, but it has 10.0.21 already, apparently the table was altered later and the version number was updated.
BUG with describe table for geometry fields exists in MariaDB 5.5.0 and 10.1
Steps to reproduce this bug:
– 1. create test table with geometry field:
CREATE TABLE `test` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`field1` varchar(100) NOT NULL DEFAULT '',
`field2` varchar(100) NOT NULL DEFAULT '',
`the_geom` linestring DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM
– 2. insert some date:
insert into `test`(`id`,`the_geom`) values(1,NULL);
insert into `test`(`id`,`the_geom`) values(2,GeomFromText('LINESTRING(18.455864 54.214971,18.455925 54.214957,18.456145 54.214956)'));
– test 1 - simple query:
select t.id, AsWKT(t.the_geom) as the_geom
from test t
where t.the_geom is not null
and GeometryType(t.the_geom) = 'LINESTRING'
– Result: OK - describe `test` returns: the_geom | linestring
– test 2 - add another field to where:
select t.id, AsWKT(t.the_geom) as the_geom
from test t
where t.the_geom is not null
and GeometryType(t.the_geom) = 'LINESTRING'
and t.id > 0
– Result: BUG - describe `test` returns: the_geom | geometry
– but:
SELECT DATA_TYPE
FROM `information_schema`.`COLUMNS`
WHERE `TABLE_SCHEMA`='test' and `TABLE_NAME`='test' and`COLUMN_NAME`='the_geom'
– returns: linestring
– repair table fix field type:
REPAIR TABLE `test`;
– now describe `test` returns: the_geom | linestring
Question is: Why select query can brake table structure returned by describe or show fields from?
The bug was fixed in 10.0.32 and corresponding higher versions by this commit:
commit a06f2f430f6cfd55929c3734b24e04fa70803156
|
Author: Alexey Botchkov
|
Date: Tue Mar 14 16:35:39 2017 +0400
|
|
|
MDEV-12078 Using spatial index changes type from point to geometry
|
this would be a problem, it's production server, gps monitoring, it has 500GB
but i think it's problem with mysql
if you create a table in mysql, put some polygon and switch to mariadb, it works fine?