Intermittent table missing error in SQLite: Memory: Database

php editor Banana brings you solutions to intermittent table loss errors in SQLite. When we use a SQLite database, we may encounter memory errors or table loss. This error is usually caused by incorrect database operations or memory problems. In order to solve this problem, we can take some measures, such as optimizing query statements, increasing memory limits, etc. In this article, we'll detail how to troubleshoot and resolve this issue to ensure proper database operation.

Question content

We use SQLite 3.39.4 in Go via github.com/mattn/go-sqlite3 v1.14.16. The connection string is ":memory:?cache=shared&mode=rwc&_mutex=no&_journal=WAL&_sync=NORMAL"

We occasionally see the error "No such table: configuration" in our tests. What's confusing is that the query in question has been used successfully in the test case, and the table subsequently shows up in queries to sqlite_master , both in the database connection and in the transaction object that throws the error. However, once an error occurs, the query no longer succeeds.

I realize this is a rather vague question, but can someone at least suggest where to look? Database connections always have the same pointer value.

renew:

On my second try, I almostcould reproduce the problem in this SSCCE:

package main

import (
    "database/sql"
    "fmt"
    _ "github.com/mattn/go-sqlite3"
    "os"
)

func main() {
    os.Remove("example.db")
    db, err := sql.Open("sqlite3", ":memory:")
    if err != nil {
        panic(err)
    }

    for _, s := range []string{
        "CREATE TABLE if not exists Configuration (" +
            "`id` varchar(1024) NOT NULL," +
            "`body` varchar(10240) DEFAULT NULL, " +
            "PRIMARY KEY (id) " +
            ")",
        "INSERT INTO Configuration (id, body) VALUES ('some-unique-value', 'another-unique-value')",
    } {
        _, err = db.Exec(s)
        if err != nil {
            panic(err)
        }
    }

    for i := 0; i < 10; i++ {
        tx, err := db.Begin()
        if err != nil {
            panic(err)
        }

        q, err := tx.Prepare("select Configuration.id, Configuration.body \n\t\t\tfrom Configuration\n\t\t\tWHERE Configuration.id = ? ")
        fmt.Println(i, err)
        if q != nil {
            _ = q.Close()
        }

        fmt.Println("tx:")
        showTables(tx)

        fmt.Println("db:")
        showTables(db)

        tx.Commit()
    }
}

func showTables(db interface {
    Query(query string, args ...interface{}) (*sql.Rows, error)
}) {
    r, err := db.Query("SELECT name FROM sqlite_master")
    if err != nil {
        panic(err)
    }
    for r.Next() {
        var name string
        _ = r.Scan(&name)
        fmt.Println(name)
    }
}

It is different from the actual problem because showTables does not show the tables in SSCCE but it does in the actual test. This example shows the problem of using :memory: instead of example.db, but only if the transaction is not closed. Is this a known or expected behavior of in-memory databases?

Solution

Go database/sql Implicitly uses the connection pool, but :memory: The database opens their connections by defaultprivate. Please refer to: https://www.php.cn/link/d346256ad566cf97801e5cecc45a2557

Since multiple SQLite connections are created/closed behind the scenes, you don't really know which connection is running each statement and against which database, so it's natural that some connections will see certain data and others won't can not see.

One way to share the connection is to use cache=shared, as you are trying. But SQLite requires it to be specified as a URI: file::memory:?cache=shared (file: is important).

A more reliable way to open a shared memory database is:

• file:memdb1?mode=memory&cache=shared (named memdb1)
• file:/memdb1?vfs=memdb (named /memdb1 and use memdb VFS)

Your other parameters may be unnecessary or even harmful (for in-memory databases).

The above is the detailed content of Intermittent table missing error in SQLite: Memory: Database. For more information, please follow other related articles on the PHP Chinese website!