SQLException when saving queries

View: New views

2 Messages — Rating Filter: Alert me

SQLException when saving queries

by Joerg.Florin :: Rate this Message:

Reply to Author | View Threaded | Show Only this Message

Hi,

We are running almost flawlessly Scarab 0.21 with Oracle 10g on a Sun Server with Tomcat for almost a year now.

One of my users created a personal query and wanted to save it as a module scoped query and I wasn't able to approve this query (SQLException). From this moment everyone gets an SQLException when he tries to save a private or module query.

I get rid off the "aprove exception" by marking the query as approved directly in the SCARAB_QUERY table. Unfortunately an SQLException is still thrown if someone wants to save a query. The exception is reported below. I found out, that the violated constraint is a unique index over almost all columns:

CREATE UNIQUE INDEX SCARAB_R_MODULE_USER_ATTRI_U_1 ON SCARAB_R_MODULE_USER_ATTRIBUTE (LIST_ID ASC, MODULE_ID ASC, USER_ID ASC, ISSUE_TYPE_ID ASC, ATTRIBUTE_ID ASC, INTERNAL_ATTRIBUTE ASC);

Is there a way to fix this inconsisteny? May I manipulate any "NEXT_ID's" int the ID_TABLE or delete any obsolete rows in the SCRAB_R_MODULE_USER_ATTRIBUTE? Is the index definition wrong?

If it is helpful I can provide dumps of the tables SCRAB_R_MODULE_USER_ATTRIBUTE or ID_TABLE.

Joerg Florin
Techniker Krankenkasse
Hauptverwaltung
IT-Systeme (ITS.4 - Systemframeworks)
Bramfelder Straße 140, D-22305 Hamburg

Tel: +49 (40) 69 09 - 25 75
Fax: +49 (40) 69 09 - 8 25 75
E-Mail: joerg.florin@...
TK-Website: www.tk-online.de

org.apache.turbine.TurbineException: org.apache.torque.TorqueException: java.sql.SQLException: ORA-00001: unique constraint (SCARAB.SCARAB_R_MODULE_USER_ATTRI_U_1) violated

at org.apache.turbine.pipeline.DefaultActionValve.invoke(DefaultActionValve.java:104)
at org.apache.turbine.pipeline.TurbinePipeline.invokeNext(TurbinePipeline.java:231)
at org.apache.turbine.pipeline.DefaultACLCreationValve.invoke(DefaultACLCreationValve.java:127)
at org.apache.turbine.pipeline.TurbinePipeline.invokeNext(TurbinePipeline.java:231)
at org.tigris.scarab.pipeline.SetLocaleValve.invoke(SetLocaleValve.java:95)
at org.apache.turbine.pipeline.TurbinePipeline.invokeNext(TurbinePipeline.java:231)
at org.tigris.scarab.pipeline.FreshenUserValve.invoke(FreshenUserValve.java:168)
at org.apache.turbine.pipeline.TurbinePipeline.invokeNext(TurbinePipeline.java:231)
at org.apache.turbine.pipeline.DefaultSessionValidationValve.invoke(DefaultSessionValidationValve.java:131)
at org.apache.turbine.pipeline.TurbinePipeline.invokeNext(TurbinePipeline.java:231)
at org.apache.turbine.pipeline.DefaultLoginValve.invoke(DefaultLoginValve.java:109)
at org.apache.turbine.pipeline.TurbinePipeline.invokeNext(TurbinePipeline.java:231)
at org.tigris.scarab.pipeline.AnonymousLoginValve.invoke(AnonymousLoginValve.java:66)
at org.apache.turbine.pipeline.TurbinePipeline.invokeNext(TurbinePipeline.java:231)
at org.tigris.scarab.pipeline.NTLMLoginValve.invoke(NTLMLoginValve.java:76)
at org.apache.turbine.pipeline.TurbinePipeline.invokeNext(TurbinePipeline.java:231)
at org.apache.turbine.pipeline.DefaultSessionTimeoutValve.invoke(DefaultSessionTimeoutValve.java:121)
at org.apache.turbine.pipeline.TurbinePipeline.invokeNext(TurbinePipeline.java:231)
at org.tigris.scarab.pipeline.DetermineTargetValve.invoke(DetermineTargetValve.java:118)
at org.apache.turbine.pipeline.TurbinePipeline.invokeNext(TurbinePipeline.java:231)
at org.apache.turbine.pipeline.DetermineActionValve.invoke(DetermineActionValve.java:107)
at org.apache.turbine.pipeline.TurbinePipeline.invokeNext(TurbinePipeline.java:231)
at org.tigris.scarab.pipeline.SetInfoMessageValve.invoke(SetInfoMessageValve.java:76)
at org.apache.turbine.pipeline.TurbinePipeline.invokeNext(TurbinePipeline.java:231)
at org.tigris.scarab.pipeline.ResetCacheValve.invoke(ResetCacheValve.java:72)
at org.apache.turbine.pipeline.TurbinePipeline.invokeNext(TurbinePipeline.java:231)
at org.tigris.scarab.pipeline.DetermineCharsetValve.invoke(DetermineCharsetValve.java:93)
at org.apache.turbine.pipeline.TurbinePipeline.invokeNext(TurbinePipeline.java:231)
at org.tigris.scarab.pipeline.TimingInfoValve.invoke(TimingInfoValve.java:108)
at org.apache.turbine.pipeline.TurbinePipeline.invokeNext(TurbinePipeline.java:231)
at org.apache.turbine.pipeline.TurbinePipeline.invoke(TurbinePipeline.java:213)
at org.apache.turbine.Turbine.doGet(Turbine.java:298)
at org.apache.turbine.Turbine.doPost(Turbine.java:343)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:710)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:803)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:269)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:188)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:210)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:174)
at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:433)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:127)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:117)
at org.apache.catalina.valves.FastCommonAccessLogValve.invoke(FastCommonAccessLogValve.java:482)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:108)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:151)
at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:870)
at org.apache.coyote.http11.Http11BaseProtocol$Http11ConnectionHandler.processConnection(Http11BaseProtocol.java:665)
at org.apache.tomcat.util.net.PoolTcpEndpoint.processSocket(PoolTcpEndpoint.java:528)
at org.apache.tomcat.util.net.LeaderFollowerWorkerThread.runIt(LeaderFollowerWorkerThread.java:81)
at org.apache.tomcat.util.threads.ThreadPool$ControlRunnable.run(ThreadPool.java:685)
at java.lang.Thread.run(Thread.java:595)
Caused by: org.apache.torque.TorqueException: java.sql.SQLException: ORA-00001: unique constraint (SCARAB.SCARAB_R_MODULE_USER_ATTRI_U_1) violated

