[Pgpool-general] db mismatch problem

[Tatsuo Ishii]

If my understanding is correct, you expect pgpool does failover and
detaches one of the node when number of UPDATE/DELETE tuples do not
agree among DB nodes?
--
Tatsuo Ishii
SRA OSS, Inc. Japan

> Hi Tatsuo !
> 
> Thanks your help!
> 
> This is my pgpool debug log download url
> http://longtime.idv.tw/tmp/pgpool_update.log 
> 
> 
> and I find another db mismatch problem with guid
> this is pgpool debug log download url
> http://longtime.idv.tw/tmp/pgpool_guid.log
> 
> example:
> GUID Key Test Case
> 
> create table tmp_data (
>    sColA         VARCHAR2(18),    -- Paimary key
>    LastModified  DATE
> );
> 
> -- newguid() Function : Randomly generated string of 18 yards
> insert into tmp_data select newguid(), sysdate from dual;
> 
> -- DB Node in each generated Primary Key values are not identical.
> Node 1:
>       scola        |    lastmodified
> --------------------+---------------------
> VO5MGHT91749819968 | 2009-05-22 16:17:30
> VO5MGHW06150886475 | 2009-05-22 16:17:32
> 
> 
> Node 2:
>       scola        |    lastmodified
> --------------------+---------------------
> VO5MGHT92778429003 | 2009-05-22 16:17:30
> VO5MGHW04817271824 | 2009-05-22 16:17:32
> 
> -- Use Primary Key field value for the index, When Update data, An error occurred will be find.
> update tmp_data set lastmodified = sysdate where sColA = 'VO5MGHT92778429003';
> update tmp_data set lastmodified = sysdate where sColA = 'VO5MGHT91749819968';
> 
> ERROR:  pgpool detected difference of the number of inserted, updated or deleted tuples. Possible last query
> was: "update tmp_data set
> message contents do not agree with length in message type "E"
> 
> this case the slave db will not be stop query
> and pcp_node_info result still are
> 192.168.1.7 9999 2 1073741823.500000
> 192.168.1.8 9999 2 1073741823.500000
> 
> It's not my expected value
> I hope slave db can be stop query when data mismatch
> 
> 
> 
> > Can you show us pgpool log file contents for these errors?
> > --
> > Tatsuo Ishii
> > SRA OSS, Inc. Japan
> 
> > ---------------------------------------------------------------------------------------------------------------------
> > I have two case
> > 
> > case 1:
> > When I both update schema & data, and master and slave DB mismatch
> > 
> > pgpool alert this messages:
> > --
> > PGDMS2=# update tmp_data set lastmodified = '2009-05-15 00:00:00' where lastmodified <= '2009-05-14';
> > ERROR:  kind mismatch among backends. Possible last query was: "update tmp_data set lastmodified = '2009-05-
> > 15 00:00:00' where lastmod
> > message contents do not agree with length in message type "E"
> > server closed the connection unexpectedly
> >       This probably means the server terminated abnormally
> >       before or while processing the request.
> > The connection to the server was lost. Attempting reset: Succeeded.
> > --
> > 
> > this case the slave db will be stop query
> > and pcp_node_info result are
> > 192.168.1.7 9999 2 1073741823.500000
> > 192.168.1.8 9999 3 1073741823.500000
> > 
> > This is my expected value
> > 
> > 
> > Case 2:
> > When I only update data, and master and slave DB mismatch
> > 
> > pgpool alert this messages:
> > --
> > PGDMS2=# update profile set lastmodified = '2009-05-15 00:00:00'
> > PGDMS2-#  where lastmodified between '2008-12-15 00:00:00' and '2009-05-10 23:59:59';
> > ERROR:  pgpool detected difference of the number of inserted, updated or deleted tuples. Possible last query
> > was: "update profile set
> > message contents do not agree with length in message type "E"
> > --
> > 
> > this case the slave db will not be stop query
> > and pcp_node_info result still are
> > 192.168.1.7 9999 2 1073741823.500000
> > 192.168.1.8 9999 2 1073741823.500000
> > 
> > It's not my expected value
> > I hope slave db can be stop query when data mismatch
> > 
> > 
> > this is my pgpool.conf about replication mode + load_balance mode
> > replication_mode = true
> > load_balance_mode = true
> > replication_stop_on_mismatch = true
> > replicate_select = true
> > reset_query_list = 'ABORT; RESET ALL; SET SESSION AUTHORIZATION DEFAULT'
> > 
> > 
> > many thanks your help!