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: ...
Under general conditions if you run these statements in sequence, you will end up with a
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 flag
LOG_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: ...
I delved into the code to know what all situations need this flag to be set. So far, it is set only when using temporary tables and with a possibility/extensibility of using with other situations in future. Other situations like transactions take care of themselves by the way they are logged.
I'm running few tests to see if it handles all the conditions related to temporary tables created inside transactions. Will publish soon if something pops up.
