"error": "driver: bad connection" followed by "Stmt.exeC Env.ociError ORA-03135: connection lost contact"

[chakrapani123]

hey guys,

i have a situation where there was a network issue earlier and then got resolved and subsequent transactions are throwing this error. is there a way to gracefully handle this error after connection resets on database?

[MichaelS11]

Yup, Oracle databases loose connections sometimes. This in itself is not a oci8 issue.

[chakrapani123]

here is my sample code : func (o *Oracle) ConsumeCdb(cdbname string) (consumed bool, err error) {

fmt.Println(cdbname)
udb := o.Db
utx, err := udb.Begin()
if err != nil {
    fmt.Println("error", err)
}
ures, err := utx.Exec("x")
if err != nil {
    fmt.Println("error in exec: ", err)
    utx.Rollback()
    return consumed, err
}
if err := utx.Commit(); err != nil {
    fmt.Println("error", err)
}
urowCnt, err := ures.RowsAffected()
if err != nil {
    fmt.Println("error", err)
}
fmt.Println("rows changed ", urowCnt)

if urowCnt > 0 {
    return true, err
} else {
    return false, err
}

}

I am beginning a transaction and making an update and commit to DB. do i have to open a connection prior to beginning a transaction as transaction takes care of taking a connection from connection pool

thanks Chakri

[MichaelS11]

Here is the transaction test code: https://github.com/mattn/go-oci8/blob/9816237c2e725b378da0a7b3f675e539f477d701/oci8Sql_test.go#L483-L754

Transactions should work. If you still think it is a oci8 issue, please provide the smallest amount of full code that will reproduce the issue. Need the full SQL and Go code.

[MichaelS11]

To your edited post:

i have a situation where there was a network issue earlier and then got resolved and subsequent transactions are throwing this error. is there a way to gracefully handle this error after connection resets on database?

You can create a function that turns the ORA-# to a int then check if it is a number that you want to retry on. Can look at the follow code as inspiration: https://github.com/mattn/go-oci8/blob/master/connection.go#L195-L231

[MichaelS11]

Also, in your connection pool code, may need to add some code to close the connection from the pool and open a new connection on error.

[cjbj]

Yup, Oracle databases loose connections sometimes. This in itself is not a oci8 issue.

I would dispute this in general, and am not sure it is exactly what @MichaelS11 meant. Dropped connections are almost always because firewalls kill idle network connections, or a transient network problem (like @chakrapani123 seems to indicate), or because DBA's have imposed resource limits like IDLE_TIME.

Other than application detection and recovery, Oracle has high availability features that help various scenarios.

@MichaelS11 the best practice recommendation for Oracle HA feature support is to use an Oracle Session Pool, even if there is only one connection in the pool. PHP OCI8 does exactly this. It may be something to consider.

[MichaelS11]

@cjbj

Go sql driver interface has connection pool built in. In my use case I have not had any issues with the connection pool and the driver reconnecting. I personally do not see the need for Oracle Session Pool but of course if someone wants to add it, go for it. :)

[MichaelS11]

One thought is maybe we need to add ORA-03135 bad connection errors list, but I am not sure.

@mattn Do you think we should add ORA-03135 to the bad connection errors list?

[mattn]

Agree with your suggestion.

[cjbj]

Other errors handled by other drivers: https://github.com/oracle/odpi/blob/9902a60068042e7409b7806b5e73fe7e66200cae/src/dpiError.c#L89

[MichaelS11]

Funny, I looked and 03135 is already added. :)

So I think there is nothing to do at this point for this issue.

[chakrapani123]

@MichaelS11 thanks for your feedback on connection pool..wondering if sql/ora driver handles tcp connection which was in Established state for any tx (assuming transaction takes a connection from connection pool) and then for any issues like in my case (network glitch or database kills this existing connection) and change connection to (CLOSE_WAIT) subsequent requests fail with this error. we are trying to debug why the tcp ttl timeout doesnt gracefully handle the connection either way, shouldn't the underlying oci driver handle close_wait ?

[MichaelS11]

Some of what you are talking about is in the Oracle OCI driver. I gather it handles timeouts but I really do not know the details. From the error you provided it looks like the Oracle OCI driver did report the error. Once that error gets reported it is passed up to the Go code and the connection is marked as bad connection. If a transaction is not used this would all be transparent and it would try to make a new connection.

The difference here is that a transaction is being used. With a transaction it can not automatically get a new connection because that would ruin the transaction. The transaction has to be on the same connection. So connection errors need to have additional handling inside of a transaction.

[MichaelS11]

@chakrapani123 Does that help? All good? If so, can you close this please?

[MichaelS11]

@chakrapani123 Can this be closed?

[MichaelS11]

@mattn Close this?