Debian Codegraph @

mysql-test/suite/rpl/t/rpl_user_lock.test

raw
1 --source include/not_group_replication_plugin.inc
2 --source include/set_privilege_checks_user_as_system_user.inc
3 --source include/master-slave.inc
4
5 # Suppress "unsafe" warnings.
6 #
7 --disable_query_log
8 CALL mtr.add_suppression(
9 CONCAT('Unsafe statement written to the binary log using ',
10 'statement format since BINLOG_FORMAT = STATEMENT'));
11 --enable_query_log
12
13 --echo #
14 --echo # Coverage for the user-level lock related functions
15 --echo # GET_LOCK, RELEASE_LOCK, RELEASE_ALL_LOCKS, IS_USED_LOCK and IS_FREE_LOCK
16 --echo # when replication with different binlog_formats is used.
17 --echo # All these function are unsafe in statement based replication.
18 --echo #
19 --echo #
20
21 --disable_warnings
22 DROP TRIGGER IF EXISTS trig_t1_ins;
23 DROP TABLE IF EXISTS t1;
24 --enable_warnings
25 CREATE TABLE t1 (col1 INT, col2 INT);
26 --disable_warnings
27 --echo # FT_RPL-1: Use all user-level lock related functions.
28 --echo # FT_RPL-1.1: binlog_format = 'statement'
29 --echo # Reveal that all these statements get a warning about being
30 --echo # in that mode.
31 --echo # FT_RPL-1.2: binlog_format = 'mixed'
32 --echo # Reveal that all these statements pass without warning.
33 --echo # FT_RPL-1.3: binlog_format = 'row'
34 --echo # Reveal that all these statements pass without warning.
35 --echo # The printing of warnings is temporary disabled. But there is some
36 --echo # routine which checks the warnings and aborts in case of failure.
37 INSERT INTO t1 SELECT GET_LOCK('a',0), 1;
38 --source suite/rpl/include/rpl_only_stmt_unsafe.inc
39 INSERT INTO t1 SELECT IS_USED_LOCK('a'), 2;
40 --source suite/rpl/include/rpl_only_stmt_unsafe.inc
41 INSERT INTO t1 SELECT IS_FREE_LOCK('a'), 3;
42 --source suite/rpl/include/rpl_only_stmt_unsafe.inc
43 INSERT INTO t1 SELECT RELEASE_LOCK('a'), 4;
44 --source suite/rpl/include/rpl_only_stmt_unsafe.inc
45 INSERT INTO t1 SELECT RELEASE_ALL_LOCKS(), 5;
46 --source suite/rpl/include/rpl_only_stmt_unsafe.inc
47 --echo # FT_RPL-2: Check GET_LOCK within some INSERT TRIGGER
48 --echo # Reveal that statements harvest a warning or not according the
49 --echo # the binlog_format used.
50 --enable_warnings
51 --delimiter |
52 CREATE TRIGGER trig_t1_ins BEFORE INSERT ON t1 FOR EACH ROW
53 BEGIN
54 SET @aux = GET_LOCK(2,0);
55 IF @aux <> 1 THEN
56 SIGNAL SQLSTATE '45000'
57 SET MESSAGE_TEXT = 'LOCK 2 not got. Abort.', MYSQL_ERRNO = 9999;
58 END IF;
59 END;|
60 --delimiter ;
61 --disable_warnings
62 INSERT INTO t1 VALUES (6,6), (7,7), (8,8);
63 --source suite/rpl/include/rpl_only_stmt_unsafe.inc
64 --enable_warnings
65 --source include/sync_slave_sql_with_master.inc
66 --connection master
67
68 --echo # FT_RPL-3: Check that there is no diff in table content between master
69 --echo # and slave in case binlog_format != 'statement'.
70 # Master and slave differ for @@binlog_format = 'statement'.
71 # We do not reveal that difference here because the warnings about unsafe
72 # statements checked above is sufficient.
73 # Master and slave must not differ for @@binlog_format <> 'statement'.
74 # That gets checked here.
75 # Getting no difference for binlog_format 'row' is no challenge because any
76 # modification of table content has to be done based on 'row'.
77 # But getting no difference for binlog_format 'mixed' is of high interest
78 # because replication has to identify our INSERTs which use user-level lock
79 # functions in order to replace the unsafe statement based operation with some
80 # safe row based operation.
81 if (`SELECT @@binlog_format <> 'statement'`)
82 {
83 let $master_avg_col1= `SELECT AVG(col1) FROM t1`;
84 let $master_avg_col2= `SELECT AVG(col2) FROM t1`;
85 --connection slave
86 let $slave_avg_col1= `SELECT AVG(col1) FROM t1`;
87 let $slave_avg_col2= `SELECT AVG(col2) FROM t1`;
88 if (`SELECT $master_avg_col1 <> $slave_avg_col1 OR
89 $master_avg_col2 <> $slave_avg_col2`)
90 {
91 --echo ERROR: The content of t1 differs between slave and master.
92 --echo Slave:
93 SELECT col1, col2 FROM t1 ORDER BY col2;
94 --connection master
95 --echo Master:
96 SELECT col1, col2 FROM t1 ORDER BY col2;
97 --echo This must not happen with @@binlog_format <> 'statement'.
98 --echo Abort.
99 --exit
100 }
101 }
102
103 --connection master
104 DROP TRIGGER trig_t1_ins;
105 DROP TABLE t1;
106
107 --echo #
108 --echo # End of tests
109 --echo #
110 --source include/rpl_end.inc
111