autocommit wont turn off

michaelm001 · **Joined:** Thu Mar 04, 2004 3:55 am **Posts:** 2

I've been trying to get this simple example to work but no matter what I do it keeps complaining about autocommit being on so it can't save...I KNOW that autocommit is off because I'm explicitly setting it on the connection AND the session. But it still complains about autocommit being on...WHAT IS GOING ON??? Latest Mysql, latest JDBC drivers, JDK 1.4.2_03 on OS X. Tomcat 5.??.??

Here is the code:

Code:

        try 
        {
            
            javax.naming.Context ctx = new javax.naming.InitialContext();
            if(ctx == null ) throw new Exception("Boom - No Context");
            
            System.out.println("got ctx:"+ctx);
            
            javax.sql.DataSource ds = (javax.sql.DataSource)ctx.lookup("java:comp/env/jdbc/abm");
            
            System.out.println("got ds: "+ds);
            
            java.sql.Connection connection = ds.getConnection();
            connection.setAutoCommit(false);            
            
            // -----------------------------------------------------------------------------------
            
            {
                Statement s = connection.createStatement();
                int rows = s.executeUpdate("update players set first_name=\"johny\"");
                
                System.out.println("before commit...");
                connection.rollback();
                System.out.println("rolled back!");
                
            }
            
            // -----------------------------------------------------------------------------------
            
            Configuration instance = new Configuration();
            Configuration config = instance.configure();
            SessionFactory sessionFactory = config.buildSessionFactory();
            
            System.out.println("got sessionfactory: "+sessionFactory);
            
            Session session = sessionFactory.openSession(connection);
            
            boolean toggleAutoCommit = session.connection().getAutoCommit();
System.out.println("toggleAutoCommit: "+toggleAutoCommit);            
            
            //if (toggleAutoCommit) session.connection().setAutoCommit(false); 
            session.connection().setAutoCommit(false); 
            
            connection.setAutoCommit(false);
            
System.out.println("connection.getAutoCommit: "+connection.getAutoCommit());            
            
            System.out.println("got session: "+session);
            
            Transaction tx= session.beginTransaction();
            
            System.out.println("got transaction: "+tx);
            
            //public Player(String firstName, String lastName, int jerseyNumber) {
            
            Player player = new Player("Mike", "McCutcheon", 21);
            session.save(player);
            
            System.out.println("saved player...");
                        
            tx.commit();
            
            session.close();
            
            //
            //Cat princess = new Cat();
            //princess.setName("Princess");
            //princess.setSex('F');
            //princess.setWeight(7.4f);
            //
            //session.save(princess);
            //tx.commit();
            //
            //HibernateUtil.closeSession();
            

        } 
        catch (HibernateException ex) 
        {
            ex.printStackTrace();
        }
        catch(Throwable t)
        {
            t.printStackTrace();
        }

Note that I explicitly try to do a transaction before I even do anything with hibernate...just to see if the connection is working...it is. Now when this runs I get:

Code:

