[pgpool-general: 4772] Re: Kind mismatch among backends

[Tatsuo Ishii]

The error means that backend 0 (mozart) returned 2 (bind complete)
while backend 1 (velvet) returned 3 (close complete) and pgpool
expected those returned messages are identical in response with
"BEGIN" messages sent from pgpool to backends.

Do you know the way to reliably reproduce the error? (or a minimum
test case is even better).

Best regards,
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese:http://www.sraoss.co.jp

> Hi,
> 
> We have two PostgreSQL instances (master/hot standby) with streaming
> replication and are trying to use pgpoolII in load balance mode. A few
> minutes after starting the application pgpool log this error message and
> the application crash:
> 
> 2016-07-06 14:17:58: pid 5224: DEBUG:  pool_read: read 16 bytes from
> backend 0
> 2016-07-06 14:17:58: pid 5224: DEBUG:  reading backend data packet kind
> 2016-07-06 14:17:58: pid 5224: DETAIL:  backend:0 of 2 kind = '2'
> 2016-07-06 14:17:58: pid 5224: DEBUG:  pool_read: read 21 bytes from
> backend 1
> 2016-07-06 14:17:58: pid 5224: DEBUG:  reading backend data packet kind
> 2016-07-06 14:17:58: pid 5224: DETAIL:  backend:1 of 2 kind = '3'
> 2016-07-06 14:17:58: pid 5224: DEBUG:  read_kind_from_backend
> max_count:1.000000 num_executed_nodes:2
> 2016-07-06 14:17:58: pid 5224: WARNING:  packet kind of backend 1 ['3']
> does not match with master/majority nodes packet kind ['2']
> 2016-07-06 14:17:58: pid 5224: FATAL:  failed to read kind from backend
> 2016-07-06 14:17:58: pid 5224: DETAIL:  kind mismatch among backends.
> Possible last query was: "BEGIN" kind details are: 0[2] 1[3]
> 2016-07-06 14:17:58: pid 5224: HINT:  check data consistency among db nodes
> 
> We do not know how to solve this problem, since the integrity of standby
> appears to be ok. Already we rebuilt the standby to make sure that there
> was no inconsistency. We have also noticed that the error occurs with
> either synchronously or asynchronous replication.
> 
> We did not find clues about what to do. Can anyone help me solve this?
> 
> Thanks in advance,
> 
> Alvaro
> 
> *More details:*
> postgres=# show pool_nodes;
>  node_id | hostname | port | status | lb_weight |  role   | select_cnt
> ---------+----------+------+--------+-----------+---------+------------
>  0       | mozart   | 5432 | 2      | 0.090909  | primary | 999
>  1       | velvet   | 5432 | 2      | 0.909091  | standby | 3015
> 
> *OS version:* CentOS Linux release 7.2.1511 (Core)
> *Kernel version:* 3.10.0-327.22.2.el7.x86_64
> *Pgpool version:* 3.5.3 (ekieboshi)
> *PostgreSQL version:* PostgreSQL 9.3.13 on x86_64-unknown-linux-gnu,
> compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-4), 64-bit
> 
> *pgpool.conf:*
> listen_addresses = '*'
> port = 6432
> socket_dir = '/tmp'
> pcp_listen_addresses = '*'
> pcp_port = 9898
> pcp_socket_dir = '/tmp'
> listen_backlog_multiplier = 2
> backend_hostname0 = 'mozart'
> backend_port0 = 5432
> backend_weight0 = 0.1
> backend_data_directory0 = '/u00/app/postgres/pgmza02/data'
> backend_flag0 = 'ALLOW_TO_FAILOVER'
> backend_hostname1 = 'velvet'
> backend_port1 = 5432
> backend_weight1 = 1
> backend_data_directory1 = '/u00/app/postgres/pgvlv02/data'
> backend_flag1 = 'DISALLOW_TO_FAILOVER'
> enable_pool_hba = on
> pool_passwd = 'pool_passwd'
> authentication_timeout = 60
> ssl = off
> num_init_children = 2000
> max_pool = 10
> child_life_time = 300
> child_max_connections = 0
> connection_life_time = 0
> client_idle_limit = 0
> log_destination = 'stderr'
> log_line_prefix = '%t: pid %p: '
> log_connections = off
> log_hostname = off
> log_statement = off
> log_per_node_statement = off
> log_standby_delay = 'if_over_threshold'
> syslog_facility = 'LOCAL0'
> syslog_ident = 'pgpool'
> debug_level = 0
> pid_file_name = '/var/run/pgpool/pgpool.pid'
> logdir = '/tmp'
> connection_cache = on
> reset_query_list = 'ABORT; DISCARD ALL'
> replication_mode = off
> replicate_select = off
> insert_lock = off
> lobj_lock_table = ''
> replication_stop_on_mismatch = off
> failover_if_affected_tuples_mismatch = off
> load_balance_mode = on
> ignore_leading_white_space = on
> white_function_list = ''
> black_function_list = 'currval,lastval,nextval,setval'
> database_redirect_preference_list = ''
> app_name_redirect_preference_list = ''
> allow_sql_comments = off
> master_slave_mode = on
> master_slave_sub_mode = 'stream'
> sr_check_period = 10
> sr_check_user = 'postgres'
> sr_check_password = 'abc123'
> delay_threshold = 10000000
> follow_master_command = ''
> health_check_period = 0
> health_check_timeout = 20
> health_check_user = 'nobody'
> health_check_password = ''
> health_check_max_retries = 0
> health_check_retry_delay = 1
> connect_timeout = 10000
> failover_command = ''
> failback_command = ''
> fail_over_on_backend_error = on
> search_primary_node_timeout = 10
> recovery_user = 'nobody'
> recovery_password = ''
> recovery_1st_stage_command = ''
> recovery_2nd_stage_command = ''
> recovery_timeout = 90
> client_idle_limit_in_recovery = 0
> use_watchdog = off
> trusted_servers = ''
> ping_path = '/bin'
> wd_hostname = ''
> wd_port = 9000
> wd_authkey = ''
> delegate_IP = ''
> ifconfig_path = '/sbin'
> if_up_cmd = 'ifconfig eth0:0 inet $_IP_$ netmask 255.255.255.0'
> if_down_cmd = 'ifconfig eth0:0 down'
> arping_path = '/usr/sbin'
> arping_cmd = 'arping -U $_IP_$ -w 1'
> clear_memqcache_on_escalation = on
> wd_escalation_command = ''
> wd_lifecheck_method = 'heartbeat'
> wd_interval = 10
> wd_heartbeat_port = 9694
> wd_heartbeat_keepalive = 2
> wd_heartbeat_deadtime = 30
> heartbeat_destination0 = 'host0_ip1'
> heartbeat_destination_port0 = 9694
> heartbeat_device0 = ''
> wd_life_point = 3
> wd_lifecheck_query = 'SELECT 1'
> wd_lifecheck_dbname = 'template1'
> wd_lifecheck_user = 'nobody'
> wd_lifecheck_password = ''
> relcache_expire = 0
> relcache_size = 256
> check_temp_table = on
> check_unlogged_table = on
> memory_cache_enabled = off
> memqcache_method = 'shmem'
> memqcache_memcached_host = 'localhost'
> memqcache_memcached_port = 11211
> memqcache_total_size = 67108864
> memqcache_max_num_cache = 1000000
> memqcache_expire = 0
> memqcache_auto_cache_invalidation = on
> memqcache_maxcache = 409600
> memqcache_cache_block_size = 1048576
> memqcache_oiddir = '/var/log/pgpool/oiddir'
> white_memqcache_table_list = ''
> black_memqcache_table_list = ''
> 
> *Master postgresql.conf:*
> listen_addresses = '*'
> max_connections = 2000
> max_prepared_transactions = 2000
> superuser_reserved_connections = 20
> temp_buffers = 8MB
> work_mem = 16MB
> effective_io_concurrency = 32
> wal_sync_method = open_sync
> checkpoint_timeout = 10min
> archive_mode = on
> max_wal_senders = 4
> random_page_cost = 2.0
> log_destination = 'stderr'
> logging_collector = on
> log_directory = 'pg_log'
> log_filename = 'postgresql-%a.log'
> log_truncate_on_rotation = on
> log_rotation_age = 1d
> log_rotation_size = 0
> log_line_prefix = '%t:%r:%u@%d:[%p]: '
> log_timezone = 'Brazil/East'
> search_path = '"$user",public,jt,acl,client,core,eg'
> bytea_output = 'escape'
> datestyle = 'iso, dmy'
> timezone = 'Brazil/East'
> lc_messages = 'pt_BR.iso88591'
> lc_monetary = 'pt_BR.iso88591'
> lc_numeric = 'pt_BR.iso88591'
> lc_time = 'pt_BR.iso88591'
> default_text_search_config = 'pg_catalog.portuguese'
> wal_level = hot_standby
> port = 5432
> archive_command = 'cp %p /u00/app/postgres/pgmza02/archive/%f'
> shared_buffers = 4GB
> maintenance_work_mem = 512MB
> checkpoint_segments = 20
> wal_keep_segments = 20
> effective_cache_size = 8GB
> autovacuum_max_workers = 4
> autovacuum = on
> hot_standby = off
> synchronous_commit = remote_write
> synchronous_standby_names = '*'
> 
> *Standby postgresql.conf:*
> listen_addresses = '*'
> max_connections = 2000
> max_prepared_transactions = 2000
> superuser_reserved_connections = 20
> temp_buffers = 8MB
> work_mem = 16MB
> effective_io_concurrency = 32
> wal_sync_method = open_sync
> checkpoint_timeout = 10min
> archive_mode = on
> max_wal_senders = 4
> random_page_cost = 2.0
> log_destination = 'stderr'
> logging_collector = on
> log_directory = 'pg_log'
> log_filename = 'postgresql-%a.log'
> log_truncate_on_rotation = on
> log_rotation_age = 1d
> log_rotation_size = 0
> log_line_prefix = '%t:%r:%u@%d:[%p]: '
> log_timezone = 'Brazil/East'
> search_path = '"$user",public,jt,acl,client,core,eg'
> bytea_output = 'escape'
> datestyle = 'iso, dmy'
> timezone = 'Brazil/East'
> lc_messages = 'pt_BR.iso88591'
> lc_monetary = 'pt_BR.iso88591'
> lc_numeric = 'pt_BR.iso88591'
> lc_time = 'pt_BR.iso88591'
> default_text_search_config = 'pg_catalog.portuguese'
> wal_level = hot_standby
> port = 5432
> archive_command = 'cp %p /u00/app/postgres/pgvlv02/archive/%f'
> shared_buffers = 4GB
> maintenance_work_mem = 512MB
> checkpoint_segments = 20
> wal_keep_segments = 20
> effective_cache_size = 8GB
> autovacuum_max_workers = 4
> autovacuum = on
> hot_standby = on
> synchronous_commit = remote_write
> synchronous_standby_names = '*'