Temporary tables and ROLLBACK in binlog
Hi,
we have hit an interesting problem with binlog. Let's suppose we have a row-based replication enabled and then we execute a script
set autocommit=0;
create temporary table tmp_ids (a int);
CREATE TABLE eyalh_test (a int);
insert into tmp_ids values (5);
commit;
drop temporary table tmp_ids;
insert into eyalh_test values (5);
rollback;
The binlog then contains
| mysql-bin.000003 | 5433 | Query | 223344 | 5548 | use `inventory`; CREATE TABLE eyalh_test (a int) |
| mysql-bin.000003 | 5548 | Anonymous_Gtid | 223344 | 5613 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql-bin.000003 | 5613 | Query | 223344 | 5702 | BEGIN |
| mysql-bin.000003 | 5702 | Query | 223344 | 5852 | use `inventory`; DROP TEMPORARY TABLE IF EXISTS `tmp_ids` /* generated by server */ |
| mysql-bin.000003 | 5852 | Table_map | 223344 | 5910 | table_id: 236 (inventory.eyalh_test) |
| mysql-bin.000003 | 5910 | Write_rows | 223344 | 5950 | table_id: 236 flags: STMT_END_F |
| mysql-bin.000003 | 5950 | Query | 223344 | 6030 | ROLLBACK |
If we omit 'drop temporary table tmp_ids;' then the tx is not written to binlog at all. Whys is that? We have read in documentation the rules for mixing tx and non-tx tables in a single transaction and an impact of it on a binlog content but I believe this case is not covered by it.
Is this an expected behaviour?
Thanks for you help/opinions
Jiri Pechanec
Subject
Views
Written By
Posted
Temporary tables and ROLLBACK in binlog
1049
October 18, 2017 11:43PM
Sorry, you can't reply to this topic. It has been closed.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion
of Oracle or any other party.