mirror of
https://github.com/MariaDB/server.git
synced 2025-10-30 18:36:12 +01:00
d20a96f9c1
In MariaDB, we have a confusing problem where: * The transaction_isolation option can be set in a configuration file, but it cannot be set dynamically. * The tx_isolation system variable can be set dynamically, but it cannot be set in a configuration file. Therefore, we have two different names for the same thing in different contexts. This is needlessly confusing, and it complicates the documentation. The same thing applys for transaction_read_only. MySQL 5.7 solved this problem by making them into system variables. https://dev.mysql.com/doc/relnotes/mysql/5.7/en/news-5-7-20.html This commit takes a similar approach by adding new system variables and marking the original ones as deprecated. This commit also resolves some legacy problems related to SET STATEMENT and transaction_isolation.
90 lines
2.9 KiB
Text
90 lines
2.9 KiB
Text
# BUG#42829: binlogging enabled for all schemas regardless of
|
|
# binlog-db-db / binlog-ignore-db
|
|
#
|
|
# WHAT
|
|
# ====
|
|
#
|
|
# We want to test whether filtered events from binlog will cause
|
|
# raising an error mentioning that statement is unable to be logged or
|
|
# not, when:
|
|
#
|
|
# 1. isolation level READ-COMMITTED; AND
|
|
#
|
|
# 2. using InnoDB engine; AND
|
|
#
|
|
# 3. using SBL (in which case InnoDB will only allow RBL).
|
|
#
|
|
# HOW
|
|
# ===
|
|
#
|
|
# The test is implemented as follows:
|
|
#
|
|
# i) set transaction_isolation to read-committed.
|
|
#
|
|
# ii) create two databases (one filtered other not - using
|
|
# binlog-do-db)
|
|
#
|
|
# iii) Create statements that are to be filtered on filtered db
|
|
#
|
|
# - At this point, before fix, an error would be raised
|
|
#
|
|
# iv) do the same thing for not the filtered database and check
|
|
# that events throw an error:
|
|
#
|
|
# - Error: ER_BINLOG_STMT_MODE_AND_ROW_ENGINE
|
|
#
|
|
|
|
-- source include/have_log_bin.inc
|
|
-- source include/have_innodb.inc
|
|
-- source include/have_binlog_format_statement.inc
|
|
RESET MASTER; # clear up binlogs
|
|
SET @old_isolation_level= @@session.transaction_isolation;
|
|
SET @@session.transaction_isolation= 'READ-COMMITTED';
|
|
|
|
-- let $engine= InnoDB
|
|
-- let $filtered= b42829_filtered
|
|
-- let $not_filtered= b42829
|
|
|
|
-- eval CREATE DATABASE $not_filtered
|
|
-- eval use $not_filtered
|
|
-- eval CREATE TABLE t1 (x int, y int) engine=$engine
|
|
-- eval CREATE TABLE t2 (x int, y int) engine=$engine
|
|
|
|
-- eval CREATE DATABASE $filtered
|
|
-- eval use $filtered
|
|
-- eval CREATE TABLE t1 (x int, y int) engine=$engine
|
|
-- eval CREATE TABLE t2 (x int, y int) engine=$engine
|
|
|
|
SET @@session.sql_log_bin= 0;
|
|
-- eval INSERT INTO $filtered.t1 VALUES (100,100)
|
|
-- eval INSERT INTO $not_filtered.t1 VALUES (100,100)
|
|
SET @@session.sql_log_bin= 1;
|
|
|
|
-- echo ### assertion: the inserts will not raise log error because
|
|
-- echo ### binlog-do-db is filtering used database
|
|
INSERT INTO t2 VALUES (1,2), (1,3), (1,4);
|
|
INSERT INTO t1 SELECT * FROM t2;
|
|
|
|
-- echo ### assertion: assert that despite updating a not filtered
|
|
-- echo ### database this wont trigger an error as the
|
|
-- echo ### used database is the filtered one.
|
|
-- eval UPDATE $filtered.t1 ft1, $not_filtered.t1 nft1 SET ft1.x=1, nft1.x=2
|
|
|
|
-- eval use $not_filtered
|
|
-- echo ### assertion: the statements *will* raise log error because
|
|
-- echo ### binlog-do-db is not filtering used database
|
|
BEGIN;
|
|
-- error ER_BINLOG_STMT_MODE_AND_ROW_ENGINE
|
|
INSERT INTO t2 VALUES (1,2), (1,3), (1,4);
|
|
-- error ER_BINLOG_STMT_MODE_AND_ROW_ENGINE
|
|
-- eval UPDATE $filtered.t1 ft1, $not_filtered.t1 nft1 SET ft1.x=1, nft1.x=2
|
|
-- error ER_BINLOG_STMT_MODE_AND_ROW_ENGINE
|
|
INSERT INTO t1 SELECT * FROM t2;
|
|
COMMIT;
|
|
|
|
-- echo ### assertion: filtered events did not make into the binlog
|
|
source include/show_binlog_events.inc;
|
|
|
|
-- eval DROP DATABASE $not_filtered
|
|
-- eval DROP DATABASE $filtered
|
|
SET @@session.transaction_isolation= @old_isolation_level;
|