Open
Description
Have 3 tables, where cascade delete relationships look like this:
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 commentedon Mar 22, 2024
We don't heavily use the in-memory implementation. We would welcome a contribution here.