mirror of
https://github.com/MariaDB/server.git
synced 2025-02-23 05:43:08 +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.
46 lines
2.7 KiB
Text
46 lines
2.7 KiB
Text
RESET MASTER;
|
|
SET @old_isolation_level= @@session.transaction_isolation;
|
|
SET @@session.transaction_isolation= 'READ-COMMITTED';
|
|
CREATE DATABASE b42829;
|
|
use b42829;
|
|
CREATE TABLE t1 (x int, y int) engine=InnoDB;
|
|
CREATE TABLE t2 (x int, y int) engine=InnoDB;
|
|
CREATE DATABASE b42829_filtered;
|
|
use b42829_filtered;
|
|
CREATE TABLE t1 (x int, y int) engine=InnoDB;
|
|
CREATE TABLE t2 (x int, y int) engine=InnoDB;
|
|
SET @@session.sql_log_bin= 0;
|
|
INSERT INTO b42829_filtered.t1 VALUES (100,100);
|
|
INSERT INTO b42829.t1 VALUES (100,100);
|
|
SET @@session.sql_log_bin= 1;
|
|
### assertion: the inserts will not raise log error because
|
|
### binlog-do-db is filtering used database
|
|
INSERT INTO t2 VALUES (1,2), (1,3), (1,4);
|
|
INSERT INTO t1 SELECT * FROM t2;
|
|
### assertion: assert that despite updating a not filtered
|
|
### database this wont trigger an error as the
|
|
### used database is the filtered one.
|
|
UPDATE b42829_filtered.t1 ft1, b42829.t1 nft1 SET ft1.x=1, nft1.x=2;
|
|
use b42829;
|
|
### assertion: the statements *will* raise log error because
|
|
### binlog-do-db is not filtering used database
|
|
BEGIN;
|
|
INSERT INTO t2 VALUES (1,2), (1,3), (1,4);
|
|
ERROR HY000: Cannot execute statement: impossible to write to binary log since BINLOG_FORMAT = STATEMENT and at least one table uses a storage engine limited to row-based logging. InnoDB is limited to row-logging when transaction isolation level is READ COMMITTED or READ UNCOMMITTED.
|
|
UPDATE b42829_filtered.t1 ft1, b42829.t1 nft1 SET ft1.x=1, nft1.x=2;
|
|
ERROR HY000: Cannot execute statement: impossible to write to binary log since BINLOG_FORMAT = STATEMENT and at least one table uses a storage engine limited to row-based logging. InnoDB is limited to row-logging when transaction isolation level is READ COMMITTED or READ UNCOMMITTED.
|
|
INSERT INTO t1 SELECT * FROM t2;
|
|
ERROR HY000: Cannot execute statement: impossible to write to binary log since BINLOG_FORMAT = STATEMENT and at least one table uses a storage engine limited to row-based logging. InnoDB is limited to row-logging when transaction isolation level is READ COMMITTED or READ UNCOMMITTED.
|
|
COMMIT;
|
|
### assertion: filtered events did not make into the binlog
|
|
include/show_binlog_events.inc
|
|
Log_name Pos Event_type Server_id End_log_pos Info
|
|
master-bin.000001 # Gtid # # GTID #-#-#
|
|
master-bin.000001 # Query # # CREATE DATABASE b42829
|
|
master-bin.000001 # Gtid # # GTID #-#-#
|
|
master-bin.000001 # Query # # use `b42829`; CREATE TABLE t1 (x int, y int) engine=InnoDB
|
|
master-bin.000001 # Gtid # # GTID #-#-#
|
|
master-bin.000001 # Query # # use `b42829`; CREATE TABLE t2 (x int, y int) engine=InnoDB
|
|
DROP DATABASE b42829;
|
|
DROP DATABASE b42829_filtered;
|
|
SET @@session.transaction_isolation= @old_isolation_level;
|