[pgpool-general: 1271] Re: database detach and I don't understand why

[Flemming Harms]

the different is that node 1 the message in jbm_msg still exist, but
on node 0 it's deleted. the jbm_msg_ref on both servers are empty.

but I don't understand how that's possible when the jms process
communicate through the pgpool? and if I read the postgresql logs
correct for both nodes all SQL statement are executed in the same
order until it detach.

/Flemming

2012/12/31 Tatsuo Ishii <ishii at postgresql.org>:
>> Hello,
>>
>> We have been using pgpool2 for sometime now and been happy with it,
>> but lately it suddenly start to detach the database on one of the
>> nodes on 2 node cluster. I have been investigated this for a while and
>> I can't understand why pgpool2 thinks is should detach.
>>
>> We are using pgpool version 3.0.4-1 with postgresql version
>> 8.4.8-0squeeze2 our application server is JBossAS 5 including (jboss
>> messaging 1.4.8-SP2). The application server only communicate through
>> pgpool so we are sure that no other processes are working around
>> pgpool
>>
>> We are pretty sure the databases are not different from the beginning
>> and it's new records that are created. We post 3 messages on 2
>> different jms queues and 3 clients that consumes the messages. When
>> jboss messaging component want to remove the messages because it
>> delivered to all the clients, pgpool detach the database because it
>> get a different response from node 1 when it want to delete the
>> message.... which I don't understand how that's possible
>>
>> I really will appreciate some help to understand what is going on and
>> why it detach the database
>>
>> Thanks!
>>
>> br,
>> Flemming
>>
>> the log_line_prefix = '%t %p %v %x' for the postgresql
>>
>> You can find the log files by following the links below for pgpool2
>> debug log and postgresql log from both servers
>>
>> jboss-detach-190.sql(postgresql log) >
>> http://ubuntuone.com/6Ku56EoHI7FAXAyC1p1prQ
>> jboss-detach-191.sql(postgresql log) >
>> http://ubuntuone.com/0ICbQCiowqDgKe7LglMxSV
>> pgpool.conf > http://ubuntuone.com/3BhoRnr3ACifYhi2QqSbat
>> pgppool-191-log  > http://ubuntuone.com/1g8ExrevBeRUj92XS5WjyZ7
>
> From the log, the cause of the detach was this:
>
> Dec 27 19:54:18 SWDEV035 pgpool: 2012-12-27 19:54:18 ERROR: pid 18412: pgpool detected difference of the number of inserted, updated or deleted tuples. Possible last query was: "DELETE FROM JBM_MSG WHERE MESSAGE_ID = $1 AND NOT EXISTS (SELECT JBM_MSG_REF.MESSAGE_ID FROM JBM_MSG_REF WHERE JBM_MSG_REF.MESSAGE_ID = $2)"
> Dec 27 19:54:18 SWDEV035 pgpool: 2012-12-27 19:54:18 LOG:   pid 18412: CommandComplete: Number of affected tuples are: 0 1
>
> As you said, it seems your node 0 DB did not have a raw satisfied the
> WHERE clause, while your node 1 had one row which satisfied it at that
> time. I suggest to check tables used by JBoss right after failover to
> see there's difference in table's data.
> --
> Tatsuo Ishii
> SRA OSS, Inc. Japan
> English: http://www.sraoss.co.jp/index_en.php
> Japanese: http://www.sraoss.co.jp