Debian Codegraph @

mysql-test/suite/binlog/t/binlog_mix_unsafe_default_expression_in_trigger.test

raw
1 # ==== Purpose ====
2 #
3 # Test that non-deterministic expressions in column default expressions invoked
4 # via triggers, are correctly logged with BINLOG_FORMAT=MIXED.
5 #
6 # ==== Requirements ====
7 #
8 # R1. If a statement triggers another statement which may modify a table that
9 # has a column with a non-deterministic DEFAULT expression, the statement
10 # should be logged in ROW format when BINLOG_FORMAT is MIXED.
11 #
12 # R2. If none of the triggered statements modifies a table that has a
13 # non-deterministic DEFAULT expression, and the triggering statement is not
14 # unsafe by itself, the statement should be logged in STATEMENT format when
15 # BINLOG_FORMAT is MIXED.
16 #
17 # R3. If a statement directly modifies a table with a column that has a
18 # non-deterministic DEFAULT expression, and the statement provides an
19 # explicit value for the column, the statement should be logged in
20 # STATEMENT format when BINLOG_FORMAT is MIXED.
21 #
22 # ==== Implementation ====
23 #
24 # Test firing triggers which modify tables with non-deterministic column
25 # defaults, and verify that they are logged in the row format.
26 #
27 # ==== References ====
28 #
29 # WL#9418: Permit default value to be a function or expression, support for LOBs
30 # Bug#28297486: WL#9418: ERROR AND WARNING RELATED ISSUES
31 #
32
33 --source include/have_binlog_format_mixed.inc
34
35 RESET MASTER;
36 CREATE TABLE t1 (a DOUBLE DEFAULT (PI()),
37 b DOUBLE DEFAULT (RAND()));
38 CREATE TABLE t2 (x INT);
39 CREATE TABLE t3 (y DOUBLE DEFAULT (PI()));
40
41 --echo # Test R1.
42
43 --let $event_sequence = !Gtid_or_anon # !Begin # Table_map # Table_map # Write_rows # Write_rows # Xid
44
45 CREATE TRIGGER t2_trigger AFTER INSERT ON t2
46 FOR EACH ROW INSERT INTO t1 VALUES ();
47 --source include/save_binlog_position.inc
48 INSERT INTO t2 VALUES ();
49 --source include/assert_binlog_events.inc
50 DROP TRIGGER t2_trigger;
51
52 CREATE TRIGGER t2_trigger AFTER INSERT ON t2
53 FOR EACH ROW INSERT INTO t1(a) VALUES (1);
54 --source include/save_binlog_position.inc
55 INSERT INTO t2 VALUES ();
56 --source include/assert_binlog_events.inc
57 DROP TRIGGER t2_trigger;
58
59 CREATE TRIGGER t2_trigger AFTER INSERT ON t2
60 FOR EACH ROW INSERT INTO t1(b) VALUES (DEFAULT);
61 --source include/save_binlog_position.inc
62 INSERT INTO t2 VALUES ();
63 --source include/assert_binlog_events.inc
64 DROP TRIGGER t2_trigger;
65
66 CREATE TRIGGER t2_trigger AFTER INSERT ON t2
67 FOR EACH ROW INSERT INTO t1(b) VALUES (1);
68 --source include/save_binlog_position.inc
69 INSERT INTO t2 VALUES ();
70 --source include/assert_binlog_events.inc
71 DROP TRIGGER t2_trigger;
72
73 --let $event_sequence = !Gtid_or_anon # !Begin # Table_map # Table_map # Write_rows # Update_rows # Xid
74 CREATE TRIGGER t2_trigger AFTER INSERT ON t2
75 FOR EACH ROW UPDATE t1 SET a = DEFAULT;
76 --source include/save_binlog_position.inc
77 INSERT INTO t2 VALUES ();
78 --source include/assert_binlog_events.inc
79 DROP TRIGGER t2_trigger;
80
81 CREATE TRIGGER t2_trigger AFTER INSERT ON t2
82 FOR EACH ROW UPDATE t1 SET b = DEFAULT;
83 --source include/save_binlog_position.inc
84 INSERT INTO t2 VALUES ();
85 --source include/assert_binlog_events.inc
86 DROP TRIGGER t2_trigger;
87
88 --echo # Test R2.
89
90 CREATE TRIGGER t2_trigger AFTER INSERT ON t2
91 FOR EACH ROW INSERT INTO t3 VALUES ();
92 # Logged in STATEMENT format, since the triggered statement does not touch a
93 # table with any non-deterministic DEFAULT expressions.
94 --source include/save_binlog_position.inc
95 INSERT INTO t2 VALUES ();
96 --let $event_sequence = !Gtid_or_anon # !Begin # Query # Xid
97 --source include/assert_binlog_events.inc
98 DROP TRIGGER t2_trigger;
99
100 --echo # Test R3.
101
102 # Logged in ROW format when the DEFAULT expression is used.
103 --source include/save_binlog_position.inc
104 INSERT INTO t1 VALUES ();
105 --let $event_sequence = !Gtid_or_anon # !Begin # Table_map # Write_rows # Xid
106 --source include/assert_binlog_events.inc
107 --source include/save_binlog_position.inc
108 INSERT INTO t1 (b) VALUES (DEFAULT);
109 --source include/assert_binlog_events.inc
110
111 # Logged in STATEMENT format when the DEFAULT expression is not used.
112 --source include/save_binlog_position.inc
113 INSERT INTO t1 (b) VALUES (1);
114 --let $event_sequence = !Gtid_or_anon # !Begin # Query # Xid
115 --source include/assert_binlog_events.inc
116
117 # Clean up
118 DROP TABLE t1, t2, t3;