forked from pingcap/dm
-
Notifications
You must be signed in to change notification settings - Fork 0
/
filter_test.go
184 lines (161 loc) · 6.68 KB
/
filter_test.go
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
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
// Copyright 2019 PingCAP, Inc.
//
// Licensed under the Apache License, Version 2.0 (the "License");
// you may not use this file except in compliance with the License.
// You may obtain a copy of the License at
//
// http://www.apache.org/licenses/LICENSE-2.0
//
// Unless required by applicable law or agreed to in writing, software
// distributed under the License is distributed on an "AS IS" BASIS,
// See the License for the specific language governing permissions and
// limitations under the License.
package syncer
import (
. "github.com/pingcap/check"
"github.com/pingcap/parser"
bf "github.com/pingcap/tidb-tools/pkg/binlog-filter"
"github.com/pingcap/tidb-tools/pkg/filter"
)
func (s *testSyncerSuite) TestSkipQueryEvent(c *C) {
cases := []struct {
sql string
expectSkipped bool
}{
{"SAVEPOINT `a1`", true},
// flush
{"flush privileges", true},
{"flush logs", true},
{"FLUSH TABLES WITH READ LOCK", true},
// table maintenance
{"OPTIMIZE TABLE foo", true},
{"ANALYZE TABLE foo", true},
{"REPAIR TABLE foo", true},
// temporary table
{"DROP /*!40005 TEMPORARY */ TABLE IF EXISTS `h2`", true},
{"DROP TEMPORARY TABLE IF EXISTS `foo`.`bar` /* generated by server */", true},
{"DROP TABLE foo.bar", false},
{"DROP TABLE `TEMPORARY TABLE`", false},
{"DROP TABLE `TEMPORARY TABLE` /* generated by server */", false},
// trigger
{"CREATE DEFINER=`root`@`%` TRIGGER ins_sum BEFORE INSERT ON bar FOR EACH ROW SET @sum = @sum + NEW.id", true},
{"CREATE TRIGGER ins_sum BEFORE INSERT ON bar FOR EACH ROW SET @sum = @sum + NEW.id", true},
{"DROP TRIGGER ins_sum", true},
{"create table `trigger`(id int)", false},
// procedure
{"drop procedure if exists prepare_data", true},
{"CREATE DEFINER=`root`@`%` PROCEDURE `simpleproc`(OUT param1 INT) BEGIN select count(*) into param1 from shard_0001; END", true},
{"CREATE PROCEDURE simpleproc(OUT param1 INT) BEGIN select count(*) into param1 from shard_0001; END", true},
{"alter procedure prepare_data comment 'i am a comment'", true},
{"create table `procedure`(id int)", false},
{`CREATE DEFINER=root@localhost PROCEDURE simpleproc(OUT param1 INT)
BEGIN
SELECT COUNT(*) INTO param1 FROM t;
END`, true},
// view
{"CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`%` SQL SECURITY DEFINER VIEW `v` AS SELECT qty, price, qty*price AS value FROM t", true},
{"CREATE OR REPLACE ALGORITHM=UNDEFINED DEFINER=`root`@`%` SQL SECURITY DEFINER VIEW `v` AS SELECT qty, price, qty*price AS value FROM t", true},
{"ALTER ALGORITHM=UNDEFINED DEFINER=`root`@`%` SQL SECURITY DEFINER VIEW `v` AS SELECT qty, price, qty*price AS value FROM t", true},
{"DROP VIEW v", true},
{"CREATE TABLE `VIEW`(id int)", false},
{"ALTER TABLE `VIEW`(id int)", false},
// function
{"CREATE FUNCTION metaphon RETURNS STRING SONAME 'udf_example.so'", true},
{"CREATE AGGREGATE FUNCTION avgcost RETURNS REAL SONAME 'udf_example.so'", true},
{"DROP FUNCTION metaphon", true},
{"DROP FUNCTION IF EXISTS `rand_string`", true},
{"ALTER FUNCTION metaphon COMMENT 'hh'", true},
{"CREATE TABLE `function` (id int)", false},
{`CREATE DEFINER=root@localhost FUNCTION rand_string(n INT) RETURNS varchar(255) CHARSET utf8
BEGIN
DECLARE chars_str VARCHAR(100) DEFAULT 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ';
DECLARE return_str VARCHAR(255) DEFAULT '';
DECLARE i INT DEFAULT 0;
WHILE i<n DO
SET return_str = CONCAT(return_str,SUBSTRING(chars_str,FLOOR(1+RAND()*52),1));
SET i = i+1;
END WHILE;
RETURN return_str;
END`, true},
// tablespace
{"CREATE TABLESPACE `ts1` ADD DATAFILE 'ts1.ibd' ENGINE=INNODB", true},
{"ALTER TABLESPACE `ts1` DROP DATAFILE 'ts1.idb' ENGIEN=NDB", true},
{"DROP TABLESPACE ts1", true},
// account management
{"CREATE USER 't'@'%' IDENTIFIED WITH 'mysql_native_password' AS '*93E34F4B81FEC9E8271655EA87646ED01AF377CC'", true},
{"ALTER USER 't'@'%' IDENTIFIED WITH 'mysql_native_password' AS '*1114744159A0EF13B12FC371C94877763F9512D0'", true},
{"rename user t to 1", true},
{"drop user t1", true},
{"GRANT ALL PRIVILEGES ON *.* TO 't2'@'%' IDENTIFIED WITH 'mysql_native_password' AS '*12033B78389744F3F39AC4CE4CCFCAD6960D8EA0'", true},
{"revoke reload on *.* from 't2'@'%'", true},
}
//filter, err := bf.NewBinlogEvent(nil)
//c.Assert(err, IsNil)
syncer := &Syncer{}
for _, t := range cases {
skipped, err := syncer.skipQuery(nil, nil, t.sql)
c.Assert(err, IsNil)
c.Assert(skipped, Equals, t.expectSkipped)
}
// system table
skipped, err := syncer.skipQuery([]*filter.Table{{Schema: "mysql", Name: "test"}}, nil, "create table mysql.test (id int)")
c.Assert(err, IsNil)
c.Assert(skipped, Equals, true)
// test binlog filter
filterRules := []*bf.BinlogEventRule{
{
SchemaPattern: "*",
TablePattern: "",
Events: []bf.EventType{bf.DropTable},
SQLPattern: []string{"^drop\\s+table"},
Action: bf.Ignore,
}, {
SchemaPattern: "foo*",
TablePattern: "",
Events: []bf.EventType{bf.CreateTable},
SQLPattern: []string{"^create\\s+table"},
Action: bf.Do,
}, {
SchemaPattern: "foo*",
TablePattern: "bar*",
Events: []bf.EventType{bf.CreateTable},
SQLPattern: []string{"^create\\s+table"},
Action: bf.Ignore,
},
}
syncer.binlogFilter, err = bf.NewBinlogEvent(false, filterRules)
c.Assert(err, IsNil)
// test global rule
sql := "drop table tx.test"
stmt, err := parser.New().ParseOneStmt(sql, "", "")
c.Assert(err, IsNil)
skipped, err = syncer.skipQuery([]*filter.Table{{Schema: "tx", Name: "test"}}, stmt, sql)
c.Assert(err, IsNil)
c.Assert(skipped, Equals, true)
sql = "create table tx.test (id int)"
stmt, err = parser.New().ParseOneStmt(sql, "", "")
c.Assert(err, IsNil)
skipped, err = syncer.skipQuery([]*filter.Table{{Schema: "tx", Name: "test"}}, stmt, sql)
c.Assert(err, IsNil)
c.Assert(skipped, Equals, false)
// test schema rule
sql = "create table foo.test(id int)"
stmt, err = parser.New().ParseOneStmt(sql, "", "")
c.Assert(err, IsNil)
skipped, err = syncer.skipQuery([]*filter.Table{{Schema: "foo", Name: "test"}}, stmt, sql)
c.Assert(err, IsNil)
c.Assert(skipped, Equals, false)
sql = "rename table foo.test to foo.test1"
stmt, err = parser.New().ParseOneStmt(sql, "", "")
c.Assert(err, IsNil)
skipped, err = syncer.skipQuery([]*filter.Table{{Schema: "foo", Name: "test"}}, stmt, sql)
c.Assert(err, IsNil)
c.Assert(skipped, Equals, true)
// test table rule
sql = "create table foo.bar(id int)"
stmt, err = parser.New().ParseOneStmt(sql, "", "")
c.Assert(err, IsNil)
skipped, err = syncer.skipQuery([]*filter.Table{{Schema: "foo", Name: "bar"}}, stmt, sql)
c.Assert(err, IsNil)
c.Assert(skipped, Equals, true)
}