got ctx:javax.naming.InitialContext@bab96e
got ds: org.apache.commons.dbcp.BasicDataSource@e22950
before commit...
rolled back!
Mar 3, 2004 11:46:37 PM net.sf.hibernate.cfg.Environment <clinit>
INFO: Hibernate 2.1.2
Mar 3, 2004 11:46:37 PM net.sf.hibernate.cfg.Environment <clinit>
INFO: hibernate.properties not found
Mar 3, 2004 11:46:37 PM net.sf.hibernate.cfg.Environment <clinit>
INFO: using CGLIB reflection optimizer
Mar 3, 2004 11:46:37 PM net.sf.hibernate.cfg.Configuration configure
INFO: configuring from resource: /hibernate.cfg.xml
Mar 3, 2004 11:46:37 PM net.sf.hibernate.cfg.Configuration getConfigurationInputStream
INFO: Configuration resource: /hibernate.cfg.xml
Mar 3, 2004 11:46:38 PM net.sf.hibernate.cfg.Configuration addResource
INFO: Mapping resource: ../../hibernate/abm.hbm.xml
Mar 3, 2004 11:46:38 PM net.sf.hibernate.cfg.Binder bindRootClass
INFO: Mapping class: example.Team -> teams
Mar 3, 2004 11:46:38 PM net.sf.hibernate.cfg.Binder bindRootClass
INFO: Mapping class: example.Player -> players
Mar 3, 2004 11:46:39 PM net.sf.hibernate.cfg.Configuration doConfigure
INFO: Configured SessionFactory: null
Mar 3, 2004 11:46:39 PM net.sf.hibernate.cfg.Configuration secondPassCompile
INFO: processing one-to-many association mappings
Mar 3, 2004 11:46:39 PM net.sf.hibernate.cfg.Binder bindCollectionSecondPass
INFO: Mapping collection: example.Team.players -> players
Mar 3, 2004 11:46:39 PM net.sf.hibernate.cfg.Configuration secondPassCompile
INFO: processing one-to-one association property references
Mar 3, 2004 11:46:39 PM net.sf.hibernate.cfg.Configuration secondPassCompile
INFO: processing foreign key constraints
Mar 3, 2004 11:46:39 PM net.sf.hibernate.dialect.Dialect <init>
INFO: Using dialect: net.sf.hibernate.dialect.MySQLDialect
Mar 3, 2004 11:46:39 PM net.sf.hibernate.cfg.SettingsFactory buildSettings
INFO: Use outer join fetching: true
Mar 3, 2004 11:46:39 PM net.sf.hibernate.util.NamingHelper getInitialContext
INFO: JNDI InitialContext properties:{}
Mar 3, 2004 11:46:39 PM net.sf.hibernate.connection.DatasourceConnectionProvider configure
INFO: Using datasource: java:comp/env/jdbc/abm
Mar 3, 2004 11:46:39 PM net.sf.hibernate.transaction.TransactionManagerLookupFactory getTransactionManagerLookup
INFO: No TransactionManagerLookup configured (in JTA environment, use of process level read-write cache is not recommended)
Mar 3, 2004 11:46:39 PM net.sf.hibernate.cfg.SettingsFactory buildSettings
INFO: Use scrollable result sets: true
Mar 3, 2004 11:46:39 PM net.sf.hibernate.cfg.SettingsFactory buildSettings
INFO: Use JDBC3 getGeneratedKeys(): true
Mar 3, 2004 11:46:39 PM net.sf.hibernate.cfg.SettingsFactory buildSettings
INFO: Optimize cache for minimal puts: false
Mar 3, 2004 11:46:39 PM net.sf.hibernate.cfg.SettingsFactory buildSettings
INFO: echoing all SQL to stdout
Mar 3, 2004 11:46:39 PM net.sf.hibernate.cfg.SettingsFactory buildSettings
INFO: Query language substitutions: {}
Mar 3, 2004 11:46:39 PM net.sf.hibernate.cfg.SettingsFactory buildSettings
INFO: cache provider: net.sf.ehcache.hibernate.Provider
Mar 3, 2004 11:46:39 PM net.sf.hibernate.cfg.Configuration configureCaches
INFO: instantiating and configuring caches
Mar 3, 2004 11:46:39 PM net.sf.hibernate.impl.SessionImpl finalize
WARNING: afterTransactionCompletion() was never called
Mar 3, 2004 11:46:39 PM net.sf.hibernate.impl.SessionImpl finalize
WARNING: unclosed connection
Mar 3, 2004 11:46:40 PM net.sf.hibernate.impl.SessionFactoryImpl <init>
INFO: building session factory
Mar 3, 2004 11:46:41 PM net.sf.hibernate.impl.SessionFactoryObjectFactory addInstance
INFO: no JNDI name configured
got sessionfactory: net.sf.hibernate.impl.SessionFactoryImpl@257ce8
toggleAutoCommit: false
connection.getAutoCommit: false
got session: net.sf.hibernate.impl.SessionImpl@5df7c6
got transaction: net.sf.hibernate.transaction.JDBCTransaction@a81e44
Mar 3, 2004 11:46:41 PM net.sf.hibernate.util.JDBCExceptionReporter logExceptions
WARNING: SQL Error: 0, SQLState: S1000
Mar 3, 2004 11:46:41 PM net.sf.hibernate.util.JDBCExceptionReporter logExceptions
SEVERE: Can't call commit when autocommit=true
Mar 3, 2004 11:46:41 PM net.sf.hibernate.JDBCException <init>
SEVERE: Could not save object
java.sql.SQLException: Can't call commit when autocommit=true
        at com.mysql.jdbc.Connection.commit(Connection.java:1094)
        at org.apache.commons.dbcp.DelegatingConnection.commit(DelegatingConnection.java:248)
        at org.apache.commons.dbcp.PoolingDataSource$PoolGuardConnectionWrapper.commit(PoolingDataSource.java:244)
        at net.sf.hibernate.id.TableGenerator.generate(TableGenerator.java:126)
        at net.sf.hibernate.id.TableHiLoGenerator.generate(TableHiLoGenerator.java:59)
        at net.sf.hibernate.impl.SessionImpl.saveWithGeneratedIdentifier(SessionImpl.java:747)
        at net.sf.hibernate.impl.SessionImpl.save(SessionImpl.java:720)
        at example.HibernateTest.doGet(HibernateTest.java:88)
        at javax.servlet.http.HttpServlet.service(HttpServlet.java:743)
        at javax.servlet.http.HttpServlet.service(HttpServlet.java:856)
        at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:284)
        at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:204)
        at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:257)
        at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:151)
        at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:564)
        at org.apache.catalina.core.StandardContextValve.invokeInternal(StandardContextValve.java:245)
        at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:199)
        at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:151)
        at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:564)
        at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:195)
        at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:151)
        at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:164)
        at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:149)
        at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:564)
        at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:156)
        at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:151)
        at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:564)
        at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:972)
        at org.apache.coyote.tomcat5.CoyoteAdapter.service(CoyoteAdapter.java:206)
        at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:828)
        at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.processConnection(Http11Protocol.java:700)
        at org.apache.tomcat.util.net.TcpWorkerThread.runIt(PoolTcpEndpoint.java:584)
        at org.apache.tomcat.util.threads.ThreadPool$ControlRunnable.run(ThreadPool.java:683)
        at java.lang.Thread.run(Thread.java:552)
