You are here

Can MySQL temporary tables be made safe for statement-based replication?

MySQL Performance Blog - Tue, 27/05/2008 - 1:17am

A while ago I wrote about how to make MySQL replication reliable, part of which is to eliminate temporary tables. The idea is this: if a slave is stopped (or crashed) while a temporary table is open and is then restarted, the temporary table doesn't exist anymore, and the slave will have problems trying to replay any further statements that refer to these tables. Thus, I claimed, there's no alternative but to eliminate temporary tables. This problem may not exist for row-based replication in MySQL 5.1 and later, but most installations I know of are using statement-based replication, even on MySQL 5.1

This is a contentious topic. People love their temporary tables and will ask hopefully "are you sure this isn't safe?" They'll propose all sorts of ways to mitigate the danger, and I've heard many of them. But I recently heard an angle on this I had not heard before.

The argument is this: "you can create an InnoDB temporary table and use it only within one transaction, and then if the slave crashes and restarts, it'll roll back the transaction to the beginning." In other words, in theory if the temporary table exists only within that one transaction, and if your transaction accesses only InnoDB tables, it's safe.

My first thought was, you can't do that. CREATE TABLE commits the transaction, so there's implicitly more than one transaction. However, as the person pointed out, that isn't true with CREATE TEMPORARY TABLE. I tested this (sometimes the manual is wrong!) and found that indeed, you can open a transaction, make some changes, create a temporary table with ENGINE=InnoDB, and the InnoDB transaction ID does not change in SHOW INNODB STATUS. The statements are all within one transaction. (However, if you type ROLLBACK the temporary table doesn't get dropped. It's not really transactional -- it just doesn't auto-commit the transaction. The ROLLBACK will produce a warning that says "Some non-transactional changed tables couldn't be rolled back", which is interesting.)

But does that mean it's safe for replication?

There is one good way to find out: test it. I fired up my master-and-two-slaves replication sandbox, flushed all the logs, and set out to get to the bottom of the matter.

