Bug with 'enum' datatype

[brsysadmin]

Hello,

We think to have a bug with the mysql_fdw connector. In the MySQL database, we create a database and a table with an enum column :

mysql> create database mydatabase;
Query OK, 1 row affected (0.00 sec)

mysql> use mydatabase;
Database changed

mysql> create table mytable1 ( MyID int(10) unsigned NOT NULL AUTO_INCREMENT, MyEnum enum('value1', 'value2', 'value3'), PRIMARY KEY (`MyID`)) ENGINE=InnoDB AUTO_INCREMENT=1;
Query OK, 0 rows affected (0.01 sec)

We insert a line with an authorized value into the new table :

mysql> insert into mytable1 (MyEnum) values ('value1');
Query OK, 1 row affected (0.00 sec)

mysql> select * from mytable1;
+------+--------+
| MyID | MyEnum |
+------+--------+
|    1 | value1 |
+------+--------+
1 row in set (0.00 sec)

=> it's ok

We try to insert a line with an unauthorized value into the new table :

mysql> insert into mytable1 (MyEnum) values ('value4');
ERROR 1265 (01000): Data truncated for column 'MyEnum' at row 1

mysql> select * from mytable1;
+------+--------+
| MyID | MyEnum |
+------+--------+
|    1 | value1 |
+------+--------+
1 row in set (0.00 sec)

=> MySQL refuse the line : it's ok

Now, we create a foreign table on PostGreSQL. As 'enum' datatype does not exists on PostGreSQL, we try to change the datatype for 'varchar' :

brsysadmin_dev=# CREATE FOREIGN TABLE MyForeignTable1("MyID" int, "MyEnum" varchar(255)) SERVER mysql_server OPTIONS (dbname 'mydatabase', table_name 'mytable1');                                            
CREATE FOREIGN TABLE

We try to insert a line with an unauthorized value into the foreign table :

brsysadmin_dev=# insert into MyForeignTable1 ("MyEnum") values ('value4');
INSERT 0 1

brsysadmin_dev=# select * from MyForeignTable1;
 MyID | MyEnum 
------+--------
    1 | value1
    2 | 
(2 rows)

mysql> select * from mytable1;
+------+--------+
| MyID | MyEnum |
+------+--------+
|    1 | value1 |
|    2 |        |
+------+--------+
2 rows in set (0.00 sec)

=> it's the problem : PostGreSQL accept an unauthorized value and MySQL save an empty line

If we try to insert a new line with an authorized value :

brsysadmin_dev=# insert into MyForeignTable1 ("MyEnum") values ('value3');
INSERT 0 1

brsysadmin_dev=# select * from MyForeignTable1;
 MyID | MyEnum 
------+--------
    1 | value1
    2 | 
    3 | value3
(3 rows)

mysql> select * from mytable1;
+------+--------+
| MyID | MyEnum |
+------+--------+
|    1 | value1 |
|    2 |        |
|    3 | value3 |
+------+--------+
3 rows in set (0.00 sec)

=> it's ok

Structure of the table on MySQL :

