Uploaded image for project: 'Percona XtraDB Cluster'
  1. Percona XtraDB Cluster
  2. PXC-2546

Binary logging not possible. An error occurred during flush stage of the commit. 'binlog_error_action' is set to 'ABORT_SERVER'

Details

    • Bug
    • Status: Done
    • High
    • Resolution: Duplicate
    • None
    • None
    • None
    • None

    Description

      We have a three node PXC cluster; two fully separate database servers and one separate arbitrator server.

      We only have a very small amount of databases in this cluster, around 70 MB for all of them.

      Today, we noticed that the MySQL service shut down on the primary node and it didn't come back up. We looked in the error log and this is what we saw. I tried searching the Internet for any related issues, and didn't find any. Any ideas would be greatly appreciated!

      2019-05-14T00:00:01.817818+02:00 4810 [Note] Bad handshake
      2019-05-14T04:41:03.394687+02:00 4873 [Note] Bad handshake
      2019-05-14T16:28:42.618511+02:00 5040 [ERROR] WSREP: Failed to initialize io-cache
      2019-05-14T16:28:42.618663+02:00 5040 [ERROR] WSREP: Failed to create write-set from binlog data_len: 0, 3
      2019-05-14T16:28:42.757012+02:00 5040 [ERROR] /usr/sbin/mysqld: Binary logging not possible. Message: An error occurred during flush stage of the commit. 'binlog_error_action' is set to 'ABORT_SERVER'
      . Hence aborting the server.
      13:28:42 UTC - mysqld got signal 6 ;
      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.
      Attempting to collect some information that could help diagnose the problem.
      As this is a crash and something is definitely wrong, the information
      collection process might fail.
      Please help us make Percona XtraDB Cluster better by reporting any
      bugs at https://jira.percona.com/projects/PXC/issues
      
      key_buffer_size=8388608
      read_buffer_size=131072
      max_used_connections=32
      max_threads=152
      thread_count=32
      connection_count=23
      It is possible that mysqld could use up to
      key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 68410 K  bytes of memory
      Hope that's ok; if not, decrease some variables in the equation.
      
      Thread pointer: 0x7fd700027280
      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 = 7fd705b97958 thread_stack 0x40000
      /usr/sbin/mysqld(my_print_stacktrace+0x35)[0xf7f895]
      /usr/sbin/mysqld(handle_fatal_signal+0x4c4)[0x7c8514]
      /lib64/libpthread.so.0(+0xf7e0)[0x7fd75fcd97e0]
      /lib64/libc.so.6(gsignal+0x35)[0x7fd75df12495]
      /lib64/libc.so.6(abort+0x175)[0x7fd75df13c75]
      /usr/sbin/mysqld[0xf0eeea]
      /usr/sbin/mysqld(_ZN13MYSQL_BIN_LOG33handle_binlog_flush_or_sync_errorEP3THDb+0x1bd)[0xf1bedd]
      /usr/sbin/mysqld(_ZN13MYSQL_BIN_LOG14ordered_commitEP3THDbb+0x5cd)[0xf2062d]
      /usr/sbin/mysqld(_ZN13MYSQL_BIN_LOG6commitEP3THDb+0xa82)[0xf213c2]
      /usr/sbin/mysqld(_Z15ha_commit_transP3THDbb+0x1f0)[0x847470]
      /usr/sbin/mysqld(_Z12trans_commitP3THD+0x56)[0xdfaa46]
      /usr/sbin/mysqld[0xdc99f8]
      /usr/sbin/mysqld(_ZN7sys_var6updateEP3THDP7set_var+0x139)[0xc9a719]
      /usr/sbin/mysqld(_ZN7set_var6updateEP3THD+0x17)[0xc9b487]
      /usr/sbin/mysqld(_Z17sql_set_variablesP3THDP4ListI12set_var_baseEb+0xa9)[0xc9aac9]
      /usr/sbin/mysqld(_Z21mysql_execute_commandP3THDb+0x1510)[0xd2ced0]
      /usr/sbin/mysqld(_Z11mysql_parseP3THDP12Parser_stateb+0x4d5)[0xd33245]
      /usr/sbin/mysqld[0xd33390]
      /usr/sbin/mysqld(_Z16dispatch_commandP3THDPK8COM_DATA19enum_server_command+0x16f4)[0xd35254]
      /usr/sbin/mysqld(_Z10do_commandP3THD+0x36b)[0xd3676b]
      /usr/sbin/mysqld(handle_connection+0x394)[0xe1aab4]
      /usr/sbin/mysqld(pfs_spawn_thread+0x174)[0xf98cb4]
      /lib64/libpthread.so.0(+0x7aa1)[0x7fd75fcd1aa1]
      /lib64/libc.so.6(clone+0x6d)[0x7fd75dfc8bdd]
      
      Trying to get some variables.
      Some pointers may be invalid and cause the dump to abort.
      Query (7fd70009c920): is an invalid pointer
      Connection ID (thread ID): 5040
      Status: NOT_KILLED
      
      You may download the Percona XtraDB Cluster operations manual by visiting
      http://www.percona.com/software/percona-xtradb-cluster/. You may find information
      in the manual which will help you identify the cause of the crash.
      

      This is what our wsrep.cnf looks like:

      [xtrabackup]
      open-files-limit=2000000
      target_dir=/var/backup/mysql/
      parallel=4
      compress
      compress-threads=4
      encrypt-threads=4
      check-privileges
      galera-info
      
      [client]
      ssl-ca=/etc/mysql/certificates/client-cert.pem
      ssl-cert=/etc/mysql/certificates/client-cert.pem
      ssl-key=/etc/mysql/certificates/client-key.pem
      protocol=tcp
      port=3306
      
      [sst]
      ssl-ca=/etc/mysql/certificates/server-cert.pem
      ssl-cert=/etc/mysql/certificates/server-cert.pem
      ssl-key=/etc/mysql/certificates/server-key.pem
      encrypt=4
      
      [mysqld]
      ### Cluster Settings ###
      # Path to Galera library.
      wsrep_provider=/usr/lib64/galera3/libgalera_smm.so
      
      # Enabling notification command when cluster changes roles.
      wsrep_notify_cmd=/etc/mysql/wsrep_notify.sh
      
      # Encrypting Replication/IST Traffic.
      wsrep_provider_options="socket.ssl=yes;socket.ssl_ca=/etc/mysql/certificates/server-cert.pem;socket.ssl_cert=/etc/mysql/certificates/server-cert.pem;socket.ssl_key=/etc/mysql/certificates/server-key.pem;socket.ssl_cipher=AES128-SHA;base_dir=/var/lib/mysql"
      
      # Cluster name.
      wsrep_cluster_name=crdlprdpdb-cat1
      
      #
      # Cluster connection URL contains IPs of nodes.
      # If no IP is found, this implies that a new cluster needs to be created.
      # In order to do that you need to bootstrap this node.
      #
      # For more information, see:
      # https://www.percona.com/doc/percona-xtradb-cluster/LATEST/bootstrap.html
      #
      wsrep_cluster_address=gcomm://x.x.x.x:4567,x.x.x.x:4567,x.x.x.x:4567
      
      # If wsrep_node_name is not specified, then system hostname will be used.
      wsrep_node_name=crdlprdpdb11
      
      # Node IP address.
      #wsrep_node_address=x.x.x.x
      
      # SST method. Do not attempt to edit this, as Percona is only reliable with xtrabackup-v2.
      # rsync also doesn't provide a secure channel.
      wsrep_sst_method=xtrabackup-v2
      
      # Authentication for SST method.
      wsrep_sst_auth="sstuser:*"
      
      # pxc_strict_mode allowed values: DISABLED,PERMISSIVE,ENFORCING,MASTER
      pxc_strict_mode=ENFORCING
      
      # In order for Galera to work correctly binlog format should be ROW.
      binlog_format=ROW
      log-bin=crdlprdpdb11-bin
      log-bin-index=crdlprdpdb11-bin.index
      master_verify_checksum=ON
      binlog_checksum=CRC32
      
      # MyISAM storage engine has only experimental support.
      default_storage_engine=InnoDB
      
      # This changes how InnoDB auto-increment locks are managed and is a requirement for Galera.
      innodb_autoinc_lock_mode=2
      
      # Slave thread to use.
      wsrep_slave_threads=8
      wsrep_log_conflicts
      
      ### Plugins ###
      early-plugin-load=keyring_file.so
      plugin-load-add=audit_log.so
      plugin-load-add=auth_pam.so
      plugin-load-add=auth_pam_compat.so
      plugin-load-add=connection_control.so
      plugin-load-add=validate_password.so
      
      ### Server Certificates ###
      ssl-ca=/etc/mysql/certificates/server-cert.pem
      ssl-cert=/etc/mysql/certificates/server-cert.pem
      ssl-key=/etc/mysql/certificates/server-key.pem
      ssl-cipher=ECDHE-RSA-AES256-GCM-SHA384:ECDHE-RSA-AES128-GCM-SHA256
      tls_version=TLSv1.2
      
      ### Misc. Security Settings ###
      user=mysql
      port=3306
      
      validate-password=FORCE_PLUS_PERMANENT
      validate_password_policy=STRONG
      validate_password_check_user_name=ON
      validate_password_dictionary_file=/etc/mysql/PasswordDictionary.txt
      validate_password_length=8
      validate_password_number_count=1
      validate_password_mixed_case_count=1
      validate_password_special_char_count=1
      
      connection-control=FORCE_PLUS_PERMANENT
      connection-control-failed-login-attempts=FORCE_PLUS_PERMANENT
      connection_control_failed_connections_threshold=3
      connection_control_min_connection_delay=5000
      connection_control_max_connection_delay=2147483647
      
      keyring_file_data=/etc/mysql/mysql-keyring/keyring
      encrypt-tmp-files=ON
      
      block_encryption_mode=aes-256-cbc
      default_password_lifetime=90
      default_authentication_plugin=sha256_password
      interactive_timeout=900
      wait_timeout=900
      skip-symbolic-links
      skip-show-database
      skip-name-resolve
      local_infile=0
      require-secure-transport=ON
      
      ### Setting Account Resource Limits ###
      max_user_connections=10
      
      ### Error and General Logging ###
      explicit_defaults_for_timestamp=1
      log_timestamps=SYSTEM
      long_query_time=2
      slow_query_log=1
      slow_query_log_file=/var/log/mysql/mysql-log-slow-queries.log
      
      log-error=/var/log/mysql/mysql-log-error.log
      
      # Only enable these for troubleshooting purposes.
      #general_log=1
      #general_log_file=/var/log/mysql/mysql-log-general.log
      
      ### Audit Logging ###
      audit_log_policy=ALL
      audit_log_format=JSON
      audit_log_file=/var/log/mysql/mysql-log-audit.log
      audit_log_rotate_on_size=64M
      audit_log_rotations=2
      

      We are running version:

      Server version: 5.7.24-26-57-log Percona XtraDB Cluster (GPL), Release rel26, Revision 65db531, WSREP version 31.33, wsrep_31.33
      

      Attachments

        Issue Links

          Activity

            People

              Unassigned Unassigned
              tsvenbla Tobias S
              Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Smart Checklist