Handle broken pipe gracefully

[Noon2Dusk]

In case of restart of DB server instance, graceful handling of broken pipe error with which would also try to reconnect would be a good solution.

[sijms]

I think I need to create custom sql package

[hmmftg]

Hi We have to sql.DB connections in our api simultanously, one for postgresq using https://github.com/lib/pq and one for oracle using https://github.com/sijms/go-ora. but unfortunately pq driver reconnects and recovers panic state and oracle driver does not, I think you shoould take a look in conn.go and error.go in pq project.

[sijms]

I think now I can make failover at stmt.Query and stmt.Exec level means if connection failed when you start query or exec it will reconnect but if error happen in fetch method => during rows.Next() and rows.Scan the driver will return error

[sijms]

I add support for failover please test and write your feedback

[hmmftg]

Hi we still have the broken pipe problem:

stable state:

[GIN] 2022/11/30 - 10:46:42 | 200 |  159.599297ms |       127.0.0.1 | GET      "/"
[GIN] 2022/11/30 - 10:46:42 | 200 |  160.122278ms |       127.0.0.1 | GET      "/"

unplug network cable and wait 10 minutes:

Error Query: read tcp ip1:34310->ip.server:1521: read: connection reset by peer, <nil>, [], SELECT * FROM table WHERE ROWNUM < 100 
[GIN] 2022/11/30 - 10:50:02 | 500 |         3m13s |       127.0.0.1 | GET      "/"
[GIN] 2022/11/30 - 10:50:02 | 500 |         3m13s |       127.0.0.1 | GET      "/"

plug in the cable:

Error Query: write tcp ip1:34310->ip.server:1521: write: broken pipe, <nil>, [], SELECT * FROM table WHERE ROWNUM < 100 
[GIN] 2022/11/30 - 10:50:06 | 500 |    1.000311ms |       127.0.0.1 | GET      "/"
[GIN] 2022/11/30 - 10:50:06 | 500 |    1.067927ms |       127.0.0.1 | GET      "/"
Error Query: write tcp ip1:34310->ip.server:1521: write: broken pipe, <nil>, [], SELECT * FROM table WHERE ROWNUM < 100 
[GIN] 2022/11/30 - 10:50:12 | 500 |     408.362µs |       127.0.0.1 | GET      "/"
[GIN] 2022/11/30 - 10:50:12 | 500 |      443.56µs |       127.0.0.1 | GET      "/"

[sijms]

when I make a testing environment for failover I do the following 1- run a simple go program in debug mode 2- stop just after Ping 3- go to the server which is simply a docker image 4- stop database and then startup again 5- at this point I receive error EOF

so I start by add error EOF as a starter for failover technique

I think you receive now different error?

[sijms]

if you can identify the type of the error and to faster the process you can modify function _query in file command.go v2 at line #1601 and line #1615

if errors.Is(err, io.EOF) || errors.Is(err, <returned error>){

[sijms]

so I will not close this issue until we catch all possible errors that related to broken pipe

[sijms]

any news here?

[agunglotto]

Hi, I face the same problem (error telling about broken pipe). Here is how you can simulate a failover scenario.

Use docker-compose with two container instances of Oracle DB on different ports. Then you can have a connection string like this: (DESCRIPTION=(ADDRESS_LIST=(LOAD_BALANCE=OFF)(FAILOVER=ON)(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.2.55)(PORT=1521))(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.2.55)(PORT=1522)))(CONNECT_DATA=(SERVICE_NAME=XEPDB1)(SERVER=DEDICATED)))

If you run an example app which is executing a query for example every 10 sec (SELECT SYSDATE FROM DUAL) you can stop and start any of those two instances and see what happens in the application. There is no failover unfortunately.

If you need more details about how to set up the environment, please let me know. Best regards

[agunglotto]

Sorry, I realized that 2.5.16 has some more fixes. I switched to that version and now it works as expected. Good job!

[ducknificient]

is the issue already fixed ? i still experience broken pipe problem with this version github.com/sijms/go-ora/v2 v2.6.3

how to reproduce the problem :

1. Run server
2. Idle for 1 day
3. Make a request that using database connection
4. Got error log err:write tcp 192.168.XX.XX:XXXXX->192.168.XX.XX:XXXX: write: broken pipe..

[sijms]

I am working on it now

[sijms]

@kalengbekas would you please test it with latest version

[ducknificient]

okay, i'll back in a few days. Thank you

[ducknificient]

after updating to v2.6.10 and adding the setConnMaxLifetime & MaxIdleTime,the error is gone. Until now the server still up. Thank you

[sijms]

I add a new option in v2.6.11 RETRYTIME which represents number of seconds that will pass before client will re-connect