Bug #22027 CREATE TABLE IF NOT EXISTS SELECT logged improperly with row-based binlog
Submitted: 5 Sep 2006 17:47 Modified: 21 Nov 2006 20:00
Reporter: Dean Ellis Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Row Based Replication ( RBR ) Severity:S2 (Serious)
Version:5.1 OS:
Assigned to: Andrei Elkin CPU Architecture:Any

[5 Sep 2006 17:47] Dean Ellis 
Description:
CREATE TABLE IF NOT EXISTS SELECT ... statements truncate the "IF NOT EXISTS" clause when using row-based binlogs.  This breaks replication.

How to repeat:
RESET MASTER;
CREATE TABLE t1 ( a int );
CREATE TABLE IF NOT EXISTS t2 SELECT * FROM t1;
CREATE TABLE IF NOT EXISTS t2 SELECT * FROM t1;
SHOW BINLOG EVENTS FROM 301\G
DROP TABLE t1, t2;

***

mysql> SHOW BINLOG EVENTS FROM 301\G
*************************** 1. row ***************************
   Log_name: abcdef-bin.000001
        Pos: 301
 Event_type: Query
  Server_id: 511
End_log_pos: 111
       Info: use `test`; CREATE TABLE `t2` (
  `a` int(11) DEFAULT NULL
)
1 row in set (0.00 sec)

Suggested fix:
n/a
[27 Sep 2006 20:30] 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/12656

ChangeSet@1.2348, 2006-09-27 23:30:20+03:00, aelkin@dsl-hkigw8-feb9fb00-191.dhcp.inet.fi +3 -0
  bug#22027: CREATE TABLE IF NOT EXISTS SELECT logged improperly with row-based binlog
  and #22762: create talbe if not exists like a-temp-table binlogged w/o "if not exists"
  
  These are rbr bugs.
  store_create_info, which generates the create statement e.g for binlogging,
  did not account a lex option HA_LEX_CREATE_IF_NOT_EXISTS.
  
  The fix makes the generated query to include the parent's statement option if that was
  supplied.
[27 Sep 2006 20: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/12658

ChangeSet@1.2348, 2006-09-27 23:45:17+03:00, aelkin@dsl-hkigw8-feb9fb00-191.dhcp.inet.fi +2 -0
  bug#22027: CREATE TABLE IF NOT EXISTS SELECT logged improperly with row-based binlog
  and #22762: create talbe if not exists like a-temp-table binlogged w/o "if not exists"
  
  These are rbr bugs.
  store_create_info, which generates the create statement e.g for binlogging,
  did not account a lex option HA_LEX_CREATE_IF_NOT_EXISTS.
  
  The fix makes the generated query to include the parent's statement option if that was
  supplied.
[27 Sep 2006 20:47] 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/12659

ChangeSet@1.2348, 2006-09-27 23:47:25+03:00, aelkin@dsl-hkigw8-feb9fb00-191.dhcp.inet.fi +2 -0
  bug#22027: CREATE TABLE IF NOT EXISTS SELECT logged improperly with row-based binlog
  and #22762: create talbe if not exists like a-temp-table binlogged w/o "if not exists"
  
  These are rbr bugs.
  store_create_info, which generates the create statement e.g for binlogging,
  did not account a lex option HA_LEX_CREATE_IF_NOT_EXISTS.
  
  The fix makes the generated query to include the parent's statement option if that was
  supplied.
[27 Sep 2006 21:09] 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/12661

ChangeSet@1.2348, 2006-09-28 00:09:45+03:00, aelkin@dsl-hkigw8-feb9fb00-191.dhcp.inet.fi +2 -0
  bug#22027: CREATE TABLE IF NOT EXISTS SELECT logged improperly with row-based
  binlog
    and #22762: create talbe if not exists like a-temp-table binlogged w/o "if not
  exists"
  
    These are rbr bugs.
    store_create_info, which generates the create statement e.g for binlogging,
    did not account a lex option HA_LEX_CREATE_IF_NOT_EXISTS.
    
    The fix makes the generated query to include the parent's statement option if
  that was supplied.
[28 Sep 2006 15:51] 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/12739

ChangeSet@1.2350, 2006-09-28 18:51:27+03:00, aelkin@dsl-hkigw8-feb9fb00-191.dhcp.inet.fi +5 -0
  bug#22027: CREATE TABLE IF NOT EXISTS SELECT logged improperly with rbr binlog,
  and #22762: create talbe if not exists like a-temp-table binlogged w/o "if not exists"
      These are rbr bugs.
      store_create_info, which generates the create statement e.g for binlogging,
      did not account a lex option HA_LEX_CREATE_IF_NOT_EXISTS.
      
      The fix makes the generated query to include the parent's statement option
  if
    that was supplied.
[18 Oct 2006 9:41] Alexander Barkov 
Looks ok to push.
[23 Oct 2006 9:37] 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/14161

ChangeSet@1.2322, 2006-10-23 12:36:05+03:00, aelkin@dsl-hkigw8-febefb00-148.dhcp.inet.fi +3 -0
  Bug #22762 create talbe if not exists like a-temp-table binlogged w/o "if not exists"
  Bug #22027 CREATE TABLE IF NOT EXISTS SELECT logged improperly with row-based binlog
      
  post-merge fix, also making results without server's version number.
[23 Oct 2006 12:11] 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/14177

ChangeSet@1.2311, 2006-10-23 15:09:04+03:00, aelkin@dsl-hkigw8-febefb00-148.dhcp.inet.fi +2 -0
  Bug #22762 create talbe if not exists like a-temp-table binlogged w/o "if not exists"
  Bug #22027 CREATE TABLE IF NOT EXISTS SELECT logged improperly with row-based binlog
      
  post-merge fix, also making results free from server's version number.
[23 Oct 2006 15:23] 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/14195

ChangeSet@1.2312, 2006-10-23 18:22:27+03:00, aelkin@dsl-hkigw8-febefb00-148.dhcp.inet.fi +1 -0
  Bug #22027 CREATE TABLE IF NOT EXISTS SELECT logged improperly with row-based binlog
  
  results of binlog_stm_binlog in sync
[20 Nov 2006 15:13] Lars Thalmann 
Pushed into 5.1.14.
[21 Nov 2006 20:00] Paul DuBois 
Noted in 5.1.14 changelog.

With row-based binary logging, CREATE TABLE IF NOT EXISTS SELECT
statements were not logged properly.