[pgpool-general: 2377] Re: Error when use pgpool2 with JDBC

[Tatsuo Ishii]

I don't know where "SET extra_float_digits = 3" comes from. Pgpool-II
never issues the query. Mabe Spring does something?

Best regards,
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese: http://www.sraoss.co.jp

> Hi,
> 
> I want use pgpool2 to create a PostgreSQL cluster in master slave
> configuration, streaming replication and load balancing. That
> correctly works when I connect to pgpool with psql, but when I try to
> connect with Java and Jdbc, I get exception.  My program perfectly
> works if it connects directly to master. I searched on Google, I saw
> many results for approaching errors (SET extra_float_digits = 3), but
> none could be help me.
> 
> I use pgpool version 3.3.2 (I tried also 3.1.1 before), PostgreSQL
> 9.1.11, Ubuntu 12.04, Java 7 and PostgreSQL JDBC 9.2-1002-jdbc4 (but
> the error is the same with versions 9.1-901-1.jdbc4 and
> 9.3-1100-jdbc41).
> 
> Here my exception :
> 
>     org.springframework.jdbc.CannotGetJdbcConnectionException: Could not
>     get JDBC Connection; nested exception is
>     org.apache.commons.dbcp.SQLNestedException: Cannot create
>     PoolableConnectionFactory (ERROR: kind mismatch among
>     backends. Possible last query was: "SET extra_float_digits = 3" kind
>     details are: 0[N: execute <unnamed>: SET extra_float_digits = 3] 1[C]
>   Indice : check data consistency among db nodes)
>     at
>     org.springframework.jdbc.datasource.DataSourceUtils.getConnection(DataSourceUtils.java:80)
>     at
>     org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:628)
>     at
>     org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:907)
>     at
>     org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:931)
>     at
>     org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate.update(NamedParameterJdbcTemplate.java:311)
>     at
>     fr.telecomdesign.testcluster.dao.jdbc.ProbeDaoJdbc.insert(ProbeDaoJdbc.java:80)
> 
> In pgpool log, I see then following lines :
> 
>     Jan  2 16:35:45 pool pgpool[16915]: Execute: portal name <>
>     Jan 2 16:35:45 pool pgpool[16915]: Execute: query string = <SET
>     extra_float_digits = 3>
>     Jan 2 16:35:45 pool pgpool[16915]: DB node id: 0 backend pid: 6885
>     statement: Execute: SET extra_float_digits = 3
>     Jan 2 16:35:45 pool pgpool[16915]: wait_for_query_response: waiting
>     for backend 0 completing the query
>     Jan 2 16:35:45 pool pgpool[16915]: pool_send_and_wait: Error or notice
>     message from backend: : DB node id: 0 backend pid: 6885 statement: SET
>     extra_float_digits = 3 message: execute <unnamed>: SET
>     extra_float_digits = 3
>     Jan 2 16:35:45 pool pgpool[16915]: DB node id: 1 backend pid: 16178
>     statement: Execute: SET extra_float_digits = 3
>     Jan 2 16:35:45 pool pgpool[16915]: wait_for_query_response: waiting
>     for backend 1 completing the query
>     Jan 2 16:35:45 pool pgpool[16915]: read_kind_from_one_backend: read
>     kind from 0 th backend N
>     Jan 2 16:35:45 pool pgpool[16915]: read_kind_from_backend: kind: N
>     from 0 th backend
>     Jan 2 16:35:45 pool pgpool[16915]: read_kind_from_backend: read kind
>     from 0 th backend N NUM_BACKENDS: 2
>     Jan 2 16:35:45 pool pgpool[16915]: read_kind_from_backend: kind: C
>     from 1 th backend
>     Jan 2 16:35:45 pool pgpool[16915]: read_kind_from_backend: read kind
>     from 1 th backend C NUM_BACKENDS: 2
>     Jan 2 16:35:45 pool pgpool[16915]: read_kind_from_backend: 1 th kind C
>     does not match with master or majority connection kind N
>     Jan 2 16:35:45 pool pgpool[16915]: kind mismatch among
>     backends. Possible last query was: "SET extra_float_digits = 3" kind
>     details are: 0[N: execute <unnamed>: SET extra_float_digits = 3] 1[C]
> 
> On master and slave logs, I see the query SET extra_float_digits =
> 3. What is this query ? I never sent it to pool. With psql on master,
> I manage to execute this query. If I am connected to pool with psql, I
> get the following error :
>     ERROR: kind mismatch among backends. Possible last query was: "SET
>     extra_float_digits = 3;" kind details are: 0[N: statement: SET
>     extra_float_digits = 3;] 1[C]
>     ASTUCE : check data consistency among db nodes
>     ERROR: kind mismatch among backends. Possible last query was: "SET
>     extra_float_digits = 3;" kind details are: 0[N: statement: SET
>     extra_float_digits = 3;] 1[C]
>     ASTUCE : check data consistency among db nodes
> 
> Why pgpool2 send this query to master and slave ? How do for that
> pgpool do not send it to backends ?
> 
> Thanks for your help.
> 
> _______________________________________________
> pgpool-general mailing list
> pgpool-general at pgpool.net
> http://www.pgpool.net/mailman/listinfo/pgpool-general