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.
