Hi Davi, and welcome Dmitry! You appear something to be a trigger
expert. :-) Can you please take a look at my conclusion below and verify
that it makes sense?
Davi Arnaut wrote:
> Hi Martin,
>
> s/test_db2\.//g
done.
>
> Also, shouldn't the delete stop at the first error? Otherwise it will
> continue removing data from the next table in the union despite errors.
I ran some tests, and stumbled upon an even simpler test case without
views or merge tables. Attached please find bug46958.test.
I ran some tests with
- A non-empty JOIN of three tables: t1, t2, t3
- A BEFORE trigger and an AFTER trigger inserting into nonexistent table.
- Dealing with the failed assertion by just removing DBUG_ASSERT(!
is_set()); from Diagnostics_area::set_ok_status.
I attach the result files with (hopefully) self-explanatory names.
- A failing BEFORE trigger on the first table -> no deletes.
- A failing AFTER trigger on the first table -> deletes first value from
first table
- A failing BEFORE trigger on the middle table (failed assertion) ->
deletes all from t1 and - curiously - all from t3.
- A failing AFTER trigger on the middle table (failed assertion) ->
deletes all from t1, 1st row from t2 and - curiously - all from t3.
- A failing BEFORE trigger on the last table -> deletes all from t1, t2.
- A failing AFTER trigger on the last table -> deletes all from t1, t2
and first row from t3.
Conclusion: As long as we don't hit the bug this is consistent behavior:
The statement deletes up until hitting an error and then stops. I say we
should preserve that behavior. It also rings consistently with the
overall MySQL behavior IMHO.
Best Regards
Martin
--echo # Bug#46958: Assertion in Diagnostics_area::set_ok_status, trigger,
--echo # merge table
--echo #
CREATE TABLE t1 ( a INT );
CREATE TABLE t2 ( a INT );
CREATE TABLE t3 ( a INT );
INSERT INTO t1 VALUES (1);
INSERT INTO t2 VALUES (1);
INSERT INTO t3 VALUES (1);
CREATE TRIGGER tr1 BEFORE DELETE ON t2
FOR EACH ROW INSERT INTO no_such_table VALUES (1);
SELECT * FROM t1, t2, t3;
DELETE t1, t2, t3 FROM t1, t2, t3;
SELECT * FROM t1;
SELECT * FROM t2;
SELECT * FROM t3;
DROP TABLE t1, t2, t3;
# Bug#46958: Assertion in Diagnostics_area::set_ok_status, trigger,
# merge table
#
CREATE TABLE t1 ( a INT );
CREATE TABLE t2 ( a INT );
CREATE TABLE t3 ( a INT );
INSERT INTO t1 VALUES (1);
INSERT INTO t2 VALUES (1);
INSERT INTO t3 VALUES (1);
CREATE TRIGGER tr1 BEFORE DELETE ON t2
FOR EACH ROW INSERT INTO no_such_table VALUES (1);
SELECT * FROM t1, t2, t3;
a a a
1 1 1
DELETE t1, t2, t3 FROM t1, t2, t3;
Warnings:
Error 1146 Table 'test.no_such_table' doesn't exist
SELECT * FROM t1;
a
SELECT * FROM t2;
a
1
SELECT * FROM t3;
a
1
DROP TABLE t1, t2, t3;
# Bug#46958: Assertion in Diagnostics_area::set_ok_status, trigger,
# merge table
#
CREATE TABLE t1 ( a INT );
CREATE TABLE t2 ( a INT );
CREATE TABLE t3 ( a INT );
INSERT INTO t1 VALUES (1), (2);
INSERT INTO t2 VALUES (1), (2);
INSERT INTO t3 VALUES (1), (2);
CREATE TRIGGER tr1 AFTER DELETE ON t2
FOR EACH ROW INSERT INTO no_such_table VALUES (1);
SELECT * FROM t1, t2, t3;
a a a
1 1 1
2 1 1
1 2 1
2 2 1
1 1 2
2 1 2
1 2 2
2 2 2
DELETE t1, t2, t3 FROM t1, t2, t3;
Warnings:
Error 1146 Table 'test.no_such_table' doesn't exist
SELECT * FROM t1;
a
SELECT * FROM t2;
a
2
SELECT * FROM t3;
a
DROP TABLE t1, t2, t3;
# Bug#46958: Assertion in Diagnostics_area::set_ok_status, trigger,
# merge table
#
CREATE TABLE t1 ( a INT );
CREATE TABLE t2 ( a INT );
CREATE TABLE t3 ( a INT );
INSERT INTO t1 VALUES (1), (2);
INSERT INTO t2 VALUES (1), (2);
INSERT INTO t3 VALUES (1), (2);
CREATE TRIGGER tr1 AFTER DELETE ON t3
FOR EACH ROW INSERT INTO no_such_table VALUES (1);
SELECT * FROM t1, t2, t3;
a a a
1 1 1
2 1 1
1 2 1
2 2 1
1 1 2
2 1 2
1 2 2
2 2 2
DELETE t1, t2, t3 FROM t1, t2, t3;
ERROR 42S02: Table 'test.no_such_table' doesn't exist
SELECT * FROM t1;
a
SELECT * FROM t2;
a
SELECT * FROM t3;
a
2
DROP TABLE t1, t2, t3;
# Bug#46958: Assertion in Diagnostics_area::set_ok_status, trigger,
# merge table
#
CREATE TABLE t1 ( a INT );
CREATE TABLE t2 ( a INT );
CREATE TABLE t3 ( a INT );
INSERT INTO t1 VALUES (1), (2);
INSERT INTO t2 VALUES (1), (2);
INSERT INTO t3 VALUES (1), (2);
CREATE TRIGGER tr1 BEFORE DELETE ON t2
FOR EACH ROW INSERT INTO no_such_table VALUES (1);
SELECT * FROM t1, t2, t3;
a a a
1 1 1
2 1 1
1 2 1
2 2 1
1 1 2
2 1 2
1 2 2
2 2 2
DELETE t1, t2, t3 FROM t1, t2, t3;
Warnings:
Error 1146 Table 'test.no_such_table' doesn't exist
SELECT * FROM t1;
a
SELECT * FROM t2;
a
1
2
SELECT * FROM t3;
a
DROP TABLE t1, t2, t3;
# Bug#46958: Assertion in Diagnostics_area::set_ok_status, trigger,
# merge table
#
CREATE TABLE t1 ( a INT );
CREATE TABLE t2 ( a INT );
CREATE TABLE t3 ( a INT );
INSERT INTO t1 VALUES (1), (2);
INSERT INTO t2 VALUES (1), (2);
INSERT INTO t3 VALUES (1), (2);
CREATE TRIGGER tr1 BEFORE DELETE ON t3
FOR EACH ROW INSERT INTO no_such_table VALUES (1);
SELECT * FROM t1, t2, t3;
a a a
1 1 1
2 1 1
1 2 1
2 2 1
1 1 2
2 1 2
1 2 2
2 2 2
DELETE t1, t2, t3 FROM t1, t2, t3;
ERROR 42S02: Table 'test.no_such_table' doesn't exist
SELECT * FROM t1;
a
SELECT * FROM t2;
a
SELECT * FROM t3;
a
1
2
DROP TABLE t1, t2, t3;
# Bug#46958: Assertion in Diagnostics_area::set_ok_status, trigger,
# merge table
#
CREATE TABLE t1 ( a INT );
CREATE TABLE t2 ( a INT );
CREATE TABLE t3 ( a INT );
INSERT INTO t1 VALUES (1), (2);
INSERT INTO t2 VALUES (1), (2);
INSERT INTO t3 VALUES (1), (2);
CREATE TRIGGER tr1 AFTER DELETE ON t1
FOR EACH ROW INSERT INTO no_such_table VALUES (1);
SELECT * FROM t1, t2, t3;
a a a
1 1 1
2 1 1
1 2 1
2 2 1
1 1 2
2 1 2
1 2 2
2 2 2
DELETE t1, t2, t3 FROM t1, t2, t3;
ERROR 42S02: Table 'test.no_such_table' doesn't exist
SELECT * FROM t1;
a
2
SELECT * FROM t2;
a
1
2
SELECT * FROM t3;
a
1
2
DROP TABLE t1, t2, t3;
# Bug#46958: Assertion in Diagnostics_area::set_ok_status, trigger,
# merge table
#
CREATE TABLE t1 ( a INT );
CREATE TABLE t2 ( a INT );
CREATE TABLE t3 ( a INT );
INSERT INTO t1 VALUES (1);
INSERT INTO t2 VALUES (1);
INSERT INTO t3 VALUES (1);
CREATE TRIGGER tr1 BEFORE DELETE ON t1
FOR EACH ROW INSERT INTO no_such_table VALUES (1);
SELECT * FROM t1, t2, t3;
a a a
1 1 1
DELETE t1, t2, t3 FROM t1, t2, t3;
ERROR 42S02: Table 'test.no_such_table' doesn't exist
SELECT * FROM t1;
a
1
SELECT * FROM t2;
a
1
SELECT * FROM t3;
a
1
DROP TABLE t1, t2, t3;
