OK, that did it! I submitted 2 PRs to the EnterpriseDB/mysql_fdw GitHub project which should resolve all outstanding issues for me.
https://github.com/EnterpriseDB/mysql_fdw/pull/81 https://github.com/EnterpriseDB/mysql_fdw/pull/82 Isn't it great when Open Source works like it's supposed to!!! Deven On Sat, Jan 9, 2016 at 12:06 AM, Deven Phillips <deven.phill...@gmail.com> wrote: > 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 >>>>> >>>> >>>> >>> >> >