net.sf.hibernate.JDBCException: Could not save object
        at net.sf.hibernate.impl.SessionImpl.saveWithGeneratedIdentifier(SessionImpl.java:765)
        at net.sf.hibernate.impl.SessionImpl.save(SessionImpl.java:720)
        at example.HibernateTest.doGet(HibernateTest.java:88)
        at javax.servlet.http.HttpServlet.service(HttpServlet.java:743)
        at javax.servlet.http.HttpServlet.service(HttpServlet.java:856)
        at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:284)
        at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:204)
        at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:257)
        at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:151)
        at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:564)
        at org.apache.catalina.core.StandardContextValve.invokeInternal(StandardContextValve.java:245)
        at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:199)
        at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:151)
        at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:564)
        at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:195)
        at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:151)
        at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:164)
        at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:149)
        at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:564)
        at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:156)
        at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:151)
        at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:564)
        at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:972)
        at org.apache.coyote.tomcat5.CoyoteAdapter.service(CoyoteAdapter.java:206)
        at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:828)
        at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.processConnection(Http11Protocol.java:700)
        at org.apache.tomcat.util.net.TcpWorkerThread.runIt(PoolTcpEndpoint.java:584)
        at org.apache.tomcat.util.threads.ThreadPool$ControlRunnable.run(ThreadPool.java:683)
        at java.lang.Thread.run(Thread.java:552)
Caused by: java.sql.SQLException: Can't call commit when autocommit=true
        at com.mysql.jdbc.Connection.commit(Connection.java:1094)
        at org.apache.commons.dbcp.DelegatingConnection.commit(DelegatingConnection.java:248)
        at org.apache.commons.dbcp.PoolingDataSource$PoolGuardConnectionWrapper.commit(PoolingDataSource.java:244)
        at net.sf.hibernate.id.TableGenerator.generate(TableGenerator.java:126)
        at net.sf.hibernate.id.TableHiLoGenerator.generate(TableHiLoGenerator.java:59)
        at net.sf.hibernate.impl.SessionImpl.saveWithGeneratedIdentifier(SessionImpl.java:747)
        ... 28 more

What in the world am I doing wrong?

Thanks,

Mike

jdragon · **Joined:** Mon Nov 17, 2003 3:50 pm **Posts:** 19

I'm having the same problem myself!!! Is this a bug in Hibernate???

jdragon · **Joined:** Mon Nov 17, 2003 3:50 pm **Posts:** 19

My platform is jboss 3.2, myslq 4, and linux.

