MySQL Bugs: #57287: Slave_SQL_Errno is sometimes zero even when there is an error

Bug #57287	Slave_SQL_Errno is sometimes zero even when there is an error
Submitted:	6 Oct 2010 14:42	Modified:	6 Oct 2010 15:07
Reporter:	Sven Sandberg	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Replication	Severity:	S2 (Serious)
Version:	5.1+	OS:	Any
Assigned to:	Assigned Account	CPU Architecture:	Any

Description:
When the replication master executes a statement that causes a change in the database, but returns an error, it writes the statement to the binary log along with the error. When the slave executes the statement, it checks if the statement generates the same error. If not, it stops with an error. However, there is no associated error code for this, it sets Slave_SQL_Errno to the errno that the statement generated on the slave (i.e., 0 if the typical case that the statement did not generate any error at all).

It would be better if there was a dedicated error code for this. Moreover, the error message is currently hard-coded in the source code; it should be moved to errmsg-utf8.txt

See also BUG#52768

How to repeat:
--source include/have_binlog_format_statement.inc
--source include/master-slave.inc

CREATE TABLE t1 (a INT PRIMARY KEY);
INSERT INTO t1 VALUES (1);
--sync_slave_with_master
DELETE FROM t1;
--connection master
# 1062 = ER_DUP_ENTRY
--error 1062
INSERT INTO t1 VALUES (2), (1);
--connection slave
--sleep 5
query_vertical SHOW SLAVE STATUS;

Suggested fix:
=== modified file 'sql/log_event.cc'
--- sql/log_event.cc	2010-08-23 22:31:12 +0000
+++ sql/log_event.cc	2010-10-06 14:37:48 +0000
@@ -3417,16 +3417,13 @@ compare_errors:
         !ignored_error_code(actual_error) &&
         !ignored_error_code(expected_error))
     {
-      rli->report(ERROR_LEVEL, 0,
-                      "\
-Query caused different errors on master and slave.     \
-Error on master: '%s' (%d), Error on slave: '%s' (%d). \
-Default database: '%s'. Query: '%s'",
-                      ER_SAFE(expected_error),
-                      expected_error,
-                      actual_error ? thd->stmt_da->message() : "no error",
-                      actual_error,
-                      print_slave_db_safe(db), query_arg);
+      rli->report(ERROR_LEVEL, ER_DIFFERENT_ERRORS_ON_MASTER_AND_SLAVE,
+                  ER(ER_DIFFERENT_ERRORS_ON_MASTER_AND_SLAVE),
+                  ER_SAFE(expected_error),
+                  expected_error,
+                  actual_error ? thd->stmt_da->message() : "no error",
+                  actual_error,
+                  print_slave_db_safe(db), query_arg);
       thd->is_slave_error= 1;
     }
     /*

=== modified file 'sql/share/errmsg-utf8.txt'
--- sql/share/errmsg-utf8.txt	2010-10-04 12:42:16 +0000
+++ sql/share/errmsg-utf8.txt	2010-10-06 14:38:46 +0000
@@ -6379,3 +6379,7 @@ ER_SET_PASSWORD_AUTH_PLUGIN
 
 ER_GRANT_PLUGIN_USER_EXISTS
         eng "GRANT with IDENTIFIED WITH is illegal because the user %-.*s already exists"
+
+
+ER_DIFFERENT_ERRORS_ON_MASTER_AND_SLAVE
+        eng "Statement caused different errors on master and slave. Error on master: '%s' (%d), Error on slave: '%s' (%d). Default database: '%s'. Statement: '%s'"

This is what we have:

macbook-pro:mysql-test openxs$ ./mtr bug57287
Logging: ./mtr  bug57287
101006 18:04:08 [Warning] Setting lower_case_table_names=2 because file system for /var/folders/dX/dXCzvuSlHX4Op1g-o1jIWk+++TI/-Tmp-/EQQxTs0BqB/ is case insensitive
101006 18:04:08 [Note] Plugin 'FEDERATED' is disabled.
101006 18:04:08 [Note] Plugin 'ndbcluster' is disabled.
MySQL Version 5.1.52
Checking supported features...
 - using ndbcluster when necessary, mysqld supports it
 - SSL connections supported
 - binaries are debug compiled
Collecting tests...
vardir: /Users/openxs/dbs/5.1/mysql-test/var
Checking leftover processes...
Removing old var directory...
Creating var directory '/Users/openxs/dbs/5.1/mysql-test/var'...
Installing system database...
Using server port 58610

==============================================================================

TEST                                      RESULT   TIME (ms)
------------------------------------------------------------

worker[1] Using MTR_BUILD_THREAD 300, with reserved ports 13000..13009
main.bug57287                            [ fail ]
        Test ended at 2010-10-06 18:04:23

CURRENT_TEST: main.bug57287
--- /Users/openxs/dbs/5.1/mysql-test/r/bug57287.result	2010-10-06 18:04:01.000000000 +0300
+++ /Users/openxs/dbs/5.1/mysql-test/r/bug57287.reject	2010-10-06 18:04:23.000000000 +0300
@@ -0,0 +1,50 @@
+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;
+CREATE TABLE t1 (a INT PRIMARY KEY);
+INSERT INTO t1 VALUES (1);
+DELETE FROM t1;
+INSERT INTO t1 VALUES (2), (1);
+ERROR 23000: Duplicate entry '1' for key 'PRIMARY'
+SHOW SLAVE STATUS;
+Slave_IO_State	Waiting for master to send event
+Master_Host	127.0.0.1
+Master_User	root
+Master_Port	13000
+Connect_Retry	1
+Master_Log_File	master-bin.000001
+Read_Master_Log_Pos	385
+Relay_Log_File	slave-relay-bin.000003
+Relay_Log_Pos	438
+Relay_Master_Log_File	master-bin.000001
+Slave_IO_Running	Yes
+Slave_SQL_Running	No
+Replicate_Do_DB	
+Replicate_Ignore_DB	
+Replicate_Do_Table	
+Replicate_Ignore_Table	
+Replicate_Wild_Do_Table	
+Replicate_Wild_Ignore_Table	
+Last_Errno	0
+Last_Error	Query caused different errors on master and slave.     Error on master: 'Duplicate entry '%-.192s' for key %d' (1062), Error on slave: 'no error' (0). Default database: 'test'. Query: 'INSERT INTO t1 VALUES (2), (1)'
+Skip_Counter	0
+Exec_Master_Log_Pos	292
+Relay_Log_Space	686
+Until_Condition	None
+Until_Log_File	
+Until_Log_Pos	0
+Master_SSL_Allowed	No
+Master_SSL_CA_File	
+Master_SSL_CA_Path	
+Master_SSL_Cert	
+Master_SSL_Cipher	
+Master_SSL_Key	
+Seconds_Behind_Master	NULL
+Master_SSL_Verify_Server_Cert	No
+Last_IO_Errno	0
+Last_IO_Error	
+Last_SQL_Errno	0
+Last_SQL_Error	Query caused different errors on master and slave.     Error on master: 'Duplicate entry '%-.192s' for key %d' (1062), Error on slave: 'no error' (0). Default database: 'test'. Query: 'INSERT INTO t1 VALUES (2), (1)'
...