Postgres Foreign Data Wrapper: insert to mysql table primary key increment issue

Question

I am using PostgreSQL foreign data wrapper Multicorn (which uses SQLAlchemy) to map tables from external MySQL database. Everything seems to be working fine so far, except one issue: if I run an insert query on my foreign mysql table without specifying primary ID, because it's set to auto increment, foreign data wrapper tries to append and starts the increment from 1 even though such a key already exists in the remote MySQL table. This is better explained by example:

MySQL table:

CREATE TABLE `test` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `value` VARCHAR(100) NULL DEFAULT NULL,
  PRIMARY KEY (`id`));

MySQL table data:

select * from test;
+----+-------+
| id | value |
+----+-------+
|  1 | val1  |
|  2 | val2  |
|  3 | val3  |
+----+-------+

PostgreSQL FDW server:

CREATE SERVER test foreign data wrapper multicorn options (wrapper 'multicorn.sqlalchemyfdw.SqlAlchemyFdw');

PostgreSQL FDW table:

create foreign table test1 (
    id serial,
    value character varying
) server test options (
    tablename 'test',
    db_url 'mysql://root:[email protected]/test',
    primary_key 'id'
);

This is the insert statement that I've tried:

insert into test1 (value) values ('val4');

It throws this error:

ERROR:  Error in python: IntegrityError
DETAIL:  (IntegrityError) (1062, "Duplicate entry '1' for key 'PRIMARY'") 'INSERT INTO test (id, value) VALUES (%s, %s)' (1L, u'val4')

For some reason there is an attempt to manually insert a primary ID which I've specified as auto increment on both databases/tables. Why is this happening and how can I fix it? What is causing this, is it Postgres, FDW plugin Multicorn or SQLAlchemy?

Answer 1

What if you use int instead of serial on the PostgreSQL side?

create foreign table test1 (
    id int,
    value character varying
) server test options (
    tablename 'test',
    db_url 'mysql://root:[email protected]/test',
    primary_key 'id'
);