0

When I do import foreign schema constructor from server mysql_svr into mysql_fdw; I get:

ERROR:  type "set" does not exist
LINE 6:   type set NOT NULL,
               ^
QUERY:  CREATE FOREIGN TABLE search_requests (
  id int NOT NULL,
  ctime timestamp NOT NULL,
  site_id int NOT NULL,
  query_id int NOT NULL,
  type set NOT NULL,
  page smallint NOT NULL
) SERVER mysql_svr OPTIONS (dbname 'constructor', table_name 'search_requests');

CONTEXT:  importing foreign table "search_requests"

The source table is:

CREATE TABLE `search_requests` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `ctime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `site_id` int(11) NOT NULL,
  `query_id` int(10) unsigned NOT NULL,
  `type` set('content','catalog','gallery') NOT NULL,
  `page` tinyint(3) unsigned NOT NULL,
  PRIMARY KEY (`id`),
  KEY `site` (`site_id`)
) ENGINE=InnoDB AUTO_INCREMENT=391 DEFAULT CHARSET=utf8

May I get SQL generated by IMPORT FOREIGN SCHEMA make all fixes and run it manually?

pozs
  • 34,608
  • 5
  • 57
  • 63
Eugen Konkov
  • 22,193
  • 17
  • 108
  • 158

1 Answers1

2

The API doesn't mention anything like that (at least, on SQL level), but you can import the schema without explicit tables. You could try:

IMPORT FOREIGN SCHEMA constructor
  EXCEPT (search_requests)
  FROM SERVER mysql_svr
  INTO mysql_fdw

After the import, you could try to write a mapping for each of the excluded table, f.ex.:

CREATE FOREIGN TABLE search_requests (
  id int NOT NULL,
  ctime timestamp NOT NULL,
  site_id int NOT NULL,
  query_id int NOT NULL,
  type text NOT NULL,
  page smallint NOT NULL
) SERVER mysql_svr OPTIONS (dbname 'constructor', table_name 'search_requests');

Note: I'm not sure that the text type is the appropriate for MySQL's set type. set in MySQL is usually means bad normalization (like the array column types in PostgreSQL), and if you can alter the foreign table, you should do that instead.

Also, this seems to be mysql_fdw bug; if that's the case, you should report it in their bug tracking system.

pozs
  • 34,608
  • 5
  • 57
  • 63
  • mysql_fdw tryes to do `DO $$BEGIN IF NOT EXISTS (SELECT 1 FROM pg_catalog.pg_type WHERE typname = 'type_t') THEN CREATE TYPE type_t AS enum('content','catalog','gallery'); END IF; END$$;` but so it should use `type_t` instead of `set`. Yes, it seems like a bug, but I not sure. – Eugen Konkov Apr 22 '16 at 14:42
  • @EugenKonkov then `type_t` is just the *enum part* of the type. PostgreSQL doesn't have a `set` type at all, the closest would be `type_t[]` (an array of the `type_t` enum, which could allow duplicates, unlike `set`), but I'm not sure the foreign wrapper can handle such mappings. My best bet stays `text` (even though I cannot test myself); with that, you could read sets as comma separated values. I'm not sure if `set` is supported at all with `mysql_fdw` (as it has no direct pair in PostgreSQL). – pozs Apr 22 '16 at 14:57