Few days back, one of my colleagues posted a good question. It sounds something like this;
"Temporary tables are session based that means under different sessions we can create temporary tables with similar names. Now since slave thread is singleton, how does it manage to keep them separate?"
He was very much right in asking this and the answer is not all that intuitive. Lets go through the binlog events to see why it is not that intuitive.Under general conditions if you run these statements in sequence, you will end up with a
Table `t` already existswhen you put second
create temporary table. But with replication this seems to just work, how? Well, the truth is
SHOW BINLOG EVENTSdoesn't show the full truth.
The Magic Behind:For such situations, MySQL uses a special flag
LOG_EVENT_THREAD_SPECIFIC_Fthat is set if the event is dependent on the connection it was executed on. This translates into setting a session level variable
pseudo_thread_idinstructing the slave thread to treat a bundle of statements in a special way and do not create any confusion. Now this is actually a very safe method of doing things and being very extra paranoid I wondered why this was not there for every session? Simple answer is; performance reasons. :)
Check the outcome of