1
#########################################
2
# Author: Serge Kozlov skozlov@mysql.com
4
# Purpose: Testing Invocation and Invoked
5
# Features for Replication.
6
#########################################
8
--source include/master-slave.inc
9
--source include/have_innodb.inc
11
# --disable_warnings/--enable_warnings added before/after query
12
# if one uses UUID() function because we need to avoid warnings
13
# for STATEMENT binlog format
15
# Non-transactional engine
16
--let $engine_type= myisam
18
# Transactional engine
19
--let $engine_type2= innodb
28
DROP VIEW IF EXISTS v1,v11;
29
DROP TABLE IF EXISTS t1,t2,t3,t11,t12,t13;
30
DROP PROCEDURE IF EXISTS p1;
31
DROP PROCEDURE IF EXISTS p11;
32
DROP FUNCTION IF EXISTS f1;
33
DROP FUNCTION IF EXISTS f2;
34
DROP EVENT IF EXISTS e1;
35
DROP EVENT IF EXISTS e11;
40
# Prepare objects (tables etc)
46
eval CREATE TABLE t1 (a INT NOT NULL PRIMARY KEY, b INT, c VARCHAR(64)) ENGINE=$engine_type;
47
INSERT INTO t1 VALUES (1,1,'1');
49
INSERT INTO t1 VALUES (2,2,UUID());
51
eval CREATE TABLE t2 (a INT UNIQUE, b INT, c VARCHAR(64)) ENGINE=$engine_type;
52
INSERT INTO t2 VALUES (1,1,'1');
54
INSERT INTO t2 VALUES (2,2,UUID());
57
eval CREATE TABLE t11 (a INT NOT NULL PRIMARY KEY, b INT, c VARCHAR(64)) ENGINE=$engine_type2;
58
INSERT INTO t11 VALUES (1,1,'1');
60
INSERT INTO t11 VALUES (2,2,UUID());
62
eval CREATE TABLE t12 (a INT UNIQUE, b INT, c VARCHAR(64)) ENGINE=$engine_type2;
63
INSERT INTO t12 VALUES (1,1,'1');
65
INSERT INTO t12 VALUES (2,2,UUID());
68
# Create invoked features
70
# Create view for tables t1,t11
71
CREATE VIEW v1 AS SELECT * FROM t1;
72
CREATE VIEW v11 AS SELECT * FROM t11;
74
# Create triggers for t1,t11
77
CREATE TRIGGER t1_tr1 BEFORE INSERT ON t1 FOR EACH ROW
79
INSERT INTO t2 VALUES (NEW.a, NEW.b, NEW.c);
80
INSERT INTO t3 VALUES (NEW.a, NEW.b, NEW.c);
83
CREATE TRIGGER t1_tr2 BEFORE UPDATE ON t1 FOR EACH ROW
89
CREATE TRIGGER t11_tr1 BEFORE INSERT ON t11 FOR EACH ROW
91
INSERT INTO t12 VALUES (NEW.a, NEW.b, NEW.c);
92
INSERT INTO t13 VALUES (NEW.a, NEW.b, NEW.c);
95
CREATE TRIGGER t11_tr2 BEFORE UPDATE ON t11 FOR EACH ROW
97
UPDATE t12 SET c = '';
98
UPDATE t13 SET c = '';
101
# Create events which will run every 1 sec
102
CREATE EVENT e1 ON SCHEDULE EVERY 1 SECOND DISABLE DO
104
ALTER EVENT e1 DISABLE;
108
CREATE EVENT e11 ON SCHEDULE EVERY 1 SECOND DISABLE DO
110
ALTER EVENT e11 DISABLE;
114
# Create functions and procedures used for events
115
CREATE FUNCTION f1 (x INT) RETURNS VARCHAR(64)
123
CREATE FUNCTION f2 (x INT) RETURNS VARCHAR(64)
128
CREATE PROCEDURE p1 (IN x INT, IN y VARCHAR(64))
130
INSERT IGNORE INTO t1 VALUES (x,x,y);
133
CREATE PROCEDURE p11 (IN x INT, IN y VARCHAR(64))
135
INSERT IGNORE INTO t11 VALUES (x,x,y);
145
# Do some actions for non-transactional tables
147
CREATE TABLE t3 SELECT * FROM v1;
148
INSERT INTO t1 VALUES (3,3,'');
149
UPDATE t1 SET c='2' WHERE a = 1;
151
INSERT INTO t1 VALUES(4,4,f1(4));
153
INSERT INTO t1 VALUES (100,100,'');
157
INSERT INTO t1 VALUES (101,101,'');
159
INSERT INTO t1 VALUES(6,6,f1(6));
161
INSERT INTO t1 VALUES (102,102,'');
163
INSERT INTO t1 VALUES(7,7,f2(7));
165
INSERT INTO t1 VALUES (103,103,'');
167
# Do some actions for transactional tables
169
CREATE TABLE t13 SELECT * FROM v11;
170
INSERT INTO t11 VALUES (3,3,'');
171
UPDATE t11 SET c='2' WHERE a = 1;
173
INSERT INTO t11 VALUES(4,4,f1(4));
175
INSERT INTO t11 VALUES (100,100,'');
179
INSERT INTO t11 VALUES (101,101,'');
181
INSERT INTO t11 VALUES(6,6,f1(6));
183
INSERT INTO t11 VALUES (102,102,'');
185
INSERT INTO t11 VALUES(7,7,f2(7));
187
INSERT INTO t11 VALUES (103,103,'');
191
# Temporally events fire sequentally due Bug#29020.
192
SET GLOBAL EVENT_SCHEDULER = on;
193
# Wait while events will executed
194
ALTER EVENT e1 ENABLE;
195
let $wait_condition= SELECT COUNT(*) = 1 FROM t1 WHERE t1.a = 10;
196
--source include/wait_condition.inc
197
ALTER EVENT e11 ENABLE;
198
let $wait_condition= SELECT COUNT(*) = 1 FROM t11 WHERE t11.a = 10;
199
--source include/wait_condition.inc
200
SET GLOBAL EVENT_SCHEDULER = off;
202
# Check original objects
205
SHOW TABLES LIKE 't%';
207
SELECT table_name FROM information_schema.views WHERE table_schema='test';
209
SELECT trigger_name, event_manipulation, event_object_table FROM information_schema.triggers WHERE trigger_schema='test';
211
SELECT routine_type, routine_name FROM information_schema.routines WHERE routine_schema='test';
213
SELECT event_name, status FROM information_schema.events WHERE event_schema='test';
215
# Check original data
217
SELECT COUNT(*) FROM t1;
218
SELECT a,b FROM t1 ORDER BY a;
219
SELECT COUNT(*) FROM t2;
220
SELECT a,b FROM t2 ORDER BY a;
221
SELECT COUNT(*) FROM t3;
222
SELECT a,b FROM t3 ORDER BY a;
223
SELECT a,b FROM v1 ORDER BY a;
224
SELECT COUNT(*) FROM t11;
225
SELECT a,b FROM t11 ORDER BY a;
226
SELECT COUNT(*) FROM t12;
227
SELECT a,b FROM t12 ORDER BY a;
228
SELECT COUNT(*) FROM t13;
229
SELECT a,b FROM t13 ORDER BY a;
230
SELECT a,b FROM v11 ORDER BY a;
232
--sync_slave_with_master slave
234
# Check replicated objects
237
SHOW TABLES LIKE 't%';
239
SELECT table_name FROM information_schema.views WHERE table_schema='test';
241
SELECT trigger_name, event_manipulation, event_object_table FROM information_schema.triggers WHERE trigger_schema='test';
243
SELECT routine_type, routine_name FROM information_schema.routines WHERE routine_schema='test';
245
SELECT event_name, status FROM information_schema.events WHERE event_schema='test';
247
# Check replicated data
249
SELECT COUNT(*) FROM t1;
250
SELECT a,b FROM t1 ORDER BY a;
251
SELECT COUNT(*) FROM t2;
252
SELECT a,b FROM t2 ORDER BY a;
253
SELECT COUNT(*) FROM t3;
254
SELECT a,b FROM t3 ORDER BY a;
255
SELECT a,b FROM v1 ORDER BY a;
256
SELECT COUNT(*) FROM t11;
257
SELECT a,b FROM t11 ORDER BY a;
258
SELECT COUNT(*) FROM t12;
259
SELECT a,b FROM t12 ORDER BY a;
260
SELECT COUNT(*) FROM t13;
261
SELECT a,b FROM t13 ORDER BY a;
262
SELECT a,b FROM v11 ORDER BY a;
264
# Remove UUID() before comparing and sort tables
275
ALTER TABLE t3 ORDER BY a;
276
ALTER TABLE t13 ORDER BY a;
278
--sync_slave_with_master slave
280
# Compare a data from master and slave
282
--exec $MYSQL_DUMP --compact --order-by-primary --skip-extended-insert --no-create-info test > $MYSQLTEST_VARDIR/tmp/rpl_invoked_features_master.sql
283
--exec $MYSQL_DUMP_SLAVE --compact --order-by-primary --skip-extended-insert --no-create-info test > $MYSQLTEST_VARDIR/tmp/rpl_invoked_features_slave.sql
284
--diff_files $MYSQLTEST_VARDIR/tmp/rpl_invoked_features_master.sql $MYSQLTEST_VARDIR/tmp/rpl_invoked_features_slave.sql
293
--remove_file $MYSQLTEST_VARDIR/tmp/rpl_invoked_features_master.sql
294
--remove_file $MYSQLTEST_VARDIR/tmp/rpl_invoked_features_slave.sql
296
# Remove tables,views,procedures,functions
299
DROP VIEW IF EXISTS v1,v11;
300
DROP TABLE IF EXISTS t1,t2,t3,t11,t12,t13;
301
DROP PROCEDURE IF EXISTS p1;
302
DROP PROCEDURE IF EXISTS p11;
303
DROP FUNCTION IF EXISTS f1;
304
DROP FUNCTION IF EXISTS f2;
305
DROP EVENT IF EXISTS e1;
306
DROP EVENT IF EXISTS e11;
308
--sync_slave_with_master slave