[Pgpool-general] db mismatch problem

[Longtime]

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!