Hello Thomas,
I'm encountering a consistent H2 corruption problem. From my testing, I
believe it involves the caching of select distinct query results. I am
seeing corruption occur consistently when a table reaches
MAX_MEMORY_ROWS_DISTINCT (default 10k) and a select distinct with inner
join is done on the table. Changing the value of this setting reliably
changes the corruption point.
Attached is the smallest reproducible test case I could create. The
MAX_MEMORY_ROWS_DISTINCT can be set below 500 but due to timing it may not
corrupt. Albeit nonsensical, the join on the second table in conjunction
with the select distinct appears a necessary factor in reproducing the bug.
The corruption always occurs after the maximum number of rows exceeds
MAX_MEMORY_ROWS_DISTINCT.
The corruption has been encountered on versions 1.3.163/170/172 of H2. Once
the database has reached this state it cannot be dumped.
Thanks
--
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 h2-database+unsubscr...@googlegroups.com.
To post to this group, send email to h2-database@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database?hl=en-US.
For more options, visit https://groups.google.com/groups/opt_out.
import java.io.File;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.concurrent.atomic.AtomicBoolean;
import org.h2.jdbc.JdbcSQLException;
/**
* A repeatable test case of corruption surrounding the MAX_MEMORY_ROWS_DISTINCT setting of H2
*
*/
public class H2CorruptionTest {
private static final String TEST_DB = "./h2.test";
private static final String TEST_TABLE_NAME = "t_tbl1";
private static final String TEST_TABLE_NAME2 = "t_tbl2";
private static final Integer MEMORY_DISTINCT_ROWS = 500;
private static final int MAX_CONNECTION_ATTEMPTS = 3;
private static int ageCounter = 0;
private static AtomicBoolean finished = new AtomicBoolean(false);
public static void doWork() throws Exception {
File dbFile = new File(TEST_DB + ".h2.db");
if (dbFile.delete()) {
System.out.println("Deleted old database");
}
Connection conn = openConnection();
conn.setAutoCommit(false);
addTable(conn);
startSelectThread();
for (int i = 1; i <= MEMORY_DISTINCT_ROWS * 4; ++i) {
if (i % 10 == 0) {
System.out.println("Inserted " + i + " rows");
}
beginTrans(conn);
try {
// Insert a new row
doInsert(conn);
commitTrans(conn);
} catch (Exception e) {
executeSQL("ROLLBACK;", conn);
throw new RuntimeException(e);
}
}
finished.set(true);
}
private static void beginTrans(Connection conn) throws Exception {
executeSQL("BEGIN;", conn);
}
private static void commitTrans(Connection conn) throws Exception {
executeSQL("COMMIT;", conn);
}
private static void rollbackTrans(Connection conn) throws Exception {
executeSQL("ROLLBACK;", conn);
}
private static Thread startSelectThread() {
Thread t = new Thread() {
@Override
public void run() {
System.out.println("\n\n---- Starting select thread ----");
try {
Connection conn = openConnection();
while (!finished.get()) {
beginTrans(conn);
try {
doDistinctSelect(conn);
commitTrans(conn);
} finally {
rollbackTrans(conn);
}
} // End while
} catch (Throwable t) {
System.err.println("Error in select thread: " + t);
throw new RuntimeException(t);
} finally {
System.out.println("---- Select thread exiting ----");
}
}
};
t.setDaemon(true);
t.start();
return t;
}
private static void doInsert(Connection conn) throws Exception {
StringBuilder sqlBuilder = new StringBuilder();
sqlBuilder.append("INSERT INTO ").append(TEST_TABLE_NAME);
sqlBuilder.append(" (age) values (");
sqlBuilder.append(ageCounter++ % (MEMORY_DISTINCT_ROWS * 2)).append(")");
executeSQL(sqlBuilder.toString(), conn);
sqlBuilder = new StringBuilder();
sqlBuilder.append("INSERT INTO ").append(TEST_TABLE_NAME2);
sqlBuilder.append(" (age_fk) values (");
sqlBuilder.append(ageCounter % (MEMORY_DISTINCT_ROWS * 2)).append(")");
executeSQL(sqlBuilder.toString(), conn);
}
private static int doDistinctSelect(Connection conn) throws Exception {
String sql = "SELECT distinct(age) FROM " + TEST_TABLE_NAME //
+ " INNER JOIN " + TEST_TABLE_NAME2 //
+ " ON age=age_fk";
PreparedStatement ps = conn.prepareStatement(sql);
ResultSet rs = ps.executeQuery();
try {
rs.getArray(1);
} catch (JdbcSQLException e) {
// Intentionally empty;
}
rs.last();
int numRows = rs.getRow();
ps.close();
return numRows;
}
private static void addTable(Connection conn) throws Exception {
StringBuilder sqlBuilder = new StringBuilder();
sqlBuilder.append("CREATE TABLE ").append(TEST_TABLE_NAME);
sqlBuilder.append(" (id INT PRIMARY KEY AUTO_INCREMENT NOT NULL,");
sqlBuilder.append("age INTEGER NOT NULL)");
executeSQL(sqlBuilder.toString(), conn);
sqlBuilder = new StringBuilder();
sqlBuilder.append("CREATE TABLE ").append(TEST_TABLE_NAME2);
sqlBuilder.append(" (id2 INT PRIMARY KEY AUTO_INCREMENT NOT NULL,");
sqlBuilder.append("age_fk INTEGER NOT NULL)");
executeSQL(sqlBuilder.toString(), conn);
}
private static Connection openConnection() throws Exception {
Connection conn = null;
String url = "jdbc:h2:"
+ TEST_DB //
+ ";DEFAULT_LOCK_TIMEOUT=30000;WRITE_DELAY=0;ACCESS_MODE_DATA=rwd;DB_CLOSE_ON_EXIT=FALSE;MAX_MEMORY_ROWS_DISTINCT=" //
+ MEMORY_DISTINCT_ROWS;
int retries = 0;
Exception failedConnectionException = null;
while (conn == null && retries < MAX_CONNECTION_ATTEMPTS) {
try {
System.out.println("Connecting to database: " + TEST_DB);
System.out.println("Connection url: " + url);
conn = DriverManager.getConnection(url, "test", "pass");
failedConnectionException = null;
} catch (Exception e) {
failedConnectionException = e;
System.err.println(" connection denied. cause: " + e.getMessage());
} finally {
retries++;
}
}
// bail out if necessary
if (failedConnectionException != null) {
throw failedConnectionException;
}
return conn;
}
private static void executeSQL(String sql, Connection conn) throws Exception {
PreparedStatement ps = conn.prepareStatement(sql);
ps.execute();
ps.close();
}
public static void main(String[] args) throws Exception {
doWork();
}
}
