Hi Noel,
I did have some time today and I managed to write a test case (for 1.3.174)
:-)
When running the application you should get:
General error: "java.lang.RuntimeException: Undo entry not written"; SQL
statement:
UPDATE values_7 SET timeend = 21332841064754 WHERE timeend =
9223372036854775807 [50000-174]
at org.h2.message.DbException.getJdbcSQLException(DbException.java:332)
at org.h2.message.DbException.get(DbException.java:161)
at org.h2.message.DbException.convert(DbException.java:284)
at org.h2.table.RegularTable.removeRow(RegularTable.java:394)
at org.h2.table.Table.updateRows(Table.java:449)
at org.h2.command.dml.Update.update(Update.java:144)
at org.h2.command.CommandContainer.update(CommandContainer.java:79)
at org.h2.command.Command.executeUpdate(Command.java:253)
at org.h2.jdbc.JdbcStatement.executeInternal(JdbcStatement.java:181)
at org.h2.jdbc.JdbcStatement.execute(JdbcStatement.java:156)
at
com.gematronik.analyst.db.test.H2MultiThreadTest1$Inserter.run(H2MultiThreadTest1.java:121)
at java.lang.Thread.run(Thread.java:744)
Caused by: java.lang.RuntimeException: Undo entry not written
at org.h2.message.DbException.throwInternalError(DbException.java:231)
at org.h2.store.PageLog.addUndo(PageLog.java:485)
at org.h2.store.PageStore.update(PageStore.java:1061)
at org.h2.index.PageBtreeLeaf.remove(PageBtreeLeaf.java:239)
at org.h2.index.PageBtreeNode.remove(PageBtreeNode.java:332)
at org.h2.index.PageBtreeNode.remove(PageBtreeNode.java:332)
at org.h2.index.PageBtreeIndex.remove(PageBtreeIndex.java:239)
at org.h2.table.RegularTable.removeRow(RegularTable.java:376)
... 8 more
If the problem does not occur after 10 minutes just remove the database
file and restart the application. Usually I get the error around each 3
attempts...
The test case seems to be quite similar to the problem described here:
https://groups.google.com/forum/#!searchin/h2-database/Undo$20entry$20not$20written/h2-database/E_2gUWb-iGc/rkbaekWMUw4J
I'll be off until next year but I hope the test case will give you some
more hints!
Uli
--
You received this message because you are subscribed to the Google Groups "H2
Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/groups/opt_out.
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.concurrent.ThreadLocalRandom;
import java.util.concurrent.TimeUnit;
import org.h2.jdbcx.JdbcConnectionPool;
public class H2MultiThreadTest1
{
private Connection conn;
private final JdbcConnectionPool pool;
private int maxId = 20;
public H2MultiThreadTest1()
throws ClassNotFoundException, SQLException
{
System.out.println("Open connections...");
Class.forName("org.h2.Driver");
this.pool = JdbcConnectionPool.create("jdbc:h2:~/h2/test/db1;LOCK_TIMEOUT=86400000;MULTI_THREADED=1", "tester",
"test");
this.pool.setMaxConnections(Integer.MAX_VALUE);
// get one connection to keep the db open
this.conn = this.pool.getConnection();
}
public Connection getConnection()
throws SQLException
{
Connection con = this.pool.getConnection();
con.setAutoCommit(false);
return con;
}
private static String[] BASE_TABLE_CREATION =
{ "DROP TABLE IF EXISTS currvalues",
"CREATE TABLE currvalues (id INTEGER NOT NULL PRIMARY KEY, value VARCHAR NOT NULL, time BIGINT NOT NULL)" };
private static String[] ID_CREATION =
{
"DROP TABLE IF EXISTS values_%id%",
"CREATE TABLE values_%id% (time BIGINT NOT NULL, timeend BIGINT NOT NULL DEFAULT 9223372036854775807,value VARCHAR NOT NULL, PRIMARY KEY (time))",
"CREATE UNIQUE INDEX IF NOT EXISTS i_values_%id% on values_%id% (timeend)", };
public void createBaseTables()
throws SQLException
{
Statement stmt = this.conn.createStatement();
for (String sql : BASE_TABLE_CREATION)
{
stmt.execute(sql);
}
for (int id = 0; id < this.maxId; id++)
{
for (String sql : ID_CREATION)
{
sql = sql.replace("%id%", String.valueOf(id));
stmt.execute(sql);
}
stmt.execute("INSERT INTO currvalues VALUES(" + id + ",'0'," + System.nanoTime() + ")");
}
this.conn.commit();
stmt.close();
}
public void runTest()
{
System.out.println("Starting test...");
new Thread(new Inserter(), "Inserter").start();
new Thread(new Deletor(), "Deletor").start();
for (int i = 0; i < 2; i++)
{
new Thread(new Selector(), "Query_" + i).start();
}
new Thread(new Selector2(), "Sel2").start();
try
{
Thread.sleep(5000);
}
catch (InterruptedException exp)
{
System.err.println(exp);
}
}
private class Inserter
implements Runnable
{
@Override
public void run()
{
ThreadLocalRandom r = ThreadLocalRandom.current();
try
{
long value = 0;
boolean cont = true;
Connection con = getConnection();
while (cont)
{
int id = r.nextInt(H2MultiThreadTest1.this.maxId);
value++;
Statement stmt = con.createStatement();
stmt.execute("SELECT * from currvalues where 1=2 FOR UPDATE");
stmt.execute("SELECT * from values_" + id + " where 1=2 FOR UPDATE");
long time = System.nanoTime();
stmt.execute("UPDATE values_" + id + " SET timeend = " + time
+ " WHERE timeend = 9223372036854775807");
stmt.execute("UPDATE currvalues SET value='" + value + "', time = " + time + " WHERE id = " + id);
stmt.execute("INSERT INTO values_" + id + " (time, value) VALUES (" + time + ", '" + value + "')");
con.commit();
stmt.close();
}
con.close();
}
catch (SQLException exp)
{
System.err.println(exp);
exp.printStackTrace();
}
}
}
private class Deletor
implements Runnable
{
@Override
public void run()
{
try
{
while (true)
{
Connection con = getConnection();
Statement stmt = con.createStatement();
long time = System.nanoTime() - TimeUnit.MINUTES.toNanos(5);
for (int id = 0; id < H2MultiThreadTest1.this.maxId; id++)
{
stmt.execute("delete from values_" + id + " where timeend < " + time);
}
con.commit();
stmt.close();
con.close();
try
{
Thread.sleep(TimeUnit.MINUTES.toMillis(3));
}
catch (InterruptedException exp)
{
}
}
}
catch (SQLException exp)
{
System.err.println(exp);
exp.printStackTrace();
}
}
}
private class Selector
implements Runnable
{
@Override
public void run()
{
try
{
while (true)
{
Connection con = getConnection();
Statement stmt = con.createStatement();
ThreadLocalRandom r = ThreadLocalRandom.current();
long timeStart = System.nanoTime() - r.nextInt(1, 300) * TimeUnit.SECONDS.toNanos(1);
long timeEnd = Math.min(System.nanoTime(),
timeStart + r.nextInt(1, 300) * TimeUnit.SECONDS.toNanos(1));
long id = r.nextLong(H2MultiThreadTest1.this.maxId);
ResultSet rs = stmt.executeQuery("SELECT time \"time\", value \"value\" FROM values_" + id
+ " WHERE time <= " + timeEnd + " AND timeend >= " + timeStart
+ " ORDER BY \"time\" LIMIT 500000");
while (rs.next())
{
long t = rs.getLong(1);
String v = rs.getString(2);
}
rs.close();
stmt.close();
con.close();
try
{
Thread.sleep(TimeUnit.SECONDS.toMillis(1));
}
catch (InterruptedException exp)
{
}
}
}
catch (SQLException exp)
{
System.err.println(exp);
exp.printStackTrace();
}
}
}
private class Selector2
implements Runnable
{
@Override
public void run()
{
try
{
while (true)
{
Connection con = getConnection();
Statement stmt = con.createStatement();
ThreadLocalRandom r = ThreadLocalRandom.current();
long timeStart = System.nanoTime() - r.nextInt(1, 300) * TimeUnit.SECONDS.toNanos(1);
long timeEnd = Math.min(System.nanoTime(),
timeStart + r.nextInt(1, 300) * TimeUnit.SECONDS.toNanos(1));
long id1 = r.nextLong(H2MultiThreadTest1.this.maxId);
long id2 = r.nextLong(H2MultiThreadTest1.this.maxId);
String query = "SELECT %timefrom% \"time\", ifnull(v1.value, v.c1) \"%label1%\", ifnull(v2.value, v.c1) \"%label2%\" "
+ "FROM values('-nc-') v "
+ "LEFT OUTER JOIN ( "
+ " SELECT vi1.value "
+ " FROM values_%id1% vi1 "
+ " WHERE vi1.time = (SELECT min(time) FROM values_%id1% WHERE timeend > %timefrom%) AND vi1.time < %timefrom% "
+ ") v1 ON 1=1 "
+ "LEFT OUTER JOIN ( "
+ " SELECT vi2.value "
+ " FROM values_%id2% vi2 "
+ " WHERE vi2.time = (SELECT min(time) FROM values_%id2% WHERE timeend > %timefrom%) AND vi2.time < %timefrom% "
+ ") v2 ON 1=1 " + //
"UNION " + //
"SELECT times.time, v1.value \"%label1%\", v2.value \"%label2%\" " + //
"FROM " + //
" ( " + //
" SELECT DISTINCT v.time " + //
" FROM values_%id1% v " + //
" WHERE v.time BETWEEN %timefrom% and %timeto% " + //
" UNION " + //
" SELECT DISTINCT v.time " + //
" FROM values_%id2% v " + //
" WHERE v.time BETWEEN %timefrom% and %timeto% " + //
" ) AS times " + //
"LEFT OUTER JOIN " + //
" values_%id1% v1 " + //
" ON times.time=v1.time AND v1.time BETWEEN %timefrom% AND %timeto% " + //
"LEFT OUTER JOIN " + //
" values_%id2% v2 " + //
" ON times.time=v2.time AND v2.time BETWEEN %timefrom% AND %timeto% " + //
"ORDER BY \"time\" " + //
"LIMIT 500000 "; //
query = query.replaceAll("%timefrom%", String.valueOf(timeStart))
.replaceAll("%timeto%", String.valueOf(timeEnd)).replaceAll("%id1%", String.valueOf(id1))
.replaceAll("%id2%", String.valueOf(id2)).replaceAll("%label1%", "label1")
.replaceAll("%label2%", "label2");
ResultSet rs = stmt.executeQuery(query);
while (rs.next())
{
long t = rs.getLong(1);
String v = rs.getString(2);
String v2 = rs.getString(3);
}
rs.close();
stmt.close();
con.close();
try
{
Thread.sleep(TimeUnit.SECONDS.toMillis(1));
}
catch (InterruptedException exp)
{
}
}
}
catch (SQLException exp)
{
System.err.println(exp);
exp.printStackTrace();
}
}
}
public static void main(String[] args)
throws SQLException, ClassNotFoundException
{
H2MultiThreadTest1 test1 = new H2MultiThreadTest1();
test1.createBaseTables();
test1.runTest();
}
}
