[jira] Created: (ODE-687) Unable to delete from table BPEL_MEX_PROPS when deployed onto Jboss5 and MS SQL Server 2005

Unable to delete from table BPEL_MEX_PROPS when deployed onto Jboss5 and MS SQL Server 2005
-------------------------------------------------------------------------------------------

                 Key: ODE-687
                 URL: https://issues.apache.org/jira/browse/ODE-687
             Project: ODE
          Issue Type: Bug
          Components: BPEL Runtime
    Affects Versions: 1.3.3
         Environment: Jboss 5.1.0.GA, MS SQL Server 2005.
            Reporter: Pratik Bhatt


We have deployed ODE onto Jboss 5.1.0.GA. It seems to be running fine, but we keep getting errors when it tries to delete data from BPEL_MEX_PROPS table. I believe this is done as a house cleaning job for old instances.

What it boils down to is that, ODE/Hibernate Creates a delete query which has 'row value constructors', which is not supported by SQL Server 2005(as per our DBA), whereas it works fine on MySQL/Oracle.  

Attaching the properties file also

ode-axis2.prorperties file
ode-axis2.db.mode=EXTERNAL
ode-axis2.db.ext.dataSource=java:jdbc/odedb
ode.persistence=hibernate


ode-axis2.tx.factory.class=org.apache.ode.axis2.util.JBossFactory


ode-axis2.dao.factory=org.apache.ode.daohib.bpel.BpelDAOConnectionFactoryImpl
hibernate.dialect=org.hibernate.dialect.SQLServerDialect
hibernate.hbm2ddl.auto=update
hibernate.show_sql=true
hibernate.current_session_context_class=jta
hibernate.transaction.manager_lookup_class=org.hibernate.transaction.JBossTransactionManagerLookup


ode-axis2.process.dehydration.maximum.age=30000000


ode-axis2.process.dehydration.maximum.count=5000


Exact error is as shown below.


2009-10-26 12:36:36,837 INFO  [STDOUT] (ODEServer-30) Hibernate: delete from LARGE_DATA where ID in (? , ? , ? , ?)
2009-10-26 12:36:36,837 INFO  [STDOUT] (ODEServer-28) ERROR - GeronimoLog.error(108) |
org.hibernate.exception.GenericJDBCException: could not execute update query
        at org.hibernate.exception.SQLStateConverter.handledNonSpecificException(SQLStateConverter.java:126)
        at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:114)
        at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:66)
        at org.hibernate.hql.ast.exec.BasicExecutor.execute(BasicExecutor.java:107)
        at org.hibernate.hql.ast.QueryTranslatorImpl.executeUpdate(QueryTranslatorImpl.java:419)
        at org.hibernate.engine.query.HQLQueryPlan.performExecuteUpdate(HQLQueryPlan.java:283)
        at org.hibernate.impl.SessionImpl.executeUpdate(SessionImpl.java:1168)
        at org.hibernate.impl.QueryImpl.executeUpdate(QueryImpl.java:117)
        at org.apache.ode.daohib.bpel.HibernateDao.deleteByIds(HibernateDao.java:106)
        at org.apache.ode.daohib.bpel.ProcessDaoImpl.deleteMessages(ProcessDaoImpl.java:242)
        at org.apache.ode.daohib.bpel.ProcessDaoImpl.deleteInstances(ProcessDaoImpl.java:178)
        at org.apache.ode.bpel.engine.ProcessCleanUpRunnable$1.call(ProcessCleanUpRunnable.java:56)
        at org.apache.ode.bpel.engine.ProcessCleanUpRunnable$1.call(ProcessCleanUpRunnable.java:51)
        at org.apache.ode.scheduler.simple.SimpleScheduler.execTransaction(SimpleScheduler.java:218)
        at org.apache.ode.bpel.engine.ProcessCleanUpRunnable.run(ProcessCleanUpRunnable.java:50)
        at org.apache.ode.bpel.engine.BpelServerImpl$PolledRunnableProcessor$1.run(BpelServerImpl.java:632)
        at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:441)
        at java.util.concurrent.FutureTask$Sync.innerRun(FutureTask.java:303)
        at java.util.concurrent.FutureTask.run(FutureTask.java:138)
        at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:886)
        at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:908)
        at java.lang.Thread.run(Thread.java:619)
