Hi,

I am getting "No such table" when I try to access attach database in 2 thread. Basically I have following setup: ```

1. main thread open database A, then attach database B
2. main thread starts thread #1 and #2
3. both thread #1 and #2 run query like "select <something> from A.tableA join B.tableB where ....
4. one thread will fail with "no such table" error if another thread is fetching data from query at the same time. ```

Following golang code can reproduce the problem:

package main

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

	_ "github.com/mattn/go-sqlite3"
)


var (
	db     *sql.DB
)

const (
	queryStr = `select student.name, grade, age from student join db2.age on student.name=db2.age.name `
)

func attachDB2(db2Path string) error {
	stmt, err := db.Prepare("ATTACH DATABASE ? as db2")
	if err != nil {
		return errors.New("attachDB2: " + err.Error())
	}
	if _, err := stmt.Exec("file:" + db2Path + "?mode=rwc"); err != nil {
		return errors.New("attachDB2: " + err.Error())
	}

	return nil
}

func setup() {
	os.Remove("db1.sq3")
	os.Remove("db2.sq3")

	var err error

	if db, err = sql.Open("sqlite3", "db1.sq3"); err != nil {
		fmt.Printf("unable to open db, error: %v\n", err)
		return
	}

	if _, err := db.Exec(`CREATE TABLE IF NOT EXISTS "student" (
		"name"	TEXT,
		"grade"	INTEGER
		)`); err != nil {
		fmt.Printf("unable to open db, error: %v\n", err)
		return
	}

	if _, err := db.Exec(`insert or replace into "student" ("name", "grade") values ("a", 99)`); err != nil {
		fmt.Printf("unable to open db, error: %v\n", err)
		return
	}


	if err := attachDB2("db2.sq3"); err != nil {
		fmt.Printf("unable to attach favs db, error: %v\n", err)
		return
	}

	db.Exec(`CREATE TABLE db2.age ("name"	TEXT,"age"	INTEGER)`)

	if _, err := db.Exec(`insert or replace into db2.age ("name", "age") values ("a", 1)`); err != nil {
		fmt.Printf("unable to open db, error: %v\n", err)
		return
	}
}

func main() {
	setup()

	querych1 := make(chan bool, 1)
	querych2 := make(chan bool, 1)
	completeCh := make(chan bool, 1)


	go queryAsync(querych1, completeCh)

	<- querych1
	go queryAsync(querych2, completeCh)

	<- querych2
	completeCh <- true
	time.Sleep(8 * time.Second)

}

func queryAsync( queryStartCh chan<- bool, queryCompleteCh <-chan bool) {
	stmt, err := db.Prepare(queryStr)
	if err != nil {
		fmt.Printf("unable to prepare query %s, err:%v", queryStr, err)
		return
	}
	fmt.Printf("prepare statement success\n")
	rows, err := stmt.Query()
	if err != nil {
		fmt.Printf("unable to run query %s, err:%v", queryStr, err)
		return
	}
	fmt.Printf("run statement success\n")
	defer rows.Close()
	rows.Next()
	queryStartCh <- true
	<-queryCompleteCh
}

I google the error, someone reported similar problem but it is on memory database, while I am using file database: https://github.com/mattn/go-sqlite3/issues/511

Is there anything wrong in my code? or it is just a limitation of sqlite with attached database?

Thanks a lot in advanced.

Tong

Is the golang sqlite3 library you use compiled to be threadsafe?

If not, you can't use the same connection from different threads at the same time. Give each thread its own connection.

-- 
Regards,
Kees Nuyt

I see not an error.

I would suspect tht this is a "go" shim thing. You should follow up with whomever wrote that shim as it apparently has problems, either functionally or conceptually.

GO apparently runs crap "asynchronously" on a "brand spanking new connection", not on the connection which you have created. Therefore, "stuff" that you have done to the connection (such as in this case apparently attaching an additional schema) have not been done on the "brand spanking new connection spun up for the purpose of running your query".

This is apparently by design. If you do not like the design, then change it. Unfortunately this is a go-shim error and you need to address and resolve the issue there.

There was a thread here along the same lines (go creating separate connections "at will") somewhen in the last year or so... can't remember whether there was an easy solution or not (and can't easily search at the moment).

Pretty sure it is compiled to be threadsafe. I debug into the code, in github.com/mattn/go-sqlite3@v1.14.10/sqlite3.go, it has following check:

func (d *SQLiteDriver) Open(dsn string) (driver.Conn, error) {
	if C.sqlite3_threadsafe() == 0 {
		return nil, errors.New("sqlite library was not compiled for thread-safe operation")
	}