| 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 |
|