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.
1: mysql> SHOW BINLOG EVENTS IN 'log-bin.000016';
2: . . . 3: | log-bin.000016 | 389 | Query | 2515922453 | 488 | use `test`; CREATE TEMPORARY TABLE test.t(a int) |
4: | log-bin.000016 | 488 | Query | 2515922453 | 582 | use `test`; INSERT INTO test.t(a) VALUES(1) |
5: | log-bin.000016 | 582 | Query | 2515922453 | 676 | use `test`; INSERT INTO test.t(a) VALUES(3) |
6: | log-bin.000016 | 676 | Query | 2515922453 | 775 | use `test`; CREATE TEMPORARY TABLE test.t(a int) |
7: | log-bin.000016 | 775 | Query | 2515922453 | 869 | use `test`; INSERT INTO test.t(a) VALUES(7) |
8: | log-bin.000016 | 869 | Query | 2515922453 | 944 | use `test`; drop table t |
9: ...
Table `t` already exists when you put second create temporary table. But with replication this seems to just work, how? Well, the truth is SHOW BINLOG EVENTS doesn't show the full truth.
The Magic Behind:
For such situations, MySQL uses a special flagLOG_EVENT_THREAD_SPECIFIC_F that is set if the event is dependent on the connection it was executed on. This translates into setting a session level variable pseudo_thread_id instructing 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 mysqlbinlog:
1: $ mysqlbinlog log-bin.000016 2: ... 3: # at 389
4: #080617 2:06:11 server id -1779044843 end_log_pos 488 Query thread_id=138 exec_time=0 error_code=0
5: SET TIMESTAMP=1213693571/*!*/; 6: SET @@session.pseudo_thread_id=138/*!*/; 7: CREATE TEMPORARY TABLE test.t(a int)/*!*/; 8: # at 488
9: #080617 2:06:15 server id -1779044843 end_log_pos 582 Query thread_id=138 exec_time=0 error_code=0
10: SET TIMESTAMP=1213693575/*!*/; 11: SET @@session.pseudo_thread_id=138/*!*/; 12: INSERT INTO test.t(a) VALUES(1)/*!*/; 13: # at 582
14: #080617 2:06:36 server id -1779044843 end_log_pos 676 Query thread_id=138 exec_time=0 error_code=0
15: SET TIMESTAMP=1213693596/*!*/; 16: SET @@session.pseudo_thread_id=138/*!*/; 17: INSERT INTO test.t(a) VALUES(3)/*!*/; 18: # at 676
19: #080617 2:06:55 server id -1779044843 end_log_pos 775 Query thread_id=141 exec_time=0 error_code=0
20: SET TIMESTAMP=1213693615/*!*/; 21: SET @@session.pseudo_thread_id=141/*!*/; 22: CREATE TEMPORARY TABLE test.t(a int)/*!*/; 23: # at 775
24: #080617 2:11:07 server id -1779044843 end_log_pos 869 Query thread_id=141 exec_time=0 error_code=0
25: SET TIMESTAMP=1213693867/*!*/; 26: SET @@session.pseudo_thread_id=141/*!*/; 27: INSERT INTO test.t(a) VALUES(7)/*!*/; 28: # at 869
29: #080617 2:15:50 server id -1779044843 end_log_pos 944 Query thread_id=141 exec_time=0 error_code=0
30: SET TIMESTAMP=1213694150/*!*/; 31: SET @@session.pseudo_thread_id=141/*!*/; 32: drop table t/*!*/; 33: ...
