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.

Portions of this website are copyright © 2001, 2002 The PHP Group

Page generated in 0.046 sec. using MySQL 8.4.7-u6-cloud

Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.