Hibernate Cannot open a connection.

Follow
Avatar
Michael Good

Hello

I have just finished to install Mysql galera, on three different servers and all work fine.

My web application uses hibernate.

this is example of my hold connection url

<property name="connection.url">jdbc:mysql://10.31.245.102/shop</property>

Now I have three replication nodes

I try to set it in this way:

<property name="hibernate.connection.url">jdbc:mysql:loadbalance://10.31.245.102,10.31.245.103,10.31.245.101/shop?loadBalanceBlacklistTimeout=5000</property>

I receive this stackTrace:

SEVERE: StandardWrapper.Throwable org.hibernate.exception.GenericJDBCException: Cannot open connection

Thanks in advance for help.

 

0

Comments

9 comments

Sort by Date Votes
  • Avatar
    Alex

    Hi,

    The connection URL looks ok.

    - Check that you can connect from the application server to the DB nodes. You need to allow clients from the application server host to connect to the DB nodes.

    Can you connect from the application server host to all Galera nodes?
    Ex:

    $ mysql -uroot -p<root password> -h 10.31.245.103 -e "select 1"

    To grant all privileges for clients connecting from the application server do on one of the Galera nodes:
    $ mysql -uroot -p<root password> -e "GRANT ALL PRIVILEGES on mydb.* to 'my-user'@'app-server-ip' IDENTIFIED BY 'my-pass'"

     

    As default with our deployment script the MySQL servers/Galera nodes are configured with a max connection setting of 200.

    - Check your hibernate connection pool size and see if you perhaps are exceeding that value. If so either decrease the pool size or increase the max_connections setting for the DB nodes.

     

    Best Regards,

    -alex

    0
    Comment actions Permalink
  • Avatar
    Michael Good

    We Checked that we can connect from the application server to the DB nodes.
    All users have "grant all privilegies"
    We Checked our hibernate connection pool size.

    After the installation of Galera on three servers, we have connected hibernate to first database (10.31.245.102) using the following set:
    <hibernate-configuration>
        <session-factory>
            <!-- Connessione -->
            <property name="hibernate.connection.driver_class">com.mysql.jdbc.Driver</property>
            <property name="connection.url">jdbc:mysql://10.31.245.102/shop</property>
            ...
        </session-factory>
    </hibernate-configuration>

    We used the setting above to check the correct replication of the data on all database servers'nodes (10.31.245.103 and 10.31.245.101), and everything is working fine, the data is correctly replicated.

    Our goal is to connect hibernate to secondary databases (10.31.245.103 and 10.31.245.101) when the main database (10.31.245.102) is busy or unreachable for any reasons.

    but when we try to connect usign these settings:
    <property name="hibernate.connection.url">jdbc:mysql:loadbalance://10.31.245.102,10.31.245.103,10.31.245.101/shop?loadBalanceBlacklistTimeout=5000</property>
    It doesn't work (Cannot open connection)

    looking on google I found this configuration:

    We replaced
    <property name="hibernate.connection.driver_class">com.mysql.jdbc.Driver</property>
    with:
    <property name="hibernate.connection.driver_class">com.mysql.jdbc.ReplicationDriver</property>

    and
    <property name="hibernate.connection.url">jdbc:mysql:loadbalance://10.31.245.102,10.31.245.103,10.31.245.101/shop?loadBalanceBlacklistTimeout=5000</property>
    with:
    <property name="hibernate.connection.url">jdbc:mysql://10.31.245.102,10.31.245.103,10.31.245.101/shop</property>

    with this new settings we do not receive this error (Cannot open connection).

    Now my application starts and works fine, Galera replicates the data on all nodes, but when I stop mysql on first server (10.31.245.102) with "#/etc/init.d/mysql stop", hibernate doesn't switch on secondary databases (10.31.245.103) or (10.31.245.101)

    Is there a way to allow hibernate to switch database when it is overloaded or busy etc..?
    Is this the right way? Are we using the right settings?

    0
    Comment actions Permalink
  • Avatar
    Alex

    Hi,

    The correct setting for load balancing with Galera cluster is to use "mysql:loadbalance" with the default MySQL driver. Can you attach the full hibernate config file? 
    Using the "ReplicationDriver" and "mysql:replication" DSN is for Master-Slave replication setups. You write to the master and have read-only slaves (http://dev.mysql.com/doc/refman/5.5/en/connector-j-reference-replication-connection.html).

     

    Can you test this short Java snippet attached to rule out any issues with your DB env.
    The table used:  create table test.lb (id int auto_increment, host varchar(250), ts timestamp, primary key(id)); 

    Run it by:

    java -cp <mysql jdbc jar file>:. Lb 10.31.245.102,10.31.245.103,10.31.245.101 <root pass>

     

    Best Regards,

    -alex

    0
    Comment actions Permalink
  • Avatar
    Michael Good

    On first test, we noticed this error:
    No suitable driver found for jdbc:mysql:loadbalance://10.31.245.102,10.31.245.103,10.31.245.101/test?loadBalanceBlacklistTimeout=5000

    We are using this jar file to connect to mysql: mysql-connector-java-5.0.4.jar

    We downloaded mysql-connector-java-5.0.8-bin.jar, and it working fine.
    this is the output:
    rows=1
    rows=2
    rows=3
    rows=4
    rows=5
    etc..

    Now tomcat start correctly with these setting:
    <session-factory>
                    <!-- Connessione -->
                    <property name="hibernate.connection.driver_class">com.mysql.jdbc.Driver</property>
                    <property name="hibernate.connection.url">jdbc:mysql:loadbalance://10.31.245.102,10.31.245.103,10.31.245.101/shop?loadBalanceBlacklistTimeout=5000</property>
                    <property name="hibernate.dialect">org.hibernate.dialect.MySQL5InnoDBDialect</property>
                    <property name="hibernate.connection.username">username</property>
                    <property name="hibernate.connection.password">password</property>

                    <!-- c3P0 -->
                    <property name="hibernate.cglib.use_reflection_optimizer">true</property>
                    <property name="hibernate.connection.provider_class">org.hibernate.connection.C3P0ConnectionProvider</property>
                    <property name="hibernate.c3p0.timeout">10</property>
                    <property name="hibernate.cache.use_query_cache">false</property>
                    <property name="hibernate.query.substitutions">true 1, false 0, yes 'Y', no 'N'</property>
                    <property name="hibernate.show_sql">false</property>
                    <!-- Mapping -->
                    <mapping resource="com/webApp/mapping/Account.hbm.xml" />
                    <mapping resource="com/webApp/mapping/Axis.hbm.xml" />
            Others mapping HBM....
    </session-factory>
    </hibernate-configuration>

    Actual Problem is :
    Where I stop (/etc/init.d/mysqld stop) first database (10.31.245.102), I will expect the secondary database to work; instead the application won't work.

    What should I do to test the correctness all loadbalancing functionalities?


    thanks  in advanced for your big help.

    -Michael

    0
    Comment actions Permalink
  • Avatar
    Alex

    Hi,

    Try adding these c3p0 settings.

    hibernate.c3p0.preferredTestQuery=SELECT 1
    (or hibernate.c3p0.preferredTestQuery=/* ping */)
    hibernate.c3p0.testConnectionOnCheckout=true

     

    Each time you checkout a connection from the pool it gets validated first by the test query. If it fails then another connection is allocated and it should come from one of the other hosts that still are up. And if you can you should probably test with the latest MySQL driver version 5.1.25.

    Does that work better for you now?

     

    Best Regards,

    -alex

    0
    Comment actions Permalink
  • Avatar
    Michael Good

    We tried to use MySQL driver version 5.1.25, but our application had some problem with this version, now we use mysql-connector-java-5.0.8-bin.jar
    We developed and upgraded our application since 2005 and when to we make any changes at libraries, many problems will occurred.
    For this reason we avoid using new libraries, if not extremely necessary.

    I try both:
    1) hibernate.c3p0.preferredTestQuery=SELECT 1
    and after after with
    2)hibernate.c3p0.preferredTestQuery=/* ping */
    hibernate.c3p0.testConnectionOnCheckout=true

             <!-- Connessione -->
                    <property name="hibernate.connection.driver_class">com.mysql.jdbc.Driver</property>
                    <property name="hibernate.connection.url">jdbc:mysql:loadbalance://10.31.245.102,10.31.245.103,10.31.245.101/shop?loadBalanceBlacklistTimeout=5000</property>
                    <property name="hibernate.dialect">org.hibernate.dialect.MySQL5InnoDBDialect</property>
                    <property name="hibernate.connection.username">username</property>
                    <property name="hibernate.connection.password">password</property>
                    <!-- C3p0 -->
                    <property name="hibernate.cglib.use_reflection_optimizer">true</property>
                    <property name="hibernate.connection.provider_class">org.hibernate.connection.C3P0ConnectionProvider</property>
                    <property name="hibernate.c3p0.preferredTestQuery">/* ping */</property>
                    <property name="hibernate.c3p0.testConnectionOnCheckout">true</property>
            <property name="hibernate.c3p0.timeout">10</property>
                    <property name="hibernate.cache.use_query_cache">false</property>
                    <property name="hibernate.query.substitutions">true 1, false 0, yes 'Y', no 'N'</property>
                    <property name="hibernate.show_sql">false</property>
                    <!-- Mapping -->

    When I stop mysql server on 10.31.245.102 tomcat dont switch mysql databses;

    I removed all setting in c3p0, and use only these settings:

                    <property name="hibernate.connection.driver_class">com.mysql.jdbc.Driver</property>
                    <property name="hibernate.connection.url">jdbc:mysql:loadbalance://10.31.245.102,10.31.245.103,10.31.245.101/shop?loadBalanceBlacklistTimeout=5000</property>
                    <property name="hibernate.dialect">org.hibernate.dialect.MySQL5InnoDBDialect</property>
                    <property name="hibernate.connection.username">username</property>
                    <property name="hibernate.connection.password">password</property>

    without c3p0 setting, I stop the server 10.31.245.102, tomcat switch database correctly and webApplication working on database 10.31.245.103.
    We use this version of c3p0: c3p0-0.9.0.jar

    But when clustercontrol restart Mysql on 102, or When I change jsp page The application crash.

    this is the stackTrace:
    ** BEGIN NESTED EXCEPTION **

    java.io.EOFException
    MESSAGE: Can not read response from server. Expected to read 4 bytes, read 0 bytes before connection was unexpectedly lost.

    STACKTRACE:

    java.io.EOFException: Can not read response from server. Expected to read 4 bytes, read 0 bytes before connection was unexpectedly lost.
        at com.mysql.jdbc.MysqlIO.readFully(MysqlIO.java:1997)
        at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:2411)
        at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2916)
        at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1631)
        at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1723)
        at com.mysql.jdbc.Connection.execSQL(Connection.java:3283)
        at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1332)
        at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:1467)
        at sun.reflect.GeneratedMethodAccessor33.invoke(Unknown Source)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
        at java.lang.reflect.Method.invoke(Method.java:606)
        at com.mysql.jdbc.LoadBalancingConnectionProxy$ConnectionErrorFiringInvocationHandler.invoke(LoadBalancingConnectionProxy.java:168)
        at com.sun.proxy.$Proxy6.executeQuery(Unknown Source)
        at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:186)
        at org.hibernate.loader.Loader.getResultSet(Loader.java:1778)
        at org.hibernate.loader.Loader.doQuery(Loader.java:662)
        at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:224)
        at org.hibernate.loader.Loader.doList(Loader.java:2211)
        at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2095)
        at org.hibernate.loader.Loader.list(Loader.java:2090)
        at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:388)
        at org.hibernate.hql.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:338)
        at org.hibernate.engine.query.HQLQueryPlan.performList(HQLQueryPlan.java:172)
        at org.hibernate.impl.SessionImpl.list(SessionImpl.java:1121)
        at org.hibernate.impl.QueryImpl.list(QueryImpl.java:79)
        at org.hibernate.impl.AbstractQueryImpl.uniqueResult(AbstractQueryImpl.java:804)
        at com.gambling.iform.admin.Login.execute(Login.java:62)
        at org.apache.jsp.commons.login2_jsp._jspService(login2_jsp.java:106)
        at org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:70)
        at javax.servlet.http.HttpServlet.service(HttpServlet.java:728)
        at org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:432)
        at org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:390)
        at org.apache.jasper.servlet.JspServlet.service(JspServlet.java:334)
        at javax.servlet.http.HttpServlet.service(HttpServlet.java:728)
        at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:305)
        at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210)
        at net.sf.packtag.filter.GzipFilter.doFilter(GzipFilter.java:43)
        at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:243)
        at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210)
        at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:222)
        at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:123)
        at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:472)
        at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:171)
        at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:99)
        at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:118)
        at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:408)
        at org.apache.coyote.ajp.AjpProcessor.process(AjpProcessor.java:200)
        at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:589)
        at org.apache.tomcat.util.net.JIoEndpoint$SocketProcessor.run(JIoEndpoint.java:310)
        at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145)
        at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615)
        at java.lang.Thread.run(Thread.java:724)
    ** END NESTED EXCEPTION **

    thanks  in advanced

    -Michael

    0
    Comment actions Permalink
  • Avatar
    Alex

    Hi,

    Can you test and run the Java LB program with 5.0.8 vs 5.1.25 while restarting one of your DB nodes. It could be a driver bug and/or version that is causing this exception. 5.0.8 is quite old and might not work properly for this.

     

    Best Regards,

    -alex

     

    0
    Comment actions Permalink
  • Avatar
    Hodan Egal

    Hi,

    I need some help on my first hibernate project to built, i have set up everything well but im getting this error.

     

    Oct 13, 2014 10:41:42 PM org.hibernate.annotations.common.reflection.java.JavaReflectionManager <clinit>
    INFO: HCANN000001: Hibernate Commons Annotations {4.0.5.Final}
    Oct 13, 2014 10:41:42 PM org.hibernate.Version logVersion
    INFO: HHH000412: Hibernate Core {4.3.6.Final}
    Oct 13, 2014 10:41:42 PM org.hibernate.cfg.Environment <clinit>
    INFO: HHH000206: hibernate.properties not found
    Oct 13, 2014 10:41:42 PM org.hibernate.cfg.Environment buildBytecodeProvider
    INFO: HHH000021: Bytecode provider name : javassist
    Oct 13, 2014 10:41:42 PM org.hibernate.cfg.Configuration configure
    INFO: HHH000043: Configuring from resource: /hibernate.cfg.xml
    Oct 13, 2014 10:41:42 PM org.hibernate.cfg.Configuration getConfigurationInputStream
    INFO: HHH000040: Configuration resource: /hibernate.cfg.xml
    Oct 13, 2014 10:41:43 PM org.hibernate.cfg.Configuration doConfigure
    INFO: HHH000041: Configured SessionFactory: null
    Oct 13, 2014 10:41:43 PM org.hibernate.engine.jdbc.connections.internal.DriverManagerConnectionProviderImpl configure
    WARN: HHH000402: Using Hibernate built-in connection pool (not for production use!)
    Oct 13, 2014 10:41:43 PM org.hibernate.engine.jdbc.connections.internal.DriverManagerConnectionProviderImpl buildCreator
    INFO: HHH000401: using driver [com.mysql.jdbc.Driver] at URL [jdbc:mysql://localhost:3306/hibernatetutorial]
    Oct 13, 2014 10:41:43 PM org.hibernate.engine.jdbc.connections.internal.DriverManagerConnectionProviderImpl buildCreator
    INFO: HHH000046: Connection properties: {user=root, password=****}
    Oct 13, 2014 10:41:43 PM org.hibernate.engine.jdbc.connections.internal.DriverManagerConnectionProviderImpl buildCreator
    INFO: HHH000006: Autocommit mode: false
    Oct 13, 2014 10:41:43 PM org.hibernate.engine.jdbc.connections.internal.DriverManagerConnectionProviderImpl configure
    INFO: HHH000115: Hibernate connection pool size: 20 (min=1)
    Exception in thread "main" org.hibernate.exception.JDBCConnectionException: Error calling Driver#connect
    at org.hibernate.engine.jdbc.connections.internal.BasicConnectionCreator$1$1.convert(BasicConnectionCreator.java:122)
    at org.hibernate.engine.jdbc.connections.internal.BasicConnectionCreator.convertSqlException(BasicConnectionCreator.java:140)
    at org.hibernate.engine.jdbc.connections.internal.DriverConnectionCreator.makeConnection(DriverConnectionCreator.java:58)
    at org.hibernate.engine.jdbc.connections.internal.BasicConnectionCreator.createConnection(BasicConnectionCreator.java:75)
    at org.hibernate.engine.jdbc.connections.internal.DriverManagerConnectionProviderImpl.configure(DriverManagerConnectionProviderImpl.java:106)
    at org.hibernate.boot.registry.internal.StandardServiceRegistryImpl.configureService(StandardServiceRegistryImpl.java:111)
    at org.hibernate.service.internal.AbstractServiceRegistryImpl.initializeService(AbstractServiceRegistryImpl.java:234)
    at org.hibernate.service.internal.AbstractServiceRegistryImpl.getService(AbstractServiceRegistryImpl.java:206)
    at org.hibernate.engine.jdbc.internal.JdbcServicesImpl.buildJdbcConnectionAccess(JdbcServicesImpl.java:260)
    at org.hibernate.engine.jdbc.internal.JdbcServicesImpl.configure(JdbcServicesImpl.java:94)
    at org.hibernate.boot.registry.internal.StandardServiceRegistryImpl.configureService(StandardServiceRegistryImpl.java:111)
    at org.hibernate.service.internal.AbstractServiceRegistryImpl.initializeService(AbstractServiceRegistryImpl.java:234)
    at org.hibernate.service.internal.AbstractServiceRegistryImpl.getService(AbstractServiceRegistryImpl.java:206)
    at org.hibernate.cfg.Configuration.buildTypeRegistrations(Configuration.java:1885)
    at org.hibernate.cfg.Configuration.buildSessionFactory(Configuration.java:1843)
    at org.hibernate.cfg.Configuration.buildSessionFactory(Configuration.java:1928)
    at com.hibernateseries.Main.main(Main.java:20)
    Caused by: java.sql.SQLException: Access denied for user 'root'@'localhost' (using password: YES)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1073)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3609)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3541)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:943)
    at com.mysql.jdbc.MysqlIO.secureAuth411(MysqlIO.java:4113)
    at com.mysql.jdbc.MysqlIO.doHandshake(MysqlIO.java:1308)
    at com.mysql.jdbc.ConnectionImpl.coreConnect(ConnectionImpl.java:2336)
    at com.mysql.jdbc.ConnectionImpl.connectOneTryOnly(ConnectionImpl.java:2369)
    at com.mysql.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:2153)
    at com.mysql.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:792)
    at com.mysql.jdbc.JDBC4Connection.<init>(JDBC4Connection.java:47)
    at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
    at sun.reflect.NativeConstructorAccessorImpl.newInstance(Unknown Source)
    at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown Source)
    at java.lang.reflect.Constructor.newInstance(Unknown Source)
    at com.mysql.jdbc.Util.handleNewInstance(Util.java:411)
    at com.mysql.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:381)
    at com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:305)
    at org.hibernate.engine.jdbc.connections.internal.DriverConnectionCreator.makeConnection(DriverConnectionCreator.java:55)
    ... 14 more

     

     

    0
    Comment actions Permalink
  • Avatar
    Ashraf Sharif

    Hi Hodan,

    It's stated there the following error:

    "Caused by: java.sql.SQLException: Access denied for user 'root'@'localhost' (using password: YES)"

    Please check the MySQL login credentials and ensure the user (root@localhost) can log in with specified password.

     

    Regards,

    Ashraf

    0
    Comment actions Permalink

Please sign in to leave a comment.

Didn't find what you were looking for?

New post
Powered by Zendesk