jdragon · **Joined:** Mon Nov 17, 2003 3:50 pm **Posts:** 19

Here is my stack trace:

2004-03-04 03:27:41,410 ERROR [net.sf.hibernate.transaction.JDBCTransaction] Commit failed
java.sql.SQLException: Can't call commit when autocommit=true
at com.mysql.jdbc.Connection.commit(Connection.java:1175)
at org.jboss.resource.adapter.jdbc.BaseWrapperManagedConnection.jdbcCommit(BaseWrapperManagedConnection.java:496)
at org.jboss.resource.adapter.jdbc.WrappedConnection.commit(WrappedConnection.java:465)
at net.sf.hibernate.transaction.JDBCTransaction.commit(JDBCTransaction.java:63)
at com.wso.persistence.event.EventDAO.save(EventDAO.java:131)
at com.wso.domain.Event.save(Event.java:94)
at com.wso.services.EventService.save(EventService.java:97)
at com.wso.struts.action.PostEventAction.execute(PostEventAction.java:240)
at org.apache.struts.action.RequestProcessor.processActionPerform(RequestProcessor.java:484)
at org.apache.struts.action.RequestProcessor.process(RequestProcessor.java:274)
at org.apache.struts.action.ActionServlet.process(ActionServlet.java:1482)
at org.apache.struts.action.ActionServlet.doPost(ActionServlet.java:525)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:760)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:853)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:247)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:193)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:256)
at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:643)
at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:480)
at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:995)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:191)
at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:643)
at org.jboss.web.tomcat.security.JBossSecurityMgrRealm.invoke(JBossSecurityMgrRealm.java:220)
at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:641)
at org.apache.catalina.valves.CertificatesValve.invoke(CertificatesValve.java:246)
at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:641)
at org.jboss.web.tomcat.tc4.statistics.ContainerStatsValve.invoke(ContainerStatsValve.java:76)
at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:641)
at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:480)
at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:995)
at org.apache.catalina.core.StandardContext.invoke(StandardContext.java:2416)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:180)
at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:643)
at org.apache.catalina.valves.ErrorDispatcherValve.invoke(ErrorDispatcherValve.java:171)
at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:641)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:172)
at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:641)
at org.jboss.web.tomcat.security.SecurityAssociationValve.invoke(SecurityAssociationValve.java:65)
at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:641)
at org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:577)
at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:641)
at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:480)
at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:995)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:174)
at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:643)
at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:480)
at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:995)
at org.apache.coyote.tomcat4.CoyoteAdapter.service(CoyoteAdapter.java:223)
at org.apache.jk.server.JkCoyoteHandler.invoke(JkCoyoteHandler.java:263)
at org.apache.jk.common.HandlerRequest.invoke(HandlerRequest.java:360)
at org.apache.jk.common.ChannelSocket.invoke(ChannelSocket.java:604)
at org.apache.jk.common.ChannelSocket.processConnection(ChannelSocket.java:562)
at org.apache.jk.common.SocketConnection.runIt(ChannelSocket.java:679)
at org.apache.tomcat.util.threads.ThreadPool$ControlRunnable.run(ThreadPool.java:619)
at java.lang.Thread.run(Thread.java:534)
okeNext(StandardPipeline.java:641)
at org.jboss.web.tomcat.tc4.statistics.ContainerStatsValve.invoke(ContainerStatsValve.java:76)
at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:641)
at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:480)
at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:995)
at org.apache.catalina.core.StandardContext.invoke(StandardContext.java:2416)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:180)
at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:643)
at org.apache.catalina.valves.ErrorDispatcherValve.invoke(ErrorDispatcherValve.java:171)
at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:641)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:172)
at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:641)
at org.jboss.web.tomcat.security.SecurityAssociationValve.invoke(SecurityAssociationValve.java:65)
at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:641)
at org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:577)
at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:641)
at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:480)
at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:995)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:174)
at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:643)
at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:480)
at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:995)
at org.apache.coyote.tomcat4.CoyoteAdapter.service(CoyoteAdapter.java:223)
at org.apache.jk.server.JkCoyoteHandler.invoke(JkCoyoteHandler.java:263)
at org.apache.jk.common.HandlerRequest.invoke(HandlerRequest.java:360)
at org.apache.jk.common.ChannelSocket.invoke(ChannelSocket.java:604)
at org.apache.jk.common.ChannelSocket.processConnection(ChannelSocket.java:562)
at org.apache.jk.common.SocketConnection.runIt(ChannelSocket.java:679)
at org.apache.tomcat.util.threads.ThreadPool$ControlRunnable.run(ThreadPool.java:619)
at java.lang.Thread.run(Thread.java:534)