Caused by: java.sql.SQLException: Incorrect syntax near ','.
        at net.sourceforge.jtds.jdbc.SQLDiagnostic.addDiagnostic(SQLDiagnostic.java:365)
        at net.sourceforge.jtds.jdbc.TdsCore.tdsErrorToken(TdsCore.java:2781)
        at net.sourceforge.jtds.jdbc.TdsCore.nextToken(TdsCore.java:2224)
        at net.sourceforge.jtds.jdbc.TdsCore.getMoreResults(TdsCore.java:628)
        at net.sourceforge.jtds.jdbc.JtdsStatement.processResults(JtdsStatement.java:525)
        at net.sourceforge.jtds.jdbc.JtdsStatement.executeSQL(JtdsStatement.java:487)
        at net.sourceforge.jtds.jdbc.JtdsPreparedStatement.executeUpdate(JtdsPreparedStatement.java:421)
        at org.jboss.resource.adapter.jdbc.WrappedPreparedStatement.executeUpdate(WrappedPreparedStatement.java:365)
        at org.hibernate.hql.ast.exec.BasicExecutor.execute(BasicExecutor.java:98)
        ... 18 more






--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.

     [ https://issues.apache.org/jira/browse/ODE-687?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Pratik Bhatt updated ODE-687:
-----------------------------

    Description:
We have deployed ODE onto Jboss 5.1.0.GA. It seems to be running fine, but we keep getting errors when it tries to delete data from BPEL_MEX_PROPS table. I believe this is done as a house cleaning job for old instances.

What it boils down to is that, ODE/Hibernate Creates a delete query which has 'row value constructors', which is not supported by SQL Server 2005(as per our DBA), whereas it works fine on MySQL/Oracle.

The same issue occurs even with the LARGE_DATA Table

Attaching the properties file also

ode-axis2.prorperties file
ode-axis2.db.mode=EXTERNAL
ode-axis2.db.ext.dataSource=java:jdbc/odedb
ode.persistence=hibernate


ode-axis2.tx.factory.class=org.apache.ode.axis2.util.JBossFactory


ode-axis2.dao.factory=org.apache.ode.daohib.bpel.BpelDAOConnectionFactoryImpl
hibernate.dialect=org.hibernate.dialect.SQLServerDialect
hibernate.hbm2ddl.auto=update
hibernate.show_sql=true
hibernate.current_session_context_class=jta
hibernate.transaction.manager_lookup_class=org.hibernate.transaction.JBossTransactionManagerLookup


ode-axis2.process.dehydration.maximum.age=30000000


ode-axis2.process.dehydration.maximum.count=5000


Exact error is as shown below.


2009-10-26 12:36:36,842 INFO  [STDOUT] (ODEServer-22) Hibernate: delete from BPEL_MEX_PROPS where (MEX, NAME, VALUE) in ((?, ?, ?))
2009-10-26 12:36:36,844 WARN  [org.hibernate.util.JDBCExceptionReporter] (ODEServer-22) SQL Warning: 102, SQLState: S1000
2009-10-26 12:36:36,844 WARN  [org.hibernate.util.JDBCExceptionReporter] (ODEServer-22) Preparing the statement failed: Incorrect syntax near ','.
2009-10-26 12:36:36,844 WARN  [org.hibernate.util.JDBCExceptionReporter] (ODEServer-22) SQL Error: 102, SQLState: S1000
2009-10-26 12:36:36,844 ERROR [org.hibernate.util.JDBCExceptionReporter] (ODEServer-22) Incorrect syntax near ','.
2009-10-26 12:36:36,848 INFO  [STDOUT] (ODEServer-22) ERROR - GeronimoLog.error(108) |
org.hibernate.exception.GenericJDBCException: could not execute update query
        at org.hibernate.exception.SQLStateConverter.handledNonSpecificException(SQLStateConverter.java:126)
        at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:114)
        at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:66)
        at org.hibernate.hql.ast.exec.BasicExecutor.execute(BasicExecutor.java:107)
        at org.hibernate.hql.ast.QueryTranslatorImpl.executeUpdate(QueryTranslatorImpl.java:419)
        at org.hibernate.engine.query.HQLQueryPlan.performExecuteUpdate(HQLQueryPlan.java:283)
        at org.hibernate.impl.SessionImpl.executeUpdate(SessionImpl.java:1168)
        at org.hibernate.impl.QueryImpl.executeUpdate(QueryImpl.java:117)
        at org.apache.ode.daohib.bpel.HibernateDao.deleteByIds(HibernateDao.java:106)
        at org.apache.ode.daohib.bpel.ProcessDaoImpl.deleteMessages(ProcessDaoImpl.java:242)
        at org.apache.ode.daohib.bpel.ProcessDaoImpl.deleteInstances(ProcessDaoImpl.java:178)
        at org.apache.ode.bpel.engine.ProcessCleanUpRunnable$1.call(ProcessCleanUpRunnable.java:56)
        at org.apache.ode.bpel.engine.ProcessCleanUpRunnable$1.call(ProcessCleanUpRunnable.java:51)
        at org.apache.ode.scheduler.simple.SimpleScheduler.execTransaction(SimpleScheduler.java:218)
        at org.apache.ode.bpel.engine.ProcessCleanUpRunnable.run(ProcessCleanUpRunnable.java:50)
        at org.apache.ode.bpel.engine.BpelServerImpl$PolledRunnableProcessor$1.run(BpelServerImpl.java:632)
        at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:441)
        at java.util.concurrent.FutureTask$Sync.innerRun(FutureTask.java:303)
        at java.util.concurrent.FutureTask.run(FutureTask.java:138)
        at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:886)
        at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:908)
        at java.lang.Thread.run(Thread.java:619)
