Getting internal exception when trying to delete rows. The program ran for
a number of days without any issue before the error started occurring. The
service inserts a few records every second or so, runs a query on the
records every 60 seconds or so, and truncates records older than a week
every 60 minutes. (The inserts/queries/truncation happen simultaneously at
times.)
It uses connection pool in multi-threaded environment. There should only
be ~3 threads/connections accessing database concurrently at any given
time, but one of the threads is often running a lengthy query (either
SELECT or DELETE). We are using MULTI_THREADED=1 option to avoid serious
thread blocking issues. Without that option, H2 seemed
basically unusable to us. I know MULTI_THREADED is still considered
experimental. Any ideas to resolve appreciated, or let me know if I can
provide more info. I would try writing a test case, but again, things
were working fine for days before this issue started occurring.
Database created/running with v1.3.168 on a 32-bit linux machine using Java
6. Connection string:
- jdbc:h2:~/.dbdir/my-db;LOCK_TIMEOUT=10000;MULTI_THREADED=1
The trace file shows this initial error:
12-19 00:04:19 jdbc[3]: exception
org.h2.jdbc.JdbcSQLException: General error: "java.lang.RuntimeException:
Undo entry not written"; SQL statement:
DELETE FROM DataPoint WHERE endTimestamp < ? [50000-168]
at org.h2.message.DbException.getJdbcSQLException(DbException.java:329)
at org.h2.message.DbException.get(DbException.java:158)
at org.h2.message.DbException.convert(DbException.java:281)
at org.h2.table.RegularTable.removeRow(RegularTable.java:379)
at org.h2.command.dml.Delete.update(Delete.java:93)
at org.h2.command.CommandContainer.update(CommandContainer.java:75)
at org.h2.command.Command.executeUpdate(Command.java:230)
at
org.h2.jdbc.JdbcPreparedStatement.executeUpdateInternal(JdbcPreparedStatement.java:156)
at
org.h2.jdbc.JdbcPreparedStatement.executeUpdate(JdbcPreparedStatement.java:142)
at
org.springframework.jdbc.core.JdbcTemplate$2.doInPreparedStatement(JdbcTemplate.java:818)
...etc...
Caused by: java.lang.RuntimeException: Undo entry not written
at org.h2.message.DbException.throwInternalError(DbException.java:228)
at org.h2.store.PageLog.addUndo(PageLog.java:486)
at org.h2.store.PageStore.update(PageStore.java:1055)
at org.h2.index.PageBtreeNode.remove(PageBtreeNode.java:362)
at org.h2.index.PageBtreeNode.remove(PageBtreeNode.java:324)
at org.h2.index.PageBtreeIndex.remove(PageBtreeIndex.java:241)
at org.h2.table.RegularTable.removeRow(RegularTable.java:361)
... 28 more
That was then followed by:
12-19 01:04:29 jdbc[4]: exception
org.h2.jdbc.JdbcSQLException: Row not found when trying to delete from
index "PUBLIC.IDX_RECEIVERUPLOADTS_DESC: ( /* key:1955899 */ 1955899,
17.666666666666668, TIMESTAMP '2012-12-18 15:48:44.565', 'sim-josh-001', 1,
'APJOSH1', '2', TIMESTAMP '2012-12-18 15:48:39.565', 'UPLOADED', TIMESTAMP
'2012-12-18 15:48:50.393')"; SQL statement:
DELETE FROM DataPoint WHERE endTimestamp < ? [90112-168]
at
org.h2.message.DbException.getJdbcSQLException(DbException.java:329)
at org.h2.message.DbException.get(DbException.java:169)
at org.h2.message.DbException.get(DbException.java:146)
at org.h2.index.PageBtreeLeaf.remove(PageBtreeLeaf.java:225)
at org.h2.index.PageBtreeNode.remove(PageBtreeNode.java:324)
at org.h2.index.PageBtreeNode.remove(PageBtreeNode.java:324)
at org.h2.index.PageBtreeNode.remove(PageBtreeNode.java:324)
at org.h2.index.PageBtreeIndex.remove(PageBtreeIndex.java:241)
at org.h2.table.RegularTable.removeRow(RegularTable.java:361)
at org.h2.command.dml.Delete.update(Delete.java:93)
at org.h2.command.CommandContainer.update(CommandContainer.java:75)
at org.h2.command.Command.executeUpdate(Command.java:230)
at
org.h2.jdbc.JdbcPreparedStatement.executeUpdateInternal(JdbcPreparedStatement.java:156)
at
org.h2.jdbc.JdbcPreparedStatement.executeUpdate(JdbcPreparedStatement.java:142)
After those initial exceptions, a repeating pattern every time the program
tries to delete records would occur. Table lock timeouts would occur (I
assume because the delete operation is running), with an eventual exception
"Row not found while deleting". Excerpt from trace file:
12-19 10:11:33 jdbc[4]: exception
org.h2.jdbc.JdbcSQLException: Timeout trying to lock table "DATAPOINT"; SQL
statement:
SELECT DISTINCT(receiverIdent) FROM DataPoint WHERE uploadStatus=? ORDER BY
receiverIdent ASC [50200-168]
12-19 10:11:35 jdbc[3]: exception
org.h2.jdbc.JdbcSQLException: Timeout trying to lock table "DATAPOINT"; SQL
statement:
insert into DataPoint (id, dataValue, endTimestamp, nodeIdent, nodeType,
receiverIdent, sensorInputIdent, startTimestamp, uploadStatus,
uploadStatusUpdated) values (null, ?, ?, ?, ?, ?, ?, ?, ?, ?) [50200-168]
12-19 10:11:39 jdbc[5]: exception
org.h2.jdbc.JdbcSQLException: Row not found when trying to delete from
index "PUBLIC.IDX_RECEIVERUPLOADTS_DESC: ( /* key:1955899 */ 1955899,
17.666666666666668, TIMESTAMP '2012-
12-18 15:48:44.565', 'sim-josh-001', 1, 'APJOSH1', '2', TIMESTAMP
'2012-12-18 15:48:39.565', 'UPLOADED', TIMESTAMP '2012-12-18
15:48:50.393')"; SQL statement:
DELETE FROM DataPoint WHERE endTimestamp < ? [90112-168]
at
org.h2.message.DbException.getJdbcSQLException(DbException.java:329)
at org.h2.message.DbException.get(DbException.java:169)
at org.h2.message.DbException.get(DbException.java:146)
at org.h2.index.PageBtreeLeaf.remove(PageBtreeLeaf.java:225)
at org.h2.index.PageBtreeNode.remove(PageBtreeNode.java:324)
at org.h2.index.PageBtreeNode.remove(PageBtreeNode.java:324)
at org.h2.index.PageBtreeNode.remove(PageBtreeNode.java:324)
at org.h2.index.PageBtreeIndex.remove(PageBtreeIndex.java:241)
at org.h2.table.RegularTable.removeRow(RegularTable.java:361)
at org.h2.command.dml.Delete.update(Delete.java:93)
at org.h2.command.CommandContainer.update(CommandContainer.java:75)
at org.h2.command.Command.executeUpdate(Command.java:230)
at
org.h2.jdbc.JdbcPreparedStatement.executeUpdateInternal(JdbcPreparedStatement.java:156)
at
org.h2.jdbc.JdbcPreparedStatement.executeUpdate(JdbcPreparedStatement.java:142)
at
org.springframework.jdbc.core.JdbcTemplate$2.doInPreparedStatement(JdbcTemplate.java:818)
...etc...
--
You received this message because you are subscribed to the Google Groups "H2
Database" group.
To view this discussion on the web visit
https://groups.google.com/d/msg/h2-database/-/rkbaekWMUw4J.
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.
