Hi Libing,
Great work. Thanks!
Some comments below. Take it if you like. Thanks!
Patch approved!
Best Regards,
Daogang
Li-Bing.Song@stripped wrote:
> #At file:///home/anders/work/bzrwork/August/bug45999/mysql-5.1-bugteam/ based on
> revid:davi.arnaut@stripped
>
> 3114 Li-Bing.Song@stripped 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.
>
> modified:
> mysql-test/extra/rpl_tests/rpl_auto_increment.test
> mysql-test/suite/rpl/r/rpl_auto_increment.result
> sql/log_event.cc
> === modified file 'mysql-test/extra/rpl_tests/rpl_auto_increment.test'
> --- a/mysql-test/extra/rpl_tests/rpl_auto_increment.test 2009-01-14 08:27:32 +0000
> +++ b/mysql-test/extra/rpl_tests/rpl_auto_increment.test 2009-09-05 15:02:00 +0000
> @@ -163,5 +163,81 @@ show create table t1;
> connection master;
> drop table t1;
>
> +#
> +# BUG#45999 Row based replication fails when auto_increment field = 0.
> +# Store engine of Slaves auto-generates new sequence numbers for
> +# auto_increment fields if the values of them are 0. There is an inconsistency
> +# between slave and master. When MODE_NO_AUTO_VALUE_ON_ZERO are masters treat
>
BUG#45999
This test verifies if store engine of Slaves auto-generates new sequence
numbers for
auto_increment fields if the values of them are 0, which will make
master and slave
become inconsistent, when ......
> +#
> +source include/master-slave-reset.inc;
> +
> +connection master;
> +--disable_warnings
> +DROP TABLE IF EXISTS t1;
> +DROP TABLE IF EXISTS t2;
> +--enable_warnings
> +
> +eval CREATE TABLE t1 (id INT NOT NULL AUTO_INCREMENT PRIMARY KEY)
> ENGINE=$engine_type;
> +eval CREATE TABLE t2 (id INT NOT NULL AUTO_INCREMENT PRIMARY KEY)
> ENGINE=$engine_type2;
> +SET SQL_MODE='';
> +# Value of the id will be 1;
> +INSERT INTO t1 VALUES(NULL);
> +INSERT INTO t2 VALUES(NULL);
> +SELECT * FROM t1;
> +SELECT * FROM t2;
> +# Value of the id will be 2;
> +INSERT INTO t1 VALUES();
> +INSERT INTO t2 VALUES();
> +SELECT * FROM t1;
> +SELECT * FROM t2;
> +# Value of the id will be 3. The master treats 0 as NULL or empty because
> +# NO_AUTO_VALUE_ON_ZERO is not assign to SQL_MODE.
> +INSERT INTO t1 VALUES(0);
> +INSERT INTO t2 VALUES(0);
> +SELECT * FROM t1;
> +SELECT * FROM t2;
> +
> +SET SQL_MODE=NO_AUTO_VALUE_ON_ZERO;
> +# Value of the id will be 0. The master does not treat 0 as NULL or empty
> +# because NO_AUTO_VALUE_ON_ZERO has assigned to SQL_MODE.
> +INSERT INTO t1 VALUES(0);
> +INSERT INTO t2 VALUES(0);
> +SELECT * FROM t1;
> +SELECT * FROM t2;
> +
> +INSERT INTO t1 VALUES(4);
> +INSERT INTO t2 VALUES(4);
> +FLUSH LOGS;
> +sync_slave_with_master;
> +
> +let $diff_table_1= master:test.t1;
> +let $diff_table_2= slave:test.t1;
> +source include/diff_tables.inc;
> +
> +let $diff_table_1= master:test.t2;
> +let $diff_table_2= slave:test.t2;
> +source include/diff_tables.inc;
> +
> +connection master;
> +DROP TABLE t1;
> +DROP TABLE t2;
> +sync_slave_with_master;
> +
> +connection master;
> +let $MYSQLD_DATADIR= `SELECT @@DATADIR`;
> +--exec $MYSQL_BINLOG $MYSQLD_DATADIR/master-bin.000001 | $MYSQL test
> +sync_slave_with_master;
> +
> +let $diff_table_1= master:test.t1;
> +let $diff_table_2= slave:test.t1;
> +source include/diff_tables.inc;
> +
> +let $diff_table_1= master:test.t2;
> +let $diff_table_2= slave:test.t2;
> +source include/diff_tables.inc;
> +
> # End cleanup
> +DROP TABLE t1;
> +DROP TABLE t2;
> +SET SQL_MODE='';
> sync_slave_with_master;
>
> === modified file 'mysql-test/suite/rpl/r/rpl_auto_increment.result'
> --- a/mysql-test/suite/rpl/r/rpl_auto_increment.result 2009-01-14 08:27:32 +0000
> +++ b/mysql-test/suite/rpl/r/rpl_auto_increment.result 2009-09-05 15:02:00 +0000
> @@ -244,3 +244,71 @@ t1 CREATE TABLE `t1` (
> PRIMARY KEY (`id`)
> ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1
> drop table t1;
> +stop slave;
> +drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9;
> +reset master;
> +reset slave;
> +drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9;
> +start slave;
> +DROP TABLE IF EXISTS t1;
> +DROP TABLE IF EXISTS t2;
> +CREATE TABLE t1 (id INT NOT NULL AUTO_INCREMENT PRIMARY KEY) ENGINE=innodb;
> +CREATE TABLE t2 (id INT NOT NULL AUTO_INCREMENT PRIMARY KEY) ENGINE=myisam;
> +SET SQL_MODE='';
> +INSERT INTO t1 VALUES(NULL);
> +INSERT INTO t2 VALUES(NULL);
> +SELECT * FROM t1;
> +id
> +1
> +SELECT * FROM t2;
> +id
> +1
> +INSERT INTO t1 VALUES();
> +INSERT INTO t2 VALUES();
> +SELECT * FROM t1;
> +id
> +1
> +2
> +SELECT * FROM t2;
> +id
> +1
> +2
> +INSERT INTO t1 VALUES(0);
> +INSERT INTO t2 VALUES(0);
> +SELECT * FROM t1;
> +id
> +1
> +2
> +3
> +SELECT * FROM t2;
> +id
> +1
> +2
> +3
> +SET SQL_MODE=NO_AUTO_VALUE_ON_ZERO;
> +INSERT INTO t1 VALUES(0);
> +INSERT INTO t2 VALUES(0);
> +SELECT * FROM t1;
> +id
> +0
> +1
> +2
> +3
> +SELECT * FROM t2;
> +id
> +0
> +1
> +2
> +3
> +INSERT INTO t1 VALUES(4);
> +INSERT INTO t2 VALUES(4);
> +FLUSH LOGS;
> +Comparing tables master:test.t1 and slave:test.t1
> +Comparing tables master:test.t2 and slave:test.t2
> +DROP TABLE t1;
> +DROP TABLE t2;
> +Comparing tables master:test.t1 and slave:test.t1
> +Comparing tables master:test.t2 and slave:test.t2
> +DROP TABLE t1;
> +DROP TABLE t2;
> +SET SQL_MODE='';
>
> === modified file 'sql/log_event.cc'
> --- a/sql/log_event.cc 2009-08-27 12:46:29 +0000
> +++ b/sql/log_event.cc 2009-09-05 15:02:00 +0000
> @@ -8312,6 +8312,16 @@ Write_rows_log_event::do_before_row_oper
>
> /* Honor next number column if present */
> m_table->next_number_field= m_table->found_next_number_field;
> + /*
> + * Fixed Bug#45999, In RBR, Store engine of Slave auto-generates new
> + * sequence numbers for auto_increment fields if the values of them are 0.
> + * 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) in update_auto_increment function.
> + * SQL_MODE of slave sql thread is always consistency with master's.
> + * In RBR, auto_increment fields never are NULL.
> + */
> + m_table->auto_increment_field_not_null= TRUE;
> return error;
> }
>
> @@ -8321,6 +8331,7 @@ Write_rows_log_event::do_after_row_opera
> {
> int local_error= 0;
> m_table->next_number_field=0;
> + m_table->auto_increment_field_not_null= FALSE;
> if (bit_is_set(slave_exec_mode, SLAVE_EXEC_MODE_IDEMPOTENT) == 1 ||
> m_table->s->db_type()->db_type == DB_TYPE_NDBCLUSTER)
> {
>
>
> ------------------------------------------------------------------------
>
>
