Foreign key constraints break auto-incrementing ids in memory mode

[seanlaff]

I receive a

Error 1062 (HY000): duplicate primary key given: [1]

error when doing these queries in memory mode

CREATE TABLE table1 (
	id int NOT NULL AUTO_INCREMENT,
	name text,
	PRIMARY KEY (id)
);

CREATE TABLE table2 (
	id int NOT NULL AUTO_INCREMENT,
	name text,
	fk int,
	PRIMARY KEY (id),
	CONSTRAINT myConstraint FOREIGN KEY (fk) REFERENCES table1 (id)
)

INSERT INTO table1 (name) VALUES ('tbl1 row 1');
INSERT INTO table1 (name) VALUES ('tbl1 row 2');

If I remove the CONSTRAINT myConstraint FOREIGN KEY (fk) REFERENCES table1 (id). I don't get any errors.

Full go code reproduction:

package main

import (
	"context"
	"database/sql"
	"fmt"
	"testing"

	sqle "github.com/dolthub/go-mysql-server"
	gmsSql "github.com/dolthub/go-mysql-server/sql"
	msql "github.com/dolthub/go-mysql-server/sql"
	"github.com/dolthub/go-mysql-server/sql/mysql_db"
	vsql "github.com/dolthub/vitess/go/mysql"

	"github.com/dolthub/go-mysql-server/memory"
	"github.com/dolthub/go-mysql-server/server"

	"github.com/go-sql-driver/mysql"
	_ "github.com/go-sql-driver/mysql"
)

var (
	dbName  = "mydb"
	address = "localhost"
	port    = 3306
)


func TestBadAutoIncrement(t *testing.T) {
	mdb := memory.NewDatabase(dbName)
	mdb.EnablePrimaryKeyIndexes()
	pro := memory.NewDBProvider(mdb)
	engine := sqle.NewDefault(pro)

	config := server.Config{
		Protocol: "tcp",
		Address:  fmt.Sprintf("%s:%d", address, port),
	}
	sessionBuilder := func(ctx context.Context, c *vsql.Conn, addr string) (gmsSql.Session, error) {
		host := ""
		user := ""
		mysqlConnectionUser, ok := c.UserData.(mysql_db.MysqlConnectionUser)
		if ok {
			host = mysqlConnectionUser.Host
			user = mysqlConnectionUser.User
		}
		client := gmsSql.Client{Address: host, User: user, Capabilities: c.Capabilities}
		return memory.NewSession(msql.NewBaseSessionWithClientServer(addr, client, c.ConnectionID), pro), nil
	}
	s, err := server.NewServer(config, engine, sessionBuilder, nil)
	if err != nil {
		panic(err)
	}
	go func() {
		if err = s.Start(); err != nil {
			panic(err)
		}
	}()

	db, err := sql.Open("mysql", "/mydb")
	if err != nil {
		panic(err)
	}
	_, err = db.Exec(`
CREATE TABLE table1 (
	id int NOT NULL AUTO_INCREMENT,
	name text,
	PRIMARY KEY (id)
)
	`)
	if err != nil {
		panic(err)
	}
	_, err = db.Exec(`
CREATE TABLE table2 (
	id int NOT NULL AUTO_INCREMENT,
	name text,
	fk int,
	PRIMARY KEY (id),
	CONSTRAINT myConstraint FOREIGN KEY (fk) REFERENCES table1 (id)
)
	`)
	if err != nil {
		panic(err)
	}
	_, err = db.Exec("INSERT INTO table1 (name) VALUES ('tbl1 row 1')")
	if err != nil {
		panic(err)
	}
	_, err = db.Exec("INSERT INTO table1 (name) VALUES ('tbl1 row 2')")
	if err != nil {
		panic(err)
	}
}

It seems like when foreign keys are involved, the auto-increment ids are managed differently. I can look into this if someone could point me in the right direction.

[max-hoffman]

Thanks for the good repro @seanlaff. We had a bug in the memory state session resetting #2353

There is some table metadata stored in the session state, and during edits we expect the references to this metadata between the session, table, and edit accumulator to be kept in sync. The presence of a foreign key triggers a codepath that caches an edit accumulator with a stale reference to the auto increment id during analysis that should be reset during execution.

[seanlaff]

Ah I was hunting this down all afternoon- I knew it had to be a oneliner somewhere but couldn't find it 😅. Thanks for fixing it!

[max-hoffman]

This was a tricky one! Thanks for taking the time to flag and debug