bugs when use attach database

[gaorongfu]

when I attach another database file, it usually wrong when operating tables in the attach database.
For example, I use this command to attach a db:
attach database 'database/test4.db' as 'test4'
Then query from tables:
select * from test4.app_users
I restart my program a lot times to test. Sometimes I got error: no such table: test4.app_user. But sometimes it works!

I wonder if there some bugs with this sqlite3 driver about attach database.

[gjrtimmer]

Can you provide a test which will reproduce this.

[andreivirtosu]

I have same problem

[gjrtimmer]

Can you provide a func main() with a test to reproduce so we can run some tests as well to research this. Thanks I'm advance.

[andreivirtosu]

I fixed it with the following hook:

sql.Register("sqlite3_hooked",
			&sqlite3.SQLiteDriver{
				ConnectHook: func(conn *sqlite3.SQLiteConn) error {
					conn.Exec("ATTACH DATABASE '"+db2+"' AS 'db2';", nil)
					return nil
				},
			})
db1, err := sql.Open("sqlite3_hooked", db1Path+"?mode=memory&cache=shared&mode=rwc")

[kac-]

I guess this issue is related to above.
If sql.Rows is not closed before next query then driver returns no such table on next query.
Issue exists only for in-memory storage, same test for file backed db runs fine.

go-sqlite3:  3.23.1 3023001 2018-04-10 17:39:29
go        :  go1.10.2 linux/amd64

package model

import (
	"database/sql"
	sq3 "github.com/mattn/go-sqlite3"
	"testing"
)

func TestIt(t *testing.T) {
	t.Log(sq3.Version())

	db, err := sql.Open("sqlite3", ":memory:")
	if err != nil {
		t.Fatal(err)
	}
	defer db.Close()
	_, err = db.Exec(`
		CREATE TABLE foo (id integer PRIMARY KEY, name text NOT NULL);
		`)
	if err != nil {
		t.Fatal(err)
	}
	if _, err = db.Exec("INSERT INTO foo (name) VALUES (?)", "bar"); err != nil {
		t.Fatal(err)
	}
	rows, err := db.Query("SELECT * FROM foo LIMIT 1")
	if err != nil {
		t.Fatal(err)
	}
	if rows.Next() {
		var id int64
		var name string
		rows.Scan(&id, &name)
		t.Log(id, name)
	}

	// this one throws "no such table: foo"
	// problem can be solved by previously calling
	// > rows.Close()
	// or indirectly
	// > for rows.Next(){...}
	rows, err = db.Query("SELECT * FROM foo")
	if err != nil {
		t.Fatal(err)
	}
}

[gjrtimmer]

@kac- Is this something we should investigate or should this be filled under programmers error ?

[kac-]

@gjrtimmer I think that this inconsistency in behaviour for memory and file storage can be misleading. It took me more than hour to catch it in my simple code (I was investigating custom ~DAO most of time and searched for some typo). Example codes have defer rows.Close() in most places "saying" "you should close it somewhere" and it's not true because you should close it immediately. The best would be to return an error explicitly pointing to programmers fault, if it's not too obstructive for drivers code/performance ofc.
I have to check how other sql drivers handle this case.

---

Edit: https://github.com/go-sql-driver/mysql passes this test without an error

[kac-]

@gjrtimmer looking at it from a perspective I think it should be filled under programmers fault.
Issue is covered under https://github.com/mattn/go-sqlite3#faq but beside Why is it racy if I use a sql.Open("sqlite3", ":memory:") database? I think it would be really nice to have something like Why I'm getting "no such table" error?.

---

Maybe worth noting: for both Python's and Ruby's sqlite drivers there is no no such table and queries results are fine but they throw unable to close due to unfinalized statements or unfinished backups on connection close.

[gjrtimmer]

@kac- will update README

[ko80]

@kac- THANK YOU SO MUCH! I've found a part of code where I had forgotten to do rows.Close(), fixed that and now it works with no issues!

Background: I'm using sqlite3 in-memory DB to simulate an Oracle DB in integration test cases. To make it possible, I added custom processing of specific Oracle functions and statements via ConnectHook/RegisterFunc and regular expressions.

[ajbouh]

Another solution, at least for my use case, appears to be:

db.SetMaxOpenConns(1)