I think that I may have found the problem. It looks like the mysql_fdw uses the following query to gather information about the foreign schema:
SELECT t.TABLE_NAME, c.COLUMN_NAME, CASE WHEN c.DATA_TYPE = 'enum' THEN LOWER(CONCAT(c.COLUMN_NAME, '_t')) WHEN c.DATA_TYPE = 'tinyint' THEN 'smallint' WHEN c.DATA_TYPE = 'mediumint' THEN 'integer' WHEN c.DATA_TYPE = 'tinyint unsigned' THEN 'smallint' WHEN c.DATA_TYPE = 'smallint unsigned' THEN 'integer' WHEN c.DATA_TYPE = 'mediumint unsigned' THEN 'integer' WHEN c.DATA_TYPE = 'int unsigned' THEN 'bigint' WHEN c.DATA_TYPE = 'bigint unsigned' THEN 'numeric(20)' WHEN c.DATA_TYPE = 'double' THEN 'double precision' WHEN c.DATA_TYPE = 'float' THEN 'real' WHEN c.DATA_TYPE = 'datetime' THEN 'timestamp' WHEN c.DATA_TYPE = 'longtext' THEN 'text' WHEN c.DATA_TYPE = 'mediumtext' THEN 'text' WHEN c.DATA_TYPE = 'blob' THEN 'bytea' ELSE c.DATA_TYPE END, c.COLUMN_TYPE, IF(c.IS_NULLABLE = 'NO', 't', 'f'), c.COLUMN_DEFAULT FROM information_schema.TABLES AS t JOIN information_schema.COLUMNS AS c ON t.TABLE_CATALOG = c.TABLE_CATALOG AND t.TABLE_SCHEMA = c.TABLE_SCHEMA AND t.TABLE_NAME = c.TABLE_NAME WHERE t.TABLE_SCHEMA = '%s' When I poked around inside of MySQL that t.TABLE_CATALOG and c.TABLE_CATALOG values are NULL. Trying to compare NULLs in MySQL using an equals sign (=) results in a "FALSE" and thus the "JOIN" does not provide an actual linkage. So, the query returns 0 tables and 0 columns to be imported. Deven On Fri, Jan 8, 2016 at 11:50 PM, Deven Phillips <deven.phill...@gmail.com> wrote: > Additional details. The MySQL server I am targeting is running > version 5.1.73. Perhaps it's too old of a version to support foreign schema > import? > > Deven > > On Fri, Jan 8, 2016 at 11:45 PM, Deven Phillips <deven.phill...@gmail.com> > wrote: > >> I DID get a foreign table to work using the following: >> >> CREATE FOREIGN TABLE customer ( >> id BIGINT, >> name VARCHAR(150), >> parent_id BIGINT, >> oracle_id BIGINT, >> last_updated_time TIMESTAMP, >> created_time TIMESTAMP) SERVER mysql OPTIONS (dbname 'mydb', table_name >> 'customer'); >> >> And I was subsequently able to query that table from PostgreSQL.. >> >> I tried to add the "OPTIONS" to the IMPORT FOREIGN SCHEMA and got an >> error that "dbname" is not a valid parameter. >> >> Thanks, >> >> Deven >> >> On Fri, Jan 8, 2016 at 11:38 PM, Deven Phillips <deven.phill...@gmail.com >> > wrote: >> >>> Apparently not, though I have done so in the past on PostgreSQL 9.4. It >>> appears to be related to the "schema" with which the foreign table is >>> associated: >>> >>> mydb=# CREATE FOREIGN TABLE customer ( >>> id BIGINT, >>> name VARCHAR(150), >>> parent_id BIGINT, >>> oracle_id BIGINT, >>> last_updated_time TIMESTAMP, >>> created_time TIMESTAMP) SERVER mysql; >>> CREATE FOREIGN TABLE >>> mydb=# SELECT * FROM customer; >>> ERROR: failed to prepare the MySQL query: >>> Table 'public.customer' doesn't exist >>> >>> Any suggestions would be greatly appreciated! >>> >>> Deven >>> >>> >>> On Fri, Jan 8, 2016 at 10:26 AM, Adrian Klaver < >>> adrian.kla...@aklaver.com> wrote: >>> >>>> On 01/08/2016 07:04 AM, Deven Phillips wrote: >>>> >>>>> Hi all, >>>>> >>>>> I installed the newly released PostgreSQL 9.5 this morning and >>>>> compiled the latest mysql_fdw extension from EnterpriseDB. I was able >>>>> to >>>>> create the SERVER and USER MAPPING, but I cannot seem to get IMPORT >>>>> FOREIGN SCHEMA to do anything. The command executes without error, but >>>>> none of the table schemas are imported from the MySQL DB. Does anyone >>>>> have any advice, links, documentation which might be of help? >>>>> >>>> >>>> Can you CREATE FOREIGN TABLE and use it? >>>> >>>> >>>>> Thanks in advance! >>>>> >>>>> Deven >>>>> >>>> >>>> >>>> -- >>>> Adrian Klaver >>>> adrian.kla...@aklaver.com >>>> >>> >>> >> >