Caused by: java.sql.SQLException: Incorrect syntax near ','.
        at net.sourceforge.jtds.jdbc.SQLDiagnostic.addDiagnostic(SQLDiagnostic.java:365)
        at net.sourceforge.jtds.jdbc.TdsCore.tdsErrorToken(TdsCore.java:2781)
        at net.sourceforge.jtds.jdbc.TdsCore.nextToken(TdsCore.java:2224)
        at net.sourceforge.jtds.jdbc.TdsCore.getMoreResults(TdsCore.java:628)
        at net.sourceforge.jtds.jdbc.JtdsStatement.processResults(JtdsStatement.java:525)
        at net.sourceforge.jtds.jdbc.JtdsStatement.executeSQL(JtdsStatement.java:487)
        at net.sourceforge.jtds.jdbc.JtdsPreparedStatement.executeUpdate(JtdsPreparedStatement.java:421)
        at org.jboss.resource.adapter.jdbc.WrappedPreparedStatement.executeUpdate(WrappedPreparedStatement.java:365)
        at org.hibernate.hql.ast.exec.BasicExecutor.execute(BasicExecutor.java:98)
        ... 18 more


  was:
We have deployed ODE onto Jboss 5.1.0.GA. It seems to be running fine, but we keep getting errors when it tries to delete data from BPEL_MEX_PROPS table. I believe this is done as a house cleaning job for old instances.

What it boils down to is that, ODE/Hibernate Creates a delete query which has 'row value constructors', which is not supported by SQL Server 2005(as per our DBA), whereas it works fine on MySQL/Oracle.  

Attaching the properties file also

ode-axis2.prorperties file
ode-axis2.db.mode=EXTERNAL
ode-axis2.db.ext.dataSource=java:jdbc/odedb
ode.persistence=hibernate


ode-axis2.tx.factory.class=org.apache.ode.axis2.util.JBossFactory


ode-axis2.dao.factory=org.apache.ode.daohib.bpel.BpelDAOConnectionFactoryImpl
hibernate.dialect=org.hibernate.dialect.SQLServerDialect
hibernate.hbm2ddl.auto=update
hibernate.show_sql=true
hibernate.current_session_context_class=jta
hibernate.transaction.manager_lookup_class=org.hibernate.transaction.JBossTransactionManagerLookup


ode-axis2.process.dehydration.maximum.age=30000000


ode-axis2.process.dehydration.maximum.count=5000


Exact error is as shown below.


