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.
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.
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.
So, this PR (a very simple one) replaces "=" in the join logic with the null-safe equals operator "<=>" so that comparison of NULL will yield a successful join.
Hi all,
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.
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.
So, this PR (a very simple one) replaces "=" in the join logic with the null-safe equals operator "<=>" so that comparison of NULL will yield a successful join.