[pgpool-general: 4574] Re: kind mismatch among backends with COMMIT;

[Muhammad Usama]

Does your application use the JDBC drivers for connection?
The JDBC driver has an autocommit option and If the autocommit is set to
false, The driver executes the query in an explicit transaction and sends
"BEGIN" and "COMMIT" by itself.

Regards
Muhammad Usama


On Wed, Mar 23, 2016 at 5:00 AM, Dan Foster <Dan.Foster at bristol.ac.uk>
wrote:

> Hi,
>
> I have a 2 node PgPool 3.4.3 set-up with streaming replication (i.e.
> replication_mode = off) & load balancing enabled. Some clients get
> disconnected and I see the following sort of behaviour in the logs (as
> an example):
>
> Mar 22 23:10:17 db-pg-p3 pgpool: 2016-03-22 23:10:17: pid 68008: LOG:
> DB node id: 0 backend pid: 68027 statement: BEGIN
> Mar 22 23:10:17 db-pg-p3 pgpool: 2016-03-22 23:10:17: pid 68008: LOG:
> DB node id: 1 backend pid: 27748 statement: BEGIN
> Mar 22 23:10:17 db-pg-p3 pgpool: 2016-03-22 23:10:17: pid 68008: LOG:
> DB node id: 0 backend pid: 68027 statement: CREATE OR REPLACE FUNCTION
> create_label() RETURNS TRIGGER AS ' BEGIN UPDATE survey_question SET
> label = ''b'' || NEW.id WHERE (NEW.label = '''' OR NEW.label IS NULL)
> AND id = NEW.id; RETURN NEW; END ' LANGUAGE plpgsql;
> Mar 22 23:10:17 db-pg-p3 pgpool: DROP TRIGGER IF EXISTS create_label
> ON survey_question;
> Mar 22 23:10:17 db-pg-p3 pgpool: CREATE TRIGGER create_label AFTER
> INSERT ON survey_question FOR EACH ROW EXECUTE PROCEDURE
> create_label();
> Mar 22 23:10:17 db-pg-p3 pgpool: CREATE OR REPLACE FUNCTION
> update_label() RETURNS TRIGGER AS ' BEGIN UPDATE survey_question SET
> label = ''b'' || NEW.id WHERE (OLD.label = '''' OR OLD.label IS NULL)
> AND id = OLD.id; RETURN NEW; END ' LANGUAGE plpgsql;
> Mar 22 23:10:17 db-pg-p3 pgpool: DROP TRIGGER IF EXISTS update_label
> ON survey_question;
> Mar 22 23:10:17 db-pg-p3 pgpool: CREATE TRIGGER update_label AFTER
> UPDATE ON survey_question FOR EACH ROW  EXECUTE PROCEDURE
> update_label();
> Mar 22 23:10:17 db-pg-p3 pgpool: -- THIS IS A HACK TO REMOVE
> content_id constraint for positive integers only
> Mar 22 23:10:17 db-pg-p3 pgpool: -- since we need negative numbers for
> FGS -1 for core -2 for default (removable)
> Mar 22 23:10:17 db-pg-p3 pgpool: ALTER TABLE
> permissions_objectpermission DROP constraint IF EXISTS
> Mar 22 23:10:17 db-pg-p3 pgpool:
> permissions_objectpermission_content_id_check;
> Mar 22 23:10:17 db-pg-p3 pgpool: COMMIT;
> Mar 22 23:10:17 db-pg-p3 pgpool: 2016-03-22 23:10:17: pid 68008: LOG:
> DB node id: 1 backend pid: 27748 statement: COMMIT
> Mar 22 23:10:17 db-pg-p3 pgpool: 2016-03-22 23:10:17: pid 68008: LOG:
> DB node id: 0 backend pid: 68027 statement: COMMIT
> Mar 22 23:10:17 db-pg-p3 pgpool: 2016-03-22 23:10:17: pid 68008: LOG:
> pool_send_and_wait: Error or notice message from
> backend: : DB node id: 0 backend pid: 68027 statement: "COMMIT"
> message: "there is no transaction in progress"
> Mar 22 23:10:17 db-pg-p3 pgpool: 2016-03-22 23:10:17: pid 68008:
> WARNING:  packet kind of backend 1 ['C'] does not match
>  with master/majority nodes packet kind ['N']
> Mar 22 23:10:17 db-pg-p3 pgpool: 2016-03-22 23:10:17: pid 68008:
> FATAL:  failed to read kind from backend
> Mar 22 23:10:17 db-pg-p3 pgpool: 2016-03-22 23:10:17: pid 68008:
> DETAIL:  kind mismatch among backends. Possible last qu
> ery was: "COMMIT" kind details are: 0[N: there is no transaction in
> progress] 1[C]
> Mar 22 23:10:17 db-pg-p3 pgpool: 2016-03-22 23:10:17: pid 68008: HINT:
>  check data consistency among db nodes
> Mar 22 23:10:17 db-pg-p3 pgpool: 2016-03-22 23:10:17: pid 67974: LOG:
> child process with pid: 68008 exits with status 5
>
> I can see that it's the return of the COMMITs returning different
> values that's causing problems. I can also see why this would be,
> because the query from the application contains a COMMIT of it's own
> on the end and this only gets run on one backend.
>
> What I do not understand is where the BEGIN and COMMIT statements are
> coming from and why they are sent to both backend nodes. Or how to
> approach fixing this.
>
> Could anyone offer some guidance?
>
> Thanks,
> Dan.
> --
> Dan Foster | Senior Storage Systems Administrator
> Advanced Computing Research Centre, University of Bristol
> _______________________________________________
> pgpool-general mailing list
> pgpool-general at pgpool.net
> http://www.pgpool.net/mailman/listinfo/pgpool-general
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://www.sraoss.jp/pipermail/pgpool-general/attachments/20160324/fe90e6e0/attachment.html>