mysql> show create table mytable1;
+----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table    | Create Table                                                                                                                                                                                                          |
+----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| mytable1 | CREATE TABLE `mytable1` (
  `MyID` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `MyEnum` enum('value1','value2','value3') DEFAULT NULL,
  PRIMARY KEY (`MyID`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1 |
+----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

We create an enumerated type on PostGreSQL :

brsysadmin_dev=# CREATE TYPE "MyEnum" AS ENUM ('value1', 'value2', 'value3');
CREATE TYPE

We create a new foreign table with the type "MyEnum" :

brsysadmin_dev=# CREATE FOREIGN TABLE MyForeignTable2("MyID" int, "MyEnum" "MyEnum") SERVER mysql_server OPTIONS (dbname 'mydatabase', table_name 'mytable1');
CREATE FOREIGN TABLE

And we will try to get the content of the table :

brsysadmin_dev=# select * from MyForeignTable2;
ERROR:  cache lookup failed for type 0

=> we have a new problem

What's the datatype to use on the foreign table for mapping with the MySQL's enum ? How to prevent PostGreSQL from saving an unauthorized value ?

Thank you in advance.

[jmealo]

I encountered the following issues with ENUM:

1. Unsupported byIMPORT FOREIGN SCHEMA
2. Creating an equivalent TYPE in PostgreSQL also results in a cache lookup failed for type error (as reported)

@brsysadmin: I used TEXT columns. PostgreSQL does not enforce constraints on foreign tables so there's not a simple fix to enforce correctness when issuing INSERT or UPDATE statements against a foreign table.

[knutwannheden]

I also encountered the problem with enum types when running IMPORT FOREIGN SCHEMA. The same also applies to the MySQL set type. As a workaround I changed the columns using ALTER TABLE ... CHANGE COLUMN ... to change the type to VARCHAR prior to the import.

[surajkharage19]

Hi @knutwannheden,

Can you please confirm exactly which issue you are facing? We have already fixed the cache lookup error mentioned above. Also, if you have enum type in MySQL table then we recommend creating the same on Postgres before IMPORT FOREIGN SCHEMA command.

[knutwannheden]

I have problems importing the schema. I will make sure I am using the latest version and will try to see if I can get it to work by first manually creating the type in PostgreSQL before importing the schema. But I suspect this may not work for columns with a SET type. Can you confirm this?

[knutwannheden]

Note: A MySQL SET would best be mapped to a PostgreSQL array of enum.

[surajkharage19]

You are right. MySQL tables with SET type gives an error while IMPORT FOREIGN SCHEMA command. We need to handle those like we are handling enums. Thank you for suggestion that we should map it with Postgres array of enum. We will do more study on this and will plan to fix this.

[surajkharage19]

Hi @knutwannheden,

After further study, found that we cannot map MySQL SET to TEXT/array of enum in PostgreSQL because certain operations may not yield desired/consistent results due to the way MySQL stores the SET type.

Instead, we will skip importing the table having SET column and emit a warning when we import the foreign schema.

[aadrian]

Any news on supporting "enum" types for IMPORT FOREIGN SCHEMA ?

This this bug greatly reduces the use of this FDW :( .

[surajkharage19]

Hi @aadrian,

Can you please let us know what exact issue you are facing with enum data types? As stated above, if you want to import enum type to text in Postgres then use import_enum_as_text option with import foreign schema command otherwise make sure that enum type is already created in Postgres before importing the tables.

[aadrian]

@surajkharage19

Can you please let us know what exact issue you are facing with enum data types?

import is not working, script with import will just fail if enums are found, since enums don't seem to be converted !

otherwise make sure that enum type is already created in Postgres before importing the tables.

This is not possible, as the DB on the MySQL side is "external" and subject to any change, so it is unknown until the moment of import driven by a script.

Isn't there any possibility for the FDW to also create the according enums if they're present in the source DB?

[surajkharage19]

Hi,

import is not working, script with import will just fail if enums are found, since enums don't seem to be converted !

I have tested this test case at my end on latest HEAD and below are the results. If enum type does not exist on Postgres before importing the table then it throws error and in hint it gives the type statement to be executed on Postgres and if execute the same on Postgres then next import foreign schema commands succeed.

edb@111467=#IMPORT FOREIGN SCHEMA suraj limit to (enum_t1) from server mysql_server INTO public;
NOTICE:  error while generating the table definition
HINT:  If you encounter an error, you may need to execute the following first:
DO $$BEGIN IF NOT EXISTS (SELECT 1 FROM pg_catalog.pg_type WHERE typname = 'enum_t1_size_t') THEN CREATE TYPE enum_t1_size_t AS enum('S','M','L'); END IF; END$$;

ERROR:  type "enum_t1_size_t" does not exist
LINE 3:   size enum_t1_size_t
               ^
QUERY:  CREATE FOREIGN TABLE enum_t1 (
  id int NOT NULL,
  size enum_t1_size_t
) SERVER mysql_server OPTIONS (dbname 'suraj', table_name 'enum_t1');

CONTEXT:  importing foreign table "enum_t1"
edb@111467=#

edb@111467=#DO $$BEGIN IF NOT EXISTS (SELECT 1 FROM pg_catalog.pg_type WHERE typname = 'enum_t1_size_t') THEN CREATE TYPE enum_t1_size_t AS enum('S','M','L'); END IF; END$$;
DO
edb@111467=#
edb@111467=#IMPORT FOREIGN SCHEMA suraj limit to (enum_t1) from server mysql_server INTO public;
NOTICE:  error while generating the table definition
HINT:  If you encounter an error, you may need to execute the following first:
DO $$BEGIN IF NOT EXISTS (SELECT 1 FROM pg_catalog.pg_type WHERE typname = 'enum_t1_size_t') THEN CREATE TYPE enum_t1_size_t AS enum('S','M','L'); END IF; END$$;

IMPORT FOREIGN SCHEMA
edb@111467=#
edb@111467=#
edb@111467=#\d enum_t1
                     Foreign table "public.enum_t1"
 Column |      Type      | Collation | Nullable | Default | FDW options 
--------+----------------+-----------+----------+---------+-------------
 id     | integer        |           | not null |         | 
 size   | enum_t1_size_t |           |          |         | 
Server: mysql_server
FDW options: (dbname 'suraj', table_name 'enum_t1')

Isn't there any possibility for the FDW to also create the according enums if they're present in the source DB?

No, We do not create those implicitly during import foreign schema due to infrastructure limitations, hence we are giving hint.