| Bug #37011 | creating temporary table inside a transaction may result in incorrect binlog | ||
|---|---|---|---|
| Submitted: | 27 May 2008 15:43 | Modified: | 4 Aug 2008 7:36 |
| Reporter: | Gregory Haase | Email Updates: | |
| Status: | Not a Bug | Impact on me: | |
| Category: | MySQL Server: Replication | Severity: | S2 (Serious) |
| Version: | 5.0.27, 5.1.24-rc | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | temporary table, transaction | ||
[9 Jun 2008 20:55]
Sveta Smirnova
Thank you for the report. According to http://dev.mysql.com/doc/refman/5.1/en/implicit-commit.html: CREATE TABLE and DROP TABLE do not commit a transaction if the TEMPORARY keyword is used. (This does not apply to other operations on temporary tables such as CREATE INDEX, which do cause a commit.) However, although no implicit commit occurs, neither can the statement be rolled back. Therefore, use of such statements will violate transaction atomicity: For example, if you use CREATE TEMPORARY TABLE and then roll back the transaction, the table remains in existence. current behavior is correct. So I close the report as "Not a Bug". Since version 5.1 you can use binary log format ROW to prevent errors like "tmp table does not exist"
[9 Jun 2008 21:06]
Gregory Haase
No! This is incorrect. The documentation you refer to discusses when a commit occurs. And it states that a temporary table does NOT cause in implicit commit. This documentation has nothing to do with how or when a transaction is started. This bug occurs with respect to when a transaction is started. Consider this outline: 1.) Start a Transaction 2a.) Create a Temporary Table 2b.) Per documentation you cited, creation of temporary table DOES NOT CAUSE A COMMIT 3.) Do some work 4.) Manually issue a COMMIT. This bug needs to be reopened. It needs to be fixed.
[2 Aug 2008 21:29]
Sveta Smirnova
Thank you for the feedback. Assuming following scenario. For first case (only temporary table used): 1. Transaction started. 2. Temporary table created. 3. Transaction rolled back -> not written to binary log. So if write CREATE TEMPORARY TABLE after BEGIN master will have and can use temporary table, but slave would not. For second case (with regular table) transaction can be rolled back before temporary table is created. So having CREATE TEMPORARY TABLE after INSERT is logical.
[4 Aug 2008 1:35]
Gregory Haase
Do not get caught up on the fact that the first action in my example is an INSERT. The begin statement can be triggered by almost anything. If, for example, you try the following sequence: 1.) START TRANSACTION 2.) DROP TEMPORARY TABLE IF EXISTS `tablename` 3.) CREATE TEMPORARY TABLE `tablename` Then your 'BEGIN' statement occurs in your binlog *after* the DROP TABLE but *before* the CREATE TEMPORARY TABLE. The case you suggest where temporary table can become available on MASTER and not SLAVE is indeed possible, but there are also a lot of other reasons you can get into this same state (e.g. slave is stopped and restarted for cold backup). Whether or not this bug is fixed - people still have to code with these sorts of scenarios in mind. Consider the scenario I mentioned above. The code wraps temporary tables inside of transactions. The slave gets shut down for a cold backup in the middle of the transcation. It gets restarted. Normally, your replication will crash because the temporary table no longer exists. If this bug is properly fixed, then the transaction is rolled back and when the slave restarts, it simply creates the temporary table again and replication proceeds. So what I'm saying here: that the very error condition you presented above that can result from this fixing this bug... that error condition can also be prevented from happening many other ways as a result of fixing this bug. Most importantly, I believe that the current behavior outlined in the bug report shows there are several levels of inconsistency here. First of all, from a developer standpoint, if I START TRANSACTION, I expect everything after that to be in the transaction. If I didn't want the temp table to be in the transaction, I would have built the temp table first, then I would have started the transaction. The documentation you referenced above is already warning me about the temp table violating atomicity - I'm choosing to heed that warning and build controls into my code to check that the temp table exists before using it, and doing "DROP IF EXISTS" before trying to create it. The documentation, however, is NOT clear - until this bug is fixed there are cases where creating a temporary table inside of a transaction doesn't violate atomicity. Fix the bug or expand on the documentation. I'd prefer you fix the bug.
[4 Aug 2008 7:36]
Susanne Ebrecht
Many thanks for writing a bug report. When I understand you right, this is not a bug it is an expected behaviour. Consider, transactions are only for DML not for DDL. That some other RDBMS have DDL transactions is a feature of them but the SQL standard only have rules for DML transactions and not for DDL which means MySQL and some other RDBMS with same behaviour are standard conform here. Also the following statements commit a transaction by automatism: http://dev.mysql.com/doc/refman/5.1/en/implicit-commit.html Consider also that temporary tables are session only and are not made for replication originally. Also it is recommend not to use stored routines for replication. Please also read here: http://dev.mysql.com/doc/refman/5.1/en/stored-procedure-logging.html http://dev.mysql.com/doc/refman/5.1/en/replication-formats.html http://dev.mysql.com/doc/refman/5.1/en/replication-features-temptables.html
Description: When creating a temporary table with InnoDB engine inside of a transaction, it should be crash safe. In reality it is not always the truth. A temp table created inside a transaction is only correctly logged in the binlog if another transaction safe statement has already occurred. How to repeat: DELIMITER $$ CREATE PROCEDURE `test`.`p_tmp_tables`() BEGIN START TRANSACTION; CREATE TEMPORARY TABLE `test`.`temporary_table` ( `id` int(11) NOT NULL, `value_1` varchar(10) default NULL, `value_2` varchar(10) default NULL, `value_3` varchar(10) default NULL) ENGINE=InnoDB; INSERT INTO `test`.`temporary_table` VALUES (1,'a','b','c'); COMMIT; END$$ DELIMITER ; Now run this: mysql> flush logs; Query OK, 0 rows affected (0.17 sec) mysql> call p_tmp_tables(); Query OK, 0 rows affected (0.04 sec) mysql> SHOW binlog events IN 'bin-logs.000028'\G *************************** 1. row *************************** Log_name: bin-logs.000028 Pos: 4 Event_type: Format_desc Server_id: 1 End_log_pos: 106 Info: Server ver: 5.1.24-rc-community-log, Binlog ver: 4 *************************** 2. row *************************** Log_name: bin-logs.000028 Pos: 106 Event_type: Query Server_id: 1 End_log_pos: 376 Info: use `test`; CREATE TEMPORARY TABLE `test`.`temporary_table` ( `id` int(11) NOT NULL, `value_1` varchar(10) default NULL, `value_2` varchar(10) default NULL, `value_3` varchar(10) default NULL) ENGINE=InnoDB *************************** 3. row *************************** Log_name: bin-logs.000028 Pos: 376 Event_type: Query Server_id: 1 End_log_pos: 444 Info: use `test`; BEGIN *************************** 4. row *************************** Log_name: bin-logs.000028 Pos: 444 Event_type: Query Server_id: 1 End_log_pos: 568 Info: use `test`; INSERT INTO `test`.`temporary_table` VALUES (1,'a','b','c') *************************** 5. row *************************** Log_name: bin-logs.000028 Pos: 568 Event_type: Query Server_id: 1 End_log_pos: 637 Info: use `test`; COMMIT 5 rows in set (0.00 sec) You see that although we put START TRANSACTION before we created the temp table, in the binlogs, it does not start until we do an insert. Now, if we create an additional table "ints" that just holds a single number, we can simulate initiating a transaction before we create the temp table: CREATE TABLE `test`.`ints` ( `id` int(11) DEFAULT NULL ) ENGINE=InnoDB; The new procedure looks like this: DELIMITER $$ CREATE PROCEDURE `test`.`p_tmp_tables`() BEGIN START TRANSACTION; INSERT INTO `test`.`ints` VALUES (1); CREATE TEMPORARY TABLE `test`.`temporary_table` ( `id` int(11) NOT NULL, `value_1` varchar(10) default NULL, `value_2` varchar(10) default NULL, `value_3` varchar(10) default NULL) ENGINE=InnoDB; INSERT INTO `test`.`temporary_table` VALUES (1,'a','b','c'); COMMIT; END$$ DELIMITER ; Now run this: mysql> flush logs; Query OK, 0 rows affected (0.17 sec) mysql> call p_tmp_tables(); Query OK, 0 rows affected (0.08 sec) mysql> SHOW binlog events IN 'bin-logs.000029'\G *************************** 1. row *************************** Log_name: bin-logs.000029 Pos: 4 Event_type: Format_desc Server_id: 3232292412 End_log_pos: 106 Info: Server ver: 5.1.24-rc-community-log, Binlog ver: 4 *************************** 2. row *************************** Log_name: bin-logs.000029 Pos: 106 Event_type: Query Server_id: 3232292412 End_log_pos: 174 Info: use `test`; BEGIN *************************** 3. row *************************** Log_name: bin-logs.000029 Pos: 174 Event_type: Query Server_id: 3232292412 End_log_pos: 282 Info: use `test`; INSERT INTO `test`.`ints` VALUES (1) *************************** 4. row *************************** Log_name: bin-logs.000029 Pos: 282 Event_type: Query Server_id: 3232292412 End_log_pos: 552 Info: use `test`; CREATE TEMPORARY TABLE `test`.`temporary_table` ( `id` int(11) NOT NULL, `value_1` varchar(10) default NULL, `value_2` varchar(10) default NULL, `value_3` varchar(10) default NULL) ENGINE=InnoDB *************************** 5. row *************************** Log_name: bin-logs.000029 Pos: 552 Event_type: Query Server_id: 3232292412 End_log_pos: 676 Info: use `test`; INSERT INTO `test`.`temporary_table` VALUES (1,'a','b','c') *************************** 6. row *************************** Log_name: bin-logs.000029 Pos: 676 Event_type: Xid Server_id: 3232292412 End_log_pos: 703 Info: COMMIT /* xid=3015426 */ It can also be noted that if you simply create your temporary table using "CREATE TEMPORARY TABLE ... AS SELECT" syntax, that select initiates a transaction and this statement would be logged correctly in the binlog Suggested fix: Fix it so that a regular "CREATE TEMPORARY TABLE" statement initiates a safe transaction. This will allow those who use replication to have transaction safe temp tables - if the slave crashes during an operation and restarts, we can pick up where we left off instead of getting the dreaded "tmp table does not exist" condition.