0

I am implementing mysql_fdw in PostgreSQL (9.4.4). I have created foreign table and they are working fine except for one thing: For some of the rows in column idvisitor with datatype bytea, it is throwing the following error

ERROR: invalid input syntax for type bytea ********** Error **********

ERROR: invalid input syntax for type bytea SQL state: 22P02

Example: A table visits with two columns: id SERIAL, idvisitor bytea Thousand rows with id from 1 to 1000

  • SELECT * FROM visits; #ERROR
  • SELECT * FROM visits WHERE id BETWEEN 1 and 900; # NO ERROR
  • SELECT * FROM visits WHERE id = 901; #ERROR
  • SELECT * FROM visits WHERE id BETWEEN 902 AND 1000; # NO ERROR
  • SELECT id FROM visits WHERE id=901; # NO ERROR

Proceeding like this, I could identify rows which are triggering the error. If I execute the same query in MySQL, it executes silently without throwing any error.

zen
  • 93
  • 1
  • 4
  • MySQL doesn't have a `bytea` data type. What is the MySQL data type you are mapping? `VARBINARY`? – Craig Ringer Aug 25 '15 at 13:02
  • Its binary(8) in MySQL table, mapped to bytea in PostgreSQL. – zen Aug 25 '15 at 14:25
  • Seems like `mysql_fdw` needs a format conversion function for `binary` to support this. MySQL's `binary` representation is different to the representation PostgreSQL expects for `bytea`. – Craig Ringer Aug 25 '15 at 14:37
  • Could you please guide me to write this format conversion function? How to integrate it with mysql_fdw? – zen Aug 25 '15 at 17:19
  • It'll be a modification to the mysql_fdw C code, if I'm right about what the problem is. I haven't done anything with mysql_fdw, so I can't really give much advice there. Consider filing a bug at https://github.com/EnterpriseDB/mysql_fdw/issues, including the MySQL table definition, the PostgreSQL foreign table definition, the problem data rows, etc. – Craig Ringer Aug 26 '15 at 01:05

0 Answers0