Sorry, in last entry I was referring to the error: org.h2.jdbc.JdbcSQLException: Statement was canceled or the session timed out;
when I said that the query: delete from sym_data_event where batch_id not in (select batch_id from sym_outgoing_batch where batch_id between 6 and 106 and status != 'OK') and batched = 1 and batch_id between 6 and 106 was causing it. I've solved it changing the configuration of SymmetricDS that was cancelling the query execution when it last more than 5 minutes. So, the problem left is the first one, caused by an update in SYM_DATA_EVENT. On Feb 22, 11:07 am, anavarrog <[email protected]> wrote: > The query that is causing these errors is: > > delete from sym_data_event where batch_id not in (select batch_id from > sym_outgoing_batch where batch_id between 6 and 106 and status != > 'OK') and batched = 1 and batch_id between 6 and 106 > > and sym_data_event is a table containing 2810225 entries. I've > launched that query in a quad core, 4GiB RAM machine and it finishes > without errors after ~ 15 minutes (881968 ms), deleting 101428 entries > from sym_data_event. It looks like the netbooks I'm using to run the > application (Atom N455 dual core 1.66GHz processors, 2GiB RAM) cannot > afford that kind of effort. I'll try to reduce the number of elements > to delete in every transaction hoping it will reduce the overhead. > > On Feb 21, 5:33 pm, anavarrog <[email protected]> wrote: > > > > > > > > > Hi all, > > > I'm using H2 database and SymmetricDS to synchronize data between > > several terminals: > > > * H2 version: 1.2.135 > > * Connection URL: jdbc:h2:~/path/to/ > > database;DB_CLOSE_DELAY=-1;CACHE_TYPE=SOFT_LRU;CIPHER=AES;LOCK_TIMEOUT=100000;MVCC=TRUE > > > and after an intensive stress test execution we've observed several > > exceptions in one of the virtual machines I'm using for testing. It > > starts showing this: > > > 2012-02-19 17:52:08,616 ERROR (AbstractServlet.java:295) Error while > > processing GET request for externalId: null, node: > > 90bba6e2-0e57-495b-9412-0c47819d99e5 at 10.80.18.30 (10.80.18.30) > > org.springframework.jdbc.UncategorizedSQLException: > > PreparedStatementCallback; uncategorized SQLException for SQL [ > > update sym_data_event set batch_id = ?, batched = ? where > > batched = ? > > and node_id = ? and channel_id = ? > > ]; SQL state [HY000]; error code [50000]; General error: > > "java.lang.RuntimeException: Unexpected code path"; SQL statement: > > > update sym_data_event set batch_id = ?, batched = ? where > > batched = ? > > and node_id = ? and channel_id = ? > > [50000-135]; nested exception is org.h2.jdbc.JdbcSQLException: > > General error: "java.lang.RuntimeException: Unexpected code path"; SQL > > statement: > > > update sym_data_event set batch_id = ?, batched = ? where > > batched = ? > > and node_id = ? and channel_id = ? > > [50000-135] > > at > > org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java: > > 83) > > at > > org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java: > > 80) > > at > > org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java: > > 80) > > at > > org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java: > > 607) > > at > > org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java: > > 792) > > at > > org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java: > > 850) > > at > > org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java: > > 858) > > at > > org.jumpmind.symmetric.service.impl.OutgoingBatchService.buildOutgoingBatches(OutgoingBatchService.java: > > 148) > > at > > org.jumpmind.symmetric.service.impl.DataExtractorService.extract(DataExtractorService.java: > > 286) > > at > > org.jumpmind.symmetric.transport.handler.PullResourceHandler.pull(PullResourceHandler.java: > > 59) > > at > > org.jumpmind.symmetric.web.PullServlet.handlePost(PullServlet.java: > > 66) > > at > > org.jumpmind.symmetric.web.PullServlet.handleGet(PullServlet.java: > > 49) > > at > > org.jumpmind.symmetric.web.SymmetricServlet.handleGet(SymmetricServlet.java: > > 133) > > at > > org.jumpmind.symmetric.web.AbstractServlet.doGet(AbstractServlet.java: > > 98) > > at javax.servlet.http.HttpServlet.service(HttpServlet.java:707) > > at javax.servlet.http.HttpServlet.service(HttpServlet.java:820) > > at > > org.mortbay.jetty.servlet.ServletHolder.handle(ServletHolder.java: > > 502) > > at org.mortbay.jetty.servlet.ServletHandler > > $CachedChain.doFilter(ServletHandler.java:1148) > > at org.jumpmind.symmetric.web.SymmetricFilter > > $SymmetricFilterChain.doFilter(SymmetricFilter.java:151) > > at org.jumpmind.symmetric.web.SymmetricFilter > > $SymmetricFilterChain.doFilter(SymmetricFilter.java:145) > > at > > org.jumpmind.symmetric.web.compression.CompressionFilter.doFilter(CompressionFilter.java: > > 157) > > at > > org.jumpmind.symmetric.web.CompressionFilter.doFilter(CompressionFilter.java: > > 73) > > at org.jumpmind.symmetric.web.SymmetricFilter > > $SymmetricFilterChain.doFilter(SymmetricFilter.java:143) > > at > > org.jumpmind.symmetric.web.NodeConcurrencyFilter.doFilter(NodeConcurrencyFilter.java: > > 74) > > at org.jumpmind.symmetric.web.SymmetricFilter > > $SymmetricFilterChain.doFilter(SymmetricFilter.java:143) > > at > > org.jumpmind.symmetric.web.InetAddressFilter.doFilter(InetAddressFilter.java: > > 89) > > at org.jumpmind.symmetric.web.SymmetricFilter > > $SymmetricFilterChain.doFilter(SymmetricFilter.java:143) > > at > > org.jumpmind.symmetric.web.AuthenticationFilter.doFilter(AuthenticationFilter.java: > > 68) > > at org.jumpmind.symmetric.web.SymmetricFilter > > $SymmetricFilterChain.doFilter(SymmetricFilter.java:143) > > at > > org.jumpmind.symmetric.web.SymmetricFilter.doFilter(SymmetricFilter.java: > > 76) > > at org.mortbay.jetty.servlet.ServletHandler > > $CachedChain.doFilter(ServletHandler.java:1139) > > at > > org.mortbay.jetty.servlet.ServletHandler.handle(ServletHandler.java: > > 378) > > at > > org.mortbay.jetty.handler.ContextHandler.handle(ContextHandler.java: > > 765) > > at > > org.mortbay.jetty.handler.HandlerCollection.handle(HandlerCollection.java: > > 114) > > at > > org.mortbay.jetty.handler.HandlerWrapper.handle(HandlerWrapper.java: > > 152) > > at org.mortbay.jetty.Server.handle(Server.java:324) > > at > > org.mortbay.jetty.HttpConnection.handleRequest(HttpConnection.java: > > 535) > > at org.mortbay.jetty.HttpConnection > > $RequestHandler.headerComplete(HttpConnection.java:865) > > at org.mortbay.jetty.HttpParser.parseNext(HttpParser.java:539) > > at org.mortbay.jetty.HttpParser.parseAvailable(HttpParser.java:212) > > at org.mortbay.jetty.HttpConnection.handle(HttpConnection.java:404) > > at > > org.mortbay.io.nio.SelectChannelEndPoint.run(SelectChannelEndPoint.java: > > 409) > > at org.mortbay.thread.QueuedThreadPool > > $PoolThread.run(QueuedThreadPool.java:520) > > Caused by: org.h2.jdbc.JdbcSQLException: General error: > > "java.lang.RuntimeException: Unexpected code path"; SQL statement: > > > update sym_data_event set batch_id = ?, batched = ? where > > batched = ? > > and node_id = ? and channel_id = ? > > [50000-135] > > at org.h2.message.DbException.getJdbcSQLException(DbException.java: > > 327) > > at org.h2.message.DbException.get(DbException.java:156) > > at org.h2.message.DbException.convert(DbException.java:279) > > at org.h2.table.RegularTable.removeRow(RegularTable.java:349) > > at org.h2.table.Table.updateRows(Table.java:400) > > at org.h2.command.dml.Update.update(Update.java:124) > > at org.h2.command.CommandContainer.update(CommandContainer.java:70) > > at org.h2.command.Command.executeUpdate(Command.java:199) > > at > > org.h2.jdbc.JdbcPreparedStatement.executeUpdateInternal(JdbcPreparedStatement.java: > > 141) > > at > > org.h2.jdbc.JdbcPreparedStatement.executeUpdate(JdbcPreparedStatement.java: > > 127) > > at > > org.apache.commons.dbcp.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java: > > 101) > > at org.springframework.jdbc.core.JdbcTemplate > > $2.doInPreparedStatement(JdbcTemplate.java:798) > > at > > org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java: > > 591) > > ... 39 more > > Caused by: java.lang.RuntimeException: Unexpected code path > > at org.h2.message.DbException.throwInternalError(DbException.java: > > 226) > > at org.h2.message.DbException.throwInternalError(DbException.java: > > 239) > > at org.h2.index.PageBtreeNode.addChild(PageBtreeNode.java:157) > > at org.h2.index.PageBtreeNode.remove(PageBtreeNode.java:356) > > at org.h2.index.PageBtreeNode.remove(PageBtreeNode.java:323) > > at org.h2.index.PageBtreeNode.remove(PageBtreeNode.java:323) > > at org.h2.index.PageBtreeIndex.remove(PageBtreeIndex.java:231) > > at org.h2.index.MultiVersionIndex.remove(MultiVersionIndex.java:148) > > at org.h2.table.RegularTable.removeRow(RegularTable.java:331) > > > after that exception, the database is already working fine, but, new > > exceptions appear: > > > 2012-02-19 17:53:51,065 INFO (PullService.java:70) Pull data received > > for ServerPollbook:00000:00000 > > 2012-02-19 17:54:52,796 ERROR (AbstractServlet.java:295) Error while > > processing GET request for externalId: null, node: > > 90bba6e2-0e57-495b-9412-0c47819d99e5 at 10.80.18.30 (10.80.18.30) > > org.springframework.dao.CannotAcquireLockException: > > PreparedStatementCallback; SQL [ > > update sym_data_event set batch_id = ?, batched = ? where > > batched = ? > > and node_id = ? and channel_id = ? > > ]; Timeout trying to lock table ; SQL statement: > > > update sym_data_event set batch_id = ?, batched = ? where > > batched = ? > > and node_id = ? and channel_id = ? > > ... > > read more » -- You received this message because you are subscribed to the Google Groups "H2 Database" group. To post to this group, send email to [email protected]. To unsubscribe from this group, send email to [email protected]. For more options, visit this group at http://groups.google.com/group/h2-database?hl=en.