michaelm001 · **Joined:** Thu Mar 04, 2004 3:55 am **Posts:** 2

jdragon wrote:

My platform is jboss 3.2, myslq 4, and linux.

The problem went away when I switched from the apache tomcat datasource (as defined in the tomcat server.xml file), to the hibernate connection setup. Basically I just deleted the apache connection pool and created a new one in the hibernate.cfg.xml file. Now the transaction works no problem (although I get tons of messages spewed). See:

Code:

<?xml version='1.0' encoding='utf-8'?>
<!DOCTYPE hibernate-configuration PUBLIC "-//Hibernate/Hibernate Configuration DTD//EN" "http://hibernate.sourceforge.net/hibernate-configuration-2.0.dtd">
<hibernate-configuration>

    <session-factory>

        <!--
        FOR SOME REASON, USING THE DATASOURCE CAUSED AUTOCOMMIT TO STAY ON AND TRANSACTIONS
        WOULD NOT WORK...DON'T KNOW WHY.  MUST EXPLICITLY SET CONNECTIONS PROPERTIES INT HIS FILE
        <property name="connection.datasource">java:comp/env/jdbc/abm</property>
        -->
        
        <property name="hibernate.connection.driver_class">com.mysql.jdbc.Driver</property>
        <property name="hibernate.connection.url">jdbc:mysql://localhost:3306/abm</property>
        <property name="hibernate.connection.username">xxx</property>
        <property name="hibernate.connection.password">xxx</property>
        
        <property name="show_sql">true</property>
        <property name="dialect">net.sf.hibernate.dialect.MySQLDialect</property>

        <!-- Mapping files -->
        <mapping resource="../../hibernate/abm.hbm.xml"/>

    </session-factory>

</hibernate-configuration>

Now I am back in business and can more forward, although I need to figure out this connectin pool stuff now.

Mike

jandockx · **Posted:** Fri May 27, 2005 5:28 am

Exactly the same problem, using Hibernate2, Tomcat 5.5, jtds 1.0.3, to a MS SqlServer.

We didn't have the problem, and now we do. The occurence happened more or less at the same time as when we switched from Hibernate.cfg connections to using JNDI and tomcat connection pooling.

Will check this out.

alin_sinpalean · **Posted:** Mon May 30, 2005 5:00 am

All these errors are caused by commit() getting called while in autocommit mode. I see in one of the stack traces that Hibernate's TableGenerator is causing this; in the other cases it's probably in user code.

jandockx, what is your stacktrace?

Alin,
The jTDS Project.

jandockx · **Posted:** Mon May 30, 2005 6:12 pm

alin,

Don't bother with the stack trace. But please consider this explanation, and tell us whether you consider it sensible.

1) DB connections have autocommit set to true when they are created (JDBC spec)

2) That's sensible for most DB interaction. Mostly 1 interaction is a transaction. We only need manual transaction control in extreme circumstances, like
* sql queries to different DB's (JTA)
* very complex actions that are difficult to write in 1 query
* calling stored procedures that don't behave well; we often encounter stored procedures that raise exceptions, but do not rollback themselves

3) The Hibernate implementors prefer autocommit=false for their work, which is understandable. If Hibernate creates it own connections, it sets autocommit to false immediately. If Hibernate gets connections from outside, e.g., from Tomcat connection pooling via JNDI, it temporarily sets autocommit to false for its own work, but then resets it to the original setting when it is done.

4) Tomcat connection pooling uses the JDBC default: autocommit is true

5) We call stored procedures using the Hibernate session connection (session.connection()). When Hibernate created the connections, we saw that autocommit was false. When we switched to Tomcat connection pooling for the connections, things stopped working.

We now changed our code so that we do not depend on the autocommit setting. In view of the above, we believe that is the only correct code. That is, if you can confirm the above 5 points.

