Broken pipe

[Mecanik]

Hello,

This is a very common issue I had with my previous (own) implementation of MySQL connector, as well as with the "official" ones... if there is no activity for a while, the connection become stale or something.

In this scenario, after a period of inactivity (a few hours), I get the following:

Error: [Broken pipe [system:32]], Server diagnostics: []

Do you have any idea how I can resolve this?

Many thanks!

[Mecanik]

I forgot to add, the latter is:

Error: [Connection reset by peer [system:104]], Server diagnostics: []
Error: [Broken pipe [system:32]], Server diagnostics: []

[anarthal]

Hi @Mecanik,

These errors mean that the underlying TCP connection with the server has been closed, so you're trying to send data on a TCP connection that is no longer valid.

By default, the MySQL server will close any connection that has been iddle for more than 8h. This behavior is configurable using the wait_timeout global and session variables.

Even if you increase the iddle timeout, the underlying connection may be broken if you keep it iddle for a very long time - IIRC TCP uses keepalive messages to prevent this from happening, but not all systems implement these.

If you need to keep the connection open indefinitely, the most robust strategy is to use application-level messages after a certain period of inactivity is elapsed. In the case of MySQL, I suggest to use connection::(async_)ping, which is a very lightweight message that verifies that the connection is OK.

Note that connection won't send ping messages by default - you will have to implement this yourself. I wanted to keep connection as "passive" as possible - it won't engage in any network activity unless you tell it to do so.

If auto-pinging to prevent connections from closing due to inactivity is something of interest, I can try to incorporate it to connection pools when I implement them (see #19).

If you have any further questions, please feel free to ask.

Regards, Ruben.

[Mecanik]

Thank you, I understand the issue. The "ping" solution would work I believe (I was thinking to do something like SELECT now(); or something similar to maintain it).

Please introduce this if possible and if your time allows it. BTW - Why don't you setup some sponsorship?

[Mecanik]

Also, not sure if it makes any difference for your, but I only use MariaDB as MySQL is... well just MySQL.

[anarthal]

ping() should be more efficient. All tests are run under MariaDB too, so ping() should work correctly in all systems.

I'm currently being sponsored by the C++alliance btw.

[Mecanik]

I see, I need to figure out how to use it. Sorry, C style developer here just dipping my toes into C++ recently. I did not know the C++ alliance has sponsorship; I have a good friend there sehe.

[anarthal]

No worries :)

It is equivalent to mysql_ping in the official drivers.

[anarthal]

If you're okay with it, I'm closing this issue and making a note in #19.