From: | Deven Phillips <deven(dot)phillips(at)gmail(dot)com> |
---|---|
To: | Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com> |
Cc: | "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: PostgreSQL 9.5, mysql_fdw, and IMPORT FOREIGN SCHEMA question |
Date: | 2016-01-09 05:06:34 |
Message-ID: | CAJw+4NBAD6eD7_ecEhnvTQZKUTRWEbbHRnDVAjSFfYJ4=o==Kw@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
>>>>
>>>
>>>
>>
>
From | Date | Subject | |
---|---|---|---|
Next Message | Deven Phillips | 2016-01-09 05:28:01 | Re: PostgreSQL 9.5, mysql_fdw, and IMPORT FOREIGN SCHEMA question |
Previous Message | Deven Phillips | 2016-01-09 04:50:55 | Re: PostgreSQL 9.5, mysql_fdw, and IMPORT FOREIGN SCHEMA question |