ERROR: failed to prepare the MySQL query: MySQL server has gone away

[hvisage]

HI There,

I have this issue that the first connection in a session, appears to be having this problem error thrown.
If I repeat the exact same procedure call, with the same values, the connection goes through and then just "works"(TM).

Any information how to debug this issue?
How should I (inside a stored procedure/function) do a check to see if I needed to retry the select query?
or perhaps: How should I check that the server hasn't really "gone away" and have just closed the connection?

Both the MySQL 5.7 & PostgreSQL 9.5 are on the same Debian 8.x VM instance. The stored procedure that fails, are called from a Python Flask microservice framework application, that was actually called from the MySQL I'm connecting back to (using the http UDF on MySQL), so the server is actually always "up" when receiving this stored procedure call, just the connection might be "down", so perhaps needed a method to check/test whether the connection is up or needed reconnecting is needed to be called by my stored procedure?

[ahsanhadi]

Can you please share your test case?

[ahsanhadi]

Need a test case to investigate this issue further...

[and0x000]

I think I have the same issue. Using PostgreSQL and MariaDB via docker.

This is my Postgres-Dockerfile

FROM postgres:latest

RUN apt update && apt -y upgrade && apt -y install git libpq-dev build-essential postgresql postgresql-server-dev-9.6 libmysqlclient-dev

RUN git clone https://github.com/EnterpriseDB/mysql_fdw.git && cd mysql_fdw && export PATH=/usr/local/pgsql/bin/:$PATH && export PATH=/usr/local/mysql/bin/:$PATH && make USE_PGXS=1 && make USE_PGXS=1 install

ENV LANG de_DE.utf8
RUN localedef -i de_DE -c -f UTF-8 -A /usr/share/locale/locale.alias de_DE.UTF-8

I run both containers, and link the MariaDB container to postgres.
docker run --name mypostgres --link db1:db1 -e POSTGRES_PASSWORD=secret -d postgres

create Database, connect to that, create extension, server, user mapping, foreign table, everything according to guide.
When I use the psql in the postgres container to perform a simple SELECT * FROM myForeignTable (query time below 1 s), it tells me that the connection has gone away.

ERROR: failed to prepare the MySQL query:
MySQL server has gone away

As described by hvisage, issueing the query a second time 'solves' it, as the connection seems established then.

[chanmix51]

This may be due to networking equipments killing connections idling for too long. Implementing a TCP keepalive every N seconds may solve the problem.

[eugeneYWang]

having trouble with this.

BTW, is this repo still under maintenance?

[eugeneYWang]

@chanmix51 Can users do this? Or only developers of this repo can implement?

[chanmix51]

I appeared this was the consequence of pooling Postgres connections on different servers. As soon as we stopped distributing the connections, it was stable.

[ahsanhadi]

Are you still having trouble with this? Yes this repository is mainted by EnterpriseDB. We provided a release in August 2017 that provided PG 10 support and fixed a number of issues.

[eugeneYWang]

@ahsanhadi I did have this error and it disappeared like other people have said after sending a second query. But I worried that this will happen again.

Here are versions of database and fdw for your infomation:
postgresql v9.6
mysql_fdw v2.3.0 (installed from Postgresql APT repository)

System: Ubuntu 16.03 LTS

[eugeneYWang]

@ahsanhadi Just a wish: it will help us if the README of this repo will be updated. At least we know how is the package updated, and what features we can expect.

[hvisage]

Sorry, the initial request for information slipped through my email filters ;(
The initial VMs have been destroyed where I experienced this, but as I explained, it was simply having the two DBs (MySQL & PG) on the same Debian 8 VM, running on Proxmox, that exhibitied this behaviour.
In my case, no in between networks, just the "localhost" (Perhaps connecting to the LAN IP), and it was so consistent, I am surprised that it wasn't picked up before, unless I had some settings in PG or MySQL that caused that.

From my perspective close, but it is still something that I think the mysql_fdw doesn't handle correctly, or have to be provided with sometime out settings to check the conenction states etc.