Bug #25093 slave replay of transaction with CREATE TMP table fails

Submitted: 15 Dec 2006 12:49
Reporter: Andrei Elkin Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Replication Severity:S3 (Non-critical)
Version:5.1.14 OS:Linux (linux5.)
Assigned to: Assigned Account CPU Architecture:Any

[15 Dec 2006 12:49] Andrei Elkin
Description:
Given the transaction executing by slave
  begin;
       ...
       CREATE temporary table;
       update that deadlocks or triggers timout;
  commit;
which gets deadlocked or timed-out and replayed
`CREATE temp' executes for the second time.
This fails because of temp was created at a previous round.

How to repeat:
connection master;
create table t1 (a int) engine=innodb;
insert into t1 values (0);
create table t2 (a int) engine=innodb;
insert into t2 values (0);

slave_sync_with_master;
connection slave;
begin;
      insert into t2 values (1),(1),(1);
      update t2 set a=2;

connection master;
begin;
       update t1 set a=1;
       CREATE temporary table tt (a int);
       update t2 set a=1;
commit;

connection slave;
      update t1 set a=2 /* deadlock, replicated ta victim */;

061215 14:47:16 [ERROR] Slave: Error 'Table 'tt' already exists' on query. Default database: 'test'. Query: 'CREATE temporary table tt (a int)', Error_code: 1050
061215 14:47:16 [Warning] Slave: Table 'tt' already exists Error_code: 1050

Suggested fix:
tolate the error of `table alread exists' on slave.
It's safe:
 slave knows it replays, and that's a temporary table which exists.
[15 Dec 2006 13:10] Guilhem Bichot
Andrei,
*if* the replication team implements Mats's suggestion of making a temporary table created inside a transaction to be dropped at rollback, the present bug would disappear (because, when the slave thread gets a timeout it does a rollback of the entire transaction, and that would drop the temporary table)

Portions of this website are copyright © 2001, 2002 The PHP Group

Page generated in 0.038 sec. using MySQL 8.4.7-u6-cloud

Timestamp references displayed by the system are UTC.
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.