Skip to content

MemoryDB: Deleting rows silently fails if there are multiple CASCADING constraints pointing to the same parent #2405

Open
@seanlaff

Description

@seanlaff

Have 3 tables, where cascade delete relationships look like this:

Loading
  graph TD;
      table2-->table1;
      table3-->table2;
      table3-->table1;

Delete a row from table1. It won't actually be deleted (a subsequent SELECT still returns it).

Here's a go reproduction. If you remove either constraint in table3, the bug doesn't happen- both must be present to see the bad behavior.

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"
	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"
)

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

func TestBadCascade(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.(msql.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,
			PRIMARY KEY (id)
		)
	`)
	_, err = db.Exec(`
		CREATE TABLE table2	(
			id int NOT NULL AUTO_INCREMENT,
			table1Id int NOT NULL,
			PRIMARY KEY (id),
			CONSTRAINT t2tot1 FOREIGN KEY (table1Id) REFERENCES table1 (id) ON DELETE CASCADE
		)
	`)
	if err != nil {
		panic(err)
	}
	_, err = db.Exec(`
		CREATE TABLE table3	(
			id int NOT NULL AUTO_INCREMENT,
			table1Id int NOT NULL,
			table2Id int NOT NULL,
			PRIMARY KEY (id),
			CONSTRAINT t3tot1 FOREIGN KEY (table1Id) REFERENCES table1 (id) ON DELETE CASCADE,
			CONSTRAINT t3tot2 FOREIGN KEY (table2Id) REFERENCES table2 (id) ON DELETE CASCADE
		)
	`)
	if err != nil {
		panic(err)
	}
	_, err = db.Exec("INSERT INTO table1 VALUES ()")
	if err != nil {
		panic(err)
	}
	_, err = db.Exec("DELETE FROM table1 WHERE id = 1")
	if err != nil {
		panic(err)
	}
	row := db.QueryRow("SELECT count(*) AS Count FROM table1")
	var dst uint64
	err = row.Scan(&dst)
	if err != nil {
		panic(err)
	}
	if dst != 0 {
		panic("found non-zero rows")
	}
}

Activity

timsehn

timsehn commented on Mar 22, 2024

@timsehn
SponsorContributor

We don't heavily use the in-memory implementation. We would welcome a contribution here.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Metadata

Metadata

Assignees

No one assigned

    Type

    No type

    Projects

    No projects

    Milestone

    No milestone

    Relationships

    None yet

    Development

    No branches or pull requests

      Participants

      @seanlaff@timsehn@coffeegoddd

      Issue actions

        MemoryDB: Deleting rows silently fails if there are multiple CASCADING constraints pointing to the same parent · Issue #2405 · dolthub/go-mysql-server