SQL查询挂起或崩溃

huangapple
go评论174阅读模式
英文:

SQL query hangs or crashes

问题

以下是Go 1.16.6上的代码，最后一个Exec调用hang住（如果从不同的goroutine调用相同的函数，则会崩溃）。

两个库 "github.com/mattn/go-sqlite3" 和 "modernc.org/sqlite" 给出相同的结果

  1. package main
  3. import (
  4.     "os"
  5.     "testing"
  7.     "database/sql"
  9.     _ "github.com/mattn/go-sqlite3"
  10.     //  _ "modernc.org/sqlite"
  11. )
  13. func Test_Bug3(t *testing.T) {
  14.     DBPath := "test.db"
  15.     os.Remove(DBPath)
  16.     DB, err := sql.Open("sqlite3", DBPath)
  17.     if err != nil {
  18.         t.Fatalf("%s: %v", DBPath, err)
  19.     }
  20.     if _, err := DB.Exec(`CREATE TABLE IF NOT EXISTS verdictcache (sha1 text);`); err != nil {
  21.         t.Fatalf("%s: %v", DBPath, err)
  22.     }
  23.     _, err = DB.Exec("INSERT OR REPLACE INTO verdictcache (sha1) VALUES ($1)", "a")
  24.     if err != nil {
  25.         t.Fatalf("%s: %v", DBPath, err)
  26.     }
  27.     _, err = DB.Query("SELECT * FROM verdictcache WHERE sha1=$1", "a")
  28.     if err != nil {
  29.         t.Fatalf("%s: %v", DBPath, err)
  30.     }
  31.     _, err = DB.Exec("INSERT OR REPLACE INTO verdictcache (sha1) VALUES ($1)", "b")
  32.     if err != nil {
  33.         t.Fatalf("%s: %v", DBPath, err)
  34.     }
  35. }
英文:

Following code on Go 1.16.6 hangs on last Exec call (or crashes if same functions are called from different goroutines)

Both libraries "github.com/mattn/go-sqlite3" and "modernc.org/sqlite" give same results

  1. package main
  3. import (
  4.     "os"
  5.     "testing"
  7.     "database/sql"
  9.     _ "github.com/mattn/go-sqlite3"
  10.     //  _ "modernc.org/sqlite"
  11. )
  13. func Test_Bug3(t *testing.T) {
  14.     DBPath := "test.db"
  15.     os.Remove(DBPath)
  16.     DB, err := sql.Open("sqlite3", DBPath)
  17.     if err != nil {
  18.         t.Fatalf("%s: %v", DBPath, err)
  19.     }
  20.     if _, err := DB.Exec(`CREATE TABLE IF NOT EXISTS verdictcache (sha1 text);`); err != nil {
  21.         t.Fatalf("%s: %v", DBPath, err)
  22.     }
  23.     _, err = DB.Exec("INSERT OR REPLACE INTO verdictcache (sha1) VALUES ($1)", "a")
  24.     if err != nil {
  25.         t.Fatalf("%s: %v", DBPath, err)
  26.     }
  27.     _, err = DB.Query("SELECT * FROM verdictcache WHERE sha1=$1", "a")
  28.     if err != nil {
  29.         t.Fatalf("%s: %v", DBPath, err)
  30.     }
  31.     _, err = DB.Exec("INSERT OR REPLACE INTO verdictcache (sha1) VALUES ($1)", "b")
  32.     if err != nil {
  33.         t.Fatalf("%s: %v", DBPath, err)
  34.     }
  35. }

答案1

得分: 3

发生的情况几乎可以确定是您没有调用Close()（或以其他方式消耗了结果）DB.Query(...)返回的结果。

尝试：

  1. func Test_Bug3(t *testing.T) {
  2.     DBPath := "test.db"
  3.     os.Remove(DBPath)
  4.     DB, err := sql.Open("sqlite3", DBPath)
  5.     if err != nil {
  6.         t.Fatalf("%s: %v", DBPath, err)
  7.     }
  8.     if _, err := DB.Exec(`CREATE TABLE IF NOT EXISTS verdictcache (sha1 text);`); err != nil {
  9.         t.Fatalf("%s: %v", DBPath, err)
  10.     }
  11.     _, err = DB.Exec("INSERT OR REPLACE INTO verdictcache (sha1) VALUES ($1)", "a")
  12.     if err != nil {
  13.         t.Fatalf("%s: %v", DBPath, err)
  14.     }
  15.     res, err := DB.Query("SELECT * FROM verdictcache WHERE sha1=$1", "a")
  16.     if err != nil {
  17.         t.Fatalf("%s: %v", DBPath, err)
  18.     }
  19.     res.Close()
  20.     _, err = DB.Exec("INSERT OR REPLACE INTO verdictcache (sha1) VALUES ($1)", "b")
  21.     if err != nil {
  22.         t.Fatalf("%s: %v", DBPath, err)
  23.     }
  24. }
英文:

What is happening is almost certainly that you did not call Close() (or otherwise consumed the rows) on the result returned by DB.Query(...).

Try:

  1. func Test_Bug3(t *testing.T) {
  2.     DBPath := "test.db"
  3.     os.Remove(DBPath)
  4.     DB, err := sql.Open("sqlite3", DBPath)
  5.     if err != nil {
  6.         t.Fatalf("%s: %v", DBPath, err)
  7.     }
  8.     if _, err := DB.Exec(`CREATE TABLE IF NOT EXISTS verdictcache (sha1 text);`); err != nil {
  9.         t.Fatalf("%s: %v", DBPath, err)
  10.     }
  11.     _, err = DB.Exec("INSERT OR REPLACE INTO verdictcache (sha1) VALUES ($1)", "a")
  12.     if err != nil {
  13.         t.Fatalf("%s: %v", DBPath, err)
  14.     }
  15.     res, err := DB.Query("SELECT * FROM verdictcache WHERE sha1=$1", "a")
  16.     if err != nil {
  17.         t.Fatalf("%s: %v", DBPath, err)
  18.     }
  19.     res.Close()
  20.     _, err = DB.Exec("INSERT OR REPLACE INTO verdictcache (sha1) VALUES ($1)", "b")
  21.     if err != nil {
  22.         t.Fatalf("%s: %v", DBPath, err)
  23.     }
  24. }
  26. </details>

huangapple
  • 本文由 发表于 2021年8月3日 05:44:01
  • 转载请务必保留本文链接：https://go.coder-hub.com/68628246.html
  • go
  • sqlite
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定