Menu ▾ ▴

ERROR: A result [org.postgresql.jdbc4.Jdbc4ResultSet@53077f45] of an operation on database server2 in cluster mycluster did not match the expected result [null]

Help
Sung Dong Ho
2015-05-21
2015-06-08

  • Sung Dong Ho

    Sung Dong Ho - 2015-05-21

    Hello ferraro.

    environment :

    • Centos 6.5
    • tomcat 7.0
    • postgresql 9.4 ( it has test/test2 database )
    • all ha-jdbc 3.0.3 jars are in tomcat/lib

    My problem is as follows:

    1) my ha-jdbc is CUD (insert, update, delete) works fine

    • my database (test, test2 ) is always in sync.

    2) but "select query" request occurs when there is a problem.

    • catalina.out log

    ERROR: A result [org.postgresql.jdbc4.Jdbc4ResultSet@53077f45] of an operation on database server2 in cluster mycluster did not match the expected result [null]

    3) and than inactive "test2" datasource ( server2 )

    • It can not be a synchronization between the test and test2.

    ha-jdbc-mycluster.xml

    <ha-jdbc xmlns="urn:ha-jdbc:cluster:3.0">
    <sync id="full">
    <property name="fetchSize">1000</property>
    <property name="maxBatchSize">100</property>
    </sync>
    <state id="simple"/>
    <cluster default-sync="full" dialect="postgresql" meta-data-cache="eager">
    <database id="server1" location="org.postgresql.ds.PGSimpleDataSource" weight="1">
    <user>pgsql</user>
    <password>pgsql123</password>
    <property name="serverName">wrangler</property>
    <property name="portNumber">5432</property>
    <property name="databaseName">test</property>
    </database>
    <database id="server2" location="org.postgresql.ds.PGSimpleDataSource" weight="1">
    <user>pgsql</user>
    <password>pgsql123</password>
    <property name="serverName">wrangler</property>
    <property name="portNumber">5432</property>
    <property name="databaseName">test2</property>
    </database>
    </cluster>
    </ha-jdbc>

    tomcat7/conf/context.xml

    <Resource name="jdbc/hajdbcDS" type="net.sf.hajdbc.sql.DataSource" factory="org.apache.naming.factory.BeanFactory" closeMethod="stop" cluster="mycluster" config="file:///NCIA/tomcat7/conf/ha-jdbc-mycluster.xml" user="pgsql" password="pgsql123"/>
    </Context>

    select.jsp ( this file occur problem, request this file and then inactive test2 )
    <%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%>
    <%@ page language="java" import="java.sql."%>
    <%@ page language="java" import="javax.sql.    "%>
    <%@ page language="java" import="javax.naming.*"%>
    <!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
    <html>
    <head>
    <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
    Insert title here
    </head>
    <body>

    <%
    InitialContext ctx = new InitialContext();
    if ( ctx == null ) {
       throw new Exception("Uh oh -- no context!");
    }

    DataSource ds = (DataSource) ctx.lookup( "java:/comp/env/jdbc/hajdbcDS" );
    if ( ds == null ) {
       throw new Exception("Data source not found!");
    }

    Connection conn = null;
    Statement stmt = null;
    ResultSet rs = null;

    try 
    {

        //conn = ds.getConnection("pgsql", "pgsql123");
        conn = ds.getConnection();
        stmt=conn.createStatement();
        out.println("DataSource lookup OK!");
        out.println("<br />");
        **stmt.executeQuery("select names from users");**
        rs = stmt.getResultSet();

        while(rs.next())
        { 
            out.print("<br />"); 
            out.println("select value : " + rs.getString("names")); 
        } 
    } 
    catch (Exception e)
    {
        e.printStackTrace();
        out.println("Connection Pool Error : " + e.getMessage());
        out.println("<br />");
    } 
    finally 
    {

            try { if ( rs != null ) rs.close(); } catch (Exception e) {} 
            try { if ( stmt != null ) stmt.close(); } catch (Exception e) {} 
            try { if ( conn != null ) conn.close(); conn = null; } catch(Exception e) {}
            try { if ( ctx != null) ctx.close(); } catch (Exception e) {} 
    }

%>

</body>

    </html>

    insert.jsp ( this file works fine )

    <%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%> <%@ page language="java" import="java.sql.*"%> <%@ page language="java" import="javax.sql.*"%> <%@ page language="java" import="javax.naming.*"%> <%@ page language="java" import="java.util.*"%> <html> <head> <meta http-equiv="Content-Type" content="text/html; charset=UTF-8"> Insert title here </head> <body> 
    <%

    InitialContext ctx = new InitialContext();
    if ( ctx == null ) {
       throw new Exception("Uh oh -- no context!");
    }

    DataSource ds = (DataSource) ctx.lookup( "java:/comp/env/jdbc/hajdbcDS" );

    if ( ds == null ) {
       throw new Exception("Data source not found!");
    }
    Connection conn = null;
        PreparedStatement pstmt = null;
    ResultSet rs = null;

    try 
    {

        StringBuffer buffer = new StringBuffer();
        Random random = new Random();
        String chars[] = "a,b,c,d,e,f,g,h,i,j,k,l,m,n,o,p,q,r,s,t,u,v,w,x,y,z".split(",");

        for (int i=0 ; i<4 ; i++)
        {
            buffer.append(chars[random.nextInt(chars.length)]);
        }

        //conn = ds.getConnection("pgsql","pgsql123");
        conn = ds.getConnection();
                conn.setAutoCommit(false);
        String query = "insert into users (names) values (?)";
        pstmt=conn.prepareStatement(query);
        pstmt.setString(1,buffer.toString());
        pstmt.executeUpdate(); 
        conn.commit();
        out.println("insert! "+buffer);
    } 
    catch (Exception e)
    {
        e.printStackTrace();
        out.println("Connection Pool Error : " + e.getMessage());
        out.println("<br />");
    } 
    finally 
    {

            try { if ( rs != null ) rs.close(); } catch (Exception e) {} 
            try { if ( pstmt != null ) pstmt.close(); } catch (Exception e) {} 
            try { if ( conn != null ) conn.close(); conn = null; } catch(Exception e) {}
            try { if ( ctx != null) ctx.close(); } catch (Exception e) {} 
    }

%>

</body>

    </html>

    Help me. please !

     

    Last edit: Sung Dong Ho 2015-05-21

  • Paul Ferraro

    Paul Ferraro - 2015-06-05

    Question...
    Why don't you use the ResultSet returned by PreparedStatement.executeQuery(...) rather than use an additional call to PreparedStatement.getResultSet()? PreparedStatement.executeQuery(...) will only executes against a single database, whereas PreparedStatement.getResultSet() returns a ResultSet proxy to both databases - however, this method will return a null for one database, since that database never executed the query.

    In summary, you can either do:
    stmt.execute(...); // This will send the query to both databases
    ResultSet rs = stmt.getResultSet(); // This will return a ResultSet proxy to both result sets.

    or

    ResultSet rs = stmt.executeQuery(...); // This will return a ResultSet against a single database.

    I recommend the latter.

     

  • Sung Dong Ho

    Sung Dong Ho - 2015-06-08

    Thank you. Paul Ferraro

    All Works fine!

    I'm sorry to my rudimentary mistakes.

     

Log in to post a comment.