update returning "key not found"

[cbrake]

What version of Genji are you using?

Genji v0.11.0
Genji CLI v0.9.0

(current Genji main:HEAD as of 2021-03-08)

Also tried with v0.10.0

Does this issue reproduce with the latest release?

Yes

What did you do?

The following fails unless I remove the index on the type field.

package main

import (
	"log"

	"github.com/genjidb/genji"
)

// Node represents the state of a device. UUID is recommended
// for ID to prevent collisions is distributed instances.
type Node struct {
	ID          string
	Type        string
	Description string
}

func main() {
	db, err := genji.Open(":memory:")

	if err != nil {
		log.Fatal(err)
	}

	defer db.Close()

	err = db.Exec(`CREATE TABLE IF NOT EXISTS nodes (id TEXT PRIMARY KEY)`)
	if err != nil {
		log.Fatal("Error creating nodes table: ", err)
	}

	// if this index is removed, this program runs fine, otherwise it fails with
	// Error updating node description2: key not found
	err = db.Exec(`CREATE INDEX IF NOT EXISTS idx_nodes_type ON nodes(type)`)
	if err != nil {
		log.Fatal("Error creating idx_nodes_type: ", err)
	}

	rootNode := Node{Type: "device", ID: "1234"}

	err = db.Exec(`insert into nodes values ?`, rootNode)
	if err != nil {
		log.Fatal("Error creating root node: ", err)
	}

	err = db.Exec(`update nodes set description = ? where id = ?`,
		"hi", rootNode.ID)

	if err != nil {
		log.Fatal("Error updating node description1: ", err)
	}

	err = db.Exec(`update nodes set description = ? where id = ?`,
		"hi", rootNode.ID)

	if err != nil {
		log.Fatal("Error updating node description2: ", err)
	}

	err = db.Exec(`update nodes set description = ? where id = ?`,
		"hi", rootNode.ID)

	if err != nil {
		log.Fatal("Error updating node description3: ", err)
	}

	err = db.Exec(`update nodes set description = ? where id = ?`,
		"hi", rootNode.ID)

	if err != nil {
		log.Fatal("Error updating node description4: ", err)
	}

	log.Println("All done :-)")
}

Also, can use: https://play.golang.org/p/G97cg9Usw9f

What did you expect to see?

[cbrake@mars go]$ go run cmd/genji-test9/main.go 
2021/02/27 15:01:56 All done :-)

What did you see instead?

[cbrake@mars go]$ go run cmd/genji-test9/main.go 
2021/02/27 15:01:22 Error updating node description2: key not found
exit status 1

What Go version and environment are you using?

go version go1.15.6 linux/amd64

go env Output

$ go env
GO111MODULE=""
GOARCH="amd64"
GOBIN=""
GOCACHE="/home/cbrake/.cache/go-build"
GOENV="/home/cbrake/.config/go/env"
GOEXE=""
GOFLAGS=""
GOHOSTARCH="amd64"
GOHOSTOS="linux"
GOINSECURE=""
GOMODCACHE="/home/cbrake/go/pkg/mod"
GONOPROXY=""
GONOSUMDB=""
GOOS="linux"
GOPATH="/home/cbrake/go"
GOPRIVATE=""
GOPROXY="https://proxy.golang.org,direct"
GOROOT="/usr/lib/go"
GOSUMDB="sum.golang.org"
GOTMPDIR=""
GOTOOLDIR="/usr/lib/go/pkg/tool/linux_amd64"
GCCGO="gccgo"
AR="ar"
CC="gcc"
CXX="g++"
CGO_ENABLED="1"
GOMOD="/scratch/simpleiot/go/go.mod"
CGO_CFLAGS="-g -O2"
CGO_CPPFLAGS=""
CGO_CXXFLAGS="-g -O2"
CGO_FFLAGS="-g -O2"
CGO_LDFLAGS="-g -O2"
PKG_CONFIG="pkg-config"
GOGCCFLAGS="-fPIC -m64 -pthread -fmessage-length=0 -fdebug-prefix-map=/tmp/go-build316596558=/tmp/go-build -gno-record-gcc-switches"

[cbrake]

It is interesting that running the following code works the first time I run it:

        err = db.Exec(`update nodes set description = ? where id = ?`,
		"hi", rootNode.ID)

	if err != nil {
		log.Fatal("Error updating node description1: ", err)
	}

But, when I run the exact same thing again, it fails:

	err = db.Exec(`update nodes set description = ? where id = ?`,
		"hi", rootNode.ID)

	if err != nil {
		log.Fatal("Error updating node description2: ", err)
	}

I then modified the code to stop after the first run, saved off the db file, and then the compared it to the db file after a failed run, and there is no difference in the db file. So this seems to indicate it is not a problem in the db data, but rather the code that runs the update. Is there caching or some state that would cause it to work the first time but not the second?

Or, am I doing something wrong?

For reference, my test program: https://github.com/simpleiot/simpleiot/blob/feature-genji-update/cmd/genji-test9/main.go

[asdine]

Thanks for the very detailed issue @cbrake, this bug was tricky and your examples helped a lot!

[cbrake]

Thanks -- seems to be working now. Yes that does look a tricky one -- nice work!

SIOT is now using latest genji

[asdine]

@cbrake Awesome! Please keep in mind that next release will contain a breaking change in the storage system, which will render previous databases incompatible.

[cbrake]

@asdine good to know. SIOT is currently in heavy development and I'm not currently deploying anything, so I'll just keep tracking main until I get to that point. I should add a note to the documentation about this for other users.