You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
{{ message }}
Repository navigation
CLI0129E: An attempt to allocate a handle failed because there are no more handles to allocate #145
$ go env
set GO111MODULE=
set GOARCH=amd64
set GOBIN=
set GOCACHE=C:\Users\Administrator\AppData\Local\go-build
set GOENV=C:\Users\Administrator\AppData\Roaming\go\env
set GOEXE=.exe
set GOEXPERIMENT=
set GOFLAGS=
set GOHOSTARCH=amd64
set GOHOSTOS=windows
set GOINSECURE=
set GOMODCACHE=C:\Users\Administrator\go\pkg\mod
set GONOPROXY=
set GONOSUMDB=
set GOOS=windows
set GOPATH=C:\Users\Administrator\go
set GOPRIVATE=
set GOPROXY=https://goproxy.cn,direct
set GOROOT=C:\Program Files\Go
set GOSUMDB=sum.golang.org
set GOTMPDIR=
set GOTOOLDIR=C:\Program Files\Go\pkg\tool\windows_amd64
set GOVCS=
set GOVERSION=go1.17.1
set GCCGO=gccgo
set AR=ar
set CC=gcc
set CXX=g++
set CGO_ENABLED=1
set GOMOD=NUL
set CGO_CFLAGS=-g -O2
set CGO_CPPFLAGS=
set CGO_CXXFLAGS=-g -O2
set CGO_FFLAGS=-g -O2
set CGO_LDFLAGS=-g -O2
set PKG_CONFIG=pkg-config
set GOGCCFLAGS=-m64 -mthreads -fmessage-length=0 -fdebug-prefix-map=C:\Users\ADMINI~1\AppData\Local\Temp\go-build1348026963=/tmp/go-build -gno-record-gcc-switches
C:\workspaces\demo\db2demo>go run main.go -o go_ibm_db
2021/10/18 21:55:12 >>> go_ibm_db
panic: SQLExecDirectW: {HY014} [IBM][CLI Driver] CLI0129E An attempt to allocate a handle failed because there are no more handles to allocate. SQLSTATE=HY014
goroutine 1 [running]:
...
C:\workspaces\demo\db2demo>go run main.go -o odbc
2021/10/18 22:02:39 >>> odbc
Is that the driver lib(go_ibm_db) NOT release db2 handle??????
@yyt030 The issue is caused by the use of defer rows.Close(). Move this call post for loop as below:
rows, err := tx.Query("select id, value, name from administrator.test")
if err != nil {
panic(err)
}
for rows.Next() {
var (
v, name string
id int
)
if err := rows.Scan(&id, &v, &name); err != nil {
panic(err)
}
}
rows.Close()
tx.Rollback()
We have modified our code with your solution, but still got the same error message when the application run for several days. Any idea about this? @bimalkjha
Here are the codes:
func Selectdata(db *sql.DB, Rowsinpage int) ([]Customerbrief, error) {
st, err := db.Prepare("SELECT cust.MIGRTN_CODE,cust.CUST_NO,cust.NAME,COALESCE(cust.CUST_NUM, ''),COALESCE(char(cust.LAST_UPDATED), ''),COALESCE(cust.FED_FLAG, ''),COALESCE(cust.Active_Flag, ''),COALESCE(cust.STATUS, '') FROM (SELECT MIGRTN_CODE,CUST_NO,NAME,CUST_NUM,LAST_UPDATED,FED_FLAG,Active_Flag,STATUS,ROW_NUMBER() OVER(partition by MIGRTN_CODE ORDER BY CUST_NO) as row_number FROM cdm1.V_CDM_CUST_DATA) cust WHERE cust.row_number > 0 AND cust.row_number <= ?")
if err != nil {
return nil, err
}
rows, err := st.Query(Rowsinpage)
if err != nil {
return nil, err
}
var databuff []Customerbrief
for rows.Next() {
var buff Customerbrief
err = rows.Scan(&buff.Migration_Code, &buff.Customer_Number, &buff.Name, &buff.Customer_Number, &buff.LAST_UPDATED, &buff.FED_FLAG, &buff.Active_Flag, &buff.Status)
if err != nil {
return nil, err
}
databuff = append(databuff, buff)
}
defer rows.Close()
return databuff, nil
@ZacZelner Do not use defer before rows.Close() in above code. See my previous post. I have not used defer.
Also, what about tx.Rollback() call? I do not see it in your new code.
Please note that CLI0129E error comes when application code continuously allocates handles and do not close the handle after use. So, running out of open handle is obvious. There is a limit on number of opened handles by underlying clidriver. Thanks.
@bimalkjha What is the reason for not to use the defer on rows.Close()?
If panic is not an option, rows shall be closed anyway
Is it required to make the Tx Rollback/ Commit? What would happen if closing in another query afterwards?
@alexcocia The reason for not to use the defer on rows.Close() is to ensure that rows are closed before Tx Rollback/Commit and return from the method. Deferring the rows.Close was causing delay in clearing the allocated resources and resource getting exhausted before it get freed.
If application code is calling db.Begin(), then tx.Rollback()/Commit() call is required to end the transaction.
Application can execute multiple query within a transaction but, after execution of all queries, the transaction must be closed i.e. all queries must get executed on same tx. Thanks.
Still the problem is not located on wether calling the rows.Close() with defer or not. The SQL Allocated handles are not released properly
We are facing the same problem and it only appears when executing multiple queries with no parameters . Here is what I've seen, so far:
In sql/sql.go, calling QueryContext with a non-params query finally executes Query method from go_ibm_db/conn.go
In the case the query has no params, conn.Query method :
Allocates Handle
SQLExecDirect the query
Returns a Rows element containing an ODBCStmt pointer with the flag usedByStmt set to true
Activity
ZacZelner commentedon Nov 19, 2021
@yyt030 @akhilravuri1
Have you fixed this issue? I got this error too when our application running for a while.
ZacZelner commentedon Dec 1, 2021
@bimalkjha
bimalkjha commentedon Dec 1, 2021
@yyt030 The issue is caused by the use of
defer rows.Close(). Move this call post for loop as below:ZacZelner commentedon Dec 31, 2021
We have modified our code with your solution, but still got the same error message when the application run for several days. Any idea about this? @bimalkjha
Here are the codes:
func Selectdata(db *sql.DB, Rowsinpage int) ([]Customerbrief, error) {
st, err := db.Prepare("SELECT cust.MIGRTN_CODE,cust.CUST_NO,cust.NAME,COALESCE(cust.CUST_NUM, ''),COALESCE(char(cust.LAST_UPDATED), ''),COALESCE(cust.FED_FLAG, ''),COALESCE(cust.Active_Flag, ''),COALESCE(cust.STATUS, '') FROM (SELECT MIGRTN_CODE,CUST_NO,NAME,CUST_NUM,LAST_UPDATED,FED_FLAG,Active_Flag,STATUS,ROW_NUMBER() OVER(partition by MIGRTN_CODE ORDER BY CUST_NO) as row_number FROM cdm1.V_CDM_CUST_DATA) cust WHERE cust.row_number > 0 AND cust.row_number <= ?")
if err != nil {
return nil, err
}
rows, err := st.Query(Rowsinpage)
if err != nil {
return nil, err
}
}
bimalkjha commentedon Jan 3, 2022
@ZacZelner Do not use defer before rows.Close() in above code. See my previous post. I have not used defer.
Also, what about tx.Rollback() call? I do not see it in your new code.
Please note that CLI0129E error comes when application code continuously allocates handles and do not close the handle after use. So, running out of open handle is obvious. There is a limit on number of opened handles by underlying clidriver. Thanks.
ZacZelner commentedon Jan 4, 2022
@bimalkjha Thank you for your answer. I thought Rollback() is the self-defined func by the issue reporter. I changed my code to be:
func Selectdata(db *sql.DB, Rowsinpage int) ([]Customerbrief, error) {
}
how about this? Can we use prepare statement? Should we use tx.Commit() do an update sql?
bimalkjha commentedon Jan 6, 2022
@ZacZelner You should use
db.Preparenottx.Prepare. tx do not have any Prepare method.Yes, you can use tx.Commit(). Let us know the result. Thanks.
alexcocia commentedon Jan 25, 2023
@bimalkjha What is the reason for not to use the defer on rows.Close()?
If panic is not an option, rows shall be closed anyway
Is it required to make the Tx Rollback/ Commit? What would happen if closing in another query afterwards?
bimalkjha commentedon Jan 25, 2023
@alexcocia The reason for not to use the defer on rows.Close() is to ensure that rows are closed before Tx Rollback/Commit and return from the method. Deferring the rows.Close was causing delay in clearing the allocated resources and resource getting exhausted before it get freed.
If application code is calling db.Begin(), then tx.Rollback()/Commit() call is required to end the transaction.
Application can execute multiple query within a transaction but, after execution of all queries, the transaction must be closed i.e. all queries must get executed on same tx. Thanks.
alexcocia commentedon Feb 6, 2023
Still the problem is not located on wether calling the
rows.Close()with defer or not. The SQL Allocated handles are not released properlyWe are facing the same problem and it only appears when executing multiple queries with no parameters . Here is what I've seen, so far:
In
sql/sql.go, callingQueryContextwith a non-params query finally executesQuerymethod fromgo_ibm_db/conn.goIn the case the query has no params,
conn.Querymethod :SQLExecDirectthe queryODBCStmtpointer with the flagusedByStmtset to trueOK, we now have the result and a Handle to be closed . The problem is that calling rows.Close, it executes this:
But the method
closeByRowsonly releases handles when usedByRows is true and therefore the handles are not releasedtravelliu commentedon Mar 9, 2023
@alexcocia I understand so
rows.Close()-> closeByRows
stmt.Close() -> closeByStmt
Fixing Release Handle
ojhwel commentedon Mar 9, 2023
We are currently also experiencing CLI0129E errors and have tried this fix and found it removed our problems as well.
Thank you @alexcocia for your very timely fix. ;)
Fixing Release Handle (#204)