Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
  1. If I use CREATE TEMPORARY TABLE, can I write and update this MySQL table and know that none of it will be stored to the binlogs?

  2. If I use data from the temp table to write to a non-temp table, is the
    part-of-the-statement-that-reads-from-temp-table
    simply substituted with plain values, or is there something else that happens in the binlog to indicate the data source?

    I ask this because normally when reading from another table, the binlog shows the whole statement, meaning it normally does not substitute the plain values.

share|improve this question
    
I think that this should be tagged with either statement-logs or binlogs. I cannot create these tags myself. –  George Bailey Mar 19 '12 at 15:29
add comment

1 Answer

Statements like CREATE TEMPORARY TABLE will land in the binary logs. This has plaugued me for years when troubleshooting. Here is why:

In MySQL Replication, if you create a temp table, it will remain in existence as long as the SQL thread is in session. I have had dozens of instances where someone would schedule a nightly backup which included STOP SLAVE; before the mysqldump and START SLAVE; afterwards. When you run STOP SLAVE;, it terminates both the IO thread and the SQL thread for Replication. When the SQL thread dies, all tables created with CREATE TEMPORARY TABLE are dropped immediately. When you try to run START SLAVE;, MySQL Replication breaks instantly, stating that the table it was INSERTing into or SELECTing from does not exist. How did I find the table? I would have to run mysqlbinlog against the master's binary logs to locate the position that the CREATE TEMPORARY TABLE was issued.

So, to answer both questions, all you have to do is this:

Any INSERT into a temp table should like like this:

SET SQL_LOG_BIN=0;
INSERT INTO tmptable ... ;
SET SQL_LOG_BIN=1;

Any UPDATE of a temp table should like like this:

SET SQL_LOG_BIN=0;
UPDATE tmptable SET ... ;
SET SQL_LOG_BIN=1;

Any INSERT into a real table using data from a temp table should like like this:

SET SQL_LOG_BIN=0;
INSERT INTO realtable SELECT ... FROM tmptable ... ;
SET SQL_LOG_BIN=1;

UPDATE 2012-03-19 17:06 EDT

I read your comment

Question 1 from your comments : What happens if two threads both create temp tables of the same name? Do the statements fall into the binlogs and conflict?

Answer : Each temp table of identical names (even identical schemas) are unique to the the DB Connection that opened it. Therefore, no conflict whatsoever. In fact, since replication is single-threaded along the SQL thread, all SQL statements with temp tables of identical names are processed in a serialized manner, i.e., like a FIFO queue.

Question 2 from your comments : Wouldn't this cause the changes to realtable to not be reflected in the binlogs?

Answer : You are correct. Therefore, since you want the changes to the realtable replicated to the Slave, you will not need SET SQL_LOG_BIN=0;

share|improve this answer
    
1. What happens if two threads both create temp tables of the same name? Do the statements fall into the binlogs and conflict? 2. SET SQL_LOG_BIN=0; INSERT INTO realtable SELECT ... FROM tmptable ... ; SET SQL_LOG_BIN=1; Wouldn't this cause the changes to realtable to not be reflected in the binlogs? –  George Bailey Mar 19 '12 at 20:50
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.