Another thing that might be interesting though, with respect to jtds, is that our DB people are very afraid of the combination of autocommit=false (or implicit transactions of SqlServer = true), and the temporary stored procedures JTDS creates on the server (prepareSQL=1). They found that these stored procedures are created for each connection, take a lot of memory (> 300MB), and lock the server, because the transaction to which they belong is not committed in this case. This might be a result of the use of jtds specifically with Hibernate. For this reason, we switched to prepareSQL=2.
I would like a comment on this from you, since it is surprising to me that this topic is nowhere discussed really, and all reference I find seem to imply that prepareSQL=1 is the better choice.

alin_sinpalean · **Posted:** Tue May 31, 2005 4:18 am

jandockx,

Your view of how Hibernate and Tomcat set the autocommit mode seems correct, but I can't confirm anything about Hibernate. Tomcat will definitely set autoCommit to true because connection pools should be transparent, i.e. connections should be in the state specified by JDBC whether they are newly created or pooled. On the other hand, if Hibernate does set autoCommit to false then it should do so regardless of where the connection comes from. But you don't have to take my word for it.

Regarding your problem with temporary stored procedures and autoCommit=false, we have changed the default prepareSQL mode to 3 (sp_prepare, sp_execute) for SQL Server in the current development code; this change will be part of the next release. However, 300MB seems A LOT to me; I have never had the curiosity to find out how much memory is used by the temporary stored procedures, but 300MB seems too much for a few thousand statements (max 500 per connection, tens of connections). Anyway, this problem should be solved in jTDS 1.1 by the new defaults.

Alin,
The jTDS Project.

svaret · **Posted:** Tue Aug 02, 2005 8:14 am

Hi,

I use this driver: mysql-connector-java-3.0.8-stable-bin.jar

Running hibernate3 and JBoss 4.0.2.

In my datasource file mysql-ds.xml. My connection string is set like this:
<connection-url>jdbc:mysql://localhost/betservant?relaxAutoCommit=true</connection-url>

This has worked for me before. However, I am having some problem with optimistic-locking but this could have other reasons.

fxjr · **Posted:** Thu May 18, 2006 2:06 pm

Hi all,

I'm having this problem too with jTds and mssqlserver 2000.

Here is the stack trace I get when I use the datasource from jboss 4.0.2:

2006-05-18 17:41:49,359 DEBUG [org.hibernate.impl.SessionImpl] before transaction completion

2006-05-18 17:41:49,359 DEBUG [org.hibernate.transaction.JDBCTransaction] re-enabling autocommit

2006-05-18 17:41:49,359 ERROR [org.hibernate.transaction.JDBCTransaction] JDBC rollback failed

java.sql.SQLException: rollback() should not be called while in auto-commit mode.

at net.sourceforge.jtds.jdbc.ConnectionJDBC2.rollback(ConnectionJDBC2.java:1893)

Here is the stack trace I get when I use a local datasource configured with spring:

2006-05-18 17:45:21,790 DEBUG [org.hibernate.impl.SessionImpl] before transaction completion

2006-05-18 17:45:21,790 DEBUG [org.hibernate.transaction.JDBCTransaction] re-enabling autocommit

2006-05-18 17:45:21,800 DEBUG [org.hibernate.transaction.JDBCTransaction] rolled back JDBC Connection

Note that this is the same application with the same jtds driver 1.1 or 1.2 and the same server jboss 4.0.2.

Also note that this problem doesn't occur with jboss 3.2.5.

Thanks in advance.

looyo · **Joined:** Thu Dec 18, 2008 12:45 am **Posts:** 4

I have the same problem.
ConnectionJDBC2.getAutoCommit()==true,but LocalManagedConnection.getAutoCommit()==false,LocalConnection.getAutoCommit()==false

java.sql.SQLException: commit() should not be called while in auto-commit mode.
at net.sourceforge.jtds.jdbc.ConnectionJDBC2.commit(ConnectionJDBC2.java:1878)
at org.jboss.resource.adapter.jdbc.local.LocalManagedConnection.jdbcCommit(LocalManagedConnection.java:781)
at org.jboss.resource.adapter.jdbc.local.LocalConnection.commit(LocalConnection.java:560)
at org.hibernate.transaction.JDBCTransaction.commitAndResetAutoCommit(JDBCTransaction.java:169)
at org.hibernate.transaction.JDBCTransaction.commit(JDBCTransaction.java:145)