Re: PostgreSQL 9.5, mysql_fdw, and IMPORT FOREIGN SCHEMA question

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(dot)phillips(at)gmail(dot)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(dot)phillips(at)gmail(dot)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(dot)phillips(at)gmail(dot)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(dot)phillips(at)gmail(dot)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(dot)klaver(at)aklaver(dot)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(dot)klaver(at)aklaver(dot)com
>>>>>
>>>>
>>>>
>>>
>>
>