debian x64

I am getting

| polygon       | geometry     | NO   |     | NULL    |       |

no matter how many times I try.

Are you just running describe geozone from MySQL client connection over and over again? Or are you reconnecting everytime? Or do you use something other than MySQL client? Could you please attach your cnf file(s) and specify which binaries and which Debian you use? Is there anything suspicious in the error log?

i used both php script and console
now i connected using linux console
MariaDB [gps_EURA]> describe geozone;
i got 6 times geometry then polygon
same connection no reconnection
debian 7.9
mariadb-server-10.0 10.0.21+maria-1~wheezy
no errors my.cnf

I ran DESCRIBE 10K times, but still haven't got even a single 'polygon' as a type.

Could you please paste the entire output, from when you open a client session, with full DESCRIBE result (all columns), all attempts up to and including the one that returns 'polygon', and further until it switches back to 'geometry'?

Are you sure there is no other connection that alters the table in parallel?

Is it a newly created table, or did it come from a previous MariaDB version?

Is it reproducible after you re-create the table, or on another table with a different name?

Could you maybe provide the frm file on which you get this issue?

Thanks.

no other connection
yes it was from previous version, maybe from mysql
it seems fine when i recreated the table geozone.ibd geozone.frm

I've removed my previous comment – sorry, did not notice at first that you have already attached files. Are these the old ones?

yes

what feedback?

dvorak, you received a notification because I removed the flag "needs_feedback" which was left there by mistake (I know, it's not obvious from the generated notifications).
But while we are on it, there is something else you could help with: unfortunately since the table is InnoDB, it cannot be moved from one database to another just like that, it still needs the matching system tablespace, otherwise the server can't pick up the table.
Is it from your production server or a test instance? If it doesn't contain strictly confidential data, could you maybe upload the whole datadir (archived) to our ftp.askmonty.org/private?

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?

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.

it was 5.6 dont' know which one

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