Обсуждение: PostgreSQL 9.5, mysql_fdw, and IMPORT FOREIGN SCHEMA question

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.klaver@aklaver.com

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.klaver@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.klaver@aklaver.com

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.phillips@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.klaver@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.klaver@aklaver.com

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.phillips@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.phillips@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.klaver@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.klaver@aklaver.com

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.phillips@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.phillips@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.phillips@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.klaver@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.klaver@aklaver.com