Overview of the Issue
This bug was found by the fuzzer that is added in #13980.
We are running a DELETE query via prepared statements on table fk_t11 and it causes the data in fk_t12 to mismatch -
--- FAIL: TestFkFuzzTest/Unsharded_-_Single_Thread_-_Balanced_Inserts_and_Deletes_Prepared_-_true (60.23s)
E0920 13:24:55.391476 15896 fk_fuzz_test.go:505] Failing query - [prepare stmt_del from 'delete from fk_t11 where id = ?' SET @id = 8 execute stmt_del using @id]
mysql.go:223: Query (SELECT * FROM fk_t12 ORDER BY id) results mismatched.
Vitess Results:
[INT64(2) VARCHAR("1")]
[INT64(7) VARCHAR("1")]
Vitess RowsAffected: 0
MySQL Results:
[INT64(2) VARCHAR("1")]
[INT64(7) VARCHAR("1")]
[INT64(9) VARCHAR("3")]
MySQL RowsAffected: 0The schema we are using looks like -
/*
* fk_t10
* │
* On Delete Cascade │
* On Update Cascade │
* │
* ▼
* fk_t11──────────────────┐
* │ │
* │ │ On Delete Restrict
* On Delete Cascade │ │ On Update Restrict
* On Update Cascade │ │
* │ │
* ▼ ▼
* fk_t12 fk_t13
*/
Initially the state of the database looks like -
E0920 13:23:55.596002 15896 fk_fuzz_test.go:224] MySQL data for fk_t10 -
[[INT64(9) VARCHAR("3")] [INT64(10) VARCHAR("1")]]
E0920 13:23:55.596005 15896 fk_fuzz_test.go:224] MySQL data for fk_t11 -
[[INT64(3) VARCHAR("1")] [INT64(6) NULL] [INT64(8) VARCHAR("3")] [INT64(10) VARCHAR("3")]]
E0920 13:23:55.596009 15896 fk_fuzz_test.go:224] MySQL data for fk_t12 -
[[INT64(2) VARCHAR("1")] [INT64(7) VARCHAR("1")] [INT64(9) VARCHAR("3")]]
E0920 13:23:55.596013 15896 fk_fuzz_test.go:224] MySQL data for fk_t13 -
[[INT64(6) VARCHAR("3")] [INT64(8) VARCHAR("3")] [INT64(9) VARCHAR("3")]]
On executing the query, we expect the query to fail because deleting the row in fk_t11 with id 8 shouldn't succeed as fk_t13 has rows which prevent its deletion.
We do see that Vitess reports the correct error. But somehow it ends up deleting some rows from fk_t12 when it shouldn't. Post running the query -
E0920 13:24:55.391619 15896 fk_fuzz_test.go:507] MySQL data for fk_t12 -
[[INT64(2) VARCHAR("1")] [INT64(7) VARCHAR("1")] [INT64(9) VARCHAR("3")]]
E0920 13:24:55.391629 15896 fk_fuzz_test.go:508] Vitess data for fk_t12 -
[[INT64(2) VARCHAR("1")] [INT64(7) VARCHAR("1")]]
Reproduction Steps
Create the tables with the given schema and insert the rows as stated.
Then, try running the delete query as listed above.
Binary Version
Operating System and Environment details
Log Fragments
No response
Overview of the Issue
This bug was found by the fuzzer that is added in #13980.
We are running a
DELETEquery via prepared statements on tablefk_t11and it causes the data infk_t12to mismatch -The schema we are using looks like -
Initially the state of the database looks like -
On executing the query, we expect the query to fail because deleting the row in
fk_t11withid8 shouldn't succeed asfk_t13has rows which prevent its deletion.We do see that Vitess reports the correct error. But somehow it ends up deleting some rows from
fk_t12when it shouldn't. Post running the query -Reproduction Steps
Create the tables with the given schema and insert the rows as stated.
Then, try running the delete query as listed above.
Binary Version
Operating System and Environment details
Log Fragments
No response