at org.apache.torque.util.BasePeer.throwTorqueException(BasePeer.java:103)
at org.apache.torque.util.BasePeer.insertOrUpdateRecord(BasePeer.java:626)
at org.apache.torque.util.BasePeer.doInsert(BasePeer.java:516)
at org.tigris.scarab.om.BaseRModuleUserAttributePeer.doInsert(BaseRModuleUserAttributePeer.java:224)
at org.tigris.scarab.om.BaseRModuleUserAttributePeer.doInsert(BaseRModuleUserAttributePeer.java:588)
at org.tigris.scarab.om.BaseRModuleUserAttribute.save(BaseRModuleUserAttribute.java:939)
at org.tigris.scarab.om.BaseRModuleUserAttribute.save(BaseRModuleUserAttribute.java:903)
at org.tigris.scarab.om.BaseRModuleUserAttribute.save(BaseRModuleUserAttribute.java:883)
at org.tigris.scarab.om.MITList.getCommonRModuleUserAttributes(MITList.java:603)
at org.tigris.scarab.actions.Search.doSavequery(Search.java:302)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:585)
at org.apache.turbine.modules.actions.TemplateAction.executeEvents(TemplateAction.java:160)
at org.apache.turbine.modules.ActionEvent.perform(ActionEvent.java:144)
at org.apache.turbine.modules.actions.TemplateSecureAction.perform(TemplateSecureAction.java:90)
at org.apache.turbine.modules.ActionEvent.execute(ActionEvent.java:179)
at org.apache.turbine.pipeline.DefaultActionValve.executeAction(DefaultActionValve.java:139)
at org.apache.turbine.pipeline.DefaultActionValve.invoke(DefaultActionValve.java:99)
... 50 more
Caused by: java.sql.SQLException: ORA-00001: unique constraint (SCARAB.SCARAB_R_MODULE_USER_ATTRI_U_1) violated

at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:331)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:288)
at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:745)
at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:219)
at oracle.jdbc.driver.T4CPreparedStatement.executeForRows(T4CPreparedStatement.java:970)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1190)
at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3370)
at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:3454)
at com.workingdogs.village.Record.saveWithInsert(Record.java:410)
at com.workingdogs.village.Record.save(Record.java:220)
at com.workingdogs.village.Record.save(Record.java:190)
at org.apache.torque.util.BasePeer.insertOrUpdateRecord(BasePeer.java:622)
... 68 more

Re: SQLException when saving queries

by Joerg.Florin :: Rate this Message:

Reply to Author | View Threaded | Show Only this Message

Hello,

I additionally found out, that the exception is only thrown, if I save a query with "All" ticket types checked. If I select a single ticket type, the query can be saved als "private" or "public" without an exception.

Joerg Florin
Techniker Krankenkasse
Hauptverwaltung
IT-Systeme (ITS.4 - Systemframeworks)
Bramfelder Straße 140, D-22305 Hamburg

Tel: +49 (40) 69 09 - 25 75
Fax: +49 (40) 69 09 - 8 25 75
E-Mail: joerg.florin@...
TK-Website: www.tk-online.de