英文:
How to fix "database is locked" when no concurrent threads are involved? golang, sqlite3
问题
我正在运行一系列的 SQL 文件。文件列表来自同一个 sqlite3 数据库,我将对该数据库执行 SQL,因此只有一个数据库连接。如果我不终止对 SQL 文件的循环,它将从第一个文件开始返回"database is locked"错误。
以下是没有起作用的方法:
- 没有效果:从 https://stackoverflow.com/questions/32479071/sqlite3-error-database-is-locked-in-golang 添加 rows.Close()。
- 移除 sqlx 并使用 database/sql 没有改变任何事情。
- 没有效果:无论 Navicat 是打开还是关闭。
- 重启我的笔记本电脑,没有效果。
- 没有效果:将数据库导出到一个新文件,然后对新文件运行该过程。
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:
-
No effect: Adding rows.Close() from https://stackoverflow.com/questions/32479071/sqlite3-error-database-is-locked-in-golang
-
Removing sqlx and using database/sql didn't change anything.
-
No effect: Having Navicat open or closed.
-
Restarting my laptop, no effect.
-
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]))
}
}
}
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论