Bug #22865 CREATE ... SELECT is logged too late
Submitted: 30 Sep 2006 20:54 Modified: 7 Feb 2007 12:05
Reporter: Andrei Elkin Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Replication Severity:S3 (Non-critical)
Version:5.1.12-BK OS:Linux (Linux)
Assigned to: Mats Kindahl CPU Architecture:Any

[30 Sep 2006 20:54] Andrei Elkin
Description:
CREATE table new engine=myisam ... SELECT should binlog its work, CREATE a new table, at the end of the statement regardless of autocommit status of the session.

If not, a concurrent connection can successfully modify the new table and leave a record in binlog, and that can be before CREATE .. SELECT running session finally
issues COMMIT. In such case binlog would have 
    insert new values ()
    create table new ()
records in that wrong order.

Note, only combination of myisam table type and @@session.binlog_format=statement
provides the correct logging. Other combinations (myisam or innodb) deliver incorrect sequence of records into binlog.

There is a related Bug#22862.

How to repeat:
drop table if exists t;
reset master; 

connection1:                                           connection2
/*1*/ set autocommit=0;
/*2*/ create table t (a int) engine=innodb \
                            select (1) as b;
/*3*/                                                  insert into t2 values(1,1)
/*4* commit;

show binlog events;   # will list insert,create                                                                 

Suggested fix:
Perhaps, create ... selected is not honored as implicitly committing.
[1 Oct 2006 5:35] Valeriy Kravchuk
Thank you for a bug report. Verified just as described.
[2 Oct 2006 13:24] Andrei Elkin
regression test

Attachment: bug22865.test (application/octet-stream, text), 711 bytes.

[16 Nov 2006 13:34] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/15421

ChangeSet@1.2309, 2006-11-16 14:34:28+01:00, mats@romeo.(none) +9 -0
  BUG#22864 (Rollback following CREATE... SELECT discards 'CREATE TABLE' from log):
  When row-based logging is used, the CREATE-SELECT is written as two parts: as a CREATE TABLE
  statement and as the rows for the table. For both transactional and non-transactional tables,
  the CREATE TABLE statement was written to the transaction cache, as were the rows, and on
  statement end, the entire transaction cache was written to the binary log if the table was
  non-transactional. For transactional tables, the events were kept in the transaction cache
  until end of transaction (or statement that were not part of a transaction).
  
  For the case when AUTOCOMMIT=0 and we are creating a transactional table using a create select,
  we would then keep the CREATE TABLE statement and the rows for the CREATE-SELECT, while
  executing the following statements. On a rollback, the transaction cache would then be
  cleared, which would also remove the CREATE TABLE statement. Hence no table would be created
  on the slave, while there is an empty table on the master.
  
  This relates to BUG#22865 where the table being created exists on the master, but not on 
  the slave during insertion of rows into the newly created table. This occurs since the
  CREATE TABLE statement were still in the transaction cache until the statement finished
  executing, and possibly longer if the table was transactional.
  
  This patch changes the behaviour of the CREATE-SELECT statement by writing the CREATE TABLE
  statement directly to the binary log as soon as it is created, then the rows that are inserted
  into the table, and on error a DROP table is written to the binary log.  For transactional tables,
  the intermediate rows are not written to the binary log, but for non-transactional tables, 
  the rows are written to the binary log immediately (hence, changes to non-transactional tables
  are propagated to the slave as soon as enough rows are collected to form a rows event).
  
  On the master, the table is locked for the duration of the CREATE-SELECT statement, but this
  behaviour is not replicated to the slave. Hence, it is possible to manipulate the table contents
  or the table definition in the time between the CREATE TABLE statement has arrived to the slave
  and the first rows event for the table is executed by the slave, at which point the table
  is locked for writing.
[1 Dec 2006 8:43] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/16277

ChangeSet@1.2309, 2006-12-01 09:43:06+01:00, mats@kindahl-laptop.dnsalias.net +11 -0
  BUG#22864 (Rollback following CREATE... SELECT discards 'CREATE TABLE'
  from log):
  When row-based logging is used, the CREATE-SELECT is written as two
  parts: as a CREATE TABLE statement and as the rows for the table. For
  both transactional and non-transactional tables, the CREATE TABLE
  statement was written to the transaction cache, as were the rows, and
  on statement end, the entire transaction cache was written to the binary
  log if the table was non-transactional. For transactional tables, the
  events were kept in the transaction cache until end of transaction (or
  statement that were not part of a transaction).
  
  For the case when AUTOCOMMIT=0 and we are creating a transactional table
  using a create select, we would then keep the CREATE TABLE statement and
  the rows for the CREATE-SELECT, while executing the following statements.
  On a rollback, the transaction cache would then be cleared, which would
  also remove the CREATE TABLE statement. Hence no table would be created
  on the slave, while there is an empty table on the master.
  
  This relates to BUG#22865 where the table being created exists on the
  master, but not on the slave during insertion of rows into the newly
  created table. This occurs since the CREATE TABLE statement were still
  in the transaction cache until the statement finished executing, and
  possibly longer if the table was transactional.
  
  This patch changes the behaviour of the CREATE-SELECT statement by
  writing the CREATE TABLE statement directly to the binary log as soon
  as it is created, then the rows that are inserted into the table, and
  on error a DROP table is written to the binary log, provided:
  - the table was created,
  - the table didn't exist prior to the statement,
  - we are replicating row-based, and
  - it was not a temporary table.
  
  For transactional tables, the intermediate rows are not written to the
  binary log, but for non-transactional tables, the rows are written to
  the binary log immediately (hence, changes to non-transactional tables
  are propagated to the slave as soon as enough rows are collected to form
  a rows event).
[1 Dec 2006 9:45] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/16286

ChangeSet@1.2309, 2006-12-01 10:36:30+01:00, mats@kindahl-laptop.dnsalias.net +11 -0
  BUG#22864 (Rollback following CREATE... SELECT discards 'CREATE TABLE'
  from log):
  When row-based logging is used, the CREATE-SELECT is written as two
  parts: as a CREATE TABLE statement and as the rows for the table. For
  both transactional and non-transactional tables, the CREATE TABLE
  statement was written to the transaction cache, as were the rows, and
  on statement end, the entire transaction cache was written to the binary
  log if the table was non-transactional. For transactional tables, the
  events were kept in the transaction cache until end of transaction (or
  statement that were not part of a transaction).
  
  For the case when AUTOCOMMIT=0 and we are creating a transactional table
  using a create select, we would then keep the CREATE TABLE statement and
  the rows for the CREATE-SELECT, while executing the following statements.
  On a rollback, the transaction cache would then be cleared, which would
  also remove the CREATE TABLE statement. Hence no table would be created
  on the slave, while there is an empty table on the master.
  
  This relates to BUG#22865 where the table being created exists on the
  master, but not on the slave during insertion of rows into the newly
  created table. This occurs since the CREATE TABLE statement were still
  in the transaction cache until the statement finished executing, and
  possibly longer if the table was transactional.
  
  This patch changes the behaviour of the CREATE-SELECT statement by
  writing the CREATE TABLE statement directly to the binary log as soon
  as it is created, then the rows that are inserted into the table, and
  on error a DROP table is written to the binary log, provided:
  - the table was created,
  - the table didn't exist prior to the statement,
  - we are replicating row-based, and
  - it was not a temporary table.
  
  For transactional tables, the intermediate rows are not written to the
  binary log, but for non-transactional tables, the rows are written to
  the binary log immediately (hence, changes to non-transactional tables
  are propagated to the slave as soon as enough rows are collected to form
  a rows event).
[12 Dec 2006 19:50] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/16853

ChangeSet@1.2309, 2006-12-12 20:50:29+01:00, mats@romeo.(none) +10 -0
  BUG#22864 (Rollback following CREATE... SELECT discards 'CREATE TABLE'
  from log):
  When row-based logging is used, the CREATE-SELECT is written as two
  parts: as a CREATE TABLE statement and as the rows for the table. For
  both transactional and non-transactional tables, the CREATE TABLE
  statement was written to the transaction cache, as were the rows, and
  on statement end, the entire transaction cache was written to the binary
  log if the table was non-transactional. For transactional tables, the
  events were kept in the transaction cache until end of transaction (or
  statement that were not part of a transaction).
  
  For the case when AUTOCOMMIT=0 and we are creating a transactional table
  using a create select, we would then keep the CREATE TABLE statement and
  the rows for the CREATE-SELECT, while executing the following statements.
  On a rollback, the transaction cache would then be cleared, which would
  also remove the CREATE TABLE statement. Hence no table would be created
  on the slave, while there is an empty table on the master.
  
  This relates to BUG#22865 where the table being created exists on the
  master, but not on the slave during insertion of rows into the newly
  created table. This occurs since the CREATE TABLE statement were still
  in the transaction cache until the statement finished executing, and
  possibly longer if the table was transactional.
  
  This patch changes the behaviour of the CREATE-SELECT statement by
  adding an implicit commit at the end of the statement when creating
  non-temporary tables. Hence, non-temporary tables will be written to the
  binary log on completion, and in the even of AUTOCOMMIT=0, a new
  transaction will be started. Temporary tables do not commit an ongoing
  transaction: neither as a pre- not a post-commit.
  
  The events for both transactional and non-transactional tables are
  saved in the transaction cache, and written to the binary log at end
  of the statement.
[19 Dec 2006 11:05] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/17151

ChangeSet@1.2309, 2006-12-19 12:04:58+01:00, mats@romeo.(none) +10 -0
  BUG#22864 (Rollback following CREATE... SELECT discards 'CREATE TABLE'
  from log):
  When row-based logging is used, the CREATE-SELECT is written as two
  parts: as a CREATE TABLE statement and as the rows for the table. For
  both transactional and non-transactional tables, the CREATE TABLE
  statement was written to the transaction cache, as were the rows, and
  on statement end, the entire transaction cache was written to the binary
  log if the table was non-transactional. For transactional tables, the
  events were kept in the transaction cache until end of transaction (or
  statement that were not part of a transaction).
  
  For the case when AUTOCOMMIT=0 and we are creating a transactional table
  using a create select, we would then keep the CREATE TABLE statement and
  the rows for the CREATE-SELECT, while executing the following statements.
  On a rollback, the transaction cache would then be cleared, which would
  also remove the CREATE TABLE statement. Hence no table would be created
  on the slave, while there is an empty table on the master.
  
  This relates to BUG#22865 where the table being created exists on the
  master, but not on the slave during insertion of rows into the newly
  created table. This occurs since the CREATE TABLE statement were still
  in the transaction cache until the statement finished executing, and
  possibly longer if the table was transactional.
  
  This patch changes the behaviour of the CREATE-SELECT statement by
  adding an implicit commit at the end of the statement when creating
  non-temporary tables. Hence, non-temporary tables will be written to the
  binary log on completion, and in the even of AUTOCOMMIT=0, a new
  transaction will be started. Temporary tables do not commit an ongoing
  transaction: neither as a pre- not a post-commit.
  
  The events for both transactional and non-transactional tables are
  saved in the transaction cache, and written to the binary log at end
  of the statement.
[21 Dec 2006 8:29] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/17255

ChangeSet@1.2309, 2006-12-21 09:29:02+01:00, mats@romeo.(none) +10 -0
  BUG#22864 (Rollback following CREATE... SELECT discards 'CREATE TABLE'
  from log):
  When row-based logging is used, the CREATE-SELECT is written as two
  parts: as a CREATE TABLE statement and as the rows for the table. For
  both transactional and non-transactional tables, the CREATE TABLE
  statement was written to the transaction cache, as were the rows, and
  on statement end, the entire transaction cache was written to the binary
  log if the table was non-transactional. For transactional tables, the
  events were kept in the transaction cache until end of transaction (or
  statement that were not part of a transaction).
  
  For the case when AUTOCOMMIT=0 and we are creating a transactional table
  using a create select, we would then keep the CREATE TABLE statement and
  the rows for the CREATE-SELECT, while executing the following statements.
  On a rollback, the transaction cache would then be cleared, which would
  also remove the CREATE TABLE statement. Hence no table would be created
  on the slave, while there is an empty table on the master.
  
  This relates to BUG#22865 where the table being created exists on the
  master, but not on the slave during insertion of rows into the newly
  created table. This occurs since the CREATE TABLE statement were still
  in the transaction cache until the statement finished executing, and
  possibly longer if the table was transactional.
  
  This patch changes the behaviour of the CREATE-SELECT statement by
  adding an implicit commit at the end of the statement when creating
  non-temporary tables. Hence, non-temporary tables will be written to the
  binary log on completion, and in the even of AUTOCOMMIT=0, a new
  transaction will be started. Temporary tables do not commit an ongoing
  transaction: neither as a pre- not a post-commit.
  
  The events for both transactional and non-transactional tables are
  saved in the transaction cache, and written to the binary log at end
  of the statement.
[22 Dec 2006 14:11] Mats Kindahl
Bug solved by adding an implicit commit at the end of all CREATE TABLE ... SELECT ... statements. Documentation needs to be updated to reflect this; but see BUG#22864 for more information.
[7 Feb 2007 12:05] MC Brown
An entry has been added to the 5.1.15 changelog, and the notes on 'Statements that issue an implicit commit' have been updated to reflect the change of behaviour in this statement.