memory leaking in Query method

[ws6]

Hi mattn,
it seems the Query function has memory leaking issue.
https://github.com/mattn/go-adodb/blob/master/adodb.go#L61
even I did a defer rows.Close()

The leaking correlated to number of queries.

here is how I used it. Appreciated your help.
```
rows, err := db.Query(query)
if err != nil {
return nil, err
}
defer rows.Close()
for rows.Next() {
if err := rows.Scan(

[mattn]

What does your compairing? Before Query and After Query? Or many calls of Query? Or many Rows?

[ws6]

many Query called, each Query with full Scan.
Can it be this?
func (s *AdodbStmt) Close() error {
s.s.Release()
return nil
}

[nevcook]

Hi mattn,
I'm seeing the same problem. I'll try to give you as much detail as I can think of...

Each query I open increases my Windows Working set memory and (other than irregular small reductions) it just keeps going up and up.

I've been able to confirm that this memory is not part of the Go garbage collected memory. Go still thinks it's only using a few MB when my process Working set is heading over 500MB (and higher, if I let it run long enough).

I've tried everything I can think of, including not only closing the rows, but also closing the database connection between each query, and even calling CoUninitialize between each query. Nothing I do seems to make any difference to the memory usage. It just keeps climbing until the process is terminated.

The structure of my code is basically the same as the snippet ws6 described above. I can provide a detailed example if you want one.

I've eliminated everything else in the loop from being the cause by removal and deduction: The bottom line is that when I comment out the call to Query()l, and the loop through the rows, the memory leak goes away. If I comment out just the loop through the rows, but still call Query() then it still leaks.

I'll try to answer the questions you asked ws6 above. I'm not sure what the first two of your questions above are asking, but by the time I've run about 3000 queries the working set has reached about 420MB. It's the same query each time, and in these tests, it's been the same query results with 2346 rows returns for each query.

Please help. This process is supposed to be long running (i.e. continuous) so a memory leak like this is devastating. Unless we can solve the problem, it's a complete road block for the use of Go in this project, and I really would like to stick with Go if I can.

If there's any other information I can give you to help diagnose the problem, please ask and I'll be happy to provide it.

[nevcook]

I thought it best to provide some sample code to allow you to duplicate this easily.
It's a modification of your mdb.go example.
For me the process Working set memory gets to about 75MB by about 6000 iterations of the query loop.
Sorry if I've made any mistakes. I'm relatively new to Go.
(By the way, I'm using 32 bit Go, as I can't make this work at all as 64 bit)

package main

import (
	"database/sql"
	"fmt"
	"log"
	"os"
	"time"

	// This is only here to make go give the process an expanded stack, which is needed for ADO to work correctly.
	// Without this the query loops below crash consistently after about 21 or 42 iterations (but not anything in between).
	_ "runtime/cgo"

	"github.com/go-ole/go-ole"
	"github.com/go-ole/go-ole/oleutil"
	_ "github.com/mattn/go-adodb"
)

func createMdb(f string) error {
	unk, err := oleutil.CreateObject("ADOX.Catalog")
	if err != nil {
		return err
	}
	cat, err := unk.QueryInterface(ole.IID_IDispatch)
	if err != nil {
		return err
	}
	_, err = oleutil.CallMethod(cat, "Create", "Provider=Microsoft.Jet.OLEDB.4.0;Data Source="+f+";")
	if err != nil {
		return err
	}
	return nil
}

func main() {
	ole.CoInitialize(0)

	f := "./example.mdb"

	os.Remove(f)

	err := createMdb(f)
	if err != nil {
		fmt.Println("create mdb", err)
		return
	}

	db, err := sql.Open("adodb", "Provider=Microsoft.Jet.OLEDB.4.0;Data Source="+f+";")
	if err != nil {
		fmt.Println("open", err)
		return
	}

	_, err = db.Exec("create table foo (id int not null primary key, name text not null, created datetime not null)")
	if err != nil {
		fmt.Println("create table", err)
		return
	}

	tx, err := db.Begin()
	if err != nil {
		fmt.Println(err)
		return
	}
	stmt, err := tx.Prepare("insert into foo(id, name, created) values(?, ?, ?)")
	if err != nil {
		fmt.Println("insert", err)
		return
	}
	defer stmt.Close()

	for i := 0; i < 100; i++ {
		_, err = stmt.Exec(i, fmt.Sprintf("こんにちわ世界%03d", i), time.Now())
		if err != nil {
			fmt.Println("exec", err)
			return
		}
	}
	tx.Commit()

	// First tried the query in a loop.
	// NOTE: For me this loop crashes after about 580 iterations with a "Cannot open any more tables" error.
	//   That's a separate problem I'm going to be following up if I can get the memory leak fixed.
	// for i := 1; ; i++ {
	// 	fmt.Printf("%d.", i)
	// 	err := queryRows(db)
	// 	if err != nil {
	// 		log.Print(err)
	// 		break
	// 	}
	// 	time.Sleep(10 * time.Millisecond)
	// }

	// Next tried re-opening the DB for each query.
	// This version has the same memory leak, but doesn't crash (at least not in the first 6000+ iterations)
	db.Close()
	for i := 1; ; i++ {
		fmt.Printf("%d.", i)
		err = queryRowsClosingDB(f)
		if err != nil {
			log.Print(err)
			break
		}
		time.Sleep(10 * time.Millisecond)
	}
}

func queryRows(db *sql.DB) error {
	rows, err := db.Query("select id, name, created from foo")
	if err != nil {
		return fmt.Errorf("select error: %s", err)
	}
	defer rows.Close()

	for rows.Next() {
		var id int
		var name string
		var created time.Time
		err = rows.Scan(&id, &name, &created)
		if err != nil {
			return fmt.Errorf("scan error: %s", err)
		}
		//fmt.Println(id, name, created)
	}

	return nil
}

func queryRowsClosingDB(f string) error {
	db, err := sql.Open("adodb", "Provider=Microsoft.Jet.OLEDB.4.0;Data Source="+f+";")
	if err != nil {
		return fmt.Errorf("open error: %s", err)
	}
	defer db.Close()

	return queryRows(db)
}

[mattn]

Anyone, could you please try some tests with removing this part?

https://github.com/mattn/go-adodb/blob/master/adodb.go#L61-L70

[nevcook]

I'm really glad you are still looking at this.

I wasn't sure exactly what you wanted to test, but I've taken a crack at it.

I first made another temporary change to that function, just to confirm that my modifications were being compiled and used.
Then I tried commenting out the specified lines (i.e. the whole function) and re-ran the test routine I posted above for 6000 cycles.

Unfortunately it made no significant difference to the memory usage with or without that function commented.

[mattn]

Hmm, How about this?

diff --git a/adodb.go b/adodb.go
index a739d0a..0fd1ac6 100644
--- a/adodb.go
+++ b/adodb.go
@@ -116,6 +116,7 @@ func (c *AdodbConn) Close() error {
 	if err != nil {
 		return err
 	}
+	c.db.Release()
 	c.db = nil
 	ole.CoUninitialize()
 	return nil
@@ -297,6 +298,7 @@ func (rc *AdodbRows) Close() error {
 	if err != nil {
 		return err
 	}
+	rc.rc.Release()
 	return nil
 }
 

[nevcook]

That change made a difference, but was not the whole problem.
Over a few iterations the average memory usage fell from around 75MB to around 67MB.
(That was with the previous modification still in place.)

It's 6:30pm here and I'll be going out for the evening shortly, but I should still have time to try another round when I get back if you have any other thoughts.

I wish I understood more about how this package works so I could help more directly, but I'm new to Go, and the insides of how the sql database processing works are a little beyond my understanding at the moment.

[mattn]

Thank you. I fixed some.
09a39b6
Could you please try this again?

[nevcook]

Those changes have helped quite a lot.

Averaging over a few iterations, the memory usage for 6000 cycles of the query loop fell from about 75MB originally to around 45MB with the latest version. This is definitely on the right track.

But it's still steadily increasing the longer the loop runs. Before the loop starts (after creating the database) it's at 17MB. After 6000 cycles it's up to 45MB, and after 12000 cycles it's up to 64MB.

Extra information: In my test code (see above) I've been using the version of the query loop that opens and closes the database for every cycle, because (as noted in the comments), not closing the database after every loop caused the loop to fail after about 580 cycles with a "Cannot open any more tables" error.

Just as a test, I tried that version of the query loop again with the latest modifications. Unfortunately it still fails after 580 cycles with the same error, so there's obviously something important that's not being disposed of correctly when you dont explicitly close the database. Hopefully this might give you some idea about what might be going on.

[chuyangyang]

Has the problem been solved? I met the same problem and expected the problem to be solved.

[ws6]

just use this driver - github.com/denisenkom/go-mssqldb
if your mssql is not 2008R1. it is well tested.
And also I have my querybuilder library can work with mssql, postgres and mysql with the same interface.
it is light-weighted and simple to use. (sorry no docs, but you can easily extend it yourself)
msi

[ws6]

package mssql

import (
"testing"

"fmt"

_ "github.com/denisenkom/go-mssqldb"
"github.com/ws6/msi"

)

func getDb() (*msi.Msi, error) {
connString := fmt.Sprintf("server=%s;database=%s;user id=%s;password=%s",
"host",
"dbname",
"user",
"password",
)
return msi.NewDb("mssql", connString, "dbname", "")
}

func TestLoad(t *testing.T) {

schema, err := getDb()

if err != nil {
	t.Fatal(err.Error())
}
defer schema.Close()

}
func TestPlainQuery(t *testing.T) {
schema, err := getDb()
defer schema.Close()
if err != nil {
t.Fatal(err.Error())
}
defer schema.Close()

projectTable := schema.GetTable("project")
if projectTable == nil {
	t.Fatal("no project table")
}
q, err := projectTable.FindQuery(map[string]interface{}{`id`: 123})
if err != nil {
	t.Fatal(err.Error())
}
t.Log(q)

}

func TestJoinQuery(t *testing.T) {
schema, err := getDb()
defer schema.Close()
if err != nil {
t.Fatal(err.Error())
}
defer schema.Close()

sampleTable := schema.GetTable("sample')
if sampleTable == nil {
	t.Fatal("no sample table")
}
q, err := sampleTable.FindQuery(
	map[string]interface{}{"id": 123},
	map[string]interface{}{msi.POPULATES: []string{"project_id"}},
)
if err != nil {
	t.Fatal(err.Error())
}
t.Log(q)

countQuery, err := sampleTable.CountQuery(
	map[string]interface{}{"id": 123},
	map[string]interface{}{msi.POPULATES: []string{"project_id"}},
)
if err != nil {
	t.Fatal(err.Error())
}
t.Log(countQuery)
//Test CountQuery

page, err := sampleTable.GetPage(
	msi.M{},
	map[string]interface{}{msi.POPULATES: []string{`project_id`}},
) 

if err != nil {
	t.Fatal(err.Error())
}
t.Logf("%+v", page)
inserts := msi.M{"project_id": 1, "sample_name": "LP_1abacsd"}
if err := sampleTable.Insert(inserts); err != nil {
	t.Log(err.Error())
} else {
	t.Logf("inserted")
}

found, err := sampleTable.FindOne(inserts)
if err != nil {
	t.Fatal(err.Error())
}

t.Logf("%+v", found)

updates := msi.M{`project_id`: 2}
if err := sampleTable.Update(msi.M{"id": found["id"]}, updates); err != nil {
	t.Fatal(err.Error())
} else {
	t.Log("updated")
}
if err := sampleTable.Remove(msi.M{"id": found["id"]}); err != nil {
	t.Fatal(err.Error())
}

t.Log("removed")

}

[nevcook]

@ws6 : Thanks for trying, but your suggestion misses the point. Although the original question doesn't specifically say so (I didn't write that), we are only using ADO here because we are needing to access the data in msaccess .mdb files, not in a proper database like mssql. (Please see the example code above.)
If we were using mssql there would be other options that would bypass the need for ADO, and if we had the option of moving this data into a proper database, we would have already done that long ago.

[ws6]

sorry I missed your point.
I suggest to either export msaccess to other datastorage or using some specific drivers.

https://github.com/bennof/accessDBwE
ps: I just googled, not tested.