MariaDB Galera Cluster Out of sync suddenly (Only sync the schema change but data not) Innodb engine
Hi
We are running a 3 (A,B,C) nodes mariadb galera cluster on production. The applications only connect to A node with read/write account. Recently we found the B and C were of sync with A and only the schema change can be syncd to each other. (Use InnoDB engine).
From the SHOW STATUS LIKE 'wsrep%' for each node , both 3 nodes has the same wsrep_cluster_state_uuid and wsrep_local_state_uuid , the wsrep_local_state_comment was labeled as 'synced'.
How to trouble shooting and resolve this kind of issue ?
Attach the status of wsrep of A node
A node | B node | B Node WSERP VARIABLES | |||||||||
wsrep_apply_oooe | 0.033243 | wsrep_apply_oooe | 0 | wsrep_osu_method | TOI | wsrep_osu_method | TOI | ||||
wsrep_apply_oool | 0.000002 | wsrep_apply_oool | 0 | wsrep_auto_increment_control | ON | wsrep_auto_increment_control | ON | ||||
wsrep_apply_window | 1.044903 | wsrep_apply_window | 1 | wsrep_causal_reads | OFF | wsrep_causal_reads | OFF | ||||
wsrep_causal_reads | 0 | wsrep_causal_reads | 0 | wsrep_certify_nonpk | ON | wsrep_certify_nonpk | ON | ||||
wsrep_cert_deps_distance | 57.829916 | wsrep_cert_deps_distance | 57.826126 | wsrep_cluster_address | "gcomm:192.168.10.11 | 192.168.10.12 | 192.168.10.13" | wsrep_cluster_address | "gcomm:192.168.10.11 | 192.168.10.12 | 192.168.10.13" |
wsrep_cert_index_size | 9 | wsrep_cert_index_size | 54 | wsrep_cluster_name | wise_galera | wsrep_cluster_name | wise_galera | ||||
wsrep_cert_interval | 0.052688 | wsrep_cert_interval | 0.052686 | wsrep_convert_lock_to_trx | OFF | wsrep_convert_lock_to_trx | OFF | ||||
wsrep_cluster_conf_id | 16 | wsrep_cluster_conf_id | 16 | wsrep_data_home_dir | /var/lib/mysql/ | wsrep_data_home_dir | /var/lib/mysql/ | ||||
wsrep_cluster_size | 3 | wsrep_cluster_size | 3 | wsrep_dbug_option | wsrep_dbug_option | ||||||
wsrep_cluster_state_uuid | 556505cf-becc-11e7-8799-8ae35d8b57ad | wsrep_cluster_state_uuid | 556505cf-becc-11e7-8799-8ae35d8b57ad | wsrep_debug | OFF | wsrep_debug | OFF | ||||
wsrep_cluster_status | Primary | wsrep_cluster_status | Primary | wsrep_desync | OFF | wsrep_desync | OFF | ||||
wsrep_commit_oooe | 0 | wsrep_commit_oooe | 0 | wsrep_dirty_reads | OFF | wsrep_dirty_reads | OFF | ||||
wsrep_commit_oool | 0.000001 | wsrep_commit_oool | 0 | wsrep_drupal_282555_workaround | OFF | wsrep_drupal_282555_workaround | OFF | ||||
wsrep_commit_window | 1.011446 | wsrep_commit_window | 1 | wsrep_forced_binlog_format | NONE | wsrep_forced_binlog_format | NONE | ||||
wsrep_connected | ON | wsrep_connected | ON | wsrep_gtid_domain_id | 0 | wsrep_gtid_domain_id | 0 | ||||
wsrep_desync_count | 0 | wsrep_desync_count | 0 | wsrep_gtid_mode | OFF | wsrep_gtid_mode | OFF | ||||
wsrep_evs_delayed | wsrep_evs_delayed | wsrep_load_data_splitting | ON | wsrep_load_data_splitting | ON | ||||||
wsrep_evs_evict_list | wsrep_evs_evict_list | wsrep_log_conflicts | OFF | wsrep_log_conflicts | OFF | ||||||
wsrep_evs_repl_latency | 0/0/0/0/0 | wsrep_evs_repl_latency | 0/0/0/0/0 | wsrep_max_ws_rows | 0 | wsrep_max_ws_rows | 0 | ||||
wsrep_evs_state | OPERATIONAL | wsrep_evs_state | OPERATIONAL | wsrep_max_ws_size | 2147483647 | wsrep_max_ws_size | 2147483647 | ||||
wsrep_flow_control_paused | 0.000266 | wsrep_flow_control_paused | 0.000264 | wsrep_mysql_replication_bundle | 0 | wsrep_mysql_replication_bundle | 0 | ||||
wsrep_flow_control_paused_ns | 7836436956029 | wsrep_flow_control_paused_ns | 7836339878098 | wsrep_node_address | 192.168.10.13 | wsrep_node_address | 192.168.10.12 | ||||
wsrep_flow_control_recv | 52306 | wsrep_flow_control_recv | 52337 | wsrep_node_incoming_address | AUTO | wsrep_node_incoming_address | AUTO | ||||
wsrep_flow_control_sent | 17 | wsrep_flow_control_sent | 46986 | wsrep_node_name | prclsmariadb03 | wsrep_node_name | prclsmariadb02 | ||||
wsrep_gcomm_uuid | 810736cf-c1be-11e7-b8e3-82ec0e916937 | wsrep_gcomm_uuid | a6f2a4a7-bee0-11e7-a8a7-2befee40d3a4 | wsrep_notify_cmd | wsrep_notify_cmd | ||||||
wsrep_incoming_addresses | "192.168.10.11:3306 | 192.168.10.12:3306 | 192.168.10.13:3306" | wsrep_incoming_addresses | "192.168.10.11:3306 | 192.168.10.12:3306 | 192.168.10.13:3306" | wsrep_on | ON | wsrep_on | ON |
wsrep_last_committed | 50683398 | wsrep_last_committed | 50683429 | wsrep_patch_version | wsrep_25.20 | wsrep_patch_version | wsrep_25.20 | ||||
wsrep_local_bf_aborts | 59 | wsrep_local_bf_aborts | 0 | wsrep_provider | /usr/lib/galera/libgalera_smm.so | wsrep_provider | /usr/lib/galera/libgalera_smm.so | ||||
wsrep_local_cached_downto | 50675404 | wsrep_local_cached_downto | 50675404 | ||||||||
wsrep_provider_options | base_dir = /var/lib/mysql/; base_host = 192.168.10.13; base_port = 4567; cert.log_conflicts = no; debug = no; evs.auto_evict = 0; evs.causal_keepalive_period = PT1S; evs.debug_log_mask = 0x1; evs.delay_margin = PT1S; evs.delayed_keep_period = PT30S; evs.inactive_check_period = PT0.5S; evs.inactive_timeout = PT15S; evs.info_log_mask = 0; evs.install_timeout = PT7.5S; evs.join_retrans_period = PT1S;... | wsrep_provider_options | base_dir = /var/lib/mysql/; base_host = 192.168.10.12; base_port = 4567; cert.log_conflicts = no; debug = no; evs.auto_evict = 0; evs.causal_keepalive_period = PT1S; evs.debug_log_mask = 0x1; evs.delay_margin = PT1S; evs.delayed_keep_period = PT30S; evs.... | ||||||||
wsrep_local_cert_failures | 58 | wsrep_local_cert_failures | 0 | wsrep_recover | OFF | wsrep_recover | OFF | ||||
wsrep_local_commits | 50662978 | wsrep_local_commits | 1 | wsrep_replicate_myisam | OFF | wsrep_replicate_myisam | OFF | ||||
wsrep_local_index | 1 | wsrep_local_index | 2 | wsrep_restart_slave | OFF | wsrep_restart_slave | OFF | ||||
wsrep_local_recv_queue | 0 | wsrep_local_recv_queue | 0 | wsrep_retry_autocommit | 1 | wsrep_retry_autocommit | 1 | ||||
wsrep_local_recv_queue_avg | 0.008634 | wsrep_local_recv_queue_avg | 0.113087 | wsrep_slave_fk_checks | ON | wsrep_slave_fk_checks | ON | ||||
wsrep_local_recv_queue_max | 30 | wsrep_local_recv_queue_max | 190 | wsrep_slave_uk_checks | OFF | wsrep_slave_uk_checks | OFF | ||||
wsrep_local_recv_queue_min | 0 | wsrep_local_recv_queue_min | 0 | wsrep_slave_threads | 1 | wsrep_slave_threads | 1 | ||||
wsrep_local_replays | 16 | wsrep_local_replays | 0 | wsrep_sst_auth | ** | wsrep_sst_auth | ** | ||||
wsrep_local_send_queue | 0 | wsrep_local_send_queue | 0 | wsrep_sst_donor | wsrep_sst_donor | ||||||
wsrep_local_send_queue_avg | 0.002476 | wsrep_local_send_queue_avg | 0.000003 | wsrep_sst_donor_rejects_queries | OFF | wsrep_sst_donor_rejects_queries | OFF | ||||
wsrep_local_send_queue_max | 167 | wsrep_local_send_queue_max | 2 | wsrep_sst_method | xtrabackup | wsrep_sst_method | xtrabackup | ||||
wsrep_local_send_queue_min | 0 | wsrep_local_send_queue_min | 0 | wsrep_sst_receive_address | AUTO | wsrep_sst_receive_address | AUTO | ||||
wsrep_local_state | 4 | wsrep_local_state | 4 | wsrep_start_position | 00000000-0000-0000-0000-000000000000:-1 | wsrep_start_position | 00000000-0000-0000-0000-000000000000:-1 | ||||
wsrep_local_state_comment | Synced | wsrep_local_state_comment | Synced | wsrep_sync_wait | 0 | wsrep_sync_wait | 0 | ||||
wsrep_local_state_uuid | 556505cf-becc-11e7-8799-8ae35d8b57ad | wsrep_local_state_uuid | 556505cf-becc-11e7-8799-8ae35d8b57ad | ||||||||
wsrep_protocol_version | 7 | wsrep_protocol_version | 7 | ||||||||
wsrep_provider_name | Galera | wsrep_provider_name | Galera | ||||||||
wsrep_provider_vendor | Codership Oy <[email protected]> | wsrep_provider_vendor | Codership Oy <[email protected]> | ||||||||
wsrep_provider_version | 25.3.20(r3703) | wsrep_provider_version | 25.3.20(r3703) | ||||||||
wsrep_ready | ON | wsrep_ready | ON | ||||||||
wsrep_received | 827810 | wsrep_received | 51498818 | ||||||||
wsrep_received_bytes | 3492240018 | wsrep_received_bytes | 866578987496 | ||||||||
wsrep_repl_data_bytes | 801586481553 | wsrep_repl_data_bytes | 1399 | ||||||||
wsrep_repl_keys | 725154316 | wsrep_repl_keys | 7 | ||||||||
wsrep_repl_keys_bytes | 6969312016 | wsrep_repl_keys_bytes | 148 | ||||||||
wsrep_repl_other_bytes | 0 | wsrep_repl_other_bytes | 0 | ||||||||
wsrep_replicated | 50667876 | wsrep_replicated | 4 | ||||||||
wsrep_replicated_bytes | 860983409659 | wsrep_replicated_bytes | 1803 | ||||||||
wsrep_thread_count | 2 | wsrep_thread_count | 2 |
Comments
Comments loading...
Content reproduced on this site is the property of its respective owners,
and this content is not reviewed in advance by MariaDB. The views, information and opinions
expressed by this content do not necessarily represent those of MariaDB or any other party.