Bug #56662 Assertion failed: next_insert_id == 0, file .\handler.cc

      
Normally, auto_increment value is generated for the column by
inserting either NULL or 0 into it. NO_AUTO_VALUE_ON_ZERO
suppresses this behavior for 0 so that only NULL generates
the auto_increment value. This behavior is also followed by
a slave, specifically by the SQL Thread, when applying events
in the statement format from a master. However, when applying
events in the row format, the flag was ignored thus causing
an assertion failure:
"Assertion failed: next_insert_id == 0, file .\handler.cc"
      
In fact, we never need to generate a auto_increment value for
the column when applying events in row format on slave. So we
don't allow it to happen by using 'MODE_NO_AUTO_VALUE_ON_ZERO'.
      
Refactoring: Get rid of all the sql_mode checks to rows_log_event
when applying it for avoiding problems caused by the inconsistency
of the sql_mode on slave and master as the sql_mode is not set for
Rows_log_event.

1 files changed, 26 insertions, 0 deletions

diff --git a/mysql-test/extra/rpl_tests/rpl_auto_increment.test b/mysql-test/extra/rpl_tests/rpl_auto_increment.test
index d81ab15a945..9bfda48df0b 100644
--- a/mysql-test/extra/rpl_tests/rpl_auto_increment.test
+++ b/mysql-test/extra/rpl_tests/rpl_auto_increment.test
@@ -229,5 +229,31 @@ source include/diff_tables.inc;
 DROP TABLE t1;
 DROP TABLE t2;
 SET SQL_MODE='';
+sync_slave_with_master;
+
+#
+# BUG#56662
+# The test verifies if the assertion of "next_insert_id == 0"
+# will fail in ha_external_lock() function.
+#
+connection master;
+CREATE TABLE t1 (id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, data INT) ENGINE=innodb;
+
+BEGIN;
+--echo # Set sql_mode with NO_AUTO_VALUE_ON_ZERO for allowing
+--echo # zero to fill the auto_increment field.
+SET SQL_MODE=NO_AUTO_VALUE_ON_ZERO;
+INSERT INTO t1(id,data) VALUES(0,2);
+--echo # Resetting sql_mode without NO_AUTO_VALUE_ON_ZERO to
+--echo # affect the execution of the transaction on slave.
+SET SQL_MODE=0;
+COMMIT;
+SELECT * FROM t1;
+sync_slave_with_master;
+SELECT * FROM t1;
+
+connection master;
+DROP TABLE t1;
+sync_slave_with_master;
 
 --source include/rpl_end.inc