Re: [GENERAL] PostgreSQL 9.5, mysql_fdw, and IMPORT FOREIGN SCHEMA question

[Adrian Klaver]

On 01/08/2016 08:45 PM, Deven Phillips 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.


Got to thinking. What happens if you do CREATE SERVER and pass dbname as 
an OPTIONS at that point, then do IMPORT FOREIGN SCHEMA using the server?




Thanks,

Deven




--
Adrian Klaver
adrian.kla...@aklaver.com


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] PostgreSQL 9.5, mysql_fdw, and IMPORT FOREIGN SCHEMA question

[Adrian Klaver]

On 01/08/2016 08:45 PM, Deven Phillips 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..


Yes, dbname is a required option to FOREIGN TABLE:

https://github.com/EnterpriseDB/mysql_fdw
"The following parameters can be set on a MySQL foreign table object:

dbname: Name of the MySQL database to query. This is a mandatory option."



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
mailto: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
mailto: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 






--
Adrian Klaver
adrian.kla...@aklaver.com


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] PostgreSQL 9.5, mysql_fdw, and IMPORT FOREIGN SCHEMA question

[Deven Phillips]

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 
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 
> 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 > > 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
>


>>>
>>
>

Re: [GENERAL] PostgreSQL 9.5, mysql_fdw, and IMPORT FOREIGN SCHEMA question

[Deven Phillips]

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 
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 
> 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 > > 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

>>>
>>>
>>
>

Re: [GENERAL] PostgreSQL 9.5, mysql_fdw, and IMPORT FOREIGN SCHEMA question

[Deven Phillips]

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 
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 
> 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 > > 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
>>>
>>
>>
>

Re: [GENERAL] PostgreSQL 9.5, mysql_fdw, and IMPORT FOREIGN SCHEMA question

[Deven Phillips]

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 
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 
> 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
>>
>
>

Re: [GENERAL] PostgreSQL 9.5, mysql_fdw, and IMPORT FOREIGN SCHEMA question

[Deven Phillips]

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 
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
>

Re: [GENERAL] PostgreSQL 9.5, mysql_fdw, and IMPORT FOREIGN SCHEMA question

[Adrian Klaver]

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


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] PostgreSQL 9.5, mysql_fdw, and IMPORT FOREIGN SCHEMA question

[Deven Phillips]

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?

Thanks in advance!

Deven