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