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 [email protected].
To post to this group, send email to [email protected].
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();
}
}
