BUG #13616: MySQL FDW Geometry Not Returning All Records

From: ryan(dot)king(at)noaa(dot)gov
To: pgsql-bugs(at)postgresql(dot)org
Subject: BUG #13616: MySQL FDW Geometry Not Returning All Records
Date: 2015-09-11 20:07:53
Message-ID: 20150911200753.363.73990@wrigleys.postgresql.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

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.

Browse pgsql-bugs by date

  From Date Subject
Next Message Bruce Momjian 2015-09-11 21:19:20 Re: BUG #13612: postgresql94-setup initdb in kickstart fails
Previous Message David G. Johnston 2015-09-11 18:31:55 Re: BUG #13615: Bug in json_populate_record().