How to fix "database is locked" when no concurrent threads are involved? golang, sqlite3

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

How to fix "database is locked" when no concurrent threads are involved? golang, sqlite3

问题

我正在运行一系列的 SQL 文件。文件列表来自同一个 sqlite3 数据库,我将对该数据库执行 SQL,因此只有一个数据库连接。如果我不终止对 SQL 文件的循环,它将从第一个文件开始返回"database is locked"错误。

以下是没有起作用的方法:

  1. 没有效果:从 https://stackoverflow.com/questions/32479071/sqlite3-error-database-is-locked-in-golang 添加 rows.Close()。
  2. 移除 sqlx 并使用 database/sql 没有改变任何事情。
  3. 没有效果:无论 Navicat 是打开还是关闭。
  4. 重启我的笔记本电脑,没有效果。
  5. 没有效果:将数据库导出到一个新文件,然后对新文件运行该过程。
package main

import (
	"database/sql"
	"fmt"
	"io/ioutil"
	"os"

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

func main() {
	dbPath := "/Users/darianhickman/Documents/wc_study/history.db"

	db, err := sql.Open("sqlite3", dbPath)
	if err != nil {
		println("db open failed.", err)
		os.Exit(-1)
	}
	defer db.Close()
	// qry := `select list_folder||'/'|| sql_file as script from run_list where 'order' > 0 ORDER BY 'order'; `
	qry := `select list_folder||'/'|| sql_file as script from run_list 
	where run_order > 0 
	ORDER BY run_order;`

	scripts, err := db.Query(qry)

	if err != nil {
		println("query failed ", qry)
		os.Exit(-1)
	}
	defer scripts.Close()

	var file string
	for scripts.Next() {
		err = scripts.Scan(&file)
		if err != nil {
			println(err)
		}
		println(file[len(file)-80:])
		sqlScript, err := ioutil.ReadFile(file)

		if err != nil {
			println("reading script file failed\n", file)
		}
		if _, err := db.Exec(string(sqlScript)); err != nil {
			fmt.Println(string(sqlScript[:80]))
			fmt.Println(err)

		} else {
			fmt.Println("Success ", string(sqlScript[:80]))
		}
	}

}

希望对你有帮助!

英文:

I'm running a list of sql files. The list of files comes from the same sqlite3 db that I'm going to execute sql against, hence there's only one db connection. If I don't kill the loop over sql files it will return "database is locked" error for every file starting with the first.
Things that didn't work:

  1. No effect: Adding rows.Close() from https://stackoverflow.com/questions/32479071/sqlite3-error-database-is-locked-in-golang

  2. Removing sqlx and using database/sql didn't change anything.

  3. No effect: Having Navicat open or closed.

  4. Restarting my laptop, no effect.

  5. No effect: Dumping database to a new file then running the process against the new file.

     package main
    
     import (
     "database/sql"
     "fmt"
     "io/ioutil"
     "os"
    
     _ "github.com/mattn/go-sqlite3"
     )
    
     func main() {
     dbPath := "/Users/darianhickman/Documents/wc_study/history.db"
    
     db, err := sql.Open("sqlite3", dbPath)
     if err != nil {
     	println("db open failed.", err)
     	os.Exit(-1)
     }
     defer db.Close()
     // qry := `select list_folder||'/'|| sql_file as script from run_list where 'order' > 0 ORDER BY 'order'; `
     qry := `select list_folder||'/'|| sql_file as script from run_list 
     where run_order > 0 
     ORDER BY run_order;`
    
     scripts, err := db.Query(qry)
    
     if err != nil {
     	println("query failed ", qry)
     	os.Exit(-1)
     }
     defer scripts.Close()
    
     var file string
     for scripts.Next() {
     	err = scripts.Scan(&file)
     	if err != nil {
     		println(err)
     	}
     	println(file[len(file)-80:])
     	sqlScript, err := ioutil.ReadFile(file)
    
     	if err != nil {
     		println("reading script file failed\n", file)
     	}
     	if _, err := db.Exec(string(sqlScript)); err != nil {
     		fmt.Println(string(sqlScript[:80]))
     		fmt.Println(err)
    
     	} else {
     		fmt.Println("Success ", string(sqlScript[:80]))
     	}
     }
    

    }

答案1

得分: 2

根据官方文档

当你遇到数据库被锁定的情况时,请使用以下选项。

在 DSN 中添加:cache=shared

示例:

  db, err := sql.Open("sqlite3", "file:locked.sqlite?cache=shared")

接下来,请将 SQL 包的数据库连接数设置为 1:

  db.SetMaxOpenConns(1)
英文:

As per the official documentation:

When you get a database is locked, please use the following options.

Add to DSN: cache=shared

Example:

  db, err := sql.Open("sqlite3", "file:locked.sqlite?cache=shared")

Next, please set the database connections of the SQL package to 1:

  db.SetMaxOpenConns(1)

答案2

得分: 0

根据@kostix的观察,我放弃了在执行脚本时读取脚本列表,这样做起作用了。

package main

import (
	"fmt"
	"io/ioutil"

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

func main() {
	// dbPath := "file:/Users/darianhickman/Documents/wc_study/history.db?cache=shared"
	dbPath := "file:/Users/darianhickman/Documents/wc_study/history.db"
	db := sqlx.MustConnect("sqlite3", dbPath)
	defer db.Close()
	// db.SetMaxOpenConns(1)

	// qry := `select list_folder||'/'|| sql_file as script from run_list where 'order' > 0 ORDER BY 'order'; `
	qry := `select list_folder||'/'|| sql_file as script from run_list 
	where run_order > 0 
	ORDER BY run_order;`

	scripts, err := db.Query(qry)
	if err != nil {
		println("script qry failed ", qry)
	}
	defer scripts.Close()
	var files []string
	var file string

	// 在读取脚本查询时放弃执行SQL脚本。
	for scripts.Next() {
		err = scripts.Scan(&file)
		if err != nil {
			println(err)
		} else {
			files = append(files, file)
		}
	}

	for _, file := range files {

		println(file[len(file)-80:])
		sqlScript, err := ioutil.ReadFile(file)
		if err != nil {
			println("reading script file failed\n", file)
		}
		if _, err := db.Exec(string(sqlScript)); err != nil {
			fmt.Println(string(sqlScript[:80]))
			fmt.Println(err)

		} else {
			fmt.Println("Success ", string(sqlScript[:80]))
		}
	}

}
英文:

Per @kostix observation, I gave up on reading the list of scripts while executing the scripts and that worked.

package main
import (
"fmt"
"io/ioutil"
"github.com/jmoiron/sqlx"
_ "github.com/mattn/go-sqlite3"
)
func main() {
// dbPath := "file:/Users/darianhickman/Documents/wc_study/history.db?cache=shared"
dbPath := "file:/Users/darianhickman/Documents/wc_study/history.db"
db := sqlx.MustConnect("sqlite3", dbPath)
defer db.Close()
// db.SetMaxOpenConns(1)
// qry := `select list_folder||'/'|| sql_file as script from run_list where 'order' > 0 ORDER BY 'order'; `
qry := `select list_folder||'/'|| sql_file as script from run_list 
where run_order > 0 
ORDER BY run_order;`
scripts, err := db.Query(qry)
if err != nil {
println("script qry failed ", qry)
}
defer scripts.Close()
var files []string
var file string
// Giving up on executing sql scripts while reading the query of scripts.
for scripts.Next() {
err = scripts.Scan(&file)
if err != nil {
println(err)
} else {
files = append(files, file)
}
}
for _, file := range files {
println(file[len(file)-80:])
sqlScript, err := ioutil.ReadFile(file)
if err != nil {
println("reading script file failed\n", file)
}
if _, err := db.Exec(string(sqlScript)); err != nil {
fmt.Println(string(sqlScript[:80]))
fmt.Println(err)
} else {
fmt.Println("Success ", string(sqlScript[:80]))
}
}
}

huangapple
  • 本文由 发表于 2022年7月9日 01:46:01
  • 转载请务必保留本文链接:https://go.coder-hub.com/72915138.html
匿名

发表评论

匿名网友

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

确定