"java.lang.IllegalArgumentException: nanos > 999999999 or < 0"

[Wang Peihan]

Hi,

I'm developing an Java application using H2, and have encounter a JDBC
exception below:

110718135417.873|WARN,DUMPER|DBAgt.java,BakMibRawAlm,767|0|bak
MIB_RAW_ALM [4000000000008,4000000002108] exception
org.h2.jdbc.JdbcSQLException: General error:
"java.lang.IllegalArgumentException: nanos > 999999999 or < 0"; SQL
statement:
INSERT INTO MIB_RAW_ALM_HIS (SELECT * FROM MIB_RAW_ALM WHERE EVENT_ID
BETWEEN ? AND ?) [50000-157]
at
org.h2.message.DbException.getJdbcSQLException(DbException.java:327)
~[h2-1.3.157.jar:1.3.157]
at org.h2.message.DbException.get(DbException.java:156)
~[h2-1.3.157.jar:1.3.157]
at org.h2.message.DbException.convert(DbException.java:279)
~[h2-1.3.157.jar:1.3.157]
at org.h2.table.RegularTable.addRow(RegularTable.java:148)
~[h2-1.3.157.jar:1.3.157]
at org.h2.command.dml.Insert.addRow(Insert.java:162)
~[h2-1.3.157.jar:1.3.157]
at org.h2.command.dml.Insert.insertRows(Insert.java:137)
~[h2-1.3.157.jar:1.3.157]
at org.h2.command.dml.Insert.update(Insert.java:84)
~[h2-1.3.157.jar:1.3.157]
at
org.h2.command.CommandContainer.update(CommandContainer.java:71)
~[h2-1.3.157.jar:1.3.157]
at org.h2.command.Command.executeUpdate(Command.java:212)
~[h2-1.3.157.jar:1.3.157]
at
org.h2.jdbc.JdbcPreparedStatement.executeUpdateInternal(JdbcPreparedStatement.java:
143) ~[h2-1.3.157.jar:1.3.157]
at
org.h2.jdbc.JdbcPreparedStatement.executeUpdate(JdbcPreparedStatement.java:
129) ~[h2-1.3.157.jar:1.3.157]
at com.hp.snmpagt.edb.DBAgt.BakMibRawAlm(DBAgt.java:758)
~[snmpAgt.jar:na]
at
com.hp.snmpagt.main.MibRawAlmDumper._exec(MibRawAlmDumper.java:125)
[snmpAgt.jar:na]
at
com.hp.snmpagt.main.MibRawAlmDumper.run(MibRawAlmDumper.java:67)
[snmpAgt.jar:na]
at java.lang.Thread.run(Thread.java:619) [na:1.6.0.08]
Caused by: java.lang.IllegalArgumentException: nanos > 999999999 or <
0
at java.sql.Timestamp.setNanos(Timestamp.java:383) ~[na:
1.6.0.08]
at org.h2.store.Data.readValue(Data.java:702) ~[h2-1.3.157.jar:
1.3.157]
at org.h2.index.PageBtreeIndex.readRow(PageBtreeIndex.java:
336) ~[h2-1.3.157.jar:1.3.157]
at org.h2.index.PageBtree.getRow(PageBtree.java:174)
~[h2-1.3.157.jar:1.3.157]
at org.h2.index.PageBtree.find(PageBtree.java:116)
~[h2-1.3.157.jar:1.3.157]
at org.h2.index.PageBtreeLeaf.addRow(PageBtreeLeaf.java:146)
~[h2-1.3.157.jar:1.3.157]
at org.h2.index.PageBtreeLeaf.addRowTry(PageBtreeLeaf.java:
100) ~[h2-1.3.157.jar:1.3.157]
at org.h2.index.PageBtreeNode.addRowTry(PageBtreeNode.java:
200) ~[h2-1.3.157.jar:1.3.157]
at org.h2.index.PageBtreeIndex.addRow(PageBtreeIndex.java:93)
~[h2-1.3.157.jar:1.3.157]
at org.h2.index.PageBtreeIndex.add(PageBtreeIndex.java:84)
~[h2-1.3.157.jar:1.3.157]
at org.h2.table.RegularTable.addRow(RegularTable.java:130)
~[h2-1.3.157.jar:1.3.157]
... 11 common frames omitted