2009-10-26 12:36:36,837 INFO  [STDOUT] (ODEServer-30) Hibernate: delete from LARGE_DATA where ID in (? , ? , ? , ?)
2009-10-26 12:36:36,837 INFO  [STDOUT] (ODEServer-28) ERROR - GeronimoLog.error(108) |
org.hibernate.exception.GenericJDBCException: could not execute update query
        at org.hibernate.exception.SQLStateConverter.handledNonSpecificException(SQLStateConverter.java:126)
        at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:114)
        at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:66)
        at org.hibernate.hql.ast.exec.BasicExecutor.execute(BasicExecutor.java:107)
        at org.hibernate.hql.ast.QueryTranslatorImpl.executeUpdate(QueryTranslatorImpl.java:419)
        at org.hibernate.engine.query.HQLQueryPlan.performExecuteUpdate(HQLQueryPlan.java:283)
        at org.hibernate.impl.SessionImpl.executeUpdate(SessionImpl.java:1168)
        at org.hibernate.impl.QueryImpl.executeUpdate(QueryImpl.java:117)
        at org.apache.ode.daohib.bpel.HibernateDao.deleteByIds(HibernateDao.java:106)
        at org.apache.ode.daohib.bpel.ProcessDaoImpl.deleteMessages(ProcessDaoImpl.java:242)
        at org.apache.ode.daohib.bpel.ProcessDaoImpl.deleteInstances(ProcessDaoImpl.java:178)
        at org.apache.ode.bpel.engine.ProcessCleanUpRunnable$1.call(ProcessCleanUpRunnable.java:56)
        at org.apache.ode.bpel.engine.ProcessCleanUpRunnable$1.call(ProcessCleanUpRunnable.java:51)
        at org.apache.ode.scheduler.simple.SimpleScheduler.execTransaction(SimpleScheduler.java:218)
        at org.apache.ode.bpel.engine.ProcessCleanUpRunnable.run(ProcessCleanUpRunnable.java:50)
        at org.apache.ode.bpel.engine.BpelServerImpl$PolledRunnableProcessor$1.run(BpelServerImpl.java:632)
        at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:441)
        at java.util.concurrent.FutureTask$Sync.innerRun(FutureTask.java:303)
        at java.util.concurrent.FutureTask.run(FutureTask.java:138)
        at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:886)
        at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:908)
        at java.lang.Thread.run(Thread.java:619)
Caused by: java.sql.SQLException: Incorrect syntax near ','.
        at net.sourceforge.jtds.jdbc.SQLDiagnostic.addDiagnostic(SQLDiagnostic.java:365)
        at net.sourceforge.jtds.jdbc.TdsCore.tdsErrorToken(TdsCore.java:2781)
        at net.sourceforge.jtds.jdbc.TdsCore.nextToken(TdsCore.java:2224)
        at net.sourceforge.jtds.jdbc.TdsCore.getMoreResults(TdsCore.java:628)
        at net.sourceforge.jtds.jdbc.JtdsStatement.processResults(JtdsStatement.java:525)
        at net.sourceforge.jtds.jdbc.JtdsStatement.executeSQL(JtdsStatement.java:487)
        at net.sourceforge.jtds.jdbc.JtdsPreparedStatement.executeUpdate(JtdsPreparedStatement.java:421)
        at org.jboss.resource.adapter.jdbc.WrappedPreparedStatement.executeUpdate(WrappedPreparedStatement.java:365)
        at org.hibernate.hql.ast.exec.BasicExecutor.execute(BasicExecutor.java:98)
        ... 18 more

> Unable to delete from table BPEL_MEX_PROPS when deployed onto Jboss5 and MS SQL Server 2005
> -------------------------------------------------------------------------------------------
>
>                 Key: ODE-687
>                 URL: https://issues.apache.org/jira/browse/ODE-687
>             Project: ODE
>          Issue Type: Bug
>          Components: BPEL Runtime
>    Affects Versions: 1.3.3
>         Environment: Jboss 5.1.0.GA, MS SQL Server 2005.
>            Reporter: Pratik Bhatt
>
> We have deployed ODE onto Jboss 5.1.0.GA. It seems to be running fine, but we keep getting errors when it tries to delete data from BPEL_MEX_PROPS table. I believe this is done as a house cleaning job for old instances.
> What it boils down to is that, ODE/Hibernate Creates a delete query which has 'row value constructors', which is not supported by SQL Server 2005(as per our DBA), whereas it works fine on MySQL/Oracle.
> The same issue occurs even with the LARGE_DATA Table
> Attaching the properties file also
> ode-axis2.prorperties file
> ode-axis2.db.mode=EXTERNAL
> ode-axis2.db.ext.dataSource=java:jdbc/odedb
> ode.persistence=hibernate
> ode-axis2.tx.factory.class=org.apache.ode.axis2.util.JBossFactory
> ode-axis2.dao.factory=org.apache.ode.daohib.bpel.BpelDAOConnectionFactoryImpl
> hibernate.dialect=org.hibernate.dialect.SQLServerDialect
> hibernate.hbm2ddl.auto=update
> hibernate.show_sql=true
> hibernate.current_session_context_class=jta
> hibernate.transaction.manager_lookup_class=org.hibernate.transaction.JBossTransactionManagerLookup
> ode-axis2.process.dehydration.maximum.age=30000000
> ode-axis2.process.dehydration.maximum.count=5000
> Exact error is as shown below.
> 2009-10-26 12:36:36,842 INFO  [STDOUT] (ODEServer-22) Hibernate: delete from BPEL_MEX_PROPS where (MEX, NAME, VALUE) in ((?, ?, ?))
> 2009-10-26 12:36:36,844 WARN  [org.hibernate.util.JDBCExceptionReporter] (ODEServer-22) SQL Warning: 102, SQLState: S1000
> 2009-10-26 12:36:36,844 WARN  [org.hibernate.util.JDBCExceptionReporter] (ODEServer-22) Preparing the statement failed: Incorrect syntax near ','.
> 2009-10-26 12:36:36,844 WARN  [org.hibernate.util.JDBCExceptionReporter] (ODEServer-22) SQL Error: 102, SQLState: S1000
> 2009-10-26 12:36:36,844 ERROR [org.hibernate.util.JDBCExceptionReporter] (ODEServer-22) Incorrect syntax near ','.
> 2009-10-26 12:36:36,848 INFO  [STDOUT] (ODEServer-22) ERROR - GeronimoLog.error(108) |
> org.hibernate.exception.GenericJDBCException: could not execute update query
>         at org.hibernate.exception.SQLStateConverter.handledNonSpecificException(SQLStateConverter.java:126)
>         at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:114)
>         at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:66)
>         at org.hibernate.hql.ast.exec.BasicExecutor.execute(BasicExecutor.java:107)
>         at org.hibernate.hql.ast.QueryTranslatorImpl.executeUpdate(QueryTranslatorImpl.java:419)
>         at org.hibernate.engine.query.HQLQueryPlan.performExecuteUpdate(HQLQueryPlan.java:283)
>         at org.hibernate.impl.SessionImpl.executeUpdate(SessionImpl.java:1168)
>         at org.hibernate.impl.QueryImpl.executeUpdate(QueryImpl.java:117)
>         at org.apache.ode.daohib.bpel.HibernateDao.deleteByIds(HibernateDao.java:106)
>         at org.apache.ode.daohib.bpel.ProcessDaoImpl.deleteMessages(ProcessDaoImpl.java:242)
>         at org.apache.ode.daohib.bpel.ProcessDaoImpl.deleteInstances(ProcessDaoImpl.java:178)
>         at org.apache.ode.bpel.engine.ProcessCleanUpRunnable$1.call(ProcessCleanUpRunnable.java:56)
>         at org.apache.ode.bpel.engine.ProcessCleanUpRunnable$1.call(ProcessCleanUpRunnable.java:51)
>         at org.apache.ode.scheduler.simple.SimpleScheduler.execTransaction(SimpleScheduler.java:218)
>         at org.apache.ode.bpel.engine.ProcessCleanUpRunnable.run(ProcessCleanUpRunnable.java:50)
>         at org.apache.ode.bpel.engine.BpelServerImpl$PolledRunnableProcessor$1.run(BpelServerImpl.java:632)
>         at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:441)
>         at java.util.concurrent.FutureTask$Sync.innerRun(FutureTask.java:303)
>         at java.util.concurrent.FutureTask.run(FutureTask.java:138)
>         at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:886)
>         at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:908)
>         at java.lang.Thread.run(Thread.java:619)
> Caused by: java.sql.SQLException: Incorrect syntax near ','.
>         at net.sourceforge.jtds.jdbc.SQLDiagnostic.addDiagnostic(SQLDiagnostic.java:365)
>         at net.sourceforge.jtds.jdbc.TdsCore.tdsErrorToken(TdsCore.java:2781)
>         at net.sourceforge.jtds.jdbc.TdsCore.nextToken(TdsCore.java:2224)
>         at net.sourceforge.jtds.jdbc.TdsCore.getMoreResults(TdsCore.java:628)
>         at net.sourceforge.jtds.jdbc.JtdsStatement.processResults(JtdsStatement.java:525)
>         at net.sourceforge.jtds.jdbc.JtdsStatement.executeSQL(JtdsStatement.java:487)
>         at net.sourceforge.jtds.jdbc.JtdsPreparedStatement.executeUpdate(JtdsPreparedStatement.java:421)
>         at org.jboss.resource.adapter.jdbc.WrappedPreparedStatement.executeUpdate(WrappedPreparedStatement.java:365)
>         at org.hibernate.hql.ast.exec.BasicExecutor.execute(BasicExecutor.java:98)
>         ... 18 more

--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.