database/mariadb
1
0
Fork 0
mirror of https://github.com/MariaDB/server.git synced 2025-08-08 11:22:35 +03:00
Code Activity
Files
MDEV-36812
mariadb/mysql-test/suite/binlog/r/binlog_stm_mix_innodb_myisam.result
Monty f8ba5ced55 MDEV-36099 Ensure that creation and usage of temporary tables in replication is predictable 
MDEV-36563 Assertion `!mysql_bin_log.is_open()' failed in
           THD::mark_tmp_table_as_free_for_reuse

The purpose of this commit is to ensure that creation and changes of
temporary tables are properly and predicable logged to the binary
log.  It also fixes some bugs where ROW logging was used in MIXED mode,
when STATEMENT would be a better (and expected) choice.

In this comment STATEMENT stands for logging to binary log in
STATEMENT format, MIXED stands for MIXED binlog format and ROW for ROW
binlog format.

New rules for logging of temporary tables
- CREATE of temporary tables are now by default binlogged only if
  STATEMENT binlog format is used. If it is binlogged, 1 is stored in
  TABLE_SHARE->table_creation_was_logged. The user can change this
  behavior by setting create_temporary_table_binlog_formats to
  MIXED,STATEMENT in which case the create is logged in statement
  format also in MIXED mode (as before).
- Changes to temporary tables are only binlogged if and only if
  the CREATE was logged. The logging happens under STATEMENT or MIXED.
  If binlog_format=ROW, temporary table changes are not binlogged. A
  temporary table that are changed under ROW are marked as 'not up to
  date in binlog' and no future row changes are logged.  Any usage of
  this temporary table will force row logging of other tables in any
  future statements using the temporary table to be row logged.
- DROP TEMPORARY is binlogged only of the CREATE was binlogged.

Changes done:
- Row logging is forced for any statement using temporary tables that
  are not up to date in the binary log.
  (Before the row logging was forced if the user has a temporary table)
- If there is any changes to the temporary table that is not binlogged,
  the table is marked as not up to date.
- TABLE_SHARE->table_creation_was_logged has a new definition for
  temporary tables:
  0  Table creating was not logged to binary log
  1  Table creating was logged to binary log and table is up to date.
  2  Table creating was logged to binary log but some changes where
     not logged to binary log.
  Table is not up to date in binary log is defined as value 0 or 2.
- If a multi-table-update or multi-table-delete fails then
  all updated temporary tables are marked as not up to date.
- Enforce row logging if the query is using temporary tables
  that are not up to date.
  Before row logging was enforced if the user had any
  temporary tables.
- When dropping temporary tables use IF EXISTS. This ensures
  that slave will not stop if it had crashed and lost the
  temporary tables.
- Remove comment and version from DROP /*!4000 TEMPORARY.. generated when
  a connection closes that has open temporary tables. Added 'generated by
  server' at the end of the DROP.

Bugs fixed:
- When using temporary tables with commands that forced row based,
  like INSERT INTO temporary_table VALUES (UUID()), this was never
  logged which causes the temporary table to be inconsistent on
  master and slave.
- Used binlog format is now clearly defined. It is now only depending
  on the current binlog_format and the tables used.
  Before it was depending on the user had ANY temporary tables and
  the state of 'current_stmt_binlog_format' set by previous queries.
  This also caused temporary tables to be logged to binary log in
  some cases.
- CREATE TABLE t1 LIKE not_logged_temporary_table caused replication
  to stop.
- Rename of not binlogged temporary tables where binlogged to binary log
  which caused replication to stop.

Changes in behavior:

- By default create_temporary_table_binlog_formats=STATEMENT, which
  means that CREATE TEMPORARY is not logged to binary log under MIXED
  binary logging. This can be changed by setting
  create_temporary_table_binlog_formats to MIXED,STATEMENT.
- Using temporary tables that was not logged to the binary log will
  cause any query using them for updating other tables to be logged in
  ROW format. Before all queries was logged in ROW format if the user had
  any temporary tables, even if they were not used by the query.
- Generated DROP TEMPORARY TABLE is now always using IF EXISTS and
  has a "generated by server" comment in the binary log.

The consequences of the above is that manipulations of a lot of rows
through temporary tables will by default be be slower in mixed mode.

For example:
  BEGIN;
  CREATE TEMPORARY TABLE tmp AS SELECT a, b, c FROM
  large_table1 JOIN large_table2 ON ...;
  INSERT INTO other_table SELECT b, c FROM tmp WHERE a <100;
  DROP TEMPORARY TABLE tmp;
  COMMIT;

By default this will create a huge entry in the binary log, compared
to just a few hundred bytes in statement mode. However the change in
this commit will make usage of temporary tables more reliable and
predicable and is thus worth it. Using statement mode or
create_temporary_table_binlog_formats can be used to avoid this issue.
2025-04-28 12:59:38 +03:00

994 lines
37 KiB
Plaintext
Raw Permalink Blame History

CALL mtr.add_suppression("Statement may not be safe to log in statement format.");
call mtr.add_suppression("Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT");
drop table if exists t1, t2;
connect con1,localhost,root,,;
connect con2,localhost,root,,;
connection con1;
create table t1 (a int) engine=innodb;
create table t2 (a int) engine=myisam;
reset master;
begin;
insert into t1 values(1);
insert into t2 select * from t1;
Warnings:
Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement accesses nontransactional table as well as transactional or temporary table, and writes to any of them
commit;
include/show_binlog_events.inc
Log_name Pos Event_type Server_id End_log_pos Info
master-bin.000001 # Gtid # # BEGIN GTID #-#-#
master-bin.000001 # Query # # use `test`; insert into t1 values(1)
master-bin.000001 # Query # # use `test`; insert into t2 select * from t1
master-bin.000001 # Xid # # COMMIT /* XID */
delete from t1;
delete from t2;
reset master;
begin;
insert into t1 values(2);
insert into t2 select * from t1;
Warnings:
Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement accesses nontransactional table as well as transactional or temporary table, and writes to any of them
rollback;
Warnings:
Warning 1196 Some non-transactional changed tables couldn't be rolled back
include/show_binlog_events.inc
Log_name Pos Event_type Server_id End_log_pos Info
master-bin.000001 # Gtid # # BEGIN GTID #-#-#
master-bin.000001 # Query # # use `test`; insert into t1 values(2)
master-bin.000001 # Query # # use `test`; insert into t2 select * from t1
master-bin.000001 # Query # # ROLLBACK
delete from t1;
delete from t2;
reset master;
begin;
insert into t1 values(3);
savepoint my_savepoint;
insert into t1 values(4);
insert into t2 select * from t1;
Warnings:
Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement accesses nontransactional table as well as transactional or temporary table, and writes to any of them
rollback to savepoint my_savepoint;
Warnings:
Warning 1196 Some non-transactional changed tables couldn't be rolled back
commit;
include/show_binlog_events.inc
Log_name Pos Event_type Server_id End_log_pos Info
master-bin.000001 # Gtid # # BEGIN GTID #-#-#
master-bin.000001 # Query # # use `test`; insert into t1 values(3)
master-bin.000001 # Query # # SAVEPOINT `my_savepoint`
master-bin.000001 # Query # # use `test`; insert into t1 values(4)
master-bin.000001 # Query # # use `test`; insert into t2 select * from t1
master-bin.000001 # Query # # ROLLBACK TO `my_savepoint`
master-bin.000001 # Xid # # COMMIT /* XID */
delete from t1;
delete from t2;
reset master;
begin;
insert into t1 values(5);
savepoint my_savepoint;
insert into t1 values(6);
insert into t2 select * from t1;
Warnings:
Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement accesses nontransactional table as well as transactional or temporary table, and writes to any of them
rollback to savepoint my_savepoint;
Warnings:
Warning 1196 Some non-transactional changed tables couldn't be rolled back
insert into t1 values(7);
commit;
select a from t1 order by a;
a
5
7
include/show_binlog_events.inc
Log_name Pos Event_type Server_id End_log_pos Info
master-bin.000001 # Gtid # # BEGIN GTID #-#-#
master-bin.000001 # Query # # use `test`; insert into t1 values(5)
master-bin.000001 # Query # # SAVEPOINT `my_savepoint`
master-bin.000001 # Query # # use `test`; insert into t1 values(6)
master-bin.000001 # Query # # use `test`; insert into t2 select * from t1
master-bin.000001 # Query # # ROLLBACK TO `my_savepoint`
master-bin.000001 # Query # # use `test`; insert into t1 values(7)
master-bin.000001 # Xid # # COMMIT /* XID */
delete from t1;
delete from t2;
reset master;
select get_lock("a",10);
get_lock("a",10)
1
begin;
insert into t1 values(8);
insert into t2 select * from t1;
Warnings:
Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement accesses nontransactional table as well as transactional or temporary table, and writes to any of them
disconnect con1;
connection con2;
select get_lock("a",10);
get_lock("a",10)
1
include/show_binlog_events.inc
Log_name Pos Event_type Server_id End_log_pos Info
master-bin.000001 # Gtid # # BEGIN GTID #-#-#
master-bin.000001 # Query # # use `test`; insert into t1 values(8)
master-bin.000001 # Query # # use `test`; insert into t2 select * from t1
master-bin.000001 # Query # # ROLLBACK
delete from t1;
delete from t2;
reset master;
insert into t1 values(9);
insert into t2 select * from t1;
include/show_binlog_events.inc
Log_name Pos Event_type Server_id End_log_pos Info
master-bin.000001 # Gtid # # BEGIN GTID #-#-#
master-bin.000001 # Query # # use `test`; insert into t1 values(9)
master-bin.000001 # Xid # # COMMIT /* XID */
master-bin.000001 # Gtid # # BEGIN GTID #-#-#
master-bin.000001 # Query # # use `test`; insert into t2 select * from t1
master-bin.000001 # Query # # COMMIT
delete from t1;
delete from t2;
reset master;
insert into t1 values(10);
begin;
insert into t2 select * from t1;
include/show_binlog_events.inc
Log_name Pos Event_type Server_id End_log_pos Info
master-bin.000001 # Gtid # # BEGIN GTID #-#-#
master-bin.000001 # Query # # use `test`; insert into t1 values(10)
master-bin.000001 # Xid # # COMMIT /* XID */
master-bin.000001 # Gtid # # BEGIN GTID #-#-#
master-bin.000001 # Query # # use `test`; insert into t2 select * from t1
master-bin.000001 # Query # # COMMIT
insert into t1 values(11);
commit;
include/show_binlog_events.inc
Log_name Pos Event_type Server_id End_log_pos Info
master-bin.000001 # Gtid # # BEGIN GTID #-#-#
master-bin.000001 # Query # # use `test`; insert into t1 values(10)
master-bin.000001 # Xid # # COMMIT /* XID */
master-bin.000001 # Gtid # # BEGIN GTID #-#-#
master-bin.000001 # Query # # use `test`; insert into t2 select * from t1
master-bin.000001 # Query # # COMMIT
master-bin.000001 # Gtid # # BEGIN GTID #-#-#
master-bin.000001 # Query # # use `test`; insert into t1 values(11)
master-bin.000001 # Xid # # COMMIT /* XID */
alter table t2 engine=INNODB;
delete from t1;
delete from t2;
reset master;
begin;
insert into t1 values(12);
insert into t2 select * from t1;
commit;
include/show_binlog_events.inc
Log_name Pos Event_type Server_id End_log_pos Info
master-bin.000001 # Gtid # # BEGIN GTID #-#-#
master-bin.000001 # Query # # use `test`; insert into t1 values(12)
master-bin.000001 # Query # # use `test`; insert into t2 select * from t1
master-bin.000001 # Xid # # COMMIT /* XID */
delete from t1;
delete from t2;
reset master;
begin;
insert into t1 values(13);
insert into t2 select * from t1;
rollback;
include/show_binlog_events.inc
delete from t1;
delete from t2;
reset master;
begin;
insert into t1 values(14);
savepoint my_savepoint;
insert into t1 values(15);
insert into t2 select * from t1;
rollback to savepoint my_savepoint;
commit;
include/show_binlog_events.inc
Log_name Pos Event_type Server_id End_log_pos Info
master-bin.000001 # Gtid # # BEGIN GTID #-#-#
master-bin.000001 # Query # # use `test`; insert into t1 values(14)
master-bin.000001 # Query # # SAVEPOINT `my_savepoint`
master-bin.000001 # Xid # # COMMIT /* XID */
delete from t1;
delete from t2;
reset master;
begin;
insert into t1 values(16);
savepoint my_savepoint;
insert into t1 values(17);
insert into t2 select * from t1;
rollback to savepoint my_savepoint;
insert into t1 values(18);
commit;
select a from t1 order by a;
a
16
18
include/show_binlog_events.inc
Log_name Pos Event_type Server_id End_log_pos Info
master-bin.000001 # Gtid # # BEGIN GTID #-#-#
master-bin.000001 # Query # # use `test`; insert into t1 values(16)
master-bin.000001 # Query # # SAVEPOINT `my_savepoint`
master-bin.000001 # Query # # use `test`; insert into t1 values(18)
master-bin.000001 # Xid # # COMMIT /* XID */
connect con3,localhost,root,,;
connection con3;
delete from t1;
delete from t2;
alter table t2 engine=MyISAM;
insert into t1 values (1);
begin;
select * from t1 for update;
a
1
connection con2;
select (@before:=unix_timestamp())*0;
(@before:=unix_timestamp())*0
0
begin;
select * from t1 for update;
connection con3;
insert into t2 values (20);
connection con2;
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
select (@after:=unix_timestamp())*0;
(@after:=unix_timestamp())*0
0
select (@after-@before) >= 2;
(@after-@before) >= 2
1
connection con3;
commit;
connection con2;
drop table t1,t2;
commit;
connection con2;
begin;
create temporary table ti (a int) engine=innodb;
rollback;
insert into ti values(1);
set autocommit=0;
create temporary table t1 (a int) engine=myisam;
commit;
insert t1 values (1);
rollback;
Warnings:
Warning 1196 Some non-transactional changed tables couldn't be rolled back
create table t0 (n int);
insert t0 select * from t1;
set autocommit=1;
insert into t0 select GET_LOCK("lock1",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
set autocommit=0;
create table t2 (n int) engine=innodb;
insert into t2 values (3);
disconnect con2;
connection con3;
select get_lock("lock1",60);
get_lock("lock1",60)
1
include/show_binlog_events.inc
Log_name Pos Event_type Server_id End_log_pos Info
master-bin.000001 # Gtid # # BEGIN GTID #-#-#
master-bin.000001 # Query # # use `test`; insert into t1 values(16)
master-bin.000001 # Query # # SAVEPOINT `my_savepoint`
master-bin.000001 # Query # # use `test`; insert into t1 values(18)
master-bin.000001 # Xid # # COMMIT /* XID */
master-bin.000001 # Gtid # # BEGIN GTID #-#-#
master-bin.000001 # Query # # use `test`; delete from t1
master-bin.000001 # Xid # # COMMIT /* XID */
master-bin.000001 # Gtid # # BEGIN GTID #-#-#
master-bin.000001 # Query # # use `test`; delete from t2
master-bin.000001 # Xid # # COMMIT /* XID */
master-bin.000001 # Gtid # # GTID #-#-#
master-bin.000001 # Query # # use `test`; alter table t2 engine=MyISAM
master-bin.000001 # Gtid # # BEGIN GTID #-#-#
master-bin.000001 # Query # # use `test`; insert into t1 values (1)
master-bin.000001 # Xid # # COMMIT /* XID */
master-bin.000001 # Gtid # # BEGIN GTID #-#-#
master-bin.000001 # Query # # use `test`; insert into t2 values (20)
master-bin.000001 # Query # # COMMIT
master-bin.000001 # Gtid # # GTID #-#-#
master-bin.000001 # Query # # use `test`; DROP TABLE `t1`,`t2` /* generated by server */
master-bin.000001 # Gtid # # BEGIN GTID #-#-#
master-bin.000001 # Query # # use `test`; create temporary table ti (a int) engine=innodb
master-bin.000001 # Query # # ROLLBACK
master-bin.000001 # Gtid # # BEGIN GTID #-#-#
master-bin.000001 # Query # # use `test`; insert into ti values(1)
master-bin.000001 # Query # # COMMIT
master-bin.000001 # Gtid # # BEGIN GTID #-#-#
master-bin.000001 # Query # # use `test`; create temporary table t1 (a int) engine=myisam
master-bin.000001 # Query # # COMMIT
master-bin.000001 # Gtid # # BEGIN GTID #-#-#
master-bin.000001 # Query # # use `test`; insert t1 values (1)
master-bin.000001 # Query # # COMMIT
master-bin.000001 # Gtid # # GTID #-#-#
master-bin.000001 # Query # # use `test`; create table t0 (n int)
master-bin.000001 # Gtid # # BEGIN GTID #-#-#
master-bin.000001 # Query # # use `test`; insert t0 select * from t1
master-bin.000001 # Query # # COMMIT
master-bin.000001 # Gtid # # BEGIN GTID #-#-#
master-bin.000001 # Query # # use `test`; insert into t0 select GET_LOCK("lock1",0)
master-bin.000001 # Query # # COMMIT
master-bin.000001 # Gtid # # GTID #-#-#
master-bin.000001 # Query # # use `test`; create table t2 (n int) engine=innodb
master-bin.000001 # Gtid # # GTID #-#-#
master-bin.000001 # Query # # use `test`; DROP TEMPORARY TABLE IF EXISTS `t1`,`ti` /* generated by server */
do release_lock("lock1");
drop table t0,t2;
set autocommit=0;
CREATE TABLE t1 (a int, b int) engine=myisam;
reset master;
INSERT INTO t1 values (1,1),(1,2);
CREATE TABLE t2 (primary key (a)) engine=innodb select * from t1;
ERROR 23000: Duplicate entry '1' for key 'PRIMARY'
DROP TABLE if exists t2;
Warnings:
Note 1051 Unknown table 'test.t2'
INSERT INTO t1 values (3,3);
CREATE TEMPORARY TABLE t2 (primary key (a)) engine=innodb select * from t1;
ERROR 23000: Duplicate entry '1' for key 'PRIMARY'
ROLLBACK;
Warnings:
Warning 1196 Some non-transactional changed tables couldn't be rolled back
DROP TABLE IF EXISTS t2;
Warnings:
Note 1051 Unknown table 'test.t2'
CREATE TABLE t2 (a int, b int, primary key (a)) engine=innodb;
INSERT INTO t1 VALUES (4,4);
CREATE TABLE IF NOT EXISTS t2 (primary key (a)) engine=innodb select * from t1;
Warnings:
Note 1050 Table 't2' already exists
SELECT * from t2;
a b
TRUNCATE table t2;
INSERT INTO t1 VALUES (5,5);
INSERT INTO t2 select * from t1;
ERROR 23000: Duplicate entry '1' for key 'PRIMARY'
SELECT * FROM t2;
a b
DROP TABLE t2;
INSERT INTO t1 values (6,6);
CREATE TEMPORARY TABLE t2 (a int, b int, primary key (a)) engine=innodb ;
INSERT INTO t1 values (7,7);
Warnings:
Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it accesses a non-transactional table after accessing a transactional table within the same transaction
ROLLBACK;
Warnings:
Warning 1196 Some non-transactional changed tables couldn't be rolled back
INSERT INTO t1 values (8,8);
CREATE TEMPORARY TABLE IF NOT EXISTS t2 (primary key (a)) engine=innodb select * from t1;
Warnings:
Note 1050 Table 't2' already exists
COMMIT;
INSERT INTO t1 values (9,9);
CREATE TEMPORARY TABLE IF NOT EXISTS t2 (primary key (a)) engine=innodb select * from t1;
Warnings:
Note 1050 Table 't2' already exists
ROLLBACK;
Warnings:
Warning 1196 Some non-transactional changed tables couldn't be rolled back
SELECT * from t2;
a b
TRUNCATE table t2;
INSERT INTO t1 values (10,10);
INSERT INTO t2 select * from t1;
ERROR 23000: Duplicate entry '1' for key 'PRIMARY'
SELECT * from t1;
a b
1 1
1 2
3 3
4 4
5 5
6 6
7 7
8 8
9 9
10 10
INSERT INTO t2 values (100,100);
CREATE TEMPORARY TABLE IF NOT EXISTS t2 (primary key (a)) engine=innodb select * from t1;
Warnings:
Note 1050 Table 't2' already exists
COMMIT;
INSERT INTO t2 values (101,101);
CREATE TEMPORARY TABLE IF NOT EXISTS t2 (primary key (a)) engine=innodb select * from t1;
Warnings:
Note 1050 Table 't2' already exists
ROLLBACK;
SELECT * from t2;
a b
100 100
DROP TABLE t1,t2;
include/show_binlog_events.inc
Log_name Pos Event_type Server_id End_log_pos Info
master-bin.000001 # Gtid # # BEGIN GTID #-#-#
master-bin.000001 # Query # # use `test`; INSERT INTO t1 values (1,1),(1,2)
master-bin.000001 # Query # # COMMIT
master-bin.000001 # Gtid # # GTID #-#-#
master-bin.000001 # Query # # use `test`; DROP TABLE IF EXISTS `t2` /* generated by server */
master-bin.000001 # Gtid # # BEGIN GTID #-#-#
master-bin.000001 # Query # # use `test`; INSERT INTO t1 values (3,3)
master-bin.000001 # Query # # COMMIT
master-bin.000001 # Gtid # # GTID #-#-#
master-bin.000001 # Query # # use `test`; DROP TABLE IF EXISTS `t2` /* generated by server */
master-bin.000001 # Gtid # # GTID #-#-#
master-bin.000001 # Query # # use `test`; CREATE TABLE t2 (a int, b int, primary key (a)) engine=innodb
master-bin.000001 # Gtid # # BEGIN GTID #-#-#
master-bin.000001 # Query # # use `test`; INSERT INTO t1 VALUES (4,4)
master-bin.000001 # Query # # COMMIT
master-bin.000001 # Gtid # # GTID #-#-#
master-bin.000001 # Query # # use `test`; TRUNCATE table t2
master-bin.000001 # Gtid # # BEGIN GTID #-#-#
master-bin.000001 # Query # # use `test`; INSERT INTO t1 VALUES (5,5)
master-bin.000001 # Query # # COMMIT
master-bin.000001 # Gtid # # GTID #-#-#
master-bin.000001 # Query # # use `test`; DROP TABLE `t2` /* generated by server */
master-bin.000001 # Gtid # # BEGIN GTID #-#-#
master-bin.000001 # Query # # use `test`; INSERT INTO t1 values (6,6)
master-bin.000001 # Query # # COMMIT
master-bin.000001 # Gtid # # BEGIN GTID #-#-#
master-bin.000001 # Query # # use `test`; CREATE TEMPORARY TABLE t2 (a int, b int, primary key (a)) engine=innodb
master-bin.000001 # Query # # use `test`; INSERT INTO t1 values (7,7)
master-bin.000001 # Query # # ROLLBACK
master-bin.000001 # Gtid # # BEGIN GTID #-#-#
master-bin.000001 # Query # # use `test`; INSERT INTO t1 values (8,8)
master-bin.000001 # Query # # COMMIT
master-bin.000001 # Gtid # # BEGIN GTID #-#-#
master-bin.000001 # Query # # use `test`; INSERT INTO t1 values (9,9)
master-bin.000001 # Query # # COMMIT
master-bin.000001 # Gtid # # GTID #-#-#
master-bin.000001 # Query # # use `test`; TRUNCATE table t2
master-bin.000001 # Gtid # # BEGIN GTID #-#-#
master-bin.000001 # Query # # use `test`; INSERT INTO t1 values (10,10)
master-bin.000001 # Query # # COMMIT
master-bin.000001 # Gtid # # BEGIN GTID #-#-#
master-bin.000001 # Query # # use `test`; INSERT INTO t2 values (100,100)
master-bin.000001 # Query # # COMMIT
master-bin.000001 # Gtid # # GTID #-#-#
master-bin.000001 # Query # # DROP TEMPORARY TABLE IF EXISTS `test`.`t2` /* generated by server */
master-bin.000001 # Gtid # # GTID #-#-#
master-bin.000001 # Query # # use `test`; DROP TABLE `t1` /* generated by server */
connect con4,localhost,root,,;
connection con3;
reset master;
create table t1 (a int) engine=innodb;
create table t2 (a int) engine=myisam;
select get_lock("a",10);
get_lock("a",10)
1
begin;
insert into t1 values(8);
insert into t2 select * from t1;
Warnings:
Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement accesses nontransactional table as well as transactional or temporary table, and writes to any of them
disconnect con3;
connection con4;
select get_lock("a",10);
get_lock("a",10)
1
flush logs;
select
(@a:=load_file("MYSQLTEST_VARDIR/tmp/mix_innodb_myisam_binlog.output"))
is not null;
(@a:=load_file("MYSQLTEST_VARDIR/tmp/mix_innodb_myisam_binlog.output"))
is not null
1
select
@a like "%#%error_code=0%ROLLBACK\n/*!*/;%ROLLBACK /* added by mysqlbinlog */;%" OR
@a like "%#%error_code=0%ROLLBACK\r\n/*!*/;%ROLLBACK /* added by mysqlbinlog */;%",
@a not like "%#%error_code=%error_code=%";
@a like "%#%error_code=0%ROLLBACK\n/*!*/;%ROLLBACK /* added by mysqlbinlog */;%" OR
@a like "%#%error_code=0%ROLLBACK\r\n/*!*/;%ROLLBACK /* added by mysqlbinlog */;%" @a not like "%#%error_code=%error_code=%"
1 1
drop table t1, t2;
create temporary table tt (a int unique);
create table ti (a int) engine=innodb;
reset master;
begin;
insert into ti values (1);
insert into ti values (2) ;
insert into tt select * from ti;
rollback;
Warnings:
Warning 1196 Some non-transactional changed tables couldn't be rolled back
select count(*) from tt /* 2 */;
count(*)
2
include/show_binlog_events.inc
Log_name Pos Event_type Server_id End_log_pos Info
master-bin.000001 # Gtid # # BEGIN GTID #-#-#
master-bin.000001 # Query # # use `test`; insert into ti values (1)
master-bin.000001 # Query # # use `test`; insert into ti values (2)
master-bin.000001 # Query # # use `test`; insert into tt select * from ti
master-bin.000001 # Query # # ROLLBACK
select count(*) from ti /* zero */;
count(*)
0
insert into ti select * from tt;
select * from ti /* that is what slave would miss - a bug */;
a
1
2
delete from ti;
delete from tt where a=1;
reset master;
begin;
insert into ti values (1);
insert into ti values (2) /* to make the dup error in the following */;
insert into tt select * from ti /* one affected and error */;
ERROR 23000: Duplicate entry '2' for key 'a'
rollback;
Warnings:
Warning 1196 Some non-transactional changed tables couldn't be rolled back
include/show_binlog_events.inc
Log_name Pos Event_type Server_id End_log_pos Info
master-bin.000001 # Gtid # # BEGIN GTID #-#-#
master-bin.000001 # Query # # use `test`; insert into ti values (1)
master-bin.000001 # Query # # use `test`; insert into ti values (2) /* to make the dup error in the following */
master-bin.000001 # Query # # use `test`; insert into tt select * from ti /* one affected and error */
master-bin.000001 # Query # # ROLLBACK
select count(*) from ti /* zero */;
count(*)
0
insert into ti select * from tt;
select * from tt /* that is what otherwise slave missed - the bug */;
a
1
2
drop table ti, tt;
drop function if exists bug27417;
drop table if exists t1,t2;
CREATE TABLE t1 (a int NOT NULL auto_increment primary key) ENGINE=MyISAM;
CREATE TABLE t2 (a int NOT NULL auto_increment, PRIMARY KEY (a));
create function bug27417(n int)
RETURNS int(11)
begin
insert into t1 values (null);
return n;
end|
reset master;
insert into t2 values (bug27417(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
insert into t2 select bug27417(2);
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
reset master;
insert into t2 values (bug27417(2));
ERROR 23000: Duplicate entry '2' for key 'PRIMARY'
include/show_binlog_events.inc
Log_name Pos Event_type Server_id End_log_pos Info
master-bin.000001 # Gtid # # BEGIN GTID #-#-#
master-bin.000001 # Intvar # # INSERT_ID=3
master-bin.000001 # Query # # use `test`; insert into t2 values (bug27417(2))
master-bin.000001 # Query # # COMMIT
/* only (!) with fixes for #23333 will show there is the query */;
select count(*) from t1 /* must be 3 */;
count(*)
3
reset master;
select count(*) from t2;
count(*)
2
delete from t2 where a=bug27417(3);
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
select count(*) from t2 /* nothing got deleted */;
count(*)
2
include/show_binlog_events.inc
Log_name Pos Event_type Server_id End_log_pos Info
master-bin.000001 # Gtid # # BEGIN GTID #-#-#
master-bin.000001 # Intvar # # INSERT_ID=4
master-bin.000001 # Query # # use `test`; delete from t2 where a=bug27417(3)
master-bin.000001 # Query # # COMMIT
/* the query must be in regardless of #23333 */;
select count(*) from t1 /* must be 5 */;
count(*)
5
delete t2 from t2 where t2.a=bug27417(100) /* must not affect t2 */;
affected rows: 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
select count(*) from t1 /* must be 7 */;
count(*)
7
drop table t1,t2;
CREATE TABLE t1 (a int NOT NULL auto_increment primary key) ENGINE=MyISAM;
CREATE TABLE t2 (a int, PRIMARY KEY (a)) ENGINE=InnoDB;
CREATE TABLE t3 (a int, PRIMARY KEY (a), b int unique) ENGINE=MyISAM;
CREATE TABLE t4 (a int, PRIMARY KEY (a), b int unique) ENGINE=Innodb;
CREATE TABLE t5 (a int, PRIMARY KEY (a)) ENGINE=InnoDB;
insert into t2 values (1);
reset master;
insert into t2 values (bug27417(1));
ERROR 23000: Duplicate entry '1' for key 'PRIMARY'
include/show_binlog_events.inc
Log_name Pos Event_type Server_id End_log_pos Info
master-bin.000001 # Gtid # # BEGIN GTID #-#-#
master-bin.000001 # Intvar # # INSERT_ID=1
master-bin.000001 # Query # # use `test`; insert into t2 values (bug27417(1))
master-bin.000001 # Query # # ROLLBACK
/* the output must denote there is the query */;
select count(*) from t1 /* must be 1 */;
count(*)
1
delete from t1;
delete from t2;
insert into t2 values (2);
reset master;
insert into t2 select bug27417(1) union select bug27417(2);
ERROR 23000: Duplicate entry '2' for key 'PRIMARY'
include/show_binlog_events.inc
Log_name Pos Event_type Server_id End_log_pos Info
master-bin.000001 # Gtid # # BEGIN GTID #-#-#
master-bin.000001 # Intvar # # INSERT_ID=2
master-bin.000001 # Query # # use `test`; insert into t2 select bug27417(1) union select bug27417(2)
master-bin.000001 # Query # # ROLLBACK
/* the output must denote there is the query */;
select count(*) from t1 /* must be 2 */;
count(*)
2
delete from t1;
insert into t3 values (1,1),(2,3),(3,4);
reset master;
update t3 set b=b+bug27417(1);
ERROR 23000: Duplicate entry '4' for key 'b'
include/show_binlog_events.inc
Log_name Pos Event_type Server_id End_log_pos Info
master-bin.000001 # Gtid # # BEGIN GTID #-#-#
master-bin.000001 # Intvar # # INSERT_ID=4
master-bin.000001 # Query # # use `test`; update t3 set b=b+bug27417(1)
master-bin.000001 # Query # # COMMIT
/* the output must denote there is the query */;
select count(*) from t1 /* must be 2 */;
count(*)
2
delete from t3;
delete from t4;
insert into t3 values (1,1);
insert into t4 values (1,1),(2,2);
reset master;
UPDATE t4,t3 SET t4.a=t3.a + bug27417(1) /* top level non-ta table */;
ERROR 23000: Duplicate entry '2' for key 'PRIMARY'
include/show_binlog_events.inc
Log_name Pos Event_type Server_id End_log_pos Info
master-bin.000001 # Gtid # # BEGIN GTID #-#-#
master-bin.000001 # Intvar # # INSERT_ID=6
master-bin.000001 # Query # # use `test`; UPDATE t4,t3 SET t4.a=t3.a + bug27417(1) /* top level non-ta table */
master-bin.000001 # Query # # ROLLBACK
/* the output must denote there is the query */;
select count(*) from t1 /* must be 4 */;
count(*)
4
delete from t1;
delete from t3;
delete from t4;
insert into t3 values (1,1),(2,2);
insert into t4 values (1,1),(2,2);
reset master;
UPDATE t3,t4 SET t3.a=t4.a + bug27417(1);
ERROR 23000: Duplicate entry '2' for key 'PRIMARY'
select count(*) from t1 /* must be 1 */;
count(*)
1
drop table t4;
delete from t1;
delete from t2;
delete from t3;
insert into t2 values (1);
insert into t3 values (1,1);
create trigger trg_del before delete on t2 for each row
insert into t3 values (bug27417(1), 2);
reset master;
delete from t2;
ERROR 23000: Duplicate entry '1' for key 'PRIMARY'
include/show_binlog_events.inc
Log_name Pos Event_type Server_id End_log_pos Info
master-bin.000001 # Gtid # # BEGIN GTID #-#-#
master-bin.000001 # Intvar # # INSERT_ID=9
master-bin.000001 # Query # # use `test`; delete from t2
master-bin.000001 # Query # # ROLLBACK
/* the output must denote there is the query */;
select count(*) from t1 /* must be 1 */;
count(*)
1
drop trigger trg_del;
delete from t1;
delete from t2;
delete from t5;
create trigger trg_del_t2 after delete on t2 for each row
insert into t1 values (1);
insert into t2 values (2),(3);
insert into t5 values (1),(2);
reset master;
delete t2.* from t2,t5 where t2.a=t5.a + 1;
ERROR 23000: Duplicate entry '1' for key 'PRIMARY'
include/show_binlog_events.inc
Log_name Pos Event_type Server_id End_log_pos Info
master-bin.000001 # Gtid # # BEGIN GTID #-#-#
master-bin.000001 # Query # # use `test`; delete t2.* from t2,t5 where t2.a=t5.a + 1
master-bin.000001 # Query # # ROLLBACK
/* the output must denote there is the query */;
select count(*) from t1 /* must be 1 */;
count(*)
1
delete from t1;
create table t4 (a int default 0, b int primary key) engine=innodb;
insert into t4 values (0, 17);
reset master;
load data infile '../../std_data/rpl_loaddata.dat' into table t4 (a, @b) set b= @b + bug27417(2);
ERROR 23000: Duplicate entry '17' for key 'PRIMARY'
select * from t4;
a b
0 17
select count(*) from t1 /* must be 2 */;
count(*)
2
include/show_binlog_events.inc
Log_name Pos Event_type Server_id End_log_pos Info
master-bin.000001 # Gtid # # BEGIN GTID #-#-#
master-bin.000001 # Intvar # # INSERT_ID=10
master-bin.000001 # Begin_load_query # # ;file_id=#;block_len=#
master-bin.000001 # Intvar # # INSERT_ID=10
master-bin.000001 # Execute_load_query # # use `test`; LOAD DATA INFILE '../../std_data/rpl_loaddata.dat' INTO TABLE `t4` FIELDS TERMINATED BY '\t' ENCLOSED BY '' ESCAPED BY '\\' LINES TERMINATED BY '\n' (`a`, @`b`) SET `b`= @b + bug27417(2) ;file_id=#
master-bin.000001 # Query # # ROLLBACK
/* the output must denote there is the query */;
drop trigger trg_del_t2;
drop table t1,t2,t3,t4,t5;
drop function bug27417;
end of tests
set @@session.binlog_format=statement;
create temporary table tt (a int unique);
create table ti (a int) engine=innodb;
reset master;
begin;
insert into ti values (1);
insert into ti values (2) ;
insert into tt select * from ti;
rollback;
Warnings:
Warning 1196 Some non-transactional changed tables couldn't be rolled back
select count(*) from tt /* 2 */;
count(*)
2
include/show_binlog_events.inc
Log_name Pos Event_type Server_id End_log_pos Info
master-bin.000001 # Gtid # # BEGIN GTID #-#-#
master-bin.000001 # Query # # use `test`; insert into ti values (1)
master-bin.000001 # Query # # use `test`; insert into ti values (2)
master-bin.000001 # Query # # use `test`; insert into tt select * from ti
master-bin.000001 # Query # # ROLLBACK
select count(*) from ti /* zero */;
count(*)
0
insert into ti select * from tt;
select * from ti /* that is what slave would miss - bug#28960 */;
a
1
2
delete from ti;
delete from tt where a=1;
reset master;
begin;
insert into ti values (1);
insert into ti values (2) /* to make the dup error in the following */;
insert into tt select * from ti /* one affected and error */;
ERROR 23000: Duplicate entry '2' for key 'a'
rollback;
Warnings:
Warning 1196 Some non-transactional changed tables couldn't be rolled back
include/show_binlog_events.inc
Log_name Pos Event_type Server_id End_log_pos Info
master-bin.000001 # Gtid # # BEGIN GTID #-#-#
master-bin.000001 # Query # # use `test`; insert into ti values (1)
master-bin.000001 # Query # # use `test`; insert into ti values (2) /* to make the dup error in the following */
master-bin.000001 # Query # # use `test`; insert into tt select * from ti /* one affected and error */
master-bin.000001 # Query # # ROLLBACK
select count(*) from ti /* zero */;
count(*)
0
insert into ti select * from tt;
select * from tt /* that is what otherwise slave missed - the bug */;
a
1
2
drop table ti;
drop function if exists bug27417;
drop table if exists t1,t2;
CREATE TABLE t1 (a int NOT NULL auto_increment primary key) ENGINE=MyISAM;
CREATE TABLE t2 (a int NOT NULL auto_increment, PRIMARY KEY (a));
create function bug27417(n int)
RETURNS int(11)
begin
insert into t1 values (null);
return n;
end|
reset master;
insert into t2 values (bug27417(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
insert into t2 select bug27417(2);
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
reset master;
insert into t2 values (bug27417(2));
ERROR 23000: Duplicate entry '2' for key 'PRIMARY'
include/show_binlog_events.inc
Log_name Pos Event_type Server_id End_log_pos Info
master-bin.000001 # Gtid # # BEGIN GTID #-#-#
master-bin.000001 # Intvar # # INSERT_ID=3
master-bin.000001 # Query # # use `test`; insert into t2 values (bug27417(2))
master-bin.000001 # Query # # COMMIT
select count(*) from t1 /* must be 3 */;
count(*)
3
reset master;
select count(*) from t2;
count(*)
2
delete from t2 where a=bug27417(3);
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
select count(*) from t2 /* nothing got deleted */;
count(*)
2
include/show_binlog_events.inc
Log_name Pos Event_type Server_id End_log_pos Info
master-bin.000001 # Gtid # # BEGIN GTID #-#-#
master-bin.000001 # Intvar # # INSERT_ID=4
master-bin.000001 # Query # # use `test`; delete from t2 where a=bug27417(3)
master-bin.000001 # Query # # COMMIT
select count(*) from t1 /* must be 5 */;
count(*)
5
delete t2 from t2 where t2.a=bug27417(100) /* must not affect t2 */;
affected rows: 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
select count(*) from t1 /* must be 7 */;
count(*)
7
drop table t1,t2;
CREATE TABLE t1 (a int NOT NULL auto_increment primary key) ENGINE=MyISAM;
CREATE TABLE t2 (a int, PRIMARY KEY (a)) ENGINE=InnoDB;
CREATE TABLE t3 (a int, PRIMARY KEY (a), b int unique) ENGINE=MyISAM;
CREATE TABLE t4 (a int, PRIMARY KEY (a), b int unique) ENGINE=Innodb;
CREATE TABLE t5 (a int, PRIMARY KEY (a)) ENGINE=InnoDB;
insert into t2 values (1);
reset master;
insert into t2 values (bug27417(1));
ERROR 23000: Duplicate entry '1' for key 'PRIMARY'
include/show_binlog_events.inc
Log_name Pos Event_type Server_id End_log_pos Info
master-bin.000001 # Gtid # # BEGIN GTID #-#-#
master-bin.000001 # Intvar # # INSERT_ID=1
master-bin.000001 # Query # # use `test`; insert into t2 values (bug27417(1))
master-bin.000001 # Query # # ROLLBACK
select count(*) from t1 /* must be 1 */;
count(*)
1
delete from t1;
delete from t2;
insert into t2 values (2);
reset master;
insert into t2 select bug27417(1) union select bug27417(2);
ERROR 23000: Duplicate entry '2' for key 'PRIMARY'
include/show_binlog_events.inc
Log_name Pos Event_type Server_id End_log_pos Info
master-bin.000001 # Gtid # # BEGIN GTID #-#-#
master-bin.000001 # Intvar # # INSERT_ID=2
master-bin.000001 # Query # # use `test`; insert into t2 select bug27417(1) union select bug27417(2)
master-bin.000001 # Query # # ROLLBACK
select count(*) from t1 /* must be 2 */;
count(*)
2
delete from t1;
insert into t3 values (1,1),(2,3),(3,4);
reset master;
update t3 set b=b+bug27417(1);
ERROR 23000: Duplicate entry '4' for key 'b'
include/show_binlog_events.inc
Log_name Pos Event_type Server_id End_log_pos Info
master-bin.000001 # Gtid # # BEGIN GTID #-#-#
master-bin.000001 # Intvar # # INSERT_ID=4
master-bin.000001 # Query # # use `test`; update t3 set b=b+bug27417(1)
master-bin.000001 # Query # # COMMIT
select count(*) from t1 /* must be 2 */;
count(*)
2
delete from t3;
delete from t4;
insert into t3 values (1,1);
insert into t4 values (1,1),(2,2);
reset master;
UPDATE t4,t3 SET t4.a=t3.a + bug27417(1) /* top level non-ta table */;
ERROR 23000: Duplicate entry '2' for key 'PRIMARY'
include/show_binlog_events.inc
Log_name Pos Event_type Server_id End_log_pos Info
master-bin.000001 # Gtid # # BEGIN GTID #-#-#
master-bin.000001 # Intvar # # INSERT_ID=6
master-bin.000001 # Query # # use `test`; UPDATE t4,t3 SET t4.a=t3.a + bug27417(1) /* top level non-ta table */
master-bin.000001 # Query # # ROLLBACK
select count(*) from t1 /* must be 4 */;
count(*)
4
delete from t1;
delete from t3;
delete from t4;
insert into t3 values (1,1),(2,2);
insert into t4 values (1,1),(2,2);
reset master;
UPDATE t3,t4 SET t3.a = t4.a + bug27417(1) where t3.a = 1;
ERROR 23000: Duplicate entry '2' for key 'PRIMARY'
select count(*) from t1 /* must be 1 */;
count(*)
1
drop table t4;
delete from t1;
delete from t2;
delete from t3;
insert into t2 values (1);
insert into t3 values (1,1);
create trigger trg_del before delete on t2 for each row
insert into t3 values (bug27417(1), 2);
reset master;
delete from t2;
ERROR 23000: Duplicate entry '1' for key 'PRIMARY'
include/show_binlog_events.inc
Log_name Pos Event_type Server_id End_log_pos Info
master-bin.000001 # Gtid # # BEGIN GTID #-#-#
master-bin.000001 # Intvar # # INSERT_ID=9
master-bin.000001 # Query # # use `test`; delete from t2
master-bin.000001 # Query # # ROLLBACK
select count(*) from t1 /* must be 1 */;
count(*)
1
drop trigger trg_del;
delete from t1;
delete from t2;
delete from t5;
create trigger trg_del_t2 after delete on t2 for each row
insert into t1 values (1);
insert into t2 values (2),(3);
insert into t5 values (1),(2);
reset master;
delete t2.* from t2,t5 where t2.a=t5.a + 1;
ERROR 23000: Duplicate entry '1' for key 'PRIMARY'
include/show_binlog_events.inc
Log_name Pos Event_type Server_id End_log_pos Info
master-bin.000001 # Gtid # # BEGIN GTID #-#-#
master-bin.000001 # Query # # use `test`; delete t2.* from t2,t5 where t2.a=t5.a + 1
master-bin.000001 # Query # # ROLLBACK
select count(*) from t1 /* must be 1 */;
count(*)
1
delete from t1;
create table t4 (a int default 0, b int primary key) engine=innodb;
insert into t4 values (0, 17);
reset master;
load data infile '../../std_data/rpl_loaddata.dat' into table t4 (a, @b) set b= @b + bug27417(2);
ERROR 23000: Duplicate entry '17' for key 'PRIMARY'
select * from t4;
a b
0 17
select count(*) from t1 /* must be 2 */;
count(*)
2
include/show_binlog_events.inc
Log_name Pos Event_type Server_id End_log_pos Info
master-bin.000001 # Gtid # # BEGIN GTID #-#-#
master-bin.000001 # Intvar # # INSERT_ID=10
master-bin.000001 # User var # # @`b`=_utf8mb4 X'3135' COLLATE utf8mb4_uca1400_ai_ci
master-bin.000001 # Begin_load_query # # ;file_id=#;block_len=#
master-bin.000001 # Intvar # # INSERT_ID=10
master-bin.000001 # User var # # @`b`=_utf8mb4 X'3135' COLLATE utf8mb4_uca1400_ai_ci
master-bin.000001 # Execute_load_query # # use `test`; LOAD DATA INFILE '../../std_data/rpl_loaddata.dat' INTO TABLE `t4` FIELDS TERMINATED BY '\t' ENCLOSED BY '' ESCAPED BY '\\' LINES TERMINATED BY '\n' (`a`, @`b`) SET `b`= @b + bug27417(2) ;file_id=#
master-bin.000001 # Query # # ROLLBACK
drop trigger trg_del_t2;
drop table t1,t2,t3,t4,t5;
drop function bug27417;
set @@session.binlog_format=@@global.binlog_format;
end of tests
View Git Blame Copy Permalink