[OFBiz] Users - 'SQLException: Cannot rollback a transactiona l connection' when importing XML

Tue Sep 21 17:55:54 EDT 2004

It's not version 7.1, that was the section of the specification it was  
referring to.

When a transaction is rolled back like this in the background then it  
is due usually to a timeout, or a hidden commit problem, like a foreign  
key violation with initially deferred turned on.

A couple of tips:

1. Try it with initially deferred turned off. This is set when the  
table is created, so you have to use a new database or use the  
drop/create foreign keys feature in WebTools.

2. If you have a question about what the code is doing: DIVE INTO IT!  
There is nothing to stop you from looking at the loop that imports the  
files to see if it is in its own transaction, and throw in some debug  
statements to verify that things are working as they seem.

You may also want to try it with a different database to see if you can  
get a better error message, like HypersonicSQL or Derby, which now  
seems to be working fine on Linux and Windows, though it has some  
issues on OS X.

-David

On Sep 21, 2004, at 2:00 PM, bjfree at free-man.net wrote:

> the first error says this is in the SQL driver.
> so that could be a memory leak, memory allocation, communication  
> between the
> SQL server and the Driver, the SQL server.
>
> could be versioning like David said.  notice the version is 7.1 .
>
> could be a bug in the driver, as well.
>
> probably putting a profiler on the SQL server side of the SQL driver  
> would
> help.
>
>
> -----Original Message-----
> From: Kyle Tippetts [mailto:ktippetts at zeroz.biz]
> Sent: Tuesday, September 21, 2004 5:37 PM
> To: OFBiz Users / Usage
> Subject: RE: [OFBiz] Users - 'SQLException: Cannot rollback a
> transactiona l connection' when importing XML
>
>
> Well that's the rub, isn't it. It's all random. I can do some just  
> fine,
> and then try the same ones the next time, and it pitches the error. For
> example, the first time, I get the error only 73 files in to the  
> import.
> The next time, those first 73 'import' just fine, but then I get the
> error 552 files later. And, when I check to see if the data actually
> made it to the database by looking in the party manager, nothing is
> there.
>
> For what it's worth, here's what I think is the germain stack trace:
>
> --snip--
>
> 216485[    TransactionImpl.java:691:INFO ] set rollback only
> (tx=bb14111130336663373339373336353830625f305f^@^@^@^@^@^@^@^@^@^@^@^@^ 
> @^@^@
> ^@^@^@^@^@^@^@^@^@^@030336663373339373336353830625f305f^@^@^@^@^@^@^@^@ 
> ^@^@^
> @^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@0)
> 216517[       SQLProcessor.java:154:ERROR]
> ---- exception report
> ----------------------------------------------------------
> Exception: java.sql.SQLException
> Message: Cannot commit a transactional connection: See JDBC 2.0  
> Optional
> Package Specification section 7.1 (p25)
> ---- stack trace
> ---------------------------------------------------------------
> java.sql.SQLException: Cannot commit a transactional connection: See
> JDBC 2.0 Optional Package Specification section 7.1 (p25)
> org.ofbiz.minerva.pool.jdbc.xa.wrapper.XAClientConnection.commit(XAClie 
> ntCon
> nection.java:250)
> org.ofbiz.entity.jdbc.SQLProcessor.commit(SQLProcessor.java:151)
> org.ofbiz.entity.jdbc.SQLProcessor.close(SQLProcessor.java:202)
> org.ofbiz.entity.datasource.GenericDAO.select(GenericDAO.java:486)
> org.ofbiz.entity.datasource.GenericDAO.select(GenericDAO.java:440)
> org.ofbiz.entity.datasource.GenericHelperDAO.findByPrimaryKey(GenericHe 
> lperD
> AO.java:90)
> org.ofbiz.entity.GenericDelegator.storeAll(GenericDelegator.java:1641)
> org.ofbiz.entity.GenericDelegator.storeAll(GenericDelegator.java:1601)
> org.ofbiz.entity.util.EntitySaxReader.writeValues(EntitySaxReader.java: 
> 213)
> org.ofbiz.entity.util.EntitySaxReader.parse(EntitySaxReader.java:196)
> org.ofbiz.entity.util.EntitySaxReader.parse(EntitySaxReader.java:157)
> com.darkcanyon.ofbiz.dataport.DataportServices.importDir(DataportServic 
> es.ja
> va:308)
> sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
> sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.ja 
> va:39
> )
> sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccesso 
> rImpl
> .java:25)
> java.lang.reflect.Method.invoke(Method.java:324)
> org.ofbiz.service.engine.StandardJavaEngine.serviceInvoker(StandardJava 
> Engin
> e.java:101)
> org.ofbiz.service.engine.StandardJavaEngine.runSync(StandardJavaEngine. 
> java:
> 63)
> org.ofbiz.service.ServiceDispatcher.runSync(ServiceDispatcher.java:313)
> org.ofbiz.service.ServiceDispatcher.runSync(ServiceDispatcher.java:194)
> org.ofbiz.service.GenericDispatcher.runSync(GenericDispatcher.java:110)
> com.darkcanyon.ofbiz.dataport.DataportServices.importData(DataportServi 
> ces.j
> ava:456)
> sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
> sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.ja 
> va:39
> )
> sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccesso 
> rImpl
> .java:25)
> java.lang.reflect.Method.invoke(Method.java:324)
> org.ofbiz.service.engine.StandardJavaEngine.serviceInvoker(StandardJava 
> Engin
> e.java:101)
> org.ofbiz.service.engine.StandardJavaEngine.runSync(StandardJavaEngine. 
> java:
> 63)
> org.ofbiz.service.ServiceDispatcher.runSync(ServiceDispatcher.java:313)
> org.ofbiz.service.ServiceDispatcher.runSync(ServiceDispatcher.java:194)
> org.ofbiz.service.GenericDispatcher.runSync(GenericDispatcher.java:110)
> sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
> sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.ja 
> va:39
> )
> sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccesso 
> rImpl
> .java:25)
> java.lang.reflect.Method.invoke(Method.java:324)
> bsh.Reflect.invokeOnMethod(Reflect.java:117)
> bsh.Reflect.invokeObjectMethod(Reflect.java:91)
> bsh.Name.invokeMethod(Name.java:689)
> bsh.BSHMethodInvocation.eval(BSHMethodInvocation.java:55)
> bsh.BSHPrimaryExpression.eval(BSHPrimaryExpression.java:69)
> bsh.BSHAssignment.eval(BSHAssignment.java:58)
> bsh.BSHBlock.eval(BSHBlock.java:79)
> bsh.BSHBlock.eval(BSHBlock.java:44)
> bsh.BSHTryStatement.eval(BSHTryStatement.java:86)
> bsh.BSHBlock.eval(BSHBlock.java:79)
> bsh.BSHBlock.eval(BSHBlock.java:44)
> bsh.BSHIfStatement.eval(BSHIfStatement.java:48)
> bsh.Interpreter.evalParsedScript(Interpreter.java:1104)
> org.ofbiz.base.util.OfbizBshBsfEngine.eval(OfbizBshBsfEngine.java:194)
> org.ofbiz.base.util.OfbizBshBsfEngine.exec(OfbizBshBsfEngine.java:208)
> com.ibm.bsf.BSFManager.exec(BSFManager.java:479)
> org.jpublish.action.ScriptAction.execute(ScriptAction.java:244)
> org.jpublish.action.ActionManager.execute(ActionManager.java:419)
> org.jpublish.repository.filesystem.ExtendedFileSystemRepository.execute 
> Actio
> ns(ExtendedFileSystemRepository.java:403)
> org.jpublish.repository.filesystem.ExtendedFileSystemRepository.get(Ext 
> ended
> FileSystemRepository.java:122)
> org.jpublish.RepositoryWrapper.get(RepositoryWrapper.java:103)
> org.jpublish.RepositoryWrapper.get(RepositoryWrapper.java:87)
> sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
> sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.ja 
> va:39
> )
> sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccesso 
> rImpl
> .java:25)
> java.lang.reflect.Method.invoke(Method.java:324)
> freemarker.ext.beans.BeansWrapper.invokeMethod(BeansWrapper.java:462)
> freemarker.ext.beans.OverloadedMethodModel.exec(OverloadedMethodModel.j 
> ava:1
> 08)
> freemarker.core.MethodCall._getAsTemplateModel(MethodCall.java:93)
> freemarker.core.Expression.getAsTemplateModel(Expression.java:89)
> freemarker.core.Expression.getStringValue(Expression.java:93)
> freemarker.core.DollarVariable.accept(DollarVariable.java:76)
> freemarker.core.Environment.visit(Environment.java:171)
> freemarker.core.MixedContent.accept(MixedContent.java:92)
> freemarker.core.Environment.visit(Environment.java:171)
> freemarker.core.Environment.process(Environment.java:156)
> freemarker.template.Template.process(Template.java:219)
> org.ofbiz.content.webapp.ftl.FreeMarkerViewRenderer.render(FreeMarkerVi 
> ewRen
> derer.java:115)
> org.jpublish.Template.merge(Template.java:198)
> org.ofbiz.content.webapp.view.JPublishWrapper.render(JPublishWrapper.ja 
> va:32
> 6)
> org.ofbiz.content.webapp.view.JPublishViewHandler.render(JPublishViewHa 
> ndler
> .java:80)
> org.ofbiz.content.webapp.control.RequestHandler.renderView(RequestHandl 
> er.ja
> va:518)
> org.ofbiz.content.webapp.control.RequestHandler.doRequest(RequestHandle 
> r.jav
> a:340)
> org.ofbiz.content.webapp.control.ControlServlet.doGet(ControlServlet.ja 
> va:19
> 0)
> org.ofbiz.content.webapp.control.ControlServlet.doPost(ControlServlet.j 
> ava:8
> 9)
> javax.servlet.http.HttpServlet.service(HttpServlet.java:709)
> javax.servlet.http.HttpServlet.service(HttpServlet.java:802)
> org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(Applic 
> ation
> FilterChain.java:237)
> org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFil 
> terCh
> ain.java:157)
> org.ofbiz.content.webapp.control.ContextFilter.doFilter(ContextFilter.j 
> ava:2
> 38)
> org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(Applic 
> ation
> FilterChain.java:186)
> org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFil 
> terCh
> ain.java:157)
> org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperVal 
> ve.ja
> va:214)
> org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveC 
> ontex
> t.java:104)
> org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java: 
> 520)
> org.apache.catalina.core.StandardContextValve.invokeInternal(StandardCo 
> ntext
> Valve.java:198)
> org.apache.catalina.core.StandardContextValve.invoke(StandardContextVal 
> ve.ja
> va:152)
> org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveC 
> ontex
> t.java:104)
> org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java: 
> 520)
> org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.jav 
> a:137
> )
> org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveC 
> ontex
> t.java:104)
> org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.jav 
> a:117
> )
> org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveC 
> ontex
> t.java:102)
> org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java: 
> 520)
> org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve 
> .java
> :109)
> org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveC 
> ontex
> t.java:104)
> org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java: 
> 535)
> org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveC 
> ontex
> t.java:102)
> org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java: 
> 520)
> org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:929)
> org.apache.coyote.tomcat5.CoyoteAdapter.service(CoyoteAdapter.java:160)
> org.apache.coyote.http11.Http11Processor.process(Http11Processor.java: 
> 799)
> org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process 
> Conne
> ction(Http11Protocol.java:705)
> org.apache.tomcat.util.net.TcpWorkerThread.runIt(PoolTcpEndpoint.java: 
> 577)
> org.apache.tomcat.util.threads.ThreadPool$ControlRunnable.run(ThreadPoo 
> l.jav
> a:683)
> java.lang.Thread.run(Thread.java:534)
>
> --snip--
>
> I also tried the closest JDBC drivers to postgresql 7.3.4 I could find
> that would work (the pg73jdbc3.jar file pitched several "method not
> implemented" errors during startup, so I used the pg74.215.jdbc3.jar),
> and I still get the error when doing the import.
>
> Another question: if each file *is* in it's own transaction, why is it
> that when the exception is thrown, *all* of the data that's supposedly
> been committed is rolled back? If the data was actually committed, I
> should see the client records in the party manager, shouldn't I? But
> they aren't there. Everything's being rolled back.
>
> Thanks,
> --Kyle
>
> On Mon, 2004-09-20 at 19:43, bjfree at free-man.net wrote:
>> take a few out of the import and try them.
>> look at the debug or log to see the errrors you are getting. post the  
>> log
> or
>> attacht it.
>> that would be the fastest way to get help.
>>
>> -----Original Message-----
>> From: Kyle Tippetts [mailto:ktippetts at zeroz.biz]
>> Sent: Monday, September 20, 2004 11:34 PM
>> To: users at lists.ofbiz.org
>> Subject: [OFBiz] Users - 'SQLException: Cannot rollback a  
>> transactional
>> connection' when importing XML
>>
>>
>> I am experiencing an SQLException when attempting to load XML data  
>> from
>> a custom-build data importer. I'm using the 8/28/2004 SVN code. Here  
>> is
>> the error:
>>
>> 172658[      SQLProcessor.java:154:ERROR]
>> ---exception report---------------------------
>> Exception: java.sql SQLException
>> Message: Cannot commit a transactional connection
>>
>> After reviewing Adrian Klingel's thread titled 'Struggling to read in
>> XML seed data', on this subject, it appears that I'm experiencing the
>> same problem he is, under very similar conditions: I'm trying to  
>> import
>> several thousand xml files into the ofbiz database (each xml file
>> contains several entity nodes). After several hundred, I start to get
>> this error, and nothing is committed to the database.
>>
>> However, I'm using the OFBiz-embedded Tomcat server. And, basically,  
>> I'm
>> using the import code from the Webtools Xml Import Dir jsp page in a
>> service.
>>
>> There is no rhyme or reason to when or where the exception is thrown.
>> Sometimes it happens 2 or 3 files into the import routine, sometimes
>> 7,467 files in. Sometimes it happens on a file that has an invalid  
>> field
>> name, sometimes it happens on a file with completely valid entity and
>> field names.
>>
>> It doesn't happen so much if I use a browser located on a different
>> machine than where the OFBiz server is running, but it still happens
>> randomly, even then.
>>
>> Here are the particulars on my setup:
>>
>> * Transaction Timeout set to 7200
>> * PostgreSQL 7.3.4
>> * Ofbiz code from 28 Aug 2004
>> * Using OFBiz-embedded Tomcat Server
>> * Using EntitySaxReader to parse the xml and store in the database.
>>
>> Any suggestions on where to look, what to try, etc. would be greatly
>> appreciated; we're trying to deploy and this is really cramping our
>> ability to do so.
>>
>> Thanks,
>> -- Kyle
>>
>> --------------------------------------------
>> --Kyle Tippetts
>> --Dark Canyon Development and Consulting
>> --ktippetts at zeroz.biz
>> --801 360 2348
>>
>>
>> _______________________________________________
>> Users mailing list
>> Users at lists.ofbiz.org
>> http://lists.ofbiz.org/mailman/listinfo/users
>>
>> _______________________________________________
>> Users mailing list
>> Users at lists.ofbiz.org
>> http://lists.ofbiz.org/mailman/listinfo/users
>>
>
>
> _______________________________________________
> Users mailing list
> Users at lists.ofbiz.org
> http://lists.ofbiz.org/mailman/listinfo/users
>
> _______________________________________________
> Users mailing list
> Users at lists.ofbiz.org
> http://lists.ofbiz.org/mailman/listinfo/users
-------------- next part --------------
A non-text attachment was scrubbed...
Name: smime.p7s
Type: application/pkcs7-signature
Size: 2363 bytes
Desc: not available
Url : http://lists.ofbiz.org/pipermail/users/attachments/20040921/4f99e426/smime-0001.bin