First, I stopped the slave threads so I could choose which statements to replay on the slave and pick the "crash point" as I wished. (I didn't shut down the slave, I just stopped the replication processes. This is safe to do even when temporary tables are open.) Then I created a temporary table on the master, inserted some rows into it, and dropped it:

PLAIN TEXT SQL:
  1. master> SET autocommit=0;
  2. master> begin;
  3. master> CREATE TEMPORARY TABLE test.t(a int) engine=innodb;
  4. master> INSERT INTO test.t(a) VALUES(1);
  5. master> DROP TEMPORARY TABLE test.t;
  6. master> commit;

In theory, that's all in one transaction. Since I flushed the logs before I did this, everything in the binary log so far comes from these statements. Let's look at the binary logs:

PLAIN TEXT SQL:
  1. master> SHOW master STATUS;
  2. +------------------+----------+--------------+------------------+
  3. | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
  4. +------------------+----------+--------------+------------------+
  5. | mysql-bin.000007 |      474 |              |                  |
  6. +------------------+----------+--------------+------------------+
  7.  
  8. master> SHOW binlog events IN 'mysql-bin.000007'\G
  9. *************************** 1. row ***************************
  10.    Log_name: mysql-bin.000007
  11.         Pos: 4
  12.  Event_type: Format_desc
  13.   Server_id: 1
  14. End_log_pos: 98
  15.        Info: Server ver: 5.0.45-log, Binlog ver: 4
  16. *************************** 2. row ***************************
  17.    Log_name: mysql-bin.000007
  18.         Pos: 98
  19.  Event_type: Query
  20.   Server_id: 1
  21. End_log_pos: 207
  22.        Info: CREATE TEMPORARY TABLE test.t(a int) engine=innodb
  23. *************************** 3. row ***************************
  24.    Log_name: mysql-bin.000007
  25.         Pos: 207
  26.  Event_type: Query
  27.   Server_id: 1
  28. End_log_pos: 271
  29.        Info: BEGIN
  30. *************************** 4. row ***************************
  31.    Log_name: mysql-bin.000007
  32.         Pos: 271
  33.  Event_type: Query
  34.   Server_id: 1
  35. End_log_pos: 90
  36.        Info: INSERT INTO test.t(a) VALUES(1)
  37. *************************** 5. row ***************************
  38.    Log_name: mysql-bin.000007
  39.         Pos: 361
  40.  Event_type: Query
  41.   Server_id: 1
  42. End_log_pos: 176
  43.        Info: DROP TEMPORARY TABLE test.t
  44. *************************** 6. row ***************************
  45.    Log_name: mysql-bin.000007
  46.         Pos: 447
  47.  Event_type: Xid
  48.   Server_id: 1
  49. End_log_pos: 474
  50.        Info: COMMIT /* xid=39 */

Very interesting! The order of statements is not the same in the binlog as I typed into the console. If you replay the binary log, you'll get two transactions here.

This shows us something interesting that isn't considered in the "all inside one transaction" argument: transactions aren't the only thing that matters. How the server logs events to the binary log is equally important. It appears that we can break replication on the slave by killing the slave after event 98 executes and before event 207 executes. But let's not draw any conclusions yet. The only way to tell for sure is to really test it.

Since I'd stopped the slave, I could easily test my theory. Let's let the slave replay events up until position 207, kill it, and restart it:

PLAIN TEXT SQL:
  1. slave1> SHOW slave STATUS\G
  2. *************************** 1. row ***************************
  3.             Master_Log_File: mysql-bin.000006
  4.         Read_Master_Log_Pos: 98
  5.              Relay_Log_File: mysql_sandbox20551-relay-bin.000028
  6.               Relay_Log_Pos: 235
  7.       Relay_Master_Log_File: mysql-bin.000006
  8.            Slave_IO_Running: No
  9.           Slave_SQL_Running: No
  10.          ...... omitted .........
  11.  
  12. slave1> start slave until master_log_file='mysql-bin.000007', master_log_pos=207;
  13. slave1> SHOW slave STATUS\G
  14. *************************** 1. row ***************************
  15.             Master_Log_File: mysql-bin.000007
  16.          .... omitted ........
  17.         Exec_Master_Log_Pos: 207
  18.  
  19. slave1> SHOW STATUS LIKE '%temp%';
  20. +------------------------+-------+
  21. | Variable_name          | Value |
  22. +------------------------+-------+
  23. | Slave_open_temp_tables | 1     |
  24. +------------------------+-------+

The slave is now "vulnerable," in theory. To test my theory, I'll shut down and restart the slave gracefully, rather than simulating a crash with kill -9, and see what happens.

PLAIN TEXT SQL:
  1. $ ./node1/stop
  2. $ ./node1/start
  3. $ ./s1
  4. slave1> SHOW slave STATUS\G
  5. *************************** 1. row ***************************
  6.                  Last_Errno: 1146
  7.                  Last_Error: Error 'Table 'test.t' doesn't exist' on query. Default database: ''. Query: 'INSERT INTO test.t(a) VALUES(1)'

That's the error I thought I'd see. Even though it was used entirely within one transaction on the master, the temporary table was not safe for replication.

I'm pretty sure this is a bug. The temporary table shouldn't be logged out-of-order on the master like this (I suspect it's logged out-of-order because CREATE TEMPORARY TABLE can't be rolled back). But bug or no, it is what it is.

There's one more angle to the email thread that inspired this article: what if the whole transaction is inside a stored procedure? Whether this works or not depends, again, on how the stored procedure call is logged to the binary log. Let's create a stored procedure to hold the transaction, which this time will insert data from the temporary table into a non-temporary InnoDB table:

PLAIN TEXT SQL:
  1. master> delimiter //
  2. master> CREATE procedure test_temp() begin
  3.     -> start transaction;
  4.     -> CREATE TEMPORARY TABLE test.t(a int) engine=innodb;
  5.     -> INSERT INTO test.t(a) VALUES(1);
  6.     -> INSERT INTO test.ins(a) SELECT * FROM test.t;
  7.     -> DROP TEMPORARY TABLE test.t;
  8.     -> commit;
  9.     -> end//
  10. master> delimiter;

Now calling the stored procedure should put a row into the test.ins table. Let's see:

PLAIN TEXT SQL:
  1. master> call test_temp();
  2. master> SELECT * FROM test.ins;
  3. +------+
  4. | a    |
  5. +------+
  6. |    1 |
  7. +------+

Good. Let's see what's in the binary log:

PLAIN TEXT SQL:
  1. master> SHOW binlog events IN 'mysql-bin.000011'\G
  2. *************************** 1. row ***************************
  3.    Log_name: mysql-bin.000011
  4.         Pos: 4
  5.  Event_type: Format_desc
  6.   Server_id: 1
  7. End_log_pos: 98
  8.        Info: Server ver: 5.0.45-log, Binlog ver: 4
  9. *************************** 2. row ***************************
  10.    Log_name: mysql-bin.000011
  11.         Pos: 98
  12.  Event_type: Query
  13.   Server_id: 1
  14. End_log_pos: 211
  15.        Info: USE `test`; CREATE TEMPORARY TABLE test.t(a int) engine=innodb
  16. *************************** 3. row ***************************
  17.    Log_name: mysql-bin.000011
  18.         Pos: 211
  19.  Event_type: Query
  20.   Server_id: 1
  21. End_log_pos: 279
  22.        Info: USE `test`; BEGIN
  23. *************************** 4. row ***************************
  24.    Log_name: mysql-bin.000011
  25.         Pos: 279
  26.  Event_type: Query
  27.   Server_id: 1
  28. End_log_pos: 94
  29.        Info: USE `test`; INSERT INTO test.t(a) VALUES(1)
  30. *************************** 5. row ***************************
  31.    Log_name: mysql-bin.000011
  32.         Pos: 373
  33.  Event_type: Query
  34.   Server_id: 1
  35. End_log_pos: 198
  36.        Info: USE `test`; INSERT INTO test.ins SELECT * FROM test.t
  37. *************************** 6. row ***************************
  38.    Log_name: mysql-bin.000011
  39.         Pos: 477
  40.  Event_type: Query
  41.   Server_id: 1
  42. End_log_pos: 288
  43.        Info: USE `test`; DROP TEMPORARY TABLE test.t
  44. *************************** 7. row ***************************
  45.    Log_name: mysql-bin.000011
  46.         Pos: 567
  47.  Event_type: Xid
  48.   Server_id: 1
  49. End_log_pos: 594
  50.        Info: COMMIT /* xid=124 */
  51. 7 rows IN SET (0.00 sec)

What you see depends on your version of MySQL, because the logging of stored procedures has changed over time. If just the CALL statement had been logged, I think we might have been safe using a stored procedure. However, since all the statements went into the binlog individually, there's clearly an opportunity to break replication here. It looks like this doesn't avoid the problem either.

Interestingly, I also created a version of the stored procedure that doesn't begin and commit a transaction. After calling it, the CREATE TEMPORARY TABLE statement is logged into the binlog; after then typing COMMIT, the rest of the statements go into the binlog. It appears to me that there's no way to get the CREATE TEMPORARY TABLE statement to be logged inside the transaction. And when it comes to a replication slave, what's logged -- not what executes on the master -- is what's important.

In summary, I still don't see any way to use temporary tables with MySQL statement-based replication without some risk of breaking slaves. At some point I may test how it works with row-based replication; I believe even row-based logging format is going to have some problems, because the CREATE TABLE is logged in statement format. But that's a topic for another post.

Entry posted by Baron Schwartz | No comment

Add to: delicious | digg | reddit | netscape | Google Bookmarks