New Option to Stop the Server If Binlogging Fails in MySQL 5.6

In this post I will try to explain the new MySQL binlog_error_action server option. This new option is available from MySQL 5.6.22 and later.

As part of MySQL replication all data changes that happen on the master server are recorded into a binary log so that they can be sent to slave and replayed there. If an error occurs that prevents mysqld from writing to the binary log (disk full, readonly file system, etc.) then the logs are simply disabled and operations continue on the master. This error mainly occurs during rotation of the binary log or while opening a binary log file.

This problem creates a serious potential for data loss within a replication group. When a master hits this failure in production, all downstream replication clients stop receiving replication events. The master will not store binlog events for committed transactions to its binary log and consequently there will be no new events in the binary log to send to replication clients. If that master then fails, then all the transactions the master received while binlogging was turned off are lost forever. This can lead to out of sync slaves and improper backups.

Error message when file system becomes readonly:
binlogging_impossible_error1As part of the bug fix for Bug#51014 the binlog_error_action server option was introduced. Using this option a user can choose to either ignore the error (still the default so as to avoid behavior changes in GA releases) or to abort the server. The IGNORE_ERROR option value refers to the default behavior (as described above) where binlogging will simply be disabled and the master will continue with its normal operations.

However, the ABORT_SERVER option value will cause the server to exit when binlogging operations fail. At the time of the resulting server exit a fatal error is pushed to clients and the server will shut down. The error details being:

Specifying the option:
This option can be specified as a startup option (either on the command-line or in a config file) or dynamically in the running server using the SET command:
mysql> SET GLOBAL binlog_error_action=ABORT_SERVER;

Demonstration of the new option in the case of a read-only file system:
Step 1: SET GLOBAL binlog_error_action= ABORT_SERVER;
Step 2: Make your file system readonly
Step 3: flush logs

If an error occurs that prevents mysqld from writing to the binary log the existing behaviour is: binary logging is disabled and the server continues with its normal operations. Now with the new binlog_error_action server option the user can choose to either ignore the error (IGNORE_ERROR) or to abort the server (ABORT_SERVER) when binary logging failures occur. This optional behavior was first introduced in MySQL 5.6.20 using the binlogging_impossible_mode server option. That option name is now deprecated in MySQL 5.6.22 and the option is instead now referred to as binlog_error_action.

We look forward to your feedback on this new feature! If you have any questions or encounter any bugs, please do let us know by opening a support ticket or filing a bug. As always, THANK YOU for using MySQL!

4 thoughts on “New Option to Stop the Server If Binlogging Fails in MySQL 5.6

  1. Is the crash intentional?

    In this contrived case, I intentionally ran out of inodes:

    # df -i /5622
    Filesystem Inodes IUsed IFree IUse% Mounted on
    tmpfs 156 154 2 99% /5622

    There are two free because the server has not created the pid or socket (it is stopped)

    After service restart on failed binlog rotation, I get this crash on subsequent start:

    2014-12-22 22:18:24 26317 [ERROR] MYSQL_BIN_LOG::open_purge_index_file failed to open register file.
    2014-12-22 22:18:24 26317 [ERROR] MYSQL_BIN_LOG::open_index_file failed to sync the index file.
    141222 22:18:24 mysqld_safe Number of processes running now: 0
    141222 22:18:24 mysqld_safe mysqld restarted
    2014-12-22 22:18:24 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use –explicit_defaults_for_timestamp server option (see documentation for more details).
    2014-12-22 22:18:24 26441 [Note] Plugin ‘FEDERATED’ is disabled.
    2014-12-22 22:18:24 26441 [Note] InnoDB: Using atomics to ref count buffer pool pages
    2014-12-22 22:18:24 26441 [Note] InnoDB: The InnoDB memory heap is disabled
    2014-12-22 22:18:24 26441 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
    2014-12-22 22:18:24 26441 [Note] InnoDB: Memory barrier is not used
    2014-12-22 22:18:24 26441 [Note] InnoDB: Compressed tables use zlib 1.2.3
    2014-12-22 22:18:24 26441 [Note] InnoDB: Using Linux native AIO
    2014-12-22 22:18:24 26441 [Note] InnoDB: Using CPU crc32 instructions
    2014-12-22 22:18:24 26441 [Note] InnoDB: Initializing buffer pool, size = 64.0M
    2014-12-22 22:18:24 26441 [Note] InnoDB: Completed initialization of buffer pool
    2014-12-22 22:18:24 26441 [Note] InnoDB: Highest supported file format is Barracuda.
    2014-12-22 22:18:24 26441 [Note] InnoDB: The log sequence numbers 1625987 and 1625987 in ibdata files do not match the log sequence number 1626017 in the ib_logfiles!
    2014-12-22 22:18:24 26441 [Note] InnoDB: Database was not shutdown normally!
    2014-12-22 22:18:24 26441 [Note] InnoDB: Starting crash recovery.
    2014-12-22 22:18:24 26441 [Note] InnoDB: Reading tablespace information from the .ibd files…
    2014-12-22 22:18:24 26441 [Note] InnoDB: Restoring possible half-written data pages
    2014-12-22 22:18:24 26441 [Note] InnoDB: from the doublewrite buffer…
    2014-12-22 22:18:24 26441 [Note] InnoDB: 128 rollback segment(s) are active.
    2014-12-22 22:18:24 26441 [Note] InnoDB: Waiting for purge to start
    2014-12-22 22:18:25 26441 [Note] InnoDB: 5.6.22 started; log sequence number 1626017
    /5622bin/bin/mysqld: File ‘./bin-log.index_crash_safe’ not found (Errcode: 28 – No space left on device)
    2014-12-22 22:18:25 26441 [ERROR] MYSQL_BIN_LOG::open_crash_safe_index_file failed to open temporary index file.
    2014-12-22 22:18:25 26441 [ERROR] MYSQL_BIN_LOG::add_log_to_index failed to open the crash safe index file.
    22:18:25 UTC – mysqld got signal 11 ;
    This could be because you hit a bug. It is also possible that this binary
    or one of the libraries it was linked against is corrupt, improperly built,
    or misconfigured. This error can also be caused by malfunctioning hardware.
    We will try our best to scrape up some info that will hopefully help
    diagnose the problem, but since we have already crashed,
    something is definitely wrong and this may fail.

    It is possible that mysqld could use up to
    key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 33645 K bytes of memory
    Hope that’s ok; if not, decrease some variables in the equation.

    Thread pointer: 0x0
    Attempting backtrace. You can use the following information to find out
    where mysqld died. If you see no messages after this, something went
    terribly wrong…
    stack_bottom = 0 thread_stack 0x40000
    The manual page at contains
    information that should help you find out what is causing the crash.
    141222 22:18:25 mysqld_safe mysqld from pid file /5622/ ended

  2. When using the default option “IGNORE_ERROR”, do the slaves receive any errors when the master hits that problem?
    IMHO, un-planned server shutdown – which is the action when using the value “ABORT_SERVER” – might not be a good idea for some online systems and they might prefer to be only notified having the master still available for the application and then plan for a shutdown out of the peak-times.
    Of course in that case they will need to resync the slaves again by either using Percona tools (pt-table-checksum and pt-table-sync) or re-initializing the slaves again which is not a simple process but at least they can plan for the shutdown.

Leave a Reply