MySQL Bugs: #40655: CREATE TEMPORARY TABLE written to binlog before commit

Bug #40655	CREATE TEMPORARY TABLE written to binlog before commit
Submitted:	11 Nov 2008 22:58	Modified:	16 Nov 2008 16:42
Reporter:	Mark Callaghan	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S2 (Serious)
Version:	5.0.67, 5.0.70, 5.1.29	OS:	Any
Assigned to:	Jon Stephens	CPU Architecture:	Any
Tags:	commit, create, replication, table, temporary

Description:
Using this as a reference:
  http://dev.mysql.com/doc/refman/5.0/en/implicit-commit.html

When this is run the 'create temporary table' statement is immediately written to the binlog.
  begin;
  create temporary table t(i int);

When this is run, it is not written until commit:
  create table fb(i int) engine=innodb;  
  begin;
  insert into fb values (1);
  create temporary table t(i int);
  commit;

This is inconsistent and writing the create temp table statement immediately contradicts the documentation on implicit commit.

How to repeat:
see above

Thank you for a problem report. Verified with 5.0.70 and 5.1.29:

C:\Program Files\MySQL\MySQL Server 5.0\bin>mysql -uroot -proot -P3310 test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.1.29-rc-community-log MySQL Community Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql>   create temporary table t(i int);
Query OK, 0 rows affected (0.08 sec)

mysql> rollback;
Query OK, 0 rows affected (0.00 sec)

mysql> show master status;
+-------------------------+----------+--------------+------------------+
| File                    | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+-------------------------+----------+--------------+------------------+
| toshiba-user-bin.000006 |      200 |              |                  |
+-------------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

mysql> show binlog events in 'toshiba-user-bin.000006'\G
*************************** 1. row ***************************
   Log_name: toshiba-user-bin.000006
        Pos: 4
 Event_type: Format_desc
  Server_id: 1
End_log_pos: 106
       Info: Server ver: 5.1.29-rc-community-log, Binlog ver: 4
*************************** 2. row ***************************
   Log_name: toshiba-user-bin.000006
        Pos: 106
 Event_type: Query
  Server_id: 1
End_log_pos: 200
       Info: use `test`; create temporary table t(i int)
2 rows in set (0.00 sec)

mysql> drop table t;
Query OK, 0 rows affected (0.13 sec)

mysql> create table fb(i int) engine=innodb;
Query OK, 0 rows affected (0.09 sec)

mysql>   begin;
Query OK, 0 rows affected (0.00 sec)

mysql>   insert into fb values (1);
Query OK, 1 row affected (0.00 sec)

mysql>   create temporary table t(i int);
Query OK, 0 rows affected (0.05 sec)

mysql> show master status;
+-------------------------+----------+--------------+------------------+
| File                    | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+-------------------------+----------+--------------+------------------+
| toshiba-user-bin.000006 |      374 |              |                  |
+-------------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

mysql> show binlog events in 'toshiba-user-bin.000006'\G
*************************** 1. row ***************************
   Log_name: toshiba-user-bin.000006
        Pos: 4
 Event_type: Format_desc
  Server_id: 1
End_log_pos: 106
       Info: Server ver: 5.1.29-rc-community-log, Binlog ver: 4
*************************** 2. row ***************************
   Log_name: toshiba-user-bin.000006
        Pos: 106
 Event_type: Query
  Server_id: 1
End_log_pos: 200
       Info: use `test`; create temporary table t(i int)
*************************** 3. row ***************************
   Log_name: toshiba-user-bin.000006
        Pos: 200
 Event_type: Query
  Server_id: 1
End_log_pos: 275
       Info: use `test`; drop table t
*************************** 4. row ***************************
   Log_name: toshiba-user-bin.000006
        Pos: 275
 Event_type: Query
  Server_id: 1
End_log_pos: 374
       Info: use `test`; create table fb(i int) engine=innodb
4 rows in set (0.00 sec)

mysql> rollback;
Query OK, 0 rows affected (0.05 sec)

mysql> show global variables like '%binlog%';
+--------------------------------+------------+
| Variable_name                  | Value      |
+--------------------------------+------------+
| binlog_cache_size              | 32768      |
| binlog_format                  | STATEMENT  |
| innodb_locks_unsafe_for_binlog | OFF        |
| max_binlog_cache_size          | 4294967295 |
| max_binlog_size                | 1073741824 |
| sync_binlog                    | 0          |
+--------------------------------+------------+
6 rows in set (0.00 sec)

This behaviour is inconsistent and, thus, is a bug.

Assigning this to myself, since it's Replication.

Setting P1 since it's a substantial issue and it affects a customer.

This issue depends on behaviour that is specific neither to CREATE TEMPORARY TABLE nor to implicit COMMITs; rather, it lies with the fact that CREATE TEMPORARY TABLE is treated just like any other non-transactional statement and thus may lead to synchronisation problems when used within replicated transactions. See http://dev.mysql.com/doc/refman/5.0/en/replication-features-transactions.html.

I have read http://dev.mysql.com/doc/refman/5.0/en/replication-features-transactions.html several times. I am not sure what behavior is specified by it. Examples would improve it.

That section doesn't explain the case in which transaction bracketing is not lost demonstrated by the example I provided. Rather it states that 'updates to the binary log may be out of synchrony'. This should be made less ambiguous.

Mark,

We say in the docs that we cannot guarantee synchrony when mixing statements on transactional and non-transactional statements within transactions because we mean *exactly that*. 

In such cases, we don't document how the server may happen to behave, because we have no way to guarantee that behaviour, to know exactly what circumstances produce it in which versions of the server, or whether it will remain consistent between versions.

It's not up to me in a case such as this to make a promise in the documentation that we support a specific behaviour. Rather, our architects and developers must commit to supporting the behaviour, *then* we can promise in the docs that we support it.

If you would like to see a specific behaviour supported, please file a Server bug/Feature Request.

Thanks!