1 2 Previous Next 19 Replies Latest reply on Apr 18, 2013 4:43 AM by Andrej Golovnin

    Reconnect to MySQL after its restart

    Alexey S. Newbie

      I am using JPA Hibernate in Timer EJB.

      After the MySQL is restarted, JBOSS should be restarted also to renew the transaction.

      the properties I am currently using in persistence.xml

       

      are



      <properties>



      <property name="hibernate.dialect" value="org.hibernate.dialect.MySQL5Dialect"/>



      <property name="hibernate.transaction.manager_lookup_class" value="org.hibernate.transaction.JBossTransactionManagerLookup"/>



      <property name="hibernate.connection.release_mode" value="on_close"/>


      </properties>

      May I somehow say to JBoss and Hibernate to reopen transaction if the old one is outdated?

        • 1. Re: Reconnect to MySQL after its restart
          Nicklas Karlsson Master

          On the AS-level there are various connection-pool validation methods how the connection should be checked before handed over etc.

          • 2. Re: Reconnect to MySQL after its restart
            Alexey S. Newbie

            Could it be more specific.
            here is my datasource

             

            <xa-datasource jndi-name="java:jboss/datasources/MySqlDSDwh" pool-name="MySqlDSDwh" enabled="true" use-java-context="true">

                                <xa-datasource-property name="ServerName">

                                    localhost

                                </xa-datasource-property>

                                <xa-datasource-property name="DatabaseName">

                                    xxxx

                                </xa-datasource-property>

                                <xa-datasource-property name="User">

                                    root

                                </xa-datasource-property>

                                <xa-datasource-property name="Password">

                                    yyyyy

                                </xa-datasource-property>

                                <driver>com.mysql</driver>

                                <transaction-isolation>TRANSACTION_READ_COMMITTED</transaction-isolation>

                            </xa-datasource>

             

            What should I add?

            • 3. Re: Reconnect to MySQL after its restart
              Nicklas Karlsson Master

              Something like

               

                                  <pool>

                                      <flush-strategy>EntirePool</flush-strategy>

                                  </pool>

                                  <validation>

                                      <valid-connection-checker class-name="org.jboss.jca.adapters.jdbc.extensions.mysql.MySqlValidConnectionChecker"/>

                                      <exception-sorter class-name="org.jboss.jca.adapters.jdbc.extensions.mysql.MySqlExceptionSorter"/>

                                  </validation>

               

              in the datasource

              • 4. Re: Reconnect to MySQL after its restart
                Alexey S. Newbie

                I checked the documentation and changed the datasource in the similar to your suggestion way.

                 

                <xa-datasource jndi-name="java:jboss/datasources/MySqlDSDwh" pool-name="MySqlDSDwh" enabled="true" use-java-context="true">

                                    <xa-datasource-property name="ServerName">

                                        localhost

                                    </xa-datasource-property>

                                    <xa-datasource-property name="DatabaseName">

                                        xxxxxx

                                    </xa-datasource-property>

                                    <xa-datasource-property name="User">

                                        root

                                    </xa-datasource-property>

                                    <xa-datasource-property name="Password">

                                        yyyyyyyyyyyyy

                                    </xa-datasource-property>

                                    <driver>com.mysql</driver>

                                    <transaction-isolation>TRANSACTION_READ_COMMITTED</transaction-isolation>

                  <validation>

                      <valid-connection-checker class-name="org.jboss.jca.adapters.jdbc.extensions.mysql.MySQLValidConnectionChecker"></valid-connection-checker>

                      <exception-sorter class-name="org.jboss.jca.adapters.jdbc.extensions.mysql.MySQLExceptionSorter"></exception-sorter>

                    </validation>

                </xa-datasource>

                 

                However after I restarted Mysql my JBoss JPA Hibernate Transaction is not restarted and whole EAR is crashing  with 

                14:40:14,706 WARN  [org.hibernate.engine.jdbc.spi.SqlExceptionHelper] (EJB default - 5) SQL Error: 0, SQLState: null

                14:40:14,708 ERROR [org.hibernate.engine.jdbc.spi.SqlExceptionHelper] (EJB default - 5) Connection is not associated with a managed connection.org.jboss.jca.adapters.jdbc.jdk6.WrappedConnectionJDK6@3155ad64

                14:40:14,714 WARN  [org.hibernate.engine.jdbc.spi.SqlExceptionHelper] (EJB default - 5) SQL Error: 0, SQLState: null

                14:40:14,714 ERROR [org.hibernate.engine.jdbc.spi.SqlExceptionHelper] (EJB default - 5) Connection is not associated with a managed connection.org.jboss.jca.adapters.jdbc.jdk6.WrappedConnectionJDK6@3155ad64

                14:40:14,716 ERROR [stderr] (EJB default - 5) javax.persistence.PersistenceException: org.hibernate.exception.GenericJDBCException: could not inspect JDBC autocommit mode

                 

                 

                14:40:14,718 ERROR [stderr] (EJB default - 5) at org.hibernate.ejb.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1377)

                 

                What shoud I put for make it working?

                • 5. Re: Reconnect to MySQL after its restart
                  Alexey S. Newbie

                  seems that it was a AutoReconnect tag in local datasource. What exists in xa-datasource?

                  • 6. Re: Reconnect to MySQL after its restart
                    Alexey S. Newbie

                    I found the correct answer and "give it back" to community.

                     

                    <xa-datasource-property name="AutoReconnectForPools">

                                            true

                                        </xa-datasource-property>

                    • 7. Re: Reconnect to MySQL after its restart
                      Alexey S. Newbie

                      Sad, but it was our QA team error. Seems that is very serious bug at JBoss in Mysql couple (XA-datasource) that is preventing Jboss AS 7 Connection pool to reconnect after MYSQL restart or failure.

                      Any contribution is welcomed.

                      • 8. Re: Reconnect to MySQL after its restart
                        Alexey S. Newbie

                        new major bug reported to JBoss and already approved.

                        https://issues.jboss.org/browse/AS7-6624?page=com.atlassian.streams.streams-jira-plugin:activity-stream-issue-tab

                         

                        Seems its a huge mess in JBoss 7.1.1 Final. One of the basic reasons to use AS is to have a scalable reconnect to db after outage.

                        • 9. Re: Reconnect to MySQL after its restart
                          Nicklas Karlsson Master

                          All JIRAs are "major" by default and a default assignee if I'm not mistaken

                          • 10. Re: Reconnect to MySQL after its restart
                            Alexey S. Newbie

                            since what I see in the history that default assignee is already changed and severity is still "major". But what is funny here, Nicklas? The issue that JBoss probably have a serious problem?

                            I wish I will be wrong and that will be a my fault that I missed something in config, but till now I dont see anything problematic in xa-datasource.

                            So iff you can help- it will be very appreciated.

                            • 11. Re: Reconnect to MySQL after its restart
                              Alexey S. Newbie

                              ок, they closed a bug without explanation, the only conclusion is that AS 7.1.1 has unsolved critical issue, which violates a basic specification for any simple AS connection pool. Very sad,

                              • 12. Re: Reconnect to MySQL after its restart
                                Justin Bertram Master

                                Contrary to what you said on AS7-6624 you have not provided "all needed information for [sic] reproduce."  Do you have a test-case you can provide which demonstrates the problem?  Aside from a test-case, can you be more specific about exactly what you're trying to accomplish?  Do you expect that an in-flight transaction will magically work if the database is restarted in the middle of the transaction?  To be clear, the validation and flushing configuration available for a JDBC JCA datasource has nothing whatsoever to do with transactions.  It only pertains to connections.  If a connection dies in the middle of a transaction that transaction will fail even if the connection is re-established.

                                • 13. Re: Reconnect to MySQL after its restart
                                  Alexey S. Newbie

                                  Hello Justin.

                                   

                                  yes you're right, I've provided only datasource description, not the code of EJB timer (which actually can just open a JPA connection). If I will do it- will you solve this?

                                  Now about what I expect: I expect that JBoss will understand that the restart occured and refresh the connection pool. Is it something that shouldnt be?

                                  JBoss should not reconnect to DB when the disconnect occured?

                                  • 14. Re: Reconnect to MySQL after its restart
                                    jaikiran pai Master

                                    Alexey S. wrote:

                                     

                                    Hello Justin.

                                     

                                    yes you're right, I've provided only datasource description, not the code of EJB timer (which actually can just open a JPA connection). If I will do it- will you solve this?

                                    I don't know if we'll "solve" it or not but at least some of us might understand what problem you are running into. So far in this thread your description of the problem has been all over the place - JPA, EJB, timers, datasources and we have no clue what problem you are running into. At one point you mentioned you got it working and then talked something about QA problem. The exception stacktraces that you posted aren't complete.

                                     

                                    The best way to get attention to a problem is to make sure you post the relevant code, configurations and the logs and explain what the problem is. It might be a bug or a config issue but trying to make it look like "7.1.1 is a mess" only distracts the readers from the actual issue.

                                     

                                    So if you are seriously looking for some help with this issue, please start over and explain the problem in detail.

                                    1 2 Previous Next