8000 CLI0129E: An attempt to allocate a handle failed because there are no more handles to allocate · Issue #145 · ibmdb/go_ibm_db · GitHub
[go: up one dir, main page]

Skip to content

CLI0129E: An attempt to allocate a handle failed because there are no more handles to allocate #145

Closed

Description

@yyt030
  • os Version: win7
  • cli-driver Version: DB2 v11.1.4040.491
  • Db2 Server Version: DB2 v11.1.4040.491
  • go Version: go version go1.17.1 windows/amd64
go env Output:
$ 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

Steps to Reproduce:

  1. go run main.go . source code is this:
package main

import (
	"database/sql"
	"flag"
	"log"

	_ "github.com/alexbrainman/odbc"
	_ "github.com/ibmdb/go_ibm_db"
)

var dbType string

func init() {
	flag.StringVar(&dbType, "o", "odbc", "dbType")

	flag.Parse()
}

func main() {
	var connStr string
	if dbType == "odbc" {
		connStr = "dsn=localdb;UID=db2admin;PWD=db2admin"
	} else {
		connStr = "HOSTNAME=localhost;PORT=50000;DATABASE=go;UID=db2admin;PWD=db2admin"
	}
	log.Printf(">>> %s", dbType)

	db, err := sql.Open(dbType, connStr) // odbc or go_ibm_db
	if err != nil {
		panic(err)
	}
	defer db.Close()

	if err := db.Ping(); err != nil {
		panic(err)
	}

	for i := 0; i < 10000; i++ {
		func() {
			tx, err := db.Begin()
			if err != nil {
				panic(err)
			}
			defer tx.Rollback()

			rows, err := tx.Query("select id, value, name from administrator.test")
			if err != nil {
				panic(err)
			}
			defer rows.Close()

			for rows.Next() {
				var (
					v, name string
					id      int
				)
				if err := rows.Scan(&id, &v, &name); err != nil {
					panic(err)
				}
			}
		}()
	}
}

the output is :

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??????

@akhilravuri1

pls help me.

thanks !

Activity

  1. ZacZelner commented on Nov 19, 2021

    @ZacZelner

    @yyt030 @akhilravuri1
    Have you fixed this issue? I got this error too when our application running for a while.

  2. ZacZelner commented on Dec 1, 2021

    @ZacZelner
  3. bimalkjha commented on Dec 1, 2021

    @bimalkjha
    Member

    @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()
    
  4. ZacZelner commented on Dec 31, 2021

    @ZacZelner

    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
    

    }

  5. bimalkjha commented on Jan 3, 2022

    @bimalkjha
    Member

    @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.

  6. ZacZelner commented on Jan 4, 2022

    @ZacZelner

    @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) {

    tx, err := db.Begin()
    
    st, err := tx.Prepare("SELECT cust.MIGRTN_CODE,cust.CUSTNO,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,CUSTNO,NAME,CUST_NUM,LAST_UPDATED,FED_FLAG,Active_Flag,STATUS,ROW_NUMBER() OVER(partition by MIGRTN_CODE ORDER BY CUSTNO) 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.CustomeNO, &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)
    }
    
    rows.Close()
    tx.Rollback()
    
    return databuff, nil
    

    }

    how about this? Can we use prepare statement? Should we use tx.Commit() do an update sql?

  7. bimalkjha commented on Jan 6, 2022

    @bimalkjha
    Member

    @ZacZelner You should use db.Prepare not tx.Prepare. tx do not have any Prepare method.
    Yes, you can use tx.Commit(). Let us know the result. Thanks.

  8. alexcocia commented on Jan 25, 2023

    @alexcocia
    Contributor

    @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?

  9. bimalkjha commented on Jan 25, 2023

    @bimalkjha
    Member

    @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.

  10. alexcocia commented on Feb 6, 2023

    @alexcocia
    Contributor

    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 :

    1. Allocates Handle
    2. SQLExecDirect the query
    3. Returns a Rows element containing an ODBCStmt pointer with the flag usedByStmt set to true
    	os = &ODBCStmt{
    		h:          h,
    		Parameters: ps,
    		usedByStmt: true}
    	err = os.BindColumns()
    	if err != nil {
    		return nil, err
    	}
    	return &Rows{os: os}, nil
    

    OK, we now have the result and a Handle to be closed . The problem is that calling rows.Close, it executes this:

    func (r *Rows) Close() error {
    	return r.os.closeByRows()
    }
    

    But the method closeByRows only releases handles when usedByRows is true and therefore the handles are not released

    func (s *ODBCStmt) closeByRows() error {
    	s.mu.Lock()
    	defer s.mu.Unlock()
    	if s.usedByRows {
    		defer func() { s.usedByRows = false }()
    		if s.usedByStmt {
    			ret := api.SQLCloseCursor(s.h)
    			if IsError(ret) {
    				return NewError("SQLCloseCursor", s.h)
    			}
    			return nil
    		} else {
    			return s.releaseHandle()
    		}
    	}
    	return nil
    }
    
  11. travelliu commented on Mar 9, 2023

    @travelliu
    stmt,err := db.PrepareContext(ctx,s)
    if err != nil {
        panic(err)
    }
    defer stmt.Close()
    rows, err := stmt.QueryContext(ctx)
    if err != nil {
        panic(err)
    }
    defer rows.Close()
    for rows.Next() {
    }
    

    @alexcocia I understand so

    rows.Close()-> closeByRows
    stmt.Close() -> closeByStmt

  12. added a commit that references this issue on Mar 9, 2023
  13. ojhwel commented on Mar 9, 2023

    @ojhwel

    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. ;)

  14. added a commit that references this issue on Mar 29, 2023
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Metadata

Metadata

Assignees

No one assigned

    Labels

    No labels
    No labels

    Type

    No type

    Projects

    No projects

      Milestone

      No milestone

      Relationships

      None yet

      Development

      No branches or pull requests

      Issue actions

      0