mirror of
https://github.com/MariaDB/server.git
synced 2025-02-09 23:24:11 +01:00
e7cf871dda
The warning out of OPTIMIZE Statement is unsafe because it uses a system function was indeed counterfactual and was resulted by checking an insufficiently strict property of lex' sql_command_flags. Fixed with deploying an additional checking of weather the current sql command that modifes a share->non_determinstic_insert table is capable of generating ROW format events. The extra check rules out the unsafety to OPTIMIZE et al, while the existing check continues to do so to CREATE TABLE (which is perculiarly tagged as ROW-event generative sql command). As a side effect sql_sequence.binlog test gets corrected and binlog_stm_unsafe_warning.test is reinforced to add up an unsafe CREATE..SELECT test.
120 lines
6.2 KiB
Text
120 lines
6.2 KiB
Text
call mtr.add_suppression("Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT.");
|
|
### NOT filtered database => assertion: warnings ARE shown
|
|
DROP TABLE IF EXISTS t1;
|
|
CREATE TABLE t1 (a int, b int, primary key (a));
|
|
INSERT INTO t1 VALUES (1,2), (2,3);
|
|
UPDATE t1 SET b='4' WHERE a=1 LIMIT 1;
|
|
Warnings:
|
|
Note 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
|
|
UPDATE t1 SET b='5' WHERE a=2 ORDER BY a LIMIT 1;
|
|
Warnings:
|
|
Note 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
|
|
DROP TABLE t1;
|
|
### NOT filtered database => assertion: binlog disabled and warnings ARE NOT shown
|
|
SET SQL_LOG_BIN= 0;
|
|
DROP TABLE IF EXISTS t1;
|
|
CREATE TABLE t1 (a int, b int, primary key (a));
|
|
INSERT INTO t1 VALUES (1,2), (2,3);
|
|
UPDATE t1 SET b='4' WHERE a=1 LIMIT 1;
|
|
UPDATE t1 SET b='5' WHERE a=2 ORDER BY a LIMIT 1;
|
|
DROP TABLE t1;
|
|
SET SQL_LOG_BIN= 1;
|
|
### FILTERED database => assertion: warnings ARE NOT shown
|
|
CREATE DATABASE b42851;
|
|
USE b42851;
|
|
DROP TABLE IF EXISTS t1;
|
|
CREATE TABLE t1 (a int, b int, primary key (a));
|
|
INSERT INTO t1 VALUES (1,2), (2,3);
|
|
UPDATE t1 SET b='4' WHERE a=1 LIMIT 1;
|
|
UPDATE t1 SET b='5' WHERE a=2 ORDER BY a LIMIT 1;
|
|
DROP TABLE t1;
|
|
CREATE TABLE t1 (a VARCHAR(1000));
|
|
INSERT INTO t1 VALUES (CURRENT_USER());
|
|
INSERT INTO t1 VALUES (FOUND_ROWS());
|
|
INSERT INTO t1 VALUES (GET_LOCK('tmp', 1));
|
|
INSERT INTO t1 VALUES (IS_FREE_LOCK('tmp'));
|
|
INSERT INTO t1 VALUES (IS_USED_LOCK('tmp'));
|
|
INSERT INTO t1 VALUES (LOAD_FILE('../../std_data/words2.dat'));
|
|
INSERT INTO t1 VALUES (MASTER_POS_WAIT('dummy arg', 4711, 1));
|
|
INSERT INTO t1 VALUES (RELEASE_LOCK('tmp'));
|
|
INSERT INTO t1 VALUES (ROW_COUNT());
|
|
INSERT INTO t1 VALUES (SESSION_USER());
|
|
INSERT INTO t1 VALUES (SLEEP(1));
|
|
INSERT INTO t1 VALUES (SYSDATE());
|
|
INSERT INTO t1 VALUES (SYSTEM_USER());
|
|
INSERT INTO t1 VALUES (USER());
|
|
INSERT INTO t1 VALUES (UUID());
|
|
INSERT INTO t1 VALUES (UUID_SHORT());
|
|
INSERT INTO t1 VALUES (VERSION());
|
|
INSERT INTO t1 VALUES (RAND());
|
|
DROP DATABASE b42851;
|
|
USE test;
|
|
#
|
|
# Bug#46265: Can not disable warning about unsafe statements for binary logging
|
|
#
|
|
SET @old_log_warnings = @@log_warnings;
|
|
DROP TABLE IF EXISTS t1;
|
|
CREATE TABLE t1 (a VARCHAR(36), b VARCHAR(15));
|
|
SET GLOBAL LOG_WARNINGS = 0;
|
|
INSERT INTO t1 VALUES(UUID(), 'timestamp');
|
|
Warnings:
|
|
Note 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
|
|
SET GLOBAL LOG_WARNINGS = 1;
|
|
INSERT INTO t1 VALUES(UUID(), 'timestamp');
|
|
Warnings:
|
|
Note 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
|
|
DROP TABLE t1;
|
|
SET GLOBAL log_warnings = @old_log_warnings;
|
|
# Count the number of times the "Unsafe" message was printed
|
|
# to the error log.
|
|
Occurrences: 1
|
|
create table t1 (n1 int, n2 int, n3 int,
|
|
key (n1, n2, n3),
|
|
key (n2, n3, n1),
|
|
key (n3, n1, n2));
|
|
insert into t1 values (1,1,1);
|
|
insert into t1 values (RAND()*1000+10, RAND()*1000+10, RAND()*1000+10);
|
|
update t1 set n1=rand() where n1=1;
|
|
Warnings:
|
|
Note 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
|
|
delete from t1 where n2=1 + rand()*0;
|
|
Warnings:
|
|
Note 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
|
|
drop table t1;
|
|
DROP TABLE IF EXISTS t1, t2;
|
|
CREATE TABLE t1 (a int);
|
|
CREATE TABLE t2 (a int auto_increment primary key, b int);
|
|
CREATE TRIGGER tr_bug50192 AFTER INSERT ON t1 FOR EACH ROW INSERT INTO t2 (b) VALUES (1);
|
|
CREATE FUNCTION sf_bug50192() RETURNS INTEGER
|
|
BEGIN
|
|
INSERT INTO t2(b) VALUES(2);
|
|
RETURN 1;
|
|
END |
|
|
INSERT INTO t1 VALUES (0);
|
|
Warnings:
|
|
Note 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
|
|
SHOW WARNINGS;
|
|
Level Code Message
|
|
Note 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
|
|
SELECT sf_bug50192();
|
|
sf_bug50192()
|
|
1
|
|
Warnings:
|
|
Note 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
|
|
SHOW WARNINGS;
|
|
Level Code Message
|
|
Note 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
|
|
CREATE TABLE t3 (a INT(11) DEFAULT NULL);
|
|
INSERT INTO t3 VALUES (1);
|
|
CREATE TABLE t4 (a INT(11) DEFAULT NULL, b BIGINT(20) DEFAULT uuid_short()) SELECT * FROM t3;
|
|
Warnings:
|
|
Note 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
|
|
SHOW WARNINGS;
|
|
Level Code Message
|
|
Note 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
|
|
CREATE OR REPLACE TABLE t4 (a INT(11) DEFAULT NULL) SELECT * FROM t3;
|
|
SHOW WARNINGS;
|
|
Level Code Message
|
|
DROP FUNCTION sf_bug50192;
|
|
DROP TRIGGER tr_bug50192;
|
|
DROP TABLE t1, t2, t3, t4;
|