search

EnterpriseDB / mysql_fdw

PostgreSQL foreign data wrapper for MySQL
Other
530 stars 162 forks source link

Sporadic failures: ERROR: failed to bind the MySQL query: #125

Closed djantzen closed 7 years ago

djantzen commented 7 years ago

Environment:

Pgpool2 3.4.3-1 on Ubuntu 16 2 instances Postgres 9.5.4 on Ubuntu 16 MySQL 5.6 on Amazon RDS

The initial first couple of queries work, then the same query results in the error message above: "failed to bind the MySQL query"

Sometimes, it causes pgpool to get into some kind of failed state, and attempted connections result in: "psql: server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request."

Pgpool then has to be restarted. Any ideas? Unfortunately pgpool doesn't log anything except for status.

djantzen commented 7 years ago

I've reproduced this without pgpool in the middle.

djantzen commented 7 years ago

Apparently this happens when you create a foreign table without declaring columns. I was hoping to be lazy and just autopopulate them from the source table. It would be nice to have an error message indicating that is invalid.

On a related note, what is the mysql "schema" when doing an "import foreign schema"? I tried database name without luck.

Thanks!

jjthiessen commented 7 years ago

You can create mysql_fdw foreign tables without declaring columns with the caveat that it only works for limited column types and data which is invalid in PostgreSQL isn't mapped/translated in any way (which, I believe, will cause the connection drops that you are seeing). For example, invalid/zero dates/datetimes won't get mapped to nulls, thus causing an error that crashes mysql_fdw whenever a query touches applicable rows. This is what you want to look at for the type mapping logic.

If your issue is that the type match from MySQL to PostgreSQL isn't valid for your dataset, then you would need to import the tables individually using queries rather than direct table mappings (so that you can translate invalid data (with NULLIF, COALESCE, and/or CASE expressions, for example). This is the case for any date/time columns where zero values are a possibility.

I'm pretty sure that the remote schema should map up with the MySQL database name as you thought. LIMIT TO and EXCEPT should also work for limiting which tables get imported. PostgreSQL is case sensitive (not just case preserving). Did you double check your case? What error/issue do you encounter with IMPORT FOREIGN SCHEMA? Do you have any special characters in your MySQL database name? It could possibly be a quoting issue in the import logic?

ahsanhadi commented 7 years ago

No response from the reporter. Please let us know if you still have a valid issue with "import foreign schema" or if you are getting the error message below please share your test case...

"failed to bind the MySQL query"

If you aren't facing any issue, please let us know so we can close this case...