DB file handler not closing: running out of max open file descriptors. about sqlite HOT 8 CLOSED

I've modified my library to use PrepareTransient/Finalize instead of Prep(or Prepare)/Reset. I'm no longer having the issue I originally reported. It works fine on the latest release as well as master.

If the test code I provided above is supposed to close file handles, then I'm still going to suggest the sqlite library has a bug that's leaving them open. It is no longer affecting me though.

Thank you for all the information!

from sqlite.

I've also tried adding query.Finalize() but that causes a nil pointer dereference. query.Reset() doesn't cause an error, but it also doesn't fix the problem. To make sure this is the sqlite library I also disabled the fsnotify code in that linked library. I cannot for the life of me figure out the trick to getting this library to close the db connection, and the open files.

from sqlite.

I'm not the maintainer of this repo but I have been using it a lot lately and making some improvements on it.

I can assure you that the library properly closes database connections.

It is unlikely that someone else is going to debug your code for you. If you truly believe there is an issue with this library, please create the simplest possible example that demonstrates the issue.

Just to clarify, if you use Prep or Prepare for your queries you should not call Finalize() on the Stmt, as they are cached and reused. If you use PrepareTransient, you must call Finalize() on the Stmt, when you are done using it.

from sqlite.

Run this on any mac.

package main

import (
	"fmt"
	"os"
	"time"

	"crawshaw.io/sqlite"
)

func main() {
	ticker := time.NewTicker(10 * time.Millisecond)
	var i int
	for range ticker.C {
		i++
		pollDB(i, os.Getenv("HOME")+"/Library/Messages/chat.db")
	}
}

func pollDB(count int, path string) {
	db, err := sqlite.OpenConn(path, sqlite.SQLITE_OPEN_READONLY)
	if err != nil {
		panic(err)
	}
	defer db.Close()

	query := db.Prep(`SELECT MAX(rowid) AS id FROM message`)
	defer query.Reset()
	if hasrow, err := query.Step(); err != nil {
		panic(err)
	} else if !hasrow {
		panic("no message rows found")
	}

	fmt.Println(count, "current iMessage rowID:", query.GetInt64("id"))
}

In another terminal window run lsof | grep chat.db a few times. The database file opens repeatedly until things crash. Sending (or receiving) messages with Messages.app while this is running will increment the rowID.

I get to 2430 before it panics. Every time.

2430 current iMessage rowID: 23148
panic: sqlite.OpenConn: SQLITE_CANTOPEN (/Users/newhalld/Library/Messages/chat.db)

goroutine 1 [running]:
main.pollDB(0x97f, 0xc0002fd8c0, 0x28)
	/test/main.go:23 +0x2dd
main.main()
	/test/main.go:16 +0xdd

Thanks for the help!

from sqlite.

I'm totally willing to accept that I'm doing something wrong here, and really appreciate someone pointing that out (debugging my code). I'm at a loss, and the above example points out the problem. This could be a macOS issue....

from sqlite.

from sqlite.

Thanks for your response Adam, much appreciated! The snippet I provided here is a working example that reproduces the panic. I tested it on two macs before posting here. I provided it because you requested it, rightfully so. This is not my use case. :) It's just a snippet of code I wrote in a couple minutes to repro the problem I run into after my application runs for a few days.

I understand I do not need to call Reset or Finalize, these were tried just to rule out any other issues. I'm not "up" on sqlite terms, unfortunately, so I was taking stabs in the dark. I'm very stumped.

This library has served me well, and I'd love to continue using it. I'm still trying to debug this, trust me. I don't really expect anyone to debug my code for me, but it looks like a bug, so I reported it. Thank you for your patience and understanding.

EDIT: You also mentioned using PrepareTransient. I changed these two lines:

	query := db.Prep(`SELECT MAX(rowid) AS id FROM message`)
	defer query.Reset()

to this:

	query, _, _ := db.PrepareTransient(`SELECT MAX(rowid) AS id FROM message`)
	defer query.Finalize()

and the test code no longer crashes. hm.

from sqlite.

I need to slightly amend what I said before about not needing to call Reset. It is necessary on Stmts that haven't completed (i.e. returned Step with err != nil or hasRow == false) if you are returning the Stmt's associated Conn to a Pool.

from sqlite.