Hi,
in my application using H2 (1.4.196) i need to insert very large CLOB
values.
Unfortunately H2 fails here with the following error:
org.h2.jdbc.JdbcSQLException: Out of memory. [90108-196]
at org.h2.message.DbException.getJdbcSQLException(DbException.java:345)
at org.h2.message.DbException.get(DbException.java:168)
at org.h2.message.DbException.convert(DbException.java:289)
at org.h2.server.TcpServerThread.sendError(TcpServerThread.java:220)
at org.h2.server.TcpServerThread.run(TcpServerThread.java:160)
at java.lang.Thread.run(Thread.java:745)
Caused by: java.lang.OutOfMemoryError: Java heap space
at java.util.Arrays.copyOf(Arrays.java:2367)
at java.lang.String.(String.java:168)
at org.h2.value.Transfer.readValue(Transfer.java:673)
at org.h2.server.TcpServerThread.setParameters(TcpServerThread.java:251)
at org.h2.server.TcpServerThread.process(TcpServerThread.java:350)
at org.h2.server.TcpServerThread.run(TcpServerThread.java:158)
... 1 more
at org.h2.engine.SessionRemote.done(SessionRemote.java:629)
at org.h2.command.CommandRemote.executeUpdate(CommandRemote.java:209)
at
org.h2.jdbc.JdbcPreparedStatement.executeUpdateInternal(JdbcPreparedStatement.java:164)
at
org.h2.jdbc.JdbcPreparedStatement.executeUpdate(JdbcPreparedStatement.java:150)
at ClobInsert.insertClob(ClobInsert.java:82)
at ClobInsert.main(ClobInsert.java:36)
The H2Server was started like this:
java.exe -Xmx512M -cp ".\h2\bin\h2-1.4.196.jar" org.h2.tools.Server
-tcpPort 9092 -tcp
The connect String is:
jdbc:h2:tcp://localhost:9092/C:\temp\testdb
My sample program for reproduction (attached at the end) is capable of
inserting a 50MB CLOB, for 100MB it fails.
Increasing XMX only has a little impact, and especially for 32bit I cannot
increase XMX to much more than 1200M, so that I am unable to insert a 200MB
CLOB on 32bit java running the H2 Server.
Is there any setting to work around this problem or is this simply a bug?
Sample Program for reproduction:
public class ClobInsert {
/**
* Main
* @param args Unused
*/
public static void main(String[] args) {
String url = "jdbc:h2:tcp://localhost:9092/C:\\temp\\testdb";
try (Connection connection = DriverManager.getConnection(url, "user"
, "psw")) {
createTable(connection);
try {
insertClob(connection, 50);
insertClob(connection, 100);
insertClob(connection, 200);
insertClob(connection, 300);
insertClob(connection, 400);
insertClob(connection, 500);
insertClob(connection, 600);
insertClob(connection, 700);
} finally {
dropTable(connection);
}
} catch (SQLException | IOException ex) {
ex.printStackTrace();
}
}
/**
* Inserts a single CLOB of the given size
* @param connection H2 Connection
* @param mb The amount of MB to insert as CLOB
* @throws IOException If file writing failed
* @throws SQLException If database error occurred
*/
private static void insertClob(Connection connection, int mb) throws
IOException, SQLException {
System.out.print("Attempting to insert " + mb + " MB of data...");
File temp = File.createTempFile("tmp", ".clob");
temp.deleteOnExit();
writeBytes(temp, mb);
try {
Reader reader = new InputStreamReader(new FileInputStream(temp
));
try (PreparedStatement st = connection.prepareStatement(
"insert into CLOBTAB (ID, value) values (?, ?)")) {
st.setInt(1, mb);
try {
st.setCharacterStream(2, reader);
} catch (AbstractMethodError err) {
/*
* As of JDBC 1.0, only setCharacterStream(int index,
Reader
* reader, int length) is specified. The method without
an
* integer length is specified by JDBC 4.0, and might
not be
* implemented by the driver.
*/
st.setCharacterStream(2,
reader,
(int)temp.length());
}
st.executeUpdate();
}
connection.commit();
System.out.println(" done.");
} catch (SQLException ex) {
System.out.println(" failed.");
throw ex;
}
}
/**
* Writes sample bytes into the given file
* @param temp Afile
* @param mb The abount of MB to write
* @throws IOException If file writing failed
*/
private static void writeBytes(File temp, int mb) throws IOException {
char[] buffer = new char[1_000_000];
for (int j = 0; j < 1_000_000; j++) {
buffer[j] =
