MySQL Forums
Forum List  »  Replication

Temporary tables and ROLLBACK in binlog
Posted by: Jiri Pechanec
Date: October 18, 2017 11:43PM

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

Options: ReplyQuote


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.