db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Mike Matrigali <mikem_...@sbcglobal.net>
Subject Re: Issue with SYSCS_UTIL.SYSCS_COMPRESS_ TABLE
Date Fri, 05 Jun 2009 19:07:16 GMT
Without a repro it is hard to say for sure what is going on.  But if I 
had to guess, this has the feel of statement cache dependency being
broken.

Underneath what SYSCS_UTIL.SYSCS_COMPRESS_TABLE does is create a new
conglomerate and copy all the data from the old conglomerate to the
new conglomerate.  These conglomerate numbers are compiled into plans.
What is supposed to happen is that we get an exclusive lock on the
table which should stop all executing statements, and then the 
dependency manager is supposed to invalidate all plans after this
ddl statement is executed.

The fact that restarting the db fixes it makes me think it is an 
inmemory problem and not a database corruption problem.

compress doesn't really do anything special it counts on the standard
dependency code for all the ddl going through the alter table statement.

This error is not supposed to happen, but not sure what you can do until
the bug is fixed.

I can't tell from your program if autocommit is on or not. It would 
probably be best if you commit after each compress, if you are not doing
so already.  In general doing queries directly against the system 
catalogs can cause some concurrency problems for the whole system, 
though should not cause the errors you are seeing.  To have the 
background task have less affect on the rest of the system I might 
suggest doing the query and sending the results either to a temp in 
memory java structure or a temporary table so that you hold locks on
the system catalog for a little as possible.  This may mean that you
have to handle errors if ddl in your system from other threads is such
that tables might disappear from the time you gathered the list and when
you do the compress.



Sai Pullabhotla wrote:
> Hello,
> 
> Our application using Derby 10.4 (in embedded mode) has a background process
> that runs the SYSCS_UTIL.SYSCS_COMPRESS_TABLE procedure periodically. We
> have been reported the following error while this procedure is being
> executed and a SELECT statement came in at the same time:
> 
> 	com.linoma.dpa.dao.DAOException: The conglomerate (71,409) requested does
> not exist.
> 	com.linoma.dpa.runtime.JobFailedException: com.linoma.dpa.dao.DAOException:
> The conglomerate (71,409) requested does not exist.
> 	    at com.linoma.dpa.runtime.Job.checkLogin(Unknown Source)
> 	    at com.linoma.dpa.runtime.Job.run(Unknown Source)
> 	    at com.linoma.dpa.runtime.Runtime.executeProject(Unknown Source)
> 	    at com.linoma.dpa.runtime.Runtime.executeProject(Unknown Source)
> 	    at com.linoma.dpa.j2ee.RunProjectCommandServlet.doPost(Unknown Source)
> 	    at javax.servlet.http.HttpServlet.service(HttpServlet.java:637)
> 	    at javax.servlet.http.HttpServlet.service(HttpServlet.java:717)
> 	    at
> org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:290)
> 	    at
> org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
> 	    at com.linoma.dpa.security.SecurityFilter.doFilter(Unknown Source)
> 	    at
> org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
> 	    at
> org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
> 	    at
> org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:233)
> 	    at
> org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:191)
> 	    at
> org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:128)
> 	    at
> org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:102)
> 	    at
> org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)
> 	    at
> org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:286)
> 	    at
> org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:845)
> 	    at
> org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:583)
> 	    at
> org.apache.tomcat.util.net.JIoEndpoint$Worker.run(JIoEndpoint.java:447)
> 	    at java.lang.Thread.run(Thread.java:810)
> 	Caused by: com.linoma.dpa.security.LoginException:
> com.linoma.dpa.dao.DAOException: The conglomerate (71,409) requested does
> not exist.
> 	    at com.linoma.dpa.security.LoginUtilities.getUserDetails(Unknown
> Source)
> 	    ... 22 more
> 	Caused by: com.linoma.dpa.dao.DAOException: The conglomerate (71,409)
> requested does not exist.
> 	    at com.linoma.dpa.dao.rdbms.UserDAOImpl.getConsolidatedRoles(Unknown
> Source)
> 	    at com.linoma.dpa.dao.rdbms.UserDAOImpl.findUser(Unknown Source)
> 	    ... 23 more
> 	Caused by: java.sql.SQLException: The conglomerate (71,409) requested does
> not exist.
> 	    at
> org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(Unknown
> Source)
> 	    at org.apache.derby.impl.jdbc.Util.generateCsSQLException(Unknown
> Source)
> 	    at
> org.apache.derby.impl.jdbc.TransactionResourceImpl.wrapInSQLException(Unknown
> Source)
> 	    at
> org.apache.derby.impl.jdbc.TransactionResourceImpl.handleException(Unknown
> Source)
> 	    at org.apache.derby.impl.jdbc.EmbedConnection.handleException(Unknown
> Source)
> 	    at org.apache.derby.impl.jdbc.ConnectionChild.handleException(Unknown
> Source)
> 	    at org.apache.derby.impl.jdbc.EmbedStatement.executeStatement(Unknown
> Source)
> 	    at
> org.apache.derby.impl.jdbc.EmbedPreparedStatement.executeStatement(Unknown
> Source)
> 	    at
> org.apache.derby.impl.jdbc.EmbedPreparedStatement.executeQuery(Unknown
> Source)
> 	    at
> org.apache.tomcat.dbcp.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:93)
> 	    ... 25 more
> 	Caused by: ERROR XSAI2: The conglomerate (71,409) requested does not exist.
> 	    at org.apache.derby.iapi.error.StandardException.newException(Unknown
> Source)
> 	    at
> org.apache.derby.impl.store.access.btree.index.B2IFactory.readConglomerate(Unknown
> Source)
> 	    at
> org.apache.derby.impl.store.access.RAMAccessManager.conglomCacheFind(Unknown
> Source)
> 	    at
> org.apache.derby.impl.store.access.RAMTransaction.findExistingConglomerate(Unknown
> Source)
> 	    at org.apache.derby.impl.store.access.RAMTransaction.openScan(Unknown
> Source)
> 	    at
> org.apache.derby.impl.store.access.BackingStoreHashTableFromScan.<init>(Unknown
> Source)
> 	    at
> org.apache.derby.impl.store.access.RAMTransaction.createBackingStoreHashtableFromScan(Unknown
> Source)
> 	    at org.apache.derby.impl.sql.execute.HashScanResultSet.openCore(Unknown
> Source)
> 	    at org.apache.derby.impl.sql.execute.JoinResultSet.openRight(Unknown
> Source)
> 	    at org.apache.derby.impl.sql.execute.JoinResultSet.openCore(Unknown
> Source)
> 	    at org.apache.derby.impl.sql.execute.JoinResultSet.openCore(Unknown
> Source)
> 	    at
> org.apache.derby.impl.sql.execute.ProjectRestrictResultSet.openCore(Unknown
> Source)
> 	    at
> org.apache.derby.impl.sql.execute.UnionResultSet.getNextRowCore(Unknown
> Source)
> 	    at
> org.apache.derby.impl.sql.execute.SortResultSet.getRowFromResultSet(Unknown
> Source)
> 	    at
> org.apache.derby.impl.sql.execute.SortResultSet.getNextRowFromRS(Unknown
> Source)
> 	    at org.apache.derby.impl.sql.execute.SortResultSet.loadSorter(Unknown
> Source)
> 	    at org.apache.derby.impl.sql.execute.SortResultSet.openCore(Unknown
> Source)
> 	    at org.apache.derby.impl.sql.execute.SortResultSet.openCore(Unknown
> Source)
> 	    at
> org.apache.derby.impl.sql.execute.BasicNoPutResultSetImpl.open(Unknown
> Source)
> 	    at org.apache.derby.impl.sql.GenericPreparedStatement.execute(Unknown
> Source)
> 	    ... 29 more
> 
> I'm not sure if this is expected or not. I would like to know if it is (or
> is not) recommended to run the SYSCS_UTIL.SYSCS_COMPRESS_TABLE procedure
> during the normal operation of an application. What kind of locks are
> acquired during the execution of this procedure? The documentation says -
> 
> This procedure acquires an exclusive table lock on the table being
> compressed.
> All statement plans dependent on the table or its indexes are invalidated.
> (I'm not sure what exactly this means).
> 
> The important thing to note is - all other subsequent SELECT queries after
> the compression was done have also failed with the same exact error
> referring to same conglomerate. We had to restart the database/application
> to fix the issue. Is this something expected too? Or is this something to do
> with the state of my connection in the connection pool.
> 
> We perform the following procedure to compress all the tables:
> 
>         String sql = "select schemaname, tablename from sys.sysschemas s, "
>             + "sys.systables t where s.schemaid=t.schemaid and
> t.tabletype='T'";
>         Statement stmt = conn.createStatement();
>         ResultSet rs = null;
>         CallableStatement cs = null;
>         try {
>             rs = stmt.executeQuery(sql);
>             cs = conn.prepareCall("call SYSCS_UTIL.SYSCS_COMPRESS_TABLE(?,
> ?, ?)");
>             while (rs.next()) {
>                 String schema = rs.getString(1).trim();
>                 String table = rs.getString(2).trim();
>                 cs.setString(1, schema);
>                 cs.setString(2, table);
>                 cs.setShort(3, (short) 1);
>                 cs.execute();
>             }
>         }
> 
> Your help/feedback is greatly appreciated.
> 
> Thanks. 


Mime
View raw message