mirror of
https://github.com/MariaDB/server.git
synced 2025-02-22 21:33:08 +01:00
5001300bd4
We now allow multitable queries with order by and limit, such as: delete t1.*, t2.* from t1, t2 order by t1.id desc limit 3; To predict what rows will be deleted, run the equivalent select: select t1.*, t2.* from t1, t2 order by t1.id desc limit 3; Additionally, index hints are now supported with single table delete statements: delete from t2 use index(xid) order by (id) limit 2; This approach changes the multi_delete SELECT result interceptor to use a temporary table to collect row ids pertaining to the rows that will be deleted, rather than directly deleting rows from the target table(s). Row ids are collected during send_data, then read during send_eof to delete target rows. In the event that the temporary table created in memory is not big enough for all matching rows, it is converted to an aria table. Other changes: - Deleting from a sequence now affects zero rows instead of emitting an error Limitations: - The federated connector does not create implicit row ids, so we to use a key when conditionally deleting. See the change in federated_maybe_16324629.test
720 lines
26 KiB
Text
720 lines
26 KiB
Text
set @old_binlog_checksum=@@binlog_checksum;
|
|
set global binlog_checksum=NONE;
|
|
set sql_mode="";
|
|
SET timestamp=1000000000;
|
|
RESET MASTER;
|
|
CREATE DATABASE test1;
|
|
CREATE TABLE test1.t1(a int);
|
|
CREATE DATABASE test2;
|
|
CREATE TABLE test2.t2(a int);
|
|
CREATE VIEW test2.v2 AS SELECT * FROM test2.t2;
|
|
CREATE DATABASE test3;
|
|
CREATE TABLE test3.t3(a int);
|
|
CREATE DATABASE xtest1;
|
|
CREATE TABLE xtest1.xt1(a int);
|
|
CREATE DATABASE xtest2;
|
|
CREATE TABLE xtest2.xt2(a int);
|
|
INSERT INTO test1.t1 VALUES (1), (2), (3);
|
|
SET SESSION binlog_annotate_row_events = ON;
|
|
INSERT INTO test2.t2 VALUES (1), (2), (3);
|
|
INSERT INTO test3.t3 VALUES (1), (2), (3);
|
|
DELETE test1.t1, test2.t2
|
|
FROM test1.t1 INNER JOIN test2.t2 INNER JOIN test3.t3
|
|
WHERE test1.t1.a=test2.t2.a AND test2.t2.a=test3.t3.a;
|
|
INSERT INTO xtest1.xt1 VALUES (1), (2), (3);
|
|
INSERT INTO test2.v2 VALUES (1), (2), (3);
|
|
DELETE xtest1.xt1, test2.t2
|
|
FROM xtest1.xt1 INNER JOIN test2.t2 INNER JOIN test3.t3
|
|
WHERE xtest1.xt1.a=test2.t2.a AND test2.t2.a=test3.t3.a;
|
|
INSERT INTO xtest1.xt1 VALUES (1), (2), (3);
|
|
INSERT INTO xtest2.xt2 VALUES (1), (2), (3);
|
|
DELETE xtest1.xt1, xtest2.xt2
|
|
FROM xtest1.xt1 INNER JOIN xtest2.xt2 INNER JOIN test3.t3
|
|
WHERE xtest1.xt1.a=xtest2.xt2.a AND xtest2.xt2.a=test3.t3.a;
|
|
FLUSH LOGS;
|
|
#####################################################################################
|
|
# The following Annotate_rows events should appear below:
|
|
# - INSERT INTO test2.t2 VALUES (1), (2), (3)
|
|
# - INSERT INTO test3.t3 VALUES (1), (2), (3)
|
|
# - DELETE test1.t1, test2.t2 FROM <...>
|
|
# - INSERT INTO test2.t2 VALUES (1), (2), (3)
|
|
# - DELETE xtest1.xt1, test2.t2 FROM <...>
|
|
#####################################################################################
|
|
include/show_binlog_events.inc
|
|
Log_name Pos Event_type Server_id End_log_pos Info
|
|
master-bin.000001 # Binlog_checkpoint # # master-bin.000001
|
|
master-bin.000001 # Gtid # # GTID #-#-#
|
|
master-bin.000001 # Query # # CREATE DATABASE test1
|
|
master-bin.000001 # Gtid # # GTID #-#-#
|
|
master-bin.000001 # Query # # CREATE DATABASE test2
|
|
master-bin.000001 # Gtid # # GTID #-#-#
|
|
master-bin.000001 # Query # # CREATE DATABASE test3
|
|
master-bin.000001 # Gtid # # BEGIN GTID #-#-#
|
|
master-bin.000001 # Annotate_rows # # INSERT INTO test1.t1 VALUES (1), (2), (3)
|
|
master-bin.000001 # Table_map # # table_id: # (test1.t1)
|
|
master-bin.000001 # Write_rows_v1 # # table_id: # flags: STMT_END_F
|
|
master-bin.000001 # Query # # COMMIT
|
|
master-bin.000001 # Gtid # # BEGIN GTID #-#-#
|
|
master-bin.000001 # Annotate_rows # # INSERT INTO test2.t2 VALUES (1), (2), (3)
|
|
master-bin.000001 # Table_map # # table_id: # (test2.t2)
|
|
master-bin.000001 # Write_rows_v1 # # table_id: # flags: STMT_END_F
|
|
master-bin.000001 # Query # # COMMIT
|
|
master-bin.000001 # Gtid # # BEGIN GTID #-#-#
|
|
master-bin.000001 # Annotate_rows # # INSERT INTO test3.t3 VALUES (1), (2), (3)
|
|
master-bin.000001 # Table_map # # table_id: # (test3.t3)
|
|
master-bin.000001 # Write_rows_v1 # # table_id: # flags: STMT_END_F
|
|
master-bin.000001 # Query # # COMMIT
|
|
master-bin.000001 # Gtid # # BEGIN GTID #-#-#
|
|
master-bin.000001 # Annotate_rows # # DELETE test1.t1, test2.t2
|
|
FROM test1.t1 INNER JOIN test2.t2 INNER JOIN test3.t3
|
|
WHERE test1.t1.a=test2.t2.a AND test2.t2.a=test3.t3.a
|
|
master-bin.000001 # Table_map # # table_id: # (test1.t1)
|
|
master-bin.000001 # Table_map # # table_id: # (test2.t2)
|
|
master-bin.000001 # Delete_rows_v1 # # table_id: #
|
|
master-bin.000001 # Delete_rows_v1 # # table_id: # flags: STMT_END_F
|
|
master-bin.000001 # Query # # COMMIT
|
|
master-bin.000001 # Gtid # # BEGIN GTID #-#-#
|
|
master-bin.000001 # Annotate_rows # # INSERT INTO test2.v2 VALUES (1), (2), (3)
|
|
master-bin.000001 # Table_map # # table_id: # (test2.t2)
|
|
master-bin.000001 # Write_rows_v1 # # table_id: # flags: STMT_END_F
|
|
master-bin.000001 # Query # # COMMIT
|
|
master-bin.000001 # Gtid # # BEGIN GTID #-#-#
|
|
master-bin.000001 # Annotate_rows # # DELETE xtest1.xt1, test2.t2
|
|
FROM xtest1.xt1 INNER JOIN test2.t2 INNER JOIN test3.t3
|
|
WHERE xtest1.xt1.a=test2.t2.a AND test2.t2.a=test3.t3.a
|
|
master-bin.000001 # Table_map # # table_id: # (test2.t2)
|
|
master-bin.000001 # Delete_rows_v1 # # table_id: # flags: STMT_END_F
|
|
master-bin.000001 # Query # # COMMIT
|
|
master-bin.000001 # Rotate # # master-bin.000002;pos=POS
|
|
#
|
|
#####################################################################################
|
|
# mysqlbinlog --read-from-remote-server
|
|
# The following Annotates should appear in this output:
|
|
# - INSERT INTO test2.t2 VALUES (1), (2), (3)
|
|
# - INSERT INTO test3.t3 VALUES (1), (2), (3)
|
|
# - DELETE test1.t1, test2.t2 FROM <...> (with two subsequent Table maps)
|
|
# - INSERT INTO test2.t2 VALUES (1), (2), (3)
|
|
# - DELETE xtest1.xt1, test2.t2 FROM <...> (with one subsequent Table map)
|
|
#####################################################################################
|
|
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
|
|
/*!40019 SET @@session.max_delayed_threads=0*/;
|
|
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
|
|
DELIMITER /*!*/;
|
|
# at #
|
|
#010909 4:46:40 server id # end_log_pos # Start: binlog v 4, server v #.##.## created 010909 4:46:40 at startup
|
|
ROLLBACK/*!*/;
|
|
# at #
|
|
#010909 4:46:40 server id # end_log_pos # Ignorable
|
|
# Ignorable event type 164 (Start_encryption)
|
|
# at #
|
|
#010909 4:46:40 server id # end_log_pos # Gtid list []
|
|
# at #
|
|
#010909 4:46:40 server id # end_log_pos # Binlog checkpoint master-bin.000001
|
|
# at #
|
|
#010909 4:46:40 server id # end_log_pos # GTID 0-1-1 ddl thread_id=#
|
|
/*M!100101 SET @@session.skip_parallel_replication=0*//*!*/;
|
|
/*M!100001 SET @@session.gtid_domain_id=0*//*!*/;
|
|
/*M!100001 SET @@session.server_id=1*//*!*/;
|
|
/*M!100001 SET @@session.gtid_seq_no=1*//*!*/;
|
|
# at #
|
|
#010909 4:46:40 server id # end_log_pos # Query thread_id=# exec_time=# error_code=0 xid=<xid>
|
|
SET TIMESTAMP=1000000000/*!*/;
|
|
SET @@session.pseudo_thread_id=#/*!*/;
|
|
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1, @@session.check_constraint_checks=1, @@session.sql_if_exists=0, @@session.explicit_defaults_for_timestamp=1, @@session.system_versioning_insert_history=0/*!*/;
|
|
SET @@session.sql_mode=0/*!*/;
|
|
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
|
|
/*!\C latin1 *//*!*/;
|
|
SET @@session.character_set_client=X,@@session.collation_connection=X,@@session.collation_server=X/*!*/;
|
|
SET @@session.lc_time_names=0/*!*/;
|
|
SET @@session.collation_database=DEFAULT/*!*/;
|
|
CREATE DATABASE test1
|
|
/*!*/;
|
|
# at #
|
|
#010909 4:46:40 server id # end_log_pos # GTID 0-1-2 ddl thread_id=#
|
|
/*M!100001 SET @@session.gtid_seq_no=2*//*!*/;
|
|
# at #
|
|
#010909 4:46:40 server id # end_log_pos # Query thread_id=# exec_time=# error_code=0 xid=<xid>
|
|
SET TIMESTAMP=1000000000/*!*/;
|
|
CREATE DATABASE test2
|
|
/*!*/;
|
|
# at #
|
|
#010909 4:46:40 server id # end_log_pos # GTID 0-1-3 ddl thread_id=#
|
|
/*M!100001 SET @@session.gtid_seq_no=3*//*!*/;
|
|
# at #
|
|
#010909 4:46:40 server id # end_log_pos # Query thread_id=# exec_time=# error_code=0 xid=<xid>
|
|
SET TIMESTAMP=1000000000/*!*/;
|
|
CREATE DATABASE test3
|
|
/*!*/;
|
|
# at #
|
|
#010909 4:46:40 server id # end_log_pos # GTID 0-1-4 thread_id=#
|
|
/*M!100001 SET @@session.gtid_seq_no=4*//*!*/;
|
|
START TRANSACTION
|
|
/*!*/;
|
|
# at #
|
|
# at #
|
|
#010909 4:46:40 server id # end_log_pos # Annotate_rows:
|
|
#Q> INSERT INTO test1.t1 VALUES (1), (2), (3)
|
|
#010909 4:46:40 server id # end_log_pos # Table_map: `test1`.`t1` mapped to number #
|
|
# at #
|
|
#010909 4:46:40 server id # end_log_pos # Write_rows: table id # flags: STMT_END_F
|
|
### INSERT INTO `test1`.`t1`
|
|
### SET
|
|
### @1=1 /* INT meta=0 nullable=1 is_null=0 */
|
|
### INSERT INTO `test1`.`t1`
|
|
### SET
|
|
### @1=2 /* INT meta=0 nullable=1 is_null=0 */
|
|
### INSERT INTO `test1`.`t1`
|
|
### SET
|
|
### @1=3 /* INT meta=0 nullable=1 is_null=0 */
|
|
# Number of rows: 3
|
|
# at #
|
|
#010909 4:46:40 server id # end_log_pos # Query thread_id=# exec_time=# error_code=0 xid=<xid>
|
|
SET TIMESTAMP=1000000000/*!*/;
|
|
COMMIT
|
|
/*!*/;
|
|
# at #
|
|
#010909 4:46:40 server id # end_log_pos # GTID 0-1-5 thread_id=#
|
|
/*M!100001 SET @@session.gtid_seq_no=5*//*!*/;
|
|
START TRANSACTION
|
|
/*!*/;
|
|
# at #
|
|
# at #
|
|
#010909 4:46:40 server id # end_log_pos # Annotate_rows:
|
|
#Q> INSERT INTO test2.t2 VALUES (1), (2), (3)
|
|
#010909 4:46:40 server id # end_log_pos # Table_map: `test2`.`t2` mapped to number #
|
|
# at #
|
|
#010909 4:46:40 server id # end_log_pos # Write_rows: table id # flags: STMT_END_F
|
|
### INSERT INTO `test2`.`t2`
|
|
### SET
|
|
### @1=1 /* INT meta=0 nullable=1 is_null=0 */
|
|
### INSERT INTO `test2`.`t2`
|
|
### SET
|
|
### @1=2 /* INT meta=0 nullable=1 is_null=0 */
|
|
### INSERT INTO `test2`.`t2`
|
|
### SET
|
|
### @1=3 /* INT meta=0 nullable=1 is_null=0 */
|
|
# Number of rows: 3
|
|
# at #
|
|
#010909 4:46:40 server id # end_log_pos # Query thread_id=# exec_time=# error_code=0 xid=<xid>
|
|
SET TIMESTAMP=1000000000/*!*/;
|
|
COMMIT
|
|
/*!*/;
|
|
# at #
|
|
#010909 4:46:40 server id # end_log_pos # GTID 0-1-6 thread_id=#
|
|
/*M!100001 SET @@session.gtid_seq_no=6*//*!*/;
|
|
START TRANSACTION
|
|
/*!*/;
|
|
# at #
|
|
# at #
|
|
#010909 4:46:40 server id # end_log_pos # Annotate_rows:
|
|
#Q> INSERT INTO test3.t3 VALUES (1), (2), (3)
|
|
#010909 4:46:40 server id # end_log_pos # Table_map: `test3`.`t3` mapped to number #
|
|
# at #
|
|
#010909 4:46:40 server id # end_log_pos # Write_rows: table id # flags: STMT_END_F
|
|
### INSERT INTO `test3`.`t3`
|
|
### SET
|
|
### @1=1 /* INT meta=0 nullable=1 is_null=0 */
|
|
### INSERT INTO `test3`.`t3`
|
|
### SET
|
|
### @1=2 /* INT meta=0 nullable=1 is_null=0 */
|
|
### INSERT INTO `test3`.`t3`
|
|
### SET
|
|
### @1=3 /* INT meta=0 nullable=1 is_null=0 */
|
|
# Number of rows: 3
|
|
# at #
|
|
#010909 4:46:40 server id # end_log_pos # Query thread_id=# exec_time=# error_code=0 xid=<xid>
|
|
SET TIMESTAMP=1000000000/*!*/;
|
|
COMMIT
|
|
/*!*/;
|
|
# at #
|
|
#010909 4:46:40 server id # end_log_pos # GTID 0-1-7 thread_id=#
|
|
/*M!100001 SET @@session.gtid_seq_no=7*//*!*/;
|
|
START TRANSACTION
|
|
/*!*/;
|
|
# at #
|
|
# at #
|
|
#010909 4:46:40 server id # end_log_pos # Annotate_rows:
|
|
#Q> DELETE test1.t1, test2.t2
|
|
#Q> FROM test1.t1 INNER JOIN test2.t2 INNER JOIN test3.t3
|
|
#Q> WHERE test1.t1.a=test2.t2.a AND test2.t2.a=test3.t3.a
|
|
#010909 4:46:40 server id # end_log_pos # Table_map: `test1`.`t1` mapped to number #
|
|
# at #
|
|
#010909 4:46:40 server id # end_log_pos # Table_map: `test2`.`t2` mapped to number #
|
|
# at #
|
|
# at #
|
|
#010909 4:46:40 server id # end_log_pos # Delete_rows: table id #
|
|
#010909 4:46:40 server id # end_log_pos # Delete_rows: table id # flags: STMT_END_F
|
|
### DELETE FROM `test1`.`t1`
|
|
### WHERE
|
|
### @1=1 /* INT meta=0 nullable=1 is_null=0 */
|
|
### DELETE FROM `test1`.`t1`
|
|
### WHERE
|
|
### @1=2 /* INT meta=0 nullable=1 is_null=0 */
|
|
### DELETE FROM `test1`.`t1`
|
|
### WHERE
|
|
### @1=3 /* INT meta=0 nullable=1 is_null=0 */
|
|
### DELETE FROM `test2`.`t2`
|
|
### WHERE
|
|
### @1=1 /* INT meta=0 nullable=1 is_null=0 */
|
|
### DELETE FROM `test2`.`t2`
|
|
### WHERE
|
|
### @1=2 /* INT meta=0 nullable=1 is_null=0 */
|
|
### DELETE FROM `test2`.`t2`
|
|
### WHERE
|
|
### @1=3 /* INT meta=0 nullable=1 is_null=0 */
|
|
# Number of rows: 6
|
|
# at #
|
|
#010909 4:46:40 server id # end_log_pos # Query thread_id=# exec_time=# error_code=0 xid=<xid>
|
|
SET TIMESTAMP=1000000000/*!*/;
|
|
COMMIT
|
|
/*!*/;
|
|
# at #
|
|
#010909 4:46:40 server id # end_log_pos # GTID 0-1-8 thread_id=#
|
|
/*M!100001 SET @@session.gtid_seq_no=8*//*!*/;
|
|
START TRANSACTION
|
|
/*!*/;
|
|
# at #
|
|
# at #
|
|
#010909 4:46:40 server id # end_log_pos # Annotate_rows:
|
|
#Q> INSERT INTO test2.v2 VALUES (1), (2), (3)
|
|
#010909 4:46:40 server id # end_log_pos # Table_map: `test2`.`t2` mapped to number #
|
|
# at #
|
|
#010909 4:46:40 server id # end_log_pos # Write_rows: table id # flags: STMT_END_F
|
|
### INSERT INTO `test2`.`t2`
|
|
### SET
|
|
### @1=1 /* INT meta=0 nullable=1 is_null=0 */
|
|
### INSERT INTO `test2`.`t2`
|
|
### SET
|
|
### @1=2 /* INT meta=0 nullable=1 is_null=0 */
|
|
### INSERT INTO `test2`.`t2`
|
|
### SET
|
|
### @1=3 /* INT meta=0 nullable=1 is_null=0 */
|
|
# Number of rows: 3
|
|
# at #
|
|
#010909 4:46:40 server id # end_log_pos # Query thread_id=# exec_time=# error_code=0 xid=<xid>
|
|
SET TIMESTAMP=1000000000/*!*/;
|
|
COMMIT
|
|
/*!*/;
|
|
# at #
|
|
#010909 4:46:40 server id # end_log_pos # GTID 0-1-9 thread_id=#
|
|
/*M!100001 SET @@session.gtid_seq_no=9*//*!*/;
|
|
START TRANSACTION
|
|
/*!*/;
|
|
# at #
|
|
# at #
|
|
#010909 4:46:40 server id # end_log_pos # Annotate_rows:
|
|
#Q> DELETE xtest1.xt1, test2.t2
|
|
#Q> FROM xtest1.xt1 INNER JOIN test2.t2 INNER JOIN test3.t3
|
|
#Q> WHERE xtest1.xt1.a=test2.t2.a AND test2.t2.a=test3.t3.a
|
|
#010909 4:46:40 server id # end_log_pos # Table_map: `test2`.`t2` mapped to number #
|
|
# at #
|
|
#010909 4:46:40 server id # end_log_pos # Delete_rows: table id # flags: STMT_END_F
|
|
### DELETE FROM `test2`.`t2`
|
|
### WHERE
|
|
### @1=1 /* INT meta=0 nullable=1 is_null=0 */
|
|
### DELETE FROM `test2`.`t2`
|
|
### WHERE
|
|
### @1=2 /* INT meta=0 nullable=1 is_null=0 */
|
|
### DELETE FROM `test2`.`t2`
|
|
### WHERE
|
|
### @1=3 /* INT meta=0 nullable=1 is_null=0 */
|
|
# Number of rows: 3
|
|
# at #
|
|
#010909 4:46:40 server id # end_log_pos # Query thread_id=# exec_time=# error_code=0 xid=<xid>
|
|
SET TIMESTAMP=1000000000/*!*/;
|
|
COMMIT
|
|
/*!*/;
|
|
# at #
|
|
#010909 4:46:40 server id # end_log_pos # Rotate to master-bin.000002 pos: 4
|
|
DELIMITER ;
|
|
# End of log file
|
|
ROLLBACK /* added by mysqlbinlog */;
|
|
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
|
|
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
|
|
#
|
|
#####################################################################################
|
|
# mysqlbinlog --read-from-remote-server --database=test1
|
|
# The following Annotate should appear in this output:
|
|
# - DELETE test1.t1, test2.t2 FROM <...>
|
|
#####################################################################################
|
|
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
|
|
/*!40019 SET @@session.max_delayed_threads=0*/;
|
|
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
|
|
DELIMITER /*!*/;
|
|
# at #
|
|
#010909 4:46:40 server id # end_log_pos # Start: binlog v 4, server v #.##.## created 010909 4:46:40 at startup
|
|
ROLLBACK/*!*/;
|
|
# at #
|
|
#010909 4:46:40 server id # end_log_pos # Ignorable
|
|
# Ignorable event type 164 (Start_encryption)
|
|
# at #
|
|
#010909 4:46:40 server id # end_log_pos # Gtid list []
|
|
# at #
|
|
#010909 4:46:40 server id # end_log_pos # Binlog checkpoint master-bin.000001
|
|
# at #
|
|
#010909 4:46:40 server id # end_log_pos # GTID 0-1-1 ddl thread_id=#
|
|
/*M!100101 SET @@session.skip_parallel_replication=0*//*!*/;
|
|
/*M!100001 SET @@session.gtid_domain_id=0*//*!*/;
|
|
/*M!100001 SET @@session.server_id=1*//*!*/;
|
|
/*M!100001 SET @@session.gtid_seq_no=1*//*!*/;
|
|
# at #
|
|
#010909 4:46:40 server id # end_log_pos # Query thread_id=# exec_time=# error_code=0 xid=<xid>
|
|
SET TIMESTAMP=1000000000/*!*/;
|
|
SET @@session.pseudo_thread_id=#/*!*/;
|
|
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1, @@session.check_constraint_checks=1, @@session.sql_if_exists=0, @@session.explicit_defaults_for_timestamp=1, @@session.system_versioning_insert_history=0/*!*/;
|
|
SET @@session.sql_mode=0/*!*/;
|
|
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
|
|
/*!\C latin1 *//*!*/;
|
|
SET @@session.character_set_client=X,@@session.collation_connection=X,@@session.collation_server=X/*!*/;
|
|
SET @@session.lc_time_names=0/*!*/;
|
|
SET @@session.collation_database=DEFAULT/*!*/;
|
|
CREATE DATABASE test1
|
|
/*!*/;
|
|
# at #
|
|
#010909 4:46:40 server id # end_log_pos # GTID 0-1-2 ddl thread_id=#
|
|
/*M!100001 SET @@session.gtid_seq_no=2*//*!*/;
|
|
# at #
|
|
# at #
|
|
#010909 4:46:40 server id # end_log_pos # GTID 0-1-3 ddl thread_id=#
|
|
/*M!100001 SET @@session.gtid_seq_no=3*//*!*/;
|
|
# at #
|
|
# at #
|
|
#010909 4:46:40 server id # end_log_pos # GTID 0-1-4 thread_id=#
|
|
/*M!100001 SET @@session.gtid_seq_no=4*//*!*/;
|
|
START TRANSACTION
|
|
/*!*/;
|
|
# at #
|
|
# at #
|
|
#010909 4:46:40 server id # end_log_pos # Annotate_rows:
|
|
#Q> INSERT INTO test1.t1 VALUES (1), (2), (3)
|
|
#010909 4:46:40 server id # end_log_pos # Table_map: `test1`.`t1` mapped to number #
|
|
# at #
|
|
#010909 4:46:40 server id # end_log_pos # Write_rows: table id # flags: STMT_END_F
|
|
### INSERT INTO `test1`.`t1`
|
|
### SET
|
|
### @1=1 /* INT meta=0 nullable=1 is_null=0 */
|
|
### INSERT INTO `test1`.`t1`
|
|
### SET
|
|
### @1=2 /* INT meta=0 nullable=1 is_null=0 */
|
|
### INSERT INTO `test1`.`t1`
|
|
### SET
|
|
### @1=3 /* INT meta=0 nullable=1 is_null=0 */
|
|
# Number of rows: 3
|
|
# at #
|
|
#010909 4:46:40 server id # end_log_pos # Query thread_id=# exec_time=# error_code=0 xid=<xid>
|
|
SET TIMESTAMP=1000000000/*!*/;
|
|
COMMIT
|
|
/*!*/;
|
|
# at #
|
|
#010909 4:46:40 server id # end_log_pos # GTID 0-1-5 thread_id=#
|
|
/*M!100001 SET @@session.gtid_seq_no=5*//*!*/;
|
|
START TRANSACTION
|
|
/*!*/;
|
|
# at #
|
|
# at #
|
|
# at #
|
|
# at #
|
|
#010909 4:46:40 server id # end_log_pos # Query thread_id=# exec_time=# error_code=0 xid=<xid>
|
|
SET TIMESTAMP=1000000000/*!*/;
|
|
COMMIT
|
|
/*!*/;
|
|
# at #
|
|
#010909 4:46:40 server id # end_log_pos # GTID 0-1-6 thread_id=#
|
|
/*M!100001 SET @@session.gtid_seq_no=6*//*!*/;
|
|
START TRANSACTION
|
|
/*!*/;
|
|
# at #
|
|
# at #
|
|
# at #
|
|
# at #
|
|
#010909 4:46:40 server id # end_log_pos # Query thread_id=# exec_time=# error_code=0 xid=<xid>
|
|
SET TIMESTAMP=1000000000/*!*/;
|
|
COMMIT
|
|
/*!*/;
|
|
# at #
|
|
#010909 4:46:40 server id # end_log_pos # GTID 0-1-7 thread_id=#
|
|
/*M!100001 SET @@session.gtid_seq_no=7*//*!*/;
|
|
START TRANSACTION
|
|
/*!*/;
|
|
# at #
|
|
# at #
|
|
#010909 4:46:40 server id # end_log_pos # Annotate_rows:
|
|
#Q> DELETE test1.t1, test2.t2
|
|
#Q> FROM test1.t1 INNER JOIN test2.t2 INNER JOIN test3.t3
|
|
#Q> WHERE test1.t1.a=test2.t2.a AND test2.t2.a=test3.t3.a
|
|
#010909 4:46:40 server id # end_log_pos # Table_map: `test1`.`t1` mapped to number #
|
|
# at #
|
|
# at #
|
|
# at #
|
|
#010909 4:46:40 server id # end_log_pos # Delete_rows: table id #
|
|
### DELETE FROM `test1`.`t1`
|
|
### WHERE
|
|
### @1=1 /* INT meta=0 nullable=1 is_null=0 */
|
|
### DELETE FROM `test1`.`t1`
|
|
### WHERE
|
|
### @1=2 /* INT meta=0 nullable=1 is_null=0 */
|
|
### DELETE FROM `test1`.`t1`
|
|
### WHERE
|
|
### @1=3 /* INT meta=0 nullable=1 is_null=0 */
|
|
# at #
|
|
#010909 4:46:40 server id # end_log_pos # Query thread_id=# exec_time=# error_code=0 xid=<xid>
|
|
SET TIMESTAMP=1000000000/*!*/;
|
|
COMMIT
|
|
/*!*/;
|
|
# at #
|
|
#010909 4:46:40 server id # end_log_pos # GTID 0-1-8 thread_id=#
|
|
/*M!100001 SET @@session.gtid_seq_no=8*//*!*/;
|
|
START TRANSACTION
|
|
/*!*/;
|
|
# at #
|
|
# at #
|
|
# at #
|
|
# at #
|
|
#010909 4:46:40 server id # end_log_pos # Query thread_id=# exec_time=# error_code=0 xid=<xid>
|
|
SET TIMESTAMP=1000000000/*!*/;
|
|
COMMIT
|
|
/*!*/;
|
|
# at #
|
|
#010909 4:46:40 server id # end_log_pos # GTID 0-1-9 thread_id=#
|
|
/*M!100001 SET @@session.gtid_seq_no=9*//*!*/;
|
|
START TRANSACTION
|
|
/*!*/;
|
|
# at #
|
|
# at #
|
|
# at #
|
|
# at #
|
|
#010909 4:46:40 server id # end_log_pos # Query thread_id=# exec_time=# error_code=0 xid=<xid>
|
|
SET TIMESTAMP=1000000000/*!*/;
|
|
COMMIT
|
|
/*!*/;
|
|
# at #
|
|
#010909 4:46:40 server id # end_log_pos # Rotate to master-bin.000002 pos: 4
|
|
DELIMITER ;
|
|
# End of log file
|
|
ROLLBACK /* added by mysqlbinlog */;
|
|
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
|
|
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
|
|
#
|
|
#####################################################################################
|
|
# mysqlbinlog --read-from-remote-server --skip-annotate-row-events
|
|
# No Annotates should appear in this output
|
|
#####################################################################################
|
|
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
|
|
/*!40019 SET @@session.max_delayed_threads=0*/;
|
|
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
|
|
DELIMITER /*!*/;
|
|
# at #
|
|
#010909 4:46:40 server id # end_log_pos # Start: binlog v 4, server v #.##.## created 010909 4:46:40 at startup
|
|
ROLLBACK/*!*/;
|
|
# at #
|
|
#010909 4:46:40 server id # end_log_pos # Ignorable
|
|
# Ignorable event type 164 (Start_encryption)
|
|
# at #
|
|
#010909 4:46:40 server id # end_log_pos # Gtid list []
|
|
# at #
|
|
#010909 4:46:40 server id # end_log_pos # Binlog checkpoint master-bin.000001
|
|
# at #
|
|
#010909 4:46:40 server id # end_log_pos # GTID 0-1-1 ddl thread_id=#
|
|
/*M!100101 SET @@session.skip_parallel_replication=0*//*!*/;
|
|
/*M!100001 SET @@session.gtid_domain_id=0*//*!*/;
|
|
/*M!100001 SET @@session.server_id=1*//*!*/;
|
|
/*M!100001 SET @@session.gtid_seq_no=1*//*!*/;
|
|
# at #
|
|
#010909 4:46:40 server id # end_log_pos # Query thread_id=# exec_time=# error_code=0 xid=<xid>
|
|
SET TIMESTAMP=1000000000/*!*/;
|
|
SET @@session.pseudo_thread_id=#/*!*/;
|
|
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1, @@session.check_constraint_checks=1, @@session.sql_if_exists=0, @@session.explicit_defaults_for_timestamp=1, @@session.system_versioning_insert_history=0/*!*/;
|
|
SET @@session.sql_mode=0/*!*/;
|
|
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
|
|
/*!\C latin1 *//*!*/;
|
|
SET @@session.character_set_client=X,@@session.collation_connection=X,@@session.collation_server=X/*!*/;
|
|
SET @@session.lc_time_names=0/*!*/;
|
|
SET @@session.collation_database=DEFAULT/*!*/;
|
|
CREATE DATABASE test1
|
|
/*!*/;
|
|
# at #
|
|
#010909 4:46:40 server id # end_log_pos # GTID 0-1-2 ddl thread_id=#
|
|
/*M!100001 SET @@session.gtid_seq_no=2*//*!*/;
|
|
# at #
|
|
#010909 4:46:40 server id # end_log_pos # Query thread_id=# exec_time=# error_code=0 xid=<xid>
|
|
SET TIMESTAMP=1000000000/*!*/;
|
|
CREATE DATABASE test2
|
|
/*!*/;
|
|
# at #
|
|
#010909 4:46:40 server id # end_log_pos # GTID 0-1-3 ddl thread_id=#
|
|
/*M!100001 SET @@session.gtid_seq_no=3*//*!*/;
|
|
# at #
|
|
#010909 4:46:40 server id # end_log_pos # Query thread_id=# exec_time=# error_code=0 xid=<xid>
|
|
SET TIMESTAMP=1000000000/*!*/;
|
|
CREATE DATABASE test3
|
|
/*!*/;
|
|
# at #
|
|
#010909 4:46:40 server id # end_log_pos # GTID 0-1-4 thread_id=#
|
|
/*M!100001 SET @@session.gtid_seq_no=4*//*!*/;
|
|
START TRANSACTION
|
|
/*!*/;
|
|
# at #
|
|
#010909 4:46:40 server id # end_log_pos # Table_map: `test1`.`t1` mapped to number #
|
|
# at #
|
|
#010909 4:46:40 server id # end_log_pos # Write_rows: table id # flags: STMT_END_F
|
|
### INSERT INTO `test1`.`t1`
|
|
### SET
|
|
### @1=1 /* INT meta=0 nullable=1 is_null=0 */
|
|
### INSERT INTO `test1`.`t1`
|
|
### SET
|
|
### @1=2 /* INT meta=0 nullable=1 is_null=0 */
|
|
### INSERT INTO `test1`.`t1`
|
|
### SET
|
|
### @1=3 /* INT meta=0 nullable=1 is_null=0 */
|
|
# Number of rows: 3
|
|
# at #
|
|
#010909 4:46:40 server id # end_log_pos # Query thread_id=# exec_time=# error_code=0 xid=<xid>
|
|
SET TIMESTAMP=1000000000/*!*/;
|
|
COMMIT
|
|
/*!*/;
|
|
# at #
|
|
#010909 4:46:40 server id # end_log_pos # GTID 0-1-5 thread_id=#
|
|
/*M!100001 SET @@session.gtid_seq_no=5*//*!*/;
|
|
START TRANSACTION
|
|
/*!*/;
|
|
# at #
|
|
#010909 4:46:40 server id # end_log_pos # Table_map: `test2`.`t2` mapped to number #
|
|
# at #
|
|
#010909 4:46:40 server id # end_log_pos # Write_rows: table id # flags: STMT_END_F
|
|
### INSERT INTO `test2`.`t2`
|
|
### SET
|
|
### @1=1 /* INT meta=0 nullable=1 is_null=0 */
|
|
### INSERT INTO `test2`.`t2`
|
|
### SET
|
|
### @1=2 /* INT meta=0 nullable=1 is_null=0 */
|
|
### INSERT INTO `test2`.`t2`
|
|
### SET
|
|
### @1=3 /* INT meta=0 nullable=1 is_null=0 */
|
|
# Number of rows: 3
|
|
# at #
|
|
#010909 4:46:40 server id # end_log_pos # Query thread_id=# exec_time=# error_code=0 xid=<xid>
|
|
SET TIMESTAMP=1000000000/*!*/;
|
|
COMMIT
|
|
/*!*/;
|
|
# at #
|
|
#010909 4:46:40 server id # end_log_pos # GTID 0-1-6 thread_id=#
|
|
/*M!100001 SET @@session.gtid_seq_no=6*//*!*/;
|
|
START TRANSACTION
|
|
/*!*/;
|
|
# at #
|
|
#010909 4:46:40 server id # end_log_pos # Table_map: `test3`.`t3` mapped to number #
|
|
# at #
|
|
#010909 4:46:40 server id # end_log_pos # Write_rows: table id # flags: STMT_END_F
|
|
### INSERT INTO `test3`.`t3`
|
|
### SET
|
|
### @1=1 /* INT meta=0 nullable=1 is_null=0 */
|
|
### INSERT INTO `test3`.`t3`
|
|
### SET
|
|
### @1=2 /* INT meta=0 nullable=1 is_null=0 */
|
|
### INSERT INTO `test3`.`t3`
|
|
### SET
|
|
### @1=3 /* INT meta=0 nullable=1 is_null=0 */
|
|
# Number of rows: 3
|
|
# at #
|
|
#010909 4:46:40 server id # end_log_pos # Query thread_id=# exec_time=# error_code=0 xid=<xid>
|
|
SET TIMESTAMP=1000000000/*!*/;
|
|
COMMIT
|
|
/*!*/;
|
|
# at #
|
|
#010909 4:46:40 server id # end_log_pos # GTID 0-1-7 thread_id=#
|
|
/*M!100001 SET @@session.gtid_seq_no=7*//*!*/;
|
|
START TRANSACTION
|
|
/*!*/;
|
|
# at #
|
|
#010909 4:46:40 server id # end_log_pos # Table_map: `test1`.`t1` mapped to number #
|
|
# at #
|
|
#010909 4:46:40 server id # end_log_pos # Table_map: `test2`.`t2` mapped to number #
|
|
# at #
|
|
# at #
|
|
#010909 4:46:40 server id # end_log_pos # Delete_rows: table id #
|
|
#010909 4:46:40 server id # end_log_pos # Delete_rows: table id # flags: STMT_END_F
|
|
### DELETE FROM `test1`.`t1`
|
|
### WHERE
|
|
### @1=1 /* INT meta=0 nullable=1 is_null=0 */
|
|
### DELETE FROM `test1`.`t1`
|
|
### WHERE
|
|
### @1=2 /* INT meta=0 nullable=1 is_null=0 */
|
|
### DELETE FROM `test1`.`t1`
|
|
### WHERE
|
|
### @1=3 /* INT meta=0 nullable=1 is_null=0 */
|
|
### DELETE FROM `test2`.`t2`
|
|
### WHERE
|
|
### @1=1 /* INT meta=0 nullable=1 is_null=0 */
|
|
### DELETE FROM `test2`.`t2`
|
|
### WHERE
|
|
### @1=2 /* INT meta=0 nullable=1 is_null=0 */
|
|
### DELETE FROM `test2`.`t2`
|
|
### WHERE
|
|
### @1=3 /* INT meta=0 nullable=1 is_null=0 */
|
|
# Number of rows: 6
|
|
# at #
|
|
#010909 4:46:40 server id # end_log_pos # Query thread_id=# exec_time=# error_code=0 xid=<xid>
|
|
SET TIMESTAMP=1000000000/*!*/;
|
|
COMMIT
|
|
/*!*/;
|
|
# at #
|
|
#010909 4:46:40 server id # end_log_pos # GTID 0-1-8 thread_id=#
|
|
/*M!100001 SET @@session.gtid_seq_no=8*//*!*/;
|
|
START TRANSACTION
|
|
/*!*/;
|
|
# at #
|
|
#010909 4:46:40 server id # end_log_pos # Table_map: `test2`.`t2` mapped to number #
|
|
# at #
|
|
#010909 4:46:40 server id # end_log_pos # Write_rows: table id # flags: STMT_END_F
|
|
### INSERT INTO `test2`.`t2`
|
|
### SET
|
|
### @1=1 /* INT meta=0 nullable=1 is_null=0 */
|
|
### INSERT INTO `test2`.`t2`
|
|
### SET
|
|
### @1=2 /* INT meta=0 nullable=1 is_null=0 */
|
|
### INSERT INTO `test2`.`t2`
|
|
### SET
|
|
### @1=3 /* INT meta=0 nullable=1 is_null=0 */
|
|
# Number of rows: 3
|
|
# at #
|
|
#010909 4:46:40 server id # end_log_pos # Query thread_id=# exec_time=# error_code=0 xid=<xid>
|
|
SET TIMESTAMP=1000000000/*!*/;
|
|
COMMIT
|
|
/*!*/;
|
|
# at #
|
|
#010909 4:46:40 server id # end_log_pos # GTID 0-1-9 thread_id=#
|
|
/*M!100001 SET @@session.gtid_seq_no=9*//*!*/;
|
|
START TRANSACTION
|
|
/*!*/;
|
|
# at #
|
|
#010909 4:46:40 server id # end_log_pos # Table_map: `test2`.`t2` mapped to number #
|
|
# at #
|
|
#010909 4:46:40 server id # end_log_pos # Delete_rows: table id # flags: STMT_END_F
|
|
### DELETE FROM `test2`.`t2`
|
|
### WHERE
|
|
### @1=1 /* INT meta=0 nullable=1 is_null=0 */
|
|
### DELETE FROM `test2`.`t2`
|
|
### WHERE
|
|
### @1=2 /* INT meta=0 nullable=1 is_null=0 */
|
|
### DELETE FROM `test2`.`t2`
|
|
### WHERE
|
|
### @1=3 /* INT meta=0 nullable=1 is_null=0 */
|
|
# Number of rows: 3
|
|
# at #
|
|
#010909 4:46:40 server id # end_log_pos # Query thread_id=# exec_time=# error_code=0 xid=<xid>
|
|
SET TIMESTAMP=1000000000/*!*/;
|
|
COMMIT
|
|
/*!*/;
|
|
# at #
|
|
#010909 4:46:40 server id # end_log_pos # Rotate to master-bin.000002 pos: 4
|
|
DELIMITER ;
|
|
# End of log file
|
|
ROLLBACK /* added by mysqlbinlog */;
|
|
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
|
|
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
|
|
set global binlog_checksum=@old_binlog_checksum;
|
|
DROP DATABASE test1;
|
|
DROP DATABASE test2;
|
|
DROP DATABASE test3;
|
|
DROP DATABASE xtest1;
|
|
DROP DATABASE xtest2;
|