Debian Codegraph @

mysql-test/suite/sys_vars/t/require_row_format_restrictions.test

raw
1 # ==== Purpose ====
2 # This test evaluates the restrictions in query execution associated to the
3 # variable @@session.require_row_format
4 #
5 # ==== Requirements ====
6 #
7 # The following scenarios are tested in this script:
8 #
9 # + Check INSERT, UPDATE, DELETE, REPLACE or LOAD commands operations
10 # are not allowed when @@session.require_row_format = 1
11 # + Check no creation or deletion of temporary tables is allowed when
12 # @@session.require_row_format = 1
13 # + Check the above restrictions are not checked when
14 # @@session.require_row_format = 0
15 # + Check encoded binary log events can still be executed.
16 #
17 # ==== Implementation ====
18 #
19 # 0. Create some tables for testing
20 # 1. Set require_row_format to 1 and check all restrictions are enforced.
21 # 1.1 Test binlog updates can still update tables
22 # 2. Set require row format to 0 and check all restrictions are gone
23 # 3. Cleanup
24 #
25 # ==== Related Worklog ====
26 #
27 # WL #12968 : Configure replication applier to require row-based replication
28
29 --echo #
30 --echo # Step 0. Create some tables for testing
31
32 CREATE TABLE t1 (c1 INT) Engine=InnoDB;
33 INSERT INTO t1 VALUES (1);
34
35 CREATE TABLE t2 (c1 INT) Engine=InnoDB;
36 INSERT INTO t2 VALUES (10);
37
38 CREATE TABLE t3 (text VARCHAR(20));
39
40 CREATE TEMPORARY TABLE temp1 (c1 INT);
41
42 --echo #
43 --echo # 1. Set require_row_format to 1 and check all restrictions are enforced.
44
45 SET SESSION require_row_format= 1;
46
47 # INSERT
48 --error ER_CLIENT_QUERY_FAILURE_INVALID_NON_ROW_FORMAT
49 INSERT INTO t1 VALUES (2);
50
51 # UPDATE
52 --error ER_CLIENT_QUERY_FAILURE_INVALID_NON_ROW_FORMAT
53 UPDATE t1 SET c1 = 2 WHERE c1 = 1;
54
55 # INSERT SELECT
56 --error ER_CLIENT_QUERY_FAILURE_INVALID_NON_ROW_FORMAT
57 INSERT INTO t1 SELECT * FROM t2;
58
59 # DELETE
60 --error ER_CLIENT_QUERY_FAILURE_INVALID_NON_ROW_FORMAT
61 DELETE FROM t1 WHERE c1 = 1;
62
63 # REPLACE
64 --error ER_CLIENT_QUERY_FAILURE_INVALID_NON_ROW_FORMAT
65 REPLACE INTO t1 (c1) VALUES(2);
66
67 # REPLACE SELECT
68 --error ER_CLIENT_QUERY_FAILURE_INVALID_NON_ROW_FORMAT
69 REPLACE t1 SELECT * FROM t2;
70
71 # LOAD DATA
72 --error ER_CLIENT_QUERY_FAILURE_INVALID_NON_ROW_FORMAT
73 LOAD DATA INFILE '../std_data_ln/words.dat' INTO TABLE t3;
74
75 # DELETE MULTI
76 --error ER_CLIENT_QUERY_FAILURE_INVALID_NON_ROW_FORMAT
77 DELETE t1, t2 FROM t1, t2 WHERE t1.c1 = 1;
78
79 # UPDATE MULTI
80 --error ER_CLIENT_QUERY_FAILURE_INVALID_NON_ROW_FORMAT
81 UPDATE t1, t2 SET t1.c1=t2.c1 WHERE t1.c1 = 1;
82
83 # CREATE TEMPORARY TABLE
84 --error ER_CLIENT_QUERY_FAILURE_INVALID_NON_ROW_FORMAT
85 CREATE TEMPORARY TABLE temp2 (c1 INT);
86
87 # DROP TEMPORARY TABLE
88 --error ER_CLIENT_QUERY_FAILURE_INVALID_NON_ROW_FORMAT
89 DROP TEMPORARY TABLE temp1;
90
91 --let $assert_text = Table t1 was not changed
92 --let $assert_cond = [SELECT count(*) FROM t1 where c1=1] = 1
93 --source include/assert.inc
94
95 --let $assert_text = Table t3 was empty
96 --let $assert_cond = [SELECT count(*) FROM t3] = 0
97 --source include/assert.inc
98
99 --echo #
100 --echo # 1.1 Test binlog updates can still update tables
101
102 # Format log event
103 BINLOG '
104 mV5DXQ8BAAAAeAAAAHwAAAABAAQAOC4wLjE5LXRyLWRlYnVnAAAAAAAAAAAAAAAAAAAAAAAAAAAA
105 AAAAAAAAAAAAAAAAAACZXkNdEwANAAgAAAAABAAEAAAAYAAEGggAAAAICAgCAAAACgoKKioAEjQA
106 CgHG1zdG';
107
108 # BINLOG INSERT
109 BINLOG '
110 mV5DXRMBAAAAMAAAADACAAAAAJoAAAAAAAEABHRlc3QAAnQxAAEDAAEBAQCnZmio
111 mV5DXR4BAAAAKAAAAFgCAAAAAJoAAAAAAAEAAgAB/wACAAAAntJyow==';
112
113 --let $assert_text = Table t1 was changed
114 --let $assert_cond = [SELECT count(*) FROM t1] = 2
115 --source include/assert.inc
116
117 --echo #
118 --echo # 2. Set require_row_format to 0 and check all restrictions are gone
119
120 SET SESSION require_row_format= 0;
121
122 # INSERT
123 INSERT INTO t1 VALUES (11);
124
125 # UPDATE
126 UPDATE t1 SET c1 = 4 WHERE c1 = 1;
127
128 # CREATE TABLE
129 CREATE TEMPORARY TABLE temp2 (c1 INT);
130
131 # BINLOG EVENT
132 BINLOG '
133 mV5DXRMBAAAAMAAAAEEDAAAAAJoAAAAAAAEABHRlc3QAAnQxAAEDAAEBAQDNxAV8
134 mV5DXR4BAAAAKAAAAGkDAAAAAJoAAAAAAAEAAgAB/wADAAAAO7EClA==';
135
136 --let $assert_text = Table t1 has 4 values
137 --let $assert_cond = [SELECT count(*) FROM t1] = 4
138 --source include/assert.inc
139
140 --echo #
141 --echo # 3. Cleanup
142
143 DROP TEMPORARY TABLE temp1;
144 DROP TEMPORARY TABLE temp2;
145
146 DROP TABLE t1;
147 DROP TABLE t2;
148 DROP TABLE t3;