Bug #69175 query caused different errors on master and slave
Submitted: 8 May 2013 11:08 Modified: 14 Sep 2013 9:23
Reporter: wei liu Email Updates:
Status: No Feedback Impact on me:
None 
Category:MySQL Server: Replication Severity:S2 (Serious)
Version:5.5.16 OS:Any
Assigned to: CPU Architecture:Any
Tags: query caused different errors on master and slave, readonly, truncate table

[8 May 2013 11:08] wei liu
Description:
The master db has been changed to read_only when 'truncate table event' was running by 'Event Scheduler', then the slave got the above error.

My Questions are:
1.
Why the master still wrote the event 'truncate table event' into bin log when got an error 'running with the read_only'?
2.
If it's a MySQL bug, how can we ignore this error automatically like 'slave-skip-errors=1062'?
Even I know we can skip N errors manually.

Details:
1. On Slave Node:
Slave node got an error:
Last_SQL_Error: Query caused different errors on master and slave.     
Error on master: message (format)='The MySQL server is running with the %s option so it cannot execute this statement' error code=1290 ; 
Error on slave: actual message='no error', error code=0. Default database: 'tra_occ'. Query: 'truncate table event'.

Slave error log:
130506 15:45:55 [Note] Semi-sync replication switched ON with slave (server_id:
1) at (master-bin.000250, 14400)
130506 15:45:55 [ERROR] Slave SQL: Query caused different errors on master and s
lave.     Error on master: message (format)='The MySQL server is running with th
e %s option so it cannot execute this statement' error code=1290 ; Error on slav
e: actual message='no error', error code=0. Default database: 'tra_occ'. Query:
'truncate table event', Error_code: 0
130506 15:45:55 [ERROR] Error running query, slave SQL thread aborted. Fix the p
roblem, and restart the slave SQL thread with "SLAVE START". We stopped at log '
master-bin.000029' position 4736

2. On Master Node:
Master changed to read_only time: 2013-05-06 15:45:11

Error log on master node:
130506 15:45:21 [ERROR] Event Scheduler: [IT271350_5@%][tra_occ.job_trun_tab] The MySQL server is running with the --read-only option so it cannot execute this statement

Master bin log:
# at 4639
#130506 15:45:11 server id 1  end_log_pos 4736 	Query	thread_id=398530	exec_time=0	error_code=0
SET TIMESTAMP=1367826311/*!*/;
SET @@session.sql_auto_is_null=1/*!*/;
SET @@session.auto_increment_increment=2, @@session.auto_increment_offset=1/*!*/;
/*!\C utf8 *//*!*/;
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=83/*!*/;
truncate table ev_combined
/*!*/;
# at 4736
#130506 15:45:21 server id 1  end_log_pos 4827 	Query	thread_id=398530	exec_time=0	error_code=1290
SET TIMESTAMP=1367826321/*!*/;
truncate table event
/*!*/;

How to repeat:
1. Arrange a DB schedule job to truncate several large tables on master node.
2. Change the Master DB to read_only when the job is running.

Suggested fix:
Don't write the DDL into bin log and replicate it to slave if it get an error in master node.
[28 May 2013 7:17] wei liu
Is there any work around to make the replication process to skip this error?
[14 Aug 2013 9:07] wei liu
Is there any progress so far?
[14 Aug 2013 9:23] Umesh Shastry
Hello Wei Liu,

Thank you for the report.
I can not repeat described behavior with dummy data. Additionally version 5.5.16 is old and many bugs were fixed since. Please could you check in latest GA release and see if you can repeat? Please let us know if it is repeatable on 5.5.33(GA).

Thanks,
Umesh
[15 Sep 2013 1:00] Bugs System
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".