MySQL FDW Issues with Geometry Type

[ryckingnoaa]

The following bug has been logged on the website:

Bug reference: 13616 Logged by: Ryan King Email address: ryan(dot)king(at)noaa(dot)gov PostgreSQL version: 9.4.4 Operating system: Red Hat 4.4.7-11), 64-bit Description:

Not all the records return when selecting geom columns: select * from public.public_zones —only 54 records return select polygon_column from public.public_zones —only 54 records return select OGR_FID from public.public_zones —all 3000 records return select name from public.public_zones —all 3000 records return

These are the steps I took to create the FDW:

MySQL:

CREATE TABLE public_zones (

OGR_FID int(11) NOT NULL AUTO_INCREMENT, polygon_column geometry NOT NULL, name varchar(254) DEFAULT NULL, UNIQUE KEY OGR_FID (OGR_FID), SPATIAL KEY polygon_column (polygon_column)

) ENGINE=MyISAM AUTO_INCREMENT=18519 DEFAULT CHARSET=latin1;

CREATE VIEW public_zones_view AS select public_zones.OGR_FID AS

OGR_FID,st_astext(public_zones.polygon_column) AS polygon_column,

public_zones.name AS name from public_zones;

PostgreSQL:

CREATE EXTENSION mysql_fdw;

CREATE FOREIGN DATA WRAPPER mysql_fdw_db1 HANDLER mysql_fdw_handler VALIDATOR mysql_fdw_validator;

CREATE SERVER mysql_svr_db1 FOREIGN DATA WRAPPER mysql_fdw_db1 OPTIONS (host 'IPADDRESS', port '3306');

CREATE USER MAPPING FOR username SERVER mysql_svr_db1 OPTIONS (username 'username', password 'password');

CREATE FOREIGN TABLE table_name (

OGR_FID SERIAL NOT NULL, polygon_column public.geometry NOT NULL, name varchar(254) DEFAULT NULL,

SERVER mysql_svr_db1 OPTIONS (dbname 'mysqldbname', table_name 'table_name_view');

I read a post that there was a bug in PostGIS 2.1.0 preventing foreign tables from being output in geometry_columns and geography_columns views and was supposed to be fixed in PostGIS 2.1.1. We are on 2.1.8.

[robe]

1) Yes that issue was fixed. In fact I have a database with a bunch of foreign tables (using ogr_fdw driver) which I get listed when I do:

SELECT * FROM geometry_columns;

The geometry_columns view is just a table that lists all database columns of type geometry. Not sure what you think it is or if you have it confused with something else.

2) The fact you are not seeing some records coming thru from your mysql_fdw table sounds like it might be an issue with the mysql_fdw driver. I don't use that driver so don't know. You might want to report your issue to them and maybe turn on logging on your database if you don't have it already to see if there is something spitting out.

All my foreign tables where I output PostGIS use the ogr_fdw driver ​https://github.com/pramsey/pgsql-ogr-fdw

and all records that are supposed to be there show up when I query them. This is from a variety of different spatial data sources.