Is it an H2 bug? Is there any workaround?

Table MIB_RAW_ALM's definition:
private static final String _C_TBL_MIB_RAW_ALM = "CREATE TABLE IF NOT
EXISTS MIB_RAW_ALM ("
+ "EVENT_ID LONG NOT NULL, "
+ "COL_TS TIMESTAMP NOT NULL, "
+ "EVENT_TS TIMESTAMP NOT NULL, "
+ "PID VARCHAR(30) NOT NULL, "
+ "TID VARCHAR(50) NOT NULL, "
+ "MODULE VARCHAR(30) NOT NULL, "
+ "INST_NM VARCHAR(30) NOT NULL, "
+ "APP_CD VARCHAR(10) NOT NULL, "
+ "BUSI VARCHAR(10) NOT NULL, "
+ "KPI_FLAG VARCHAR(10) NOT NULL, "
+ "ALM_CODE INTEGER NOT NULL, "
+ "ALM_NM VARCHAR(255) NOT NULL, "
+ "ALM_GRADE INTEGER NOT NULL, "
+ "ALM_TYPE INTEGER NOT NULL, "
+ "ALM_IND VARCHAR(10), "
+ "ALM_CONTENT VARCHAR(2000), "
+ "EXP_ADV VARCHAR(2000), "
+ "PROC_FLAG INTEGER NOT NULL, " + "PROC_INFO VARCHAR(2000))";

private static final String _C_UK1_MIB_RAW_ALM = "CREATE UNIQUE INDEX
IF NOT EXISTS "
+ "UK1_MIB_RAW_ALM ON MIB_RAW_ALM(EVENT_ID)";

private static final String _C_IX1_MIB_RAW_ALM = "CREATE INDEX IF NOT
EXISTS "
+ "IX1_MIB_RAW_ALM ON MIB_RAW_ALM(PROC_FLAG)";

private static final String _C_SEQ_MIB_RAW_ALM = "CREATE SEQUENCE IF
NOT EXISTS SEQ_MIB_RAW_ALM "
+ "START WITH 4000000000000 CACHE 1";

Table MIB_RAW_ALM_HIS for history data backup, almost same schema:
private static final String _C_TBL_MIB_RAW_ALM_HIS = "CREATE TABLE IF
NOT EXISTS MIB_RAW_ALM_HIS ("
+ "EVENT_ID LONG NOT NULL, "
+ "COL_TS TIMESTAMP NOT NULL, "
+ "EVENT_TS TIMESTAMP NOT NULL, "
+ "PID VARCHAR(30) NOT NULL, "
+ "TID VARCHAR(50) NOT NULL, "
+ "MODULE VARCHAR(30) NOT NULL, "
+ "INST_NM VARCHAR(30) NOT NULL, "
+ "APP_CD VARCHAR(10) NOT NULL, "
+ "BUSI VARCHAR(10) NOT NULL, "
+ "KPI_FLAG VARCHAR(10) NOT NULL, "
+ "ALM_CODE INTEGER NOT NULL, "
+ "ALM_NM VARCHAR(255) NOT NULL, "
+ "ALM_GRADE INTEGER NOT NULL, "
+ "ALM_TYPE INTEGER NOT NULL, "
+ "ALM_IND VARCHAR(10), "
+ "ALM_CONTENT VARCHAR(2000), "
+ "EXP_ADV VARCHAR(2000), "
+ "PROC_FLAG INTEGER NOT NULL, " + "PROC_INFO VARCHAR(2000))";

private static final String _C_IX1_MIB_RAW_ALM_HIS = "CREATE INDEX IF
NOT EXISTS "
+ "IX1_MIB_RAW_ALM_HIS ON MIB_RAW_ALM_HIS(EVENT_ID)";

private static final String _C_IX2_MIB_RAW_ALM_HIS = "CREATE INDEX IF
NOT EXISTS "
+ "IX2_MIB_RAW_ALM_HIS ON MIB_RAW_ALM_HIS(EVENT_TS)";

Related Java code:

final private static String _B_MIB_RAW_ALM = "INSERT INTO
MIB_RAW_ALM_HIS ("
+ "SELECT * FROM MIB_RAW_ALM WHERE EVENT_ID BETWEEN ? AND ?)";
final private static String _D_MIB_RAW_ALM = "DELETE FROM MIB_RAW_ALM
"
+ "WHERE EVENT_ID BETWEEN ? AND ?";

public static boolean BakMibRawAlm(long min_alm_id, long max_alm_id)
{
boolean rc_ = true;
Connection conn_ = null;
PreparedStatement stmt_ = null;
try {
conn_ = EDB.GetInstance()._cp.getConnection(); // obtain a
connection from pool
stmt_ = conn_.prepareStatement(_B_MIB_RAW_ALM);
stmt_.setLong(1, min_alm_id);
stmt_.setLong(2, max_alm_id);
stmt_.executeUpdate();
stmt_.close();

stmt_ = conn_.prepareStatement(_D_MIB_RAW_ALM);
stmt_.setLong(1, min_alm_id);
stmt_.setLong(2, max_alm_id);
stmt_.executeUpdate();
conn_.commit();
} catch (Exception e) {
Warn(0, e, "bak MIB_RAW_ALM [%d,%d] exception", min_alm_id,
max_alm_id);
rc_ = false;
} finally {
EDB.CloseHandle(conn_, stmt_, null);
}
return rc_;
}

The above code is OK when backup only several records from MIB_RAW_ALM
to MIB_RAW_ALM_HIS,
but when there are 2000 records, it throws the exception. Any
suggestions?

[Wang Peihan]

Sorry, I don't think it's an H2 bug now.

Perhaps it's caused by previous abnormal shutdown of H2.
(I think the db file is corrupted by a kill -9 operation)
I'v tried the case in a new test environment, everything is OK now.

[Thomas Mueller]

Hi,

It does look like a problem, kill -9 shouldn't result in a corrupt
database. Could you send me your test case, and / or the corrupt
database? I would like to investigate what the problem is.

Regards,
Thomas

[Thomas Mueller]

Hi,

This is strange. The checksum of the btree index page is correct,
meaning it doesn't look like a corruption of the database file. But
the timestamp in the index doesn't match the timestamp in the table
itself:

In the table: TIMESTAMP '2011-05-11 15:12:48.386'
in the index: TIMESTAMP '2011-05-11 15:12:48.-419306368'

So there is a negative value for the timestamp nanoseconds. That's
very strange. But there are also some other strange timestamps, some
with nanoseconds, and dates 0-12-31 00:00:00.259, -2010-06-06
00:00:12.936, 1970-01-01 00:01:08.018.

To me, it looks like the timestamp is overwritten somehow while
inserting the data. This might be possible because timestamps are
mutable (java.sql.Timestamp.setNanos, setTime).

Do you re-use the same timestamp object in your application, and use
one of those methods to update it in a different thread?

Regards,
Thomas

[Wang Peihan]

Thank you for your examination and analysis first.

Yes, I use Timestamp.setTime(long mills) to update a temporary
Timestamp object,
then call JDBC to insert into DB, and these operations are enveloped
in a loop.

So you mean I should always new a Timestamp object before each JDBC
call
for a Timestamp field ?

[Noel Grandin]

Surely H2 should copy any mutable data-structures coming from the user at first opportunity?
I'm happy to make sure a change, if you agree Thomas.

-- Noel.

[Noel Grandin]

Hi

That doesn't make any sense - we convert java.sql.Timestamp to a ValueTimestamp object internally, and convert back when
the user requests a value, so there is no way they could corrupt the data by writing to a Timestamp object.

-- Noel.

[Thomas Mueller]

Hi,

> Yes, I use Timestamp.setTime(long mills) to update a temporary
> Timestamp object,
> then call JDBC to insert into DB, and these operations are enveloped
> in a loop.

In theory, what you do should work. Except if you share the same
timestamp object by multiple threads (in which case the timestamp
object might change between reading the millis and the nanos, which
can't be solved).

Possibly it's a problem for this version of H2 even if you only use
one thread. There should be a special test case. Probably in the
newest version of H2 this problem is solved however, as
java.sql.Timestamp is no longer used internally (within the
ValueTimestamp object).

> So you mean I should always new a Timestamp object before each JDBC call for a Timestamp field ?

Well, if you use multiple threads, then you need to ensure each thread
uses its own object. Otherwise you should be fine.

Regards,
Thomas