Tomcat integration issue with MySql Database

[shbhoi]

I am facing issue while integrating JBossTS with Tomcat 8. I am following MySQL database.

Below is the stack strace :

WARN: ARJUNA012125: TwoPhaseCoordinator.beforeCompletion - failed for SynchronizationImple< 0:ffff0a22ca2f:e226:59dcb6da:9, org.hibernate.resource.transaction.backend.jta.internal.synchronization.RegisteredSynchronization@1ccd3fe8 >

javax.persistence.PersistenceException: org.hibernate.exception.GenericJDBCException: could not execute statement

at org.hibernate.jpa.spi.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1692)

at org.hibernate.jpa.spi.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1602)

at org.hibernate.jpa.spi.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1608)

at org.hibernate.jpa.internal.EntityManagerImpl$CallbackExceptionMapperImpl.mapManagedFlushFailure(EntityManagerImpl.java:235)

at org.hibernate.internal.SessionImpl.flushBeforeTransactionCompletion(SessionImpl.java:3139)

at org.hibernate.internal.SessionImpl.beforeTransactionCompletion(SessionImpl.java:2352)

at org.hibernate.engine.jdbc.internal.JdbcCoordinatorImpl.beforeTransactionCompletion(JdbcCoordinatorImpl.java:491)

at org.hibernate.resource.transaction.backend.jta.internal.JtaTransactionCoordinatorImpl.beforeCompletion(JtaTransactionCoordinatorImpl.java:316)

at org.hibernate.resource.transaction.backend.jta.internal.synchronization.SynchronizationCallbackCoordinatorNonTrackingImpl.beforeCompletion(SynchronizationCallbackCoordinatorNonTrackingImpl.java:47)

at org.hibernate.resource.transaction.backend.jta.internal.synchronization.RegisteredSynchronization.beforeCompletion(RegisteredSynchronization.java:37)

at com.arjuna.ats.internal.jta.resources.arjunacore.SynchronizationImple.beforeCompletion(SynchronizationImple.java:76)

at com.arjuna.ats.arjuna.coordinator.TwoPhaseCoordinator.beforeCompletion(TwoPhaseCoordinator.java:368)

at com.arjuna.ats.arjuna.coordinator.TwoPhaseCoordinator.end(TwoPhaseCoordinator.java:91)

at com.arjuna.ats.arjuna.AtomicAction.commit(AtomicAction.java:162)

at com.arjuna.ats.internal.jta.transaction.arjunacore.TransactionImple.commitAndDisassociate(TransactionImple.java:1289)

at com.arjuna.ats.internal.jta.transaction.arjunacore.BaseTransaction.commit(BaseTransaction.java:126)

at MainServ.doGet(MainServ.java:62)

at javax.servlet.http.HttpServlet.service(HttpServlet.java:622)

at javax.servlet.http.HttpServlet.service(HttpServlet.java:729)

at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:292)

at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:207)

at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52)

at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:240)

at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:207)

at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:212)

at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:94)

at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:504)

at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:141)

at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:79)

at org.apache.catalina.valves.AbstractAccessLogValve.invoke(AbstractAccessLogValve.java:620)

at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:88)

at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:502)

at org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1132)

at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:684)

at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1539)

at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.run(NioEndpoint.java:1495)

at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)

at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)

at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)

at java.lang.Thread.run(Unknown Source)

Caused by: org.hibernate.exception.GenericJDBCException: could not execute statement

at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:47)

at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:111)

at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:97)

at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.executeUpdate(ResultSetReturnImpl.java:207)

at org.hibernate.engine.jdbc.batch.internal.NonBatchingBatch.addToBatch(NonBatchingBatch.java:45)

at org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:2999)

at org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:3499)

at org.hibernate.action.internal.EntityInsertAction.execute(EntityInsertAction.java:89)

at org.hibernate.engine.spi.ActionQueue.executeActions(ActionQueue.java:586)

at org.hibernate.engine.spi.ActionQueue.executeActions(ActionQueue.java:460)

at org.hibernate.event.internal.AbstractFlushingEventListener.performExecutions(AbstractFlushingEventListener.java:337)

at org.hibernate.event.internal.DefaultFlushEventListener.onFlush(DefaultFlushEventListener.java:39)

at org.hibernate.internal.SessionImpl.flush(SessionImpl.java:1295)

at org.hibernate.internal.SessionImpl.managedFlush(SessionImpl.java:468)

at org.hibernate.internal.SessionImpl.flushBeforeTransactionCompletion(SessionImpl.java:3135)

... 35 more

Caused by: java.sql.SQLException: No database selected

at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1094)

at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4226)

at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4158)

at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2615)

at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2776)

at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2840)

at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2082)

at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2334)

at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2262)

at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2246)

at com.mysql.jdbc.jdbc2.optional.PreparedStatementWrapper.executeUpdate(PreparedStatementWrapper.java:873)

at org.apache.tomcat.dbcp.dbcp2.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:97)

at org.apache.tomcat.dbcp.dbcp2.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:97)

at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.executeUpdate(ResultSetReturnImpl.java:204)

... 46 more

javax.transaction.RollbackException: ARJUNA016053: Could not commit transaction.

at com.arjuna.ats.internal.jta.transaction.arjunacore.TransactionImple.commitAndDisassociate(TransactionImple.java:1301)

at com.arjuna.ats.internal.jta.transaction.arjunacore.BaseTransaction.commit(BaseTransaction.java:126)

at MainServ.doGet(MainServ.java:62)

at javax.servlet.http.HttpServlet.service(HttpServlet.java:622)

at javax.servlet.http.HttpServlet.service(HttpServlet.java:729)

at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:292)

at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:207)

at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52)

at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:240)

at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:207)

at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:212)

at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:94)

at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:504)

at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:141)

at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:79)

at org.apache.catalina.valves.AbstractAccessLogValve.invoke(AbstractAccessLogValve.java:620)

at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:88)

at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:502)

at org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1132)

at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:684)

at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1539)

at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.run(NioEndpoint.java:1495)

at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)

at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)

at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)

at java.lang.Thread.run(Unknown Source)

Caused by: javax.persistence.PersistenceException: org.hibernate.exception.GenericJDBCException: could not execute statement

at org.hibernate.jpa.spi.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1692)

at org.hibernate.jpa.spi.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1602)

at org.hibernate.jpa.spi.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1608)

at org.hibernate.jpa.internal.EntityManagerImpl$CallbackExceptionMapperImpl.mapManagedFlushFailure(EntityManagerImpl.java:235)

at org.hibernate.internal.SessionImpl.flushBeforeTransactionCompletion(SessionImpl.java:3139)

at org.hibernate.internal.SessionImpl.beforeTransactionCompletion(SessionImpl.java:2352)

at org.hibernate.engine.jdbc.internal.JdbcCoordinatorImpl.beforeTransactionCompletion(JdbcCoordinatorImpl.java:491)

at org.hibernate.resource.transaction.backend.jta.internal.JtaTransactionCoordinatorImpl.beforeCompletion(JtaTransactionCoordinatorImpl.java:316)

at org.hibernate.resource.transaction.backend.jta.internal.synchronization.SynchronizationCallbackCoordinatorNonTrackingImpl.beforeCompletion(SynchronizationCallbackCoordinatorNonTrackingImpl.java:47)

at org.hibernate.resource.transaction.backend.jta.internal.synchronization.RegisteredSynchronization.beforeCompletion(RegisteredSynchronization.java:37)

at com.arjuna.ats.internal.jta.resources.arjunacore.SynchronizationImple.beforeCompletion(SynchronizationImple.java:76)

at com.arjuna.ats.arjuna.coordinator.TwoPhaseCoordinator.beforeCompletion(TwoPhaseCoordinator.java:368)

at com.arjuna.ats.arjuna.coordinator.TwoPhaseCoordinator.end(TwoPhaseCoordinator.java:91)

at com.arjuna.ats.arjuna.AtomicAction.commit(AtomicAction.java:162)

at com.arjuna.ats.internal.jta.transaction.arjunacore.TransactionImple.commitAndDisassociate(TransactionImple.java:1289)

... 25 more

Caused by: org.hibernate.exception.GenericJDBCException: could not execute statement

at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:47)

at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:111)

at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:97)

at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.executeUpdate(ResultSetReturnImpl.java:207)

at org.hibernate.engine.jdbc.batch.internal.NonBatchingBatch.addToBatch(NonBatchingBatch.java:45)

at org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:2999)

at org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:3499)

at org.hibernate.action.internal.EntityInsertAction.execute(EntityInsertAction.java:89)

at org.hibernate.engine.spi.ActionQueue.executeActions(ActionQueue.java:586)

at org.hibernate.engine.spi.ActionQueue.executeActions(ActionQueue.java:460)

at org.hibernate.event.internal.AbstractFlushingEventListener.performExecutions(AbstractFlushingEventListener.java:337)

at org.hibernate.event.internal.DefaultFlushEventListener.onFlush(DefaultFlushEventListener.java:39)

at org.hibernate.internal.SessionImpl.flush(SessionImpl.java:1295)

at org.hibernate.internal.SessionImpl.managedFlush(SessionImpl.java:468)

at org.hibernate.internal.SessionImpl.flushBeforeTransactionCompletion(SessionImpl.java:3135)

... 35 more

Caused by: java.sql.SQLException: No database selected

at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1094)

at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4226)

at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4158)

at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2615)

at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2776)

at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2840)

at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2082)

at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2334)

at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2262)

at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2246)

at com.mysql.jdbc.jdbc2.optional.PreparedStatementWrapper.executeUpdate(PreparedStatementWrapper.java:873)

at org.apache.tomcat.dbcp.dbcp2.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:97)

at org.apache.tomcat.dbcp.dbcp2.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:97)

at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.executeUpdate(ResultSetReturnImpl.java:204)

... 46 more

My persistence file (persistence.xml ) looks like below:

<persistence xmlns="http://xmlns.jcp.org/xml/ns/persistence"

             xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"

             xsi:schemaLocation="http://xmlns.jcp.org/xml/ns/persistence http://xmlns.jcp.org/xml/ns/persistence/persistence_2_1.xsd"

             version="2.1">

    <persistence-unit name="JBossTS" transaction-type="JTA">

        <properties>

           <property name="hibernate.format_sql" value="true" />

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

     <property name="current_session_context_class" value="jta"/>

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

            <!-- Set Hibernate to use Narayana as a JTA platform -->

            <property name="hibernate.transaction.io.narayana.jta.platform" value="JBossTS"/>

            <!-- We provide our own connection provider, in order to integrate TransactionalDriver with Hibernate -->

            <property name="hibernate.connection.provider_class" value="io.narayana.TransactionalConnectionProvider" />

        </properties>

    </persistence-unit>

</persistence>

My Web.xml is like below :

<web-app>

    <listener>

        <!-- Register context listener to bootstrap Narayana services -->

        <listener-class>org.jboss.narayana.tomcat.jta.NarayanaJtaServletContextListener</listener-class>

    </listener>

</web-app>

My Context.xml is like below:

<?xml version="1.0" encoding="UTF-8"?>

<Context>

    <!-- Narayana resources -->

    <Transaction factory="org.jboss.narayana.tomcat.io.narayana.jta.UserTransactionFactory"/>

    <Resource name="TransactionManager" type="javax.transaction.TransactionManager"

              factory="org.jboss.narayana.tomcat.jta.TransactionManagerFactory"/>

    <Resource name="TransactionSynchronizationRegistry" type="javax.transaction.TransactionSynchronizationRegistry"

              factory="org.jboss.narayana.tomcat.jta.TransactionSynchronizationRegistryFactory"/>

    <Resource name="tomcatTest" uniqueName="tomcatTest" auth="Container" type="com.mysql.jdbc.jdbc2.optional.MysqlXADataSource"

        driverClassName="com.mysql.jdbc.Driver"

        factory="com.mysql.jdbc.jdbc2.optional.MysqlDataSourceFactory"

        username="admin" user="admin" password="admin" url="jdbc:mysql://localhost/tomcattest"/>

     <Resource name="transactionalDataSource" uniqueName="transactionalDataSource" auth="Container" type="javax.sql.DataSource"

              username="admin" password="admin" driverClassName="com.arjuna.ats.jdbc.TransactionalDriver"

              url="jdbc:arjuna:java:comp/env/tomcatTest" description="Transactional Driver Data Source"/>

</Context>

Is my servlet is like below :

protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {

try{

transactionManager = InitialContext.doLookup("java:comp/env/TransactionManager");

try{

EntityManagerFactory emf = Persistence.createEntityManagerFactory("JBossTS");

EntityManager em = emf.createEntityManager();

transactionManager.begin();

Address aa = new Address();

aa.setId(11);

aa.setStreet("My Home");

em.persist(aa);

transactionManager.commit();

}catch(Exception e){

e.printStackTrace();

}

}catch(Exception e){

e.printStackTrace();

}

}

I have copied TransactionalConnectionProvider from quick start . I have attached all the document.

Please help me out.

[andey]

- Check the database name, DB connection URL(URL string you are using to connect to the database in which make sure that you have provided the server and the database name), Driver class name, appropriate DB username and password are properly defined in persistence.xml file.

-  Make sure that appropriate selected MySQL user (username and password) which you are using to access the database have sufficient GRANT permissions .

- Make sure that perhaps 'database' isn't valid, double check your values and try making it an external variable.

- This is normally your URL followed by a "/DBNAME". String URL = "jdbc:mysql://localhost:3306/mydb"; Where "mydb" is your database name.

You need to do some steps:

1# Download Connector/J(MySQL :: Download Connector/J ) and add it to your class path.

2# Create your database in your MySQL program.

3# See this example below example below I made for you demonstrates how to connect and execute queries on MySQL :

import java.sql.*;

  public class MySqlConnection {

  private String MYSQL_DRIVER = "com.mysql.jdbc.Driver";

  private String MYSQL_URL = "jdbc:mysql://localhost:3306/test";

  private Connection con;

  private Statement st;

  private ResultSet rs;

  public MySqlConnection() {

    try {

      Class.forName(MYSQL_DRIVER);

      System.out.println("Class Loaded....");

      con = DriverManager.getConnection(MYSQL_URL,"","");

      System.out.println("Connected to the database....");

      st = con.createStatement();

      int c =st.executeUpdate("CREATE TABLE Accounts (Name VARCHAR(30))");

      System.out.println("Table have been created.");

      System.out.println(c+" Row(s) have been affected");

      con.close();

    } catch(ClassNotFoundException ex) {

       System.out.println("ClassNotFoundException:\n"+ex.toString());

       ex.printStackTrace();

    } catch(SQLException ex) {

        System.out.println("SQLException:\n"+ex.toString());

        ex.printStackTrace();

    }

  }

  public static void main(String...args) {

    new MySqlConnection();

  }

}

I Would like to suggest to contact your DBA for the external DB. You may need to contact MySQL for support.

[ochaloup]

Hi shbhoi , I was interested if there is some issue with Narayana but I think you have small misconfiguration in your context.xml file. You miss defining databaseName on MySQL XADatasource definition. That's what the exception talks about

Caused by: java.sql.SQLException: No database selected

at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1094)

at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4226)

at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4158)

at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2615)

at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2776)

My test context.xml then looks

<?xml version="1.0" encoding="UTF-8"?>

<Context>

    <!-- Narayana resources -->

    <Transaction factory="org.jboss.narayana.tomcat.io.narayana.jta.UserTransactionFactory"/>

    <Resource name="TransactionManager" type="javax.transaction.TransactionManager"

              factory="org.jboss.narayana.tomcat.jta.TransactionManagerFactory"/>

    <Resource name="TransactionSynchronizationRegistry" type="javax.transaction.TransactionSynchronizationRegistry"

              factory="org.jboss.narayana.tomcat.jta.TransactionSynchronizationRegistryFactory"/>

    <Resource name="tomcatTest" uniqueName="tomcatTest" auth="Container" type="com.mysql.jdbc.jdbc2.optional.MysqlXADataSource"

        driverClassName="com.mysql.jdbc.Driver"

        factory="com.mysql.jdbc.jdbc2.optional.MysqlDataSourceFactory"

        user="test" databaseName="test" password="test" url="jdbc:mysql://localhost:3306/test"

        description="MySQL XA datasource by mysql jdbc factory" />

     <Resource name="transactionalDataSource" uniqueName="transactionalDataSource" auth="Container" type="javax.sql.DataSource"

        driverClassName="com.arjuna.ats.jdbc.TransactionalDriver"

        username="test" password="test" jdbc="jdbc:mysql://localhost:3306/test"

        url="jdbc:arjuna:java:comp/env/tomcatTest"

        description="Transactional Driver Data Source"/>

</Context>

I hope this helps.

Cheers,

Ondra