MySQL Bugs: #45999: row based replication fails when auto

Bug #45999	row based replication fails when auto_increment field = 0
Submitted:	7 Jul 2009 10:34	Modified:	19 Dec 2009 11:05
Reporter:	Uxio Faria	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Row Based Replication ( RBR )	Severity:	S2 (Serious)
Version:	5.1, 5.4 bzr	OS:	Linux
Assigned to:	Libing Song	CPU Architecture:	Any

Description:
When import a dump made with mysqldump and replicate a row with auto_increment field = 0 the master writes correctly because the dump file has the variable 
NO_AUTO_VALUE_ON_ZERO active.

On the slave even setting the global variable sql_mode=NO_AUTO_VALUE_ON_ZERO the slave thread fails because a "duplicate key" made by the change of auto_increment field to 1.

How to repeat:
replicate an auto_increment field with '0' value.

Suggested fix:
replicate "as is" rows in RBR.

Thank you for the report.

Verified, but in my case slave crashes:

Thread 1 (process 25697):
#0  0x0000003429e0b002 in pthread_kill () from /lib64/libpthread.so.0
#1  0x0000000000b45920 in my_write_core (sig=6) at stacktrace.c:309
#2  0x00000000006bfd3a in handle_segfault (sig=6) at mysqld.cc:2715
#3  <signal handler called>
#4  0x0000003429230015 in raise () from /lib64/libc.so.6
#5  0x0000003429231980 in abort () from /lib64/libc.so.6
#6  0x0000003429229726 in __assert_fail () from /lib64/libc.so.6
#7  0x0000000000826168 in handler::ha_external_lock (this=0xf1c10a0, thd=0xf19b3d8, lock_type=2) at handler.cc:5439
#8  0x00000000006b815d in unlock_external (thd=0xf19b3d8, table=0xf1d2490, count=1) at lock.cc:822
#9  0x00000000006b7342 in mysql_unlock_tables (thd=0xf19b3d8, sql_lock=0xf1d2468) at lock.cc:437
#10 0x00000000007241ab in close_thread_tables (thd=0xf19b3d8, is_back_off=false) at sql_base.cc:1465
#11 0x000000000089f08b in Relay_log_info::slave_close_thread_tables (this=0xee60760, thd=0xf19b3d8) at rpl_rli.cc:957
#12 0x000000000089eecf in Relay_log_info::cleanup_context (this=0xee60760, thd=0xf19b3d8, error=false) at rpl_rli.cc:924
#13 0x00000000007e4b3e in rows_event_stmt_cleanup (rli=0xee60760, thd=0xf19b3d8) at log_event.cc:7604
#14 0x00000000007e4bf2 in Rows_log_event::do_update_pos (this=0xeff4e08, rli=0xee60760) at log_event.cc:7630
#15 0x0000000000893f38 in Log_event::update_pos (this=0xeff4e08, rli=0xee60760) at log_event.h:1093
#16 0x000000000088e057 in apply_event_and_update_pos (ev=0xeff4e08, thd=0xf19b3d8, rli=0xee60760, skip=true) at slave.cc:2204
#17 0x000000000088e3de in exec_relay_log_event (thd=0xf19b3d8, rli=0xee60760) at slave.cc:2319
#18 0x0000000000890280 in handle_slave_sql (arg=0xeee0e10) at slave.cc:3075
#19 0x0000003429e061b5 in start_thread () from /lib64/libpthread.so.0
#20 0x00000034292cd39d in clone () from /lib64/libc.so.6
#21 0x0000000000000000 in ?? ()

Error log contains:

handler.cc:4556: failed assertion `next_insert_id == 0'

Test used:

--source include/master-slave.inc

set sql_mode='NO_AUTO_VALUE_ON_ZERO';

create table t1(f1 int not null auto_increment primary key);

insert into t1 values(0);
insert into t1 values(1);

select * from t1;

sleep 1;

connection slave;

--vertical_results
show slave status;

select * from t1;

Option file:

--binlog-format=row

Workaround: --binlog-format=statement

Sveta Smirnova,
DBUG_ASSERT(next_insert_id == 0) results in the crash when DBUG opened.

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/81672

3078 Li-Bing.Song@sun.com	2009-08-27
      BUG#45999 Row based replication fails when auto_increment field = 0
      
      In RBP, Slave trait 0 as NULL of an auto_increment field whenever NO_AUTO_VALUE_ON_ZERO has setted or not.
      So slave always generates a new index for an auto_increment field if the value of an auto_increment field is 0.
      This results in an inconsistancy of the data between master and slave. 
      Many times, it results in an error of "duplicate key".
      
      In fact, RBP slave does not care about what value is an auto_increment field.
      It must write the value without any change. 
      This patch wrote code to trait 0 as a value of an auto_increment field on slave
      whenever NO_AUTO_VALUE_ON_ZERO has setted or not.
     @ sql/handler.cc
        DBUG_ASSERT(next_insert_id == 0) results in a crash when DBUG opened.
        So it is erased.

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/82201

3078 Li-Bing.Song@sun.com	2009-09-02
      BUG#45999 Row based replication fails when auto_increment field = 0
      
      In RBR, When we insert an entry into a table in which has a auto_increment field, 
      Master treats 0 as value of the auto_increment field if NO_AUTO_VALUE_ON_ZERO is seted into SQL_MODE.
      In contrast, slave generates new sequence numbers when it encounters the 0 values.
      So, it results in a inconsistency between master and slave.
      
      The table->auto_increment_is_not_null is FALSE, which causes this bug to appear.
      When store engine encounters the 0 values, it will generate new sequence numbers unless 
      table->auto_increment_is_not_null is TRUE and NO_AUTO_VALUE_ON_ZERO is seted into SQL_MODE.
      This patch assigns TRUE to table->auto_increment_is_not_null at the begin of the Write_rows_log_event's execution.

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/82526

3114 Li-Bing.Song@sun.com	2009-09-05
      BUG#45999 Row based replication fails when auto_increment field = 0
      
      In RBR, There is an inconsistency between slaves and master.
      When INSERT statement which includes an auto_increment field is executed,
      Store engine of master will check the value of the auto_increment field. 
      It will generate a sequence number and then replace the value, if its value is NULL or empty.
      if the field's value is 0, the store engine will do like encountering the NULL values 
      unless NO_AUTO_VALUE_ON_ZERO is set into SQL_MODE.
      In contrast, if the field's value is 0, Store engine of slave always generates a new sequence number 
      whether or not NO_AUTO_VALUE_ON_ZERO is set into SQL_MODE.
      
      SQL MODE of slave sql thread is always consistency with master's.
      Another variable is related to this bug.
      If generateing a sequence number is decided by the values of
      table->auto_increment_field_not_null and SQL_MODE(if includes MODE_NO_AUTO_VALUE_ON_ZERO)
      The table->auto_increment_is_not_null is FALSE, which causes this bug to appear.

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/82759

3110 Li-Bing.Song@sun.com	2009-09-09
      BUG#45999 Row based replication fails when auto_increment field = 0
      
      In RBR, There is an inconsistency between slaves and master.
      When INSERT statement which includes an auto_increment field is executed,
      Store engine of master will check the value of the auto_increment field. 
      It will generate a sequence number and then replace the value, if its value is NULL or empty.
      if the field's value is 0, the store engine will do like encountering the NULL values 
      unless NO_AUTO_VALUE_ON_ZERO is set into SQL_MODE.
      In contrast, if the field's value is 0, Store engine of slave always generates a new sequence number 
      whether or not NO_AUTO_VALUE_ON_ZERO is set into SQL_MODE.
      
      SQL MODE of slave sql thread is always consistency with master's.
      Another variable is related to this bug.
      If generateing a sequence number is decided by the values of
      table->auto_increment_field_not_null and SQL_MODE(if includes MODE_NO_AUTO_VALUE_ON_ZERO)
      The table->auto_increment_is_not_null is FALSE, which causes this bug to appear.

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/82909

3116 Li-Bing.Song@sun.com	2009-09-10
      BUG#45999 Row based replication fails when auto_increment field = 0
      
      In RBR, There is an inconsistency between slaves and master.
      When INSERT statement which includes an auto_increment field is executed,
      Store engine of master will check the value of the auto_increment field. 
      It will generate a sequence number and then replace the value, if its value is NULL or empty.
      if the field's value is 0, the store engine will do like encountering the NULL values 
      unless NO_AUTO_VALUE_ON_ZERO is set into SQL_MODE.
      In contrast, if the field's value is 0, Store engine of slave always generates a new sequence number 
      whether or not NO_AUTO_VALUE_ON_ZERO is set into SQL_MODE.
      
      SQL MODE of slave sql thread is always consistency with master's.
      Another variable is related to this bug.
      If generateing a sequence number is decided by the values of
      table->auto_increment_field_not_null and SQL_MODE(if includes MODE_NO_AUTO_VALUE_ON_ZERO)
      The table->auto_increment_is_not_null is FALSE, which causes this bug to appear. ..

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/82916

3117 Li-Bing.Song@sun.com	2009-09-10
      BUG#45999 Row based replication fails when auto_increment field = 0
      
      In RBR, There is an inconsistency between slaves and master.
      When INSERT statement which includes an auto_increment field is executed,
      Store engine of master will check the value of the auto_increment field. 
      It will generate a sequence number and then replace the value, if its value is NULL or empty.
      if the field's value is 0, the store engine will do like encountering the NULL values 
      unless NO_AUTO_VALUE_ON_ZERO is set into SQL_MODE.
      In contrast, if the field's value is 0, Store engine of slave always generates a new sequence number 
      whether or not NO_AUTO_VALUE_ON_ZERO is set into SQL_MODE.
      
      SQL MODE of slave sql thread is always consistency with master's.
      Another variable is related to this bug.
      If generateing a sequence number is decided by the values of
      table->auto_increment_field_not_null and SQL_MODE(if includes MODE_NO_AUTO_VALUE_ON_ZERO)
      The table->auto_increment_is_not_null is FALSE, which causes this bug to appear. ..

Pushed into mysql-5.1-bugteam and mysql-pe

Pushed into 5.4.4-alpha (revid:alik@sun.com-20090914155317-m1g9wodmndzdj4l1) (version source revid:alik@sun.com-20090914155317-m1g9wodmndzdj4l1) (merge vers: 5.4.4-alpha) (pib:11)

Documented bugfix in the 5.4.4 changelog as follows:

        When using row-based replication, importing a dump made with
        mysqldump and replicating a row with an AUTO_INCREMENT column
        set to 0, with NO_AUTO_VALUE_ON_ZERO active on the master, the
        row was inserted successfully on the master; however any setting
        for NO_AUTO_VALUE_ON_ZERO was ignored on the slave. When the
        AUTO_INCREMENT column was incremented, this caused replication
        to fail on the slave due to a duplicate key error. In some cases
        it could also cause the slave to crash.

Set Status to NDI, waiting for push to 5.1 tree.

Moved 5.4 changelog entry from 5.4.4 to 5.4.3.

Pushed into 5.1.40 (revid:joro@sun.com-20091006073316-lea2cpijh9r6on7c) (version source revid:ingo.struewing@sun.com-20090916070128-6053el2ucp5z7pyn) (merge vers: 5.1.39) (pib:11)

Fix also documented in the 5.1.40 changelog.

Closed.

Pushed into 5.1.41-ndb-7.1.0 (revid:jonas@mysql.com-20091218102229-64tk47xonu3dv6r6) (version source revid:jonas@mysql.com-20091218095730-26gwjidfsdw45dto) (merge vers: 5.1.41-ndb-7.1.0) (pib:15)

Pushed into 5.1.41-ndb-6.2.19 (revid:jonas@mysql.com-20091218100224-vtzr0fahhsuhjsmt) (version source revid:jonas@mysql.com-20091217101452-qwzyaig50w74xmye) (merge vers: 5.1.41-ndb-6.2.19) (pib:15)

Pushed into 5.1.41-ndb-6.3.31 (revid:jonas@mysql.com-20091218100616-75d9tek96o6ob6k0) (version source revid:jonas@mysql.com-20091217154335-290no45qdins5bwo) (merge vers: 5.1.41-ndb-6.3.31) (pib:15)

Pushed into 5.1.41-ndb-7.0.11 (revid:jonas@mysql.com-20091218101303-ga32mrnr15jsa606) (version source revid:jonas@mysql.com-20091218064304-ezreonykd9f4kelk) (merge vers: 5.1.41-ndb-7.0.11) (pib:15)

No additional changelog entries needed. Closed.