1call mtr.add_suppression("Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT."); 2### NOT filtered database => assertion: warnings ARE shown 3DROP TABLE IF EXISTS t1; 4CREATE TABLE t1 (a int, b int, primary key (a)); 5INSERT INTO t1 VALUES (1,2), (2,3); 6UPDATE t1 SET b='4' WHERE a=1 LIMIT 1; 7Warnings: 8Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. The statement is unsafe because it uses a LIMIT clause. This is unsafe because the set of rows included cannot be predicted 9UPDATE t1 SET b='5' WHERE a=2 ORDER BY a LIMIT 1; 10Warnings: 11Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. The statement is unsafe because it uses a LIMIT clause. This is unsafe because the set of rows included cannot be predicted 12DROP TABLE t1; 13### NOT filtered database => assertion: binlog disabled and warnings ARE NOT shown 14SET SQL_LOG_BIN= 0; 15DROP TABLE IF EXISTS t1; 16CREATE TABLE t1 (a int, b int, primary key (a)); 17INSERT INTO t1 VALUES (1,2), (2,3); 18UPDATE t1 SET b='4' WHERE a=1 LIMIT 1; 19UPDATE t1 SET b='5' WHERE a=2 ORDER BY a LIMIT 1; 20DROP TABLE t1; 21SET SQL_LOG_BIN= 1; 22### FILTERED database => assertion: warnings ARE NOT shown 23CREATE DATABASE b42851; 24USE b42851; 25DROP TABLE IF EXISTS t1; 26CREATE TABLE t1 (a int, b int, primary key (a)); 27INSERT INTO t1 VALUES (1,2), (2,3); 28UPDATE t1 SET b='4' WHERE a=1 LIMIT 1; 29UPDATE t1 SET b='5' WHERE a=2 ORDER BY a LIMIT 1; 30DROP TABLE t1; 31CREATE TABLE t1 (a VARCHAR(1000)); 32INSERT INTO t1 VALUES (CURRENT_USER()); 33INSERT INTO t1 VALUES (FOUND_ROWS()); 34INSERT INTO t1 VALUES (GET_LOCK('tmp', 1)); 35INSERT INTO t1 VALUES (IS_FREE_LOCK('tmp')); 36INSERT INTO t1 VALUES (IS_USED_LOCK('tmp')); 37INSERT INTO t1 VALUES (LOAD_FILE('../../std_data/words2.dat')); 38INSERT INTO t1 VALUES (MASTER_POS_WAIT('dummy arg', 4711, 1)); 39INSERT INTO t1 VALUES (RELEASE_LOCK('tmp')); 40INSERT INTO t1 VALUES (ROW_COUNT()); 41INSERT INTO t1 VALUES (SESSION_USER()); 42INSERT INTO t1 VALUES (SLEEP(1)); 43INSERT INTO t1 VALUES (SYSDATE()); 44INSERT INTO t1 VALUES (SYSTEM_USER()); 45INSERT INTO t1 VALUES (USER()); 46INSERT INTO t1 VALUES (UUID()); 47INSERT INTO t1 VALUES (UUID_SHORT()); 48INSERT INTO t1 VALUES (VERSION()); 49INSERT INTO t1 VALUES (RAND()); 50DROP DATABASE b42851; 51USE test; 52# 53# Bug#46265: Can not disable warning about unsafe statements for binary logging 54# 55SET @old_log_warnings = @@log_warnings; 56DROP TABLE IF EXISTS t1; 57CREATE TABLE t1 (a VARCHAR(36), b VARCHAR(15)); 58SET GLOBAL LOG_WARNINGS = 0; 59INSERT INTO t1 VALUES(UUID(), 'timestamp'); 60Warnings: 61Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a system function that may return a different value on the slave 62SET GLOBAL LOG_WARNINGS = 1; 63INSERT INTO t1 VALUES(UUID(), 'timestamp'); 64Warnings: 65Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a system function that may return a different value on the slave 66DROP TABLE t1; 67SET GLOBAL log_warnings = @old_log_warnings; 68# Count the number of times the "Unsafe" message was printed 69# to the error log. 70Occurrences: 1 71create table t1 (n1 int, n2 int, n3 int, 72key (n1, n2, n3), 73key (n2, n3, n1), 74key (n3, n1, n2)); 75insert into t1 values (1,1,1); 76insert into t1 values (RAND()*1000+10, RAND()*1000+10, RAND()*1000+10); 77update t1 set n1=rand() where n1=1; 78Warnings: 79Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a system function that may return a different value on the slave 80delete from t1 where n2=1 + rand()*0; 81Warnings: 82Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a system function that may return a different value on the slave 83drop table t1; 84DROP TABLE IF EXISTS t1, t2; 85CREATE TABLE t1 (a int); 86CREATE TABLE t2 (a int auto_increment primary key, b int); 87CREATE TRIGGER tr_bug50192 AFTER INSERT ON t1 FOR EACH ROW INSERT INTO t2 (b) VALUES (1); 88CREATE FUNCTION sf_bug50192() RETURNS INTEGER 89BEGIN 90INSERT INTO t2(b) VALUES(2); 91RETURN 1; 92END | 93INSERT INTO t1 VALUES (0); 94Warnings: 95Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it invokes a trigger or a stored function that inserts into an AUTO_INCREMENT column. Inserted values cannot be logged correctly 96SHOW WARNINGS; 97Level Code Message 98Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it invokes a trigger or a stored function that inserts into an AUTO_INCREMENT column. Inserted values cannot be logged correctly 99SELECT sf_bug50192(); 100sf_bug50192() 1011 102Warnings: 103Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it invokes a trigger or a stored function that inserts into an AUTO_INCREMENT column. Inserted values cannot be logged correctly 104SHOW WARNINGS; 105Level Code Message 106Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it invokes a trigger or a stored function that inserts into an AUTO_INCREMENT column. Inserted values cannot be logged correctly 107DROP FUNCTION sf_bug50192; 108DROP TRIGGER tr_bug50192; 109DROP TABLE t1, t2; 110