[Pgpool-general] read_kind_from_backend: 1 th kind C does not match with master or majority connection kind D

Tue Oct 27 15:36:07 UTC 2009

select code,country from i10_hts where party_serial_id=$1 order by
party_serial_id,id\\\" kind details are: 0[D] 1[C]

0[D] means SELECT on 0 th backend keeps on sending query resuls, while
1[C] means 1 th backend finishes sending query results. My guess is, 

select code,country from i10_hts where party_serial_id='983022' order by party_serial_id,id

returns different number of rows from 0 and 1 PostgreSQL. How many
rows do you get if you issue the SELECT above via psql?
--
Tatsuo Ishii
SRA OSS, Inc. Japan

> Hello,
>  I am having troubles with a pgpool server in development and can't seem to find the cause after a few months of idle poking. Here is what I see;
> 
> We have two postgres 8.3 v. 8.3.7-0ubuntu8.04.1 servers (db01 and db02) running on VMs. They are configured nearly identically minus the different hostnames, IPs, etc. These are attached to one pgpool-II v.2.2.5 server (lb01). Pgpool is configured to replicate between them. Since we use nextval we have the system configured with load balancing off. All of this works fine under light load conditions, a few connections per second perhaps. Under high load however pgpool fairly quickly starts a failover due to  a kind mismatch error. Any attempts to recover the failed DB server fail with a backend error until he load goes back down. The master DB continues to carry traffic so there is no outage, just a loss of redundancy. I have seen this behavior about  a dozen times with the most current versions of pgpool at the time.
> 
> In trying to track this problem I have set up central logging so I can see the pgpool traffic which causes postgres traffic. I have included a short except of this showing the kind mismatch and the traffic just before. All clocks on hosts are in sync so the time stamps can be trusted but order can a bit off.
> 
> Below I have my configuration file listed along with the loglines showing the error and DB traffic.
> Am I doing something wrong? How do read the "read_kind_from_backend:" error line? I looks like there may be useful information in there but I don't know what to make of it like which is "C" and "D"?
> 
> Thanks for your time and help,
> --Tim
> 
> 
> +++++++++++++++++++BEGIN LOGLINES++++++++++++++++++++++
> 2009-10-26T15:30:19.082-04:00 db02 user.notice postgres[7583]: [33319-1] LOG:  execute <unnamed>: select code,country from i10_hts where party_serial_id=$1 order by party_serial_id,id
> 2009-10-26T15:30:19.082-04:00 db02 user.notice postgres[7583]: [33319-2] DETAIL:  parameters: $1 = \\\'983021\\\'
> 2009-10-26T15:30:19.084-04:00 db01 user.notice postgres[9003]: [33340-1] LOG:  execute <unnamed>: select code,country from i10_hts where party_serial_id=$1 order by party_serial_id,id
> 2009-10-26T15:30:19.084-04:00 db01 user.notice postgres[9003]: [33340-2] DETAIL:  parameters: $1 = \\\'983022\\\'
> 2009-10-26T15:30:19.084-04:00 db01 user.notice postgres[9217]: [8379-1] LOG:  execute <unnamed>: select surety_id,bond_holder,bond_activity,bond_type from i where id=$1
> 2009-10-26T15:30:19.084-04:00 db01 user.notice postgres[9217]: [8379-2] DETAIL:  parameters: $1 = \\\'472\\\'
> 2009-10-26T15:30:19.084-04:00 db02 user.notice postgres[7583]: [33320-1] LOG:  execute <unnamed>: select code,country from i10_hts where party_serial_id=$1 order by party_serial_id,id
> 2009-10-26T15:30:19.084-04:00 db02 user.notice postgres[7583]: [33320-2] DETAIL:  parameters: $1 = \\\'983022\\\'
> 2009-10-26T15:30:19.084-04:00 lb01 user.notice pgpool: 2009-10-26 15:30:19 ERROR: pid 9830: read_kind_from_backend: 1 th kind C does not match with master or majority connection kind D
> 2009-10-26T15:30:19.084-04:00 db02 user.notice postgres[7775]: [8369-1] LOG:  execute <unnamed>: select surety_id,bond_holder,bond_activity,bond_type from i where id=$1
> 2009-10-26T15:30:19.084-04:00 db02 user.notice postgres[7775]: [8369-2] DETAIL:  parameters: $1 = \\\'472\\\'
> 2009-10-26T15:30:19.084-04:00 lb01 user.notice pgpool: 2009-10-26 15:30:19 ERROR: pid 9830: kind mismatch among backends. Possible last query was: \\\"select code,country from i10_hts where party_serial_id=$1 order by party_serial_id,id\\\" kind details are: 0[D] 1[C]
> 2009-10-26T15:30:19.085-04:00 lb01 user.notice pgpool: 2009-10-26 15:30:19 LOG:   pid 9830: notice_backend_error: 1 fail over request from pid 9830
> 2009-10-26T15:30:19.153-04:00 lb01 user.notice pgpool: 2009-10-26 15:30:19 LOG:   pid 7366: starting degeneration. shutdown host db02(5432)
> 2009-10-26T15:30:19.153-04:00 lb01 user.notice pgpool: 2009-10-26 15:30:19 LOG:   pid 7366: failover_handler: set new master node: 0
> 2009-10-26T15:30:19.194-04:00 lb01 user.notice pgpool: 2009-10-26 15:30:19 LOG:   pid 7366: failover done. shutdown host db02(5432)
> +++++++++++++++++++++++++++++++++++++++++
> +++++++++++++++++++ BEGIN pgpool config++++++++++++++++++++++
> listen_addresses = '*'
> port = 5433
> pcp_port = 9898
> socket_dir = '/var/run/pgpool'
> pcp_socket_dir = '/var/run/pgpool'
> backend_socket_dir = '/var/run/pgpool'
> pcp_timeout = 10
> num_init_children = 32
> max_pool = 4
> child_life_time = 300
> connection_life_time = 60
> child_max_connections = 1000
> authentication_timeout = 60
> recovery_timeout = 120
> client_idle_limit_in_recovery = 10
> replication_mode = true
> replication_strict = true
> replication_timeout = 5000
> load_balance_mode = false
> replication_stop_on_mismatch = true
> replicate_select = true
> reset_query_list = 'ABORT; DISCARD ALL'
> print_timestamp = true
> master_slave_mode = false
> connection_cache = true
> health_check_timeout = 3
> health_check_period = 10
> health_check_user = 'postgres'
> insert_lock = true
> ignore_leading_white_space = true
> log_statement = true
> log_connections = true
> log_hostname = false
> parallel_mode = false
> enable_query_cache = false
> pgpool2_hostname = 'localhost'
> backend_hostname0 = 'db01'
> backend_port0 = 5432
> backend_weight0 = 1
> backend_data_directory0 = '/var/lib/postgresql/8.3/main'
> backend_hostname1 = 'db02'
> backend_port1 = 5432
> backend_weight1 = 1
> backend_data_directory1 = '/var/lib/postgresql/8.3/main'
> recovery_user = 'postgres'
> recovery_password = 'PASSWORD'
> recovery_1st_stage_command = 'copy-base-backup'
> recovery_2nd_stage_command = 'pgpool_recovery_pitr'
> enable_pool_hba = false
> +++++++++++++++++++++++++++++++++++++++++
> 