[h2] Re: DEFRAG SHUTDOWN not removing lock?
It is not the defrag that fails, it fails when I try to open the connection again dbConnection = DriverManager.getConnection(jdbc:h2://C:\\H2DB, user, password); Statement statement = dbConnection.createStatement(); statement.execute(SHUTDOWN DEFRAG); dbConnection.close(); //is already closed, but just to be sure... dbConnection = DriverManager.getConnection(jdbc:h2://C:\\H2DB, user, password); //FAILS The last statement fails with the stack trace I posted... Op donderdag 6 augustus 2015 10:59:38 UTC+2 schreef sim: This works for 1.4.188 in my project with no problem val conn = ds.getConnection val stat = conn.createStatement() stat.addBatch(set exclusive 2) stat.addBatch(shutdown defrag) stat.executeBatch() conn.close() On Thursday, August 6, 2015 at 6:37:26 AM UTC+3, Reinier L'abee wrote: Hi all, I use H2 version 1.4.188 in embeddded mode (mvstore enabled) and when I execute the statement DEFRAG SHUTDOWN I'm not able to open a new connection anymore. I use a single connection in my application and that gets closed when I execute the shutdown statement. (connection.isClosed() returns true). But when I then I try to open a new connection it tells me the file is locked. Am I missing something here? I believe this used to work a couple of months ago. Has something changed, or do I have to review my code? ;) Caused by: java.lang.IllegalStateException: The file is locked: nio:C:/H2DB.mv.db [1.4.188/7] at org.h2.mvstore.DataUtils.newIllegalStateException(DataUtils.java:773) at org.h2.mvstore.FileStore.open(FileStore.java:167) at org.h2.mvstore.MVStore.init(MVStore.java:346) at org.h2.mvstore.MVStore$Builder.open(MVStore.java:2888) at org.h2.mvstore.db.MVTableEngine$Store.open(MVTableEngine.java:154) ... 68 more Caused by: java.nio.channels.OverlappingFileLockException at sun.nio.ch.SharedFileLockTable.checkList(Unknown Source) at sun.nio.ch.SharedFileLockTable.add(Unknown Source) at sun.nio.ch.FileChannelImpl.tryLock(Unknown Source) at org.h2.store.fs.FileNio.tryLock(FilePathNio.java:121) at org.h2.mvstore.cache.FilePathCache$FileCache.tryLock(FilePathCache.java:152) at java.nio.channels.FileChannel.tryLock(Unknown Source) at org.h2.mvstore.FileStore.open(FileStore.java:164) ... 71 more -- 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. For more options, visit https://groups.google.com/d/optout.
[h2] QUERY_CACHE_SIZE default value too low?
Hi,
Did you try using a more advanced connection pool? One that re-uses
prepared statements. The H2 one is really simple and prevents that.
Regards,
Thomas
On Thursday, August 6, 2015, Steve McLeod steve.mcl...@gmail.com wrote:
Noel, I think you are right. I use this pattern for each query:
public void insertARow(int x) {
String sql = insert into yada yada yada;
try (Connection conn = getConnectionFromConnectionPool();
PreparedStatement statement = conn.prepareStatement(sql)) {
statement.setInt(1, x);
statement.executeUpdate();
}
}
It is based on keeping the database as unlocked as possible, in my
multi-threaded app. I may need to change the pattern a bit.
I deduced that Parser.initialize, for an SQL statement with n characters
* creates an array of n+1 ints
* an array of n+1 chars
* calls String.getChars() , which in turn calls System.arraycopy() for n+1
characters
* calls new String() , which in turn calls System.arraycopy() for n+1
characters
All of these result in memory that escapes the method, so will be created
on the JVM's heap.
Although this should all be blindingly fast, the fact that the rest of H2
is so fast, like you said, makes this show up. I think I was seeing this,
because for an SQL statement with 3000 characters, being performed 10,000
times a second, roughly 3000 * 10 bytes * 10,000 invocations = 300 MB being
allocated on the heap. And indeed, in my profiling, I noticed a lot of
churn on the heap.
Cheers,
Steve
On Wednesday, 5 August 2015 20:10:38 UTC+2, Noel Grandin wrote:
The thing is, I don't think there is a problem. I think that your code is
not caching PreparedStatement 's properly, and the rest of H2 is so fast,
that the only thing left in the profile is the parser initialisation :)
On Wed, 05 Aug 2015 at 16:27, Steve McLeod steve@gmail.com wrote:
Hi Noel,
I've actually solved this problem of PreparedStatement caching for my
app by increasing QUERY_CACHE_SIZE to 100. But I'm interested in helping
solve the bigger issue of why it seems to take a comparatively significant
time to create a PreparedStatement.
Cheers,
Steve
On Wednesday, 5 August 2015 16:04:46 UTC+2, Noel Grandin wrote:
Thanks, I'll have a look tomorrow at them in detail.
Tell me, how often is JdbcConnection@preparedStatement called compared
to how many times you execute a query?
If it's every time, it means that your PreparedStatement caching is not
working, which would indicate a problem with
your connection pool, or something similar.
--
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...@googlegroups.com.
To post to this group, send email to h2-da...@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.
--
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.
For more options, visit https://groups.google.com/d/optout.
--
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.
For more options, visit https://groups.google.com/d/optout.
Re: [h2] QUERY_CACHE_SIZE default value too low?
Noel, I think you are right. I use this pattern for each query:
public void insertARow(int x) {
String sql = insert into yada yada yada;
try (Connection conn = getConnectionFromConnectionPool();
PreparedStatement statement = conn.prepareStatement(sql)) {
statement.setInt(1, x);
statement.executeUpdate();
}
}
It is based on keeping the database as unlocked as possible, in my
multi-threaded app. I may need to change the pattern a bit.
I deduced that Parser.initialize, for an SQL statement with n characters
* creates an array of n+1 ints
* an array of n+1 chars
* calls String.getChars() , which in turn calls System.arraycopy() for n+1
characters
* calls new String() , which in turn calls System.arraycopy() for n+1
characters
All of these result in memory that escapes the method, so will be created
on the JVM's heap.
Although this should all be blindingly fast, the fact that the rest of H2
is so fast, like you said, makes this show up. I think I was seeing this,
because for an SQL statement with 3000 characters, being performed 10,000
times a second, roughly 3000 * 10 bytes * 10,000 invocations = 300 MB being
allocated on the heap. And indeed, in my profiling, I noticed a lot of
churn on the heap.
Cheers,
Steve
On Wednesday, 5 August 2015 20:10:38 UTC+2, Noel Grandin wrote:
The thing is, I don't think there is a problem. I think that your code is
not caching PreparedStatement 's properly, and the rest of H2 is so fast,
that the only thing left in the profile is the parser initialisation :)
On Wed, 05 Aug 2015 at 16:27, Steve McLeod steve@gmail.com
javascript: wrote:
Hi Noel,
I've actually solved this problem of PreparedStatement caching for my app
by increasing QUERY_CACHE_SIZE to 100. But I'm interested in helping solve
the bigger issue of why it seems to take a comparatively significant time
to create a PreparedStatement.
Cheers,
Steve
On Wednesday, 5 August 2015 16:04:46 UTC+2, Noel Grandin wrote:
Thanks, I'll have a look tomorrow at them in detail.
Tell me, how often is JdbcConnection@preparedStatement called compared
to how many times you execute a query?
If it's every time, it means that your PreparedStatement caching is not
working, which would indicate a problem with
your connection pool, or something similar.
--
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...@googlegroups.com javascript:.
To post to this group, send email to h2-da...@googlegroups.com
javascript:.
Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.
--
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.
For more options, visit https://groups.google.com/d/optout.
Re: [h2] Database created with H2 1.3 possibly incompatible with H2 1.4 due to h2.storeLocalTime?
Hi, I will remove the system property h2.storeLocalTime. As a workaround for existing H2 version 1.4.x, you can set the system property h2.storeLocalTime to false, if you want to write to old (PageStore) databases. So an ArrayIndexOutOfBoundsException is thrown in some cases when opening an old version 1.3 databases, or a 1.4 database with the settings mv_store=false and the system property h2.storeLocalTime set to false. It mainly shows up if there is an index on a time, date, or timestamp column. The system property h2.storeLocalTime will no longer be supported (MVStore databases always store local time, and PageStore databases never do, in the next version). The root cause reason is that adding a row to a page only allocated space for the new row, but didn't take into account that existing rows now can use more space, due to the changed format. Regards, Thomas On Thursday, August 6, 2015, Steve McLeod steve.mcl...@gmail.com wrote: Steve, thanks a lot for your help! My pleasure! This is probably the remaining impediment stopping me from moving Poker Copilot to H2 1.4, so I had a good reason for finding the problem. On Wednesday, August 5, 2015, Thomas Mueller thomas.to...@gmail.com wrote: Hi, I can reproduce the problem now, and I think I know what the problem is. I hope to have a fix ready in a few days. Regards, Thomas On Wednesday, August 5, 2015, Steve McLeod steve.mcl...@gmail.com wrote: Here's a stack trace in the same scenario from h2 1.4.188 org.h2.jdbc.JdbcSQLException: General error: java.lang.ArrayIndexOutOfBoundsException: 4096; SQL statement: ROLLBACK [5-188] 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:295) at org.h2.command.Command.executeUpdate(Command.java:262) at org.h2.jdbc.JdbcConnection.rollbackInternal(JdbcConnection.java:1500) at org.h2.jdbc.JdbcConnection.rollback(JdbcConnection.java:488) at org.h2.jdbcx.JdbcXAConnection$PooledJdbcConnection.close(JdbcXAConnection.java:465) at com.barbarysoftware.databaseframework.JdbcTemplateCore.update(JdbcTemplateCore.java:278) ... Caused by: java.lang.ArrayIndexOutOfBoundsException: 4096 at org.h2.store.Data.writeVarLong(Data.java:1254) at org.h2.store.Data.writeValue(Data.java:523) at org.h2.index.PageBtreeIndex.writeRow(PageBtreeIndex.java:393) at org.h2.index.PageBtreeNode.writeData(PageBtreeNode.java:453) at org.h2.index.PageBtreeNode.write(PageBtreeNode.java:426) at org.h2.store.PageStore.writeBack(PageStore.java:1046) at org.h2.store.PageStore.writeBack(PageStore.java:418) at org.h2.store.PageStore.checkpoint(PageStore.java:436) at org.h2.store.PageStore.commit(PageStore.java:1480) at org.h2.engine.Database.commit(Database.java:1995) at org.h2.engine.Session.rollback(Session.java:614) at org.h2.command.dml.TransactionCommand.update(TransactionCommand.java:49) at org.h2.command.CommandContainer.update(CommandContainer.java:78) at org.h2.command.Command.executeUpdate(Command.java:254) ... 17 more On Wednesday, 5 August 2015 13:08:37 UTC+2, Steve McLeod wrote: More info: I've been unable to reproduce this using the MVStore. I can make this problem happen when I only use h2 1.3.176 and I can make it happen when I only use h2 1.4.188. I initially create the database and load some data with h2.storeLocalTime=false, then restart the app with h2.storeLocalTime=true and load some more data. Usually database corruption ensues. I've stripped almost every config option from the database url so that it just reads: jdbc:h2:/Users/steve/Library/Application Support/com.barbarysoftware.pokercopilot/database/pokercopilot;MV_STORE=false I believe this confirms it is not a new issue, but it has become apparent now because the default value of h2.storeLocalTime changes from 1.3 to 1.4, and I'm trying to move our app from 1.3 to 1.4. Here's the most recent stack trace: org.h2.jdbc.JdbcSQLException: General error: java.lang.ArrayIndexOutOfBoundsException: 2048 [5-176] at org.h2.message.DbException.getJdbcSQLException(DbException.java:344) at org.h2.message.DbException.get(DbException.java:167) at org.h2.message.DbException.convert(DbException.java:294) at org.h2.table.RegularTable.addRow(RegularTable.java:138) at org.h2.engine.UndoLogRecord.undo(UndoLogRecord.java:111) at org.h2.engine.Session.rollbackTo(Session.java:595) at org.h2.command.Command.executeUpdate(Command.java:278) at org.h2.jdbc.JdbcPreparedStatement.executeUpdateInternal(JdbcPreparedStatement.java:158) at org.h2.jdbc.JdbcPreparedStatement.executeUpdate(JdbcPreparedStatement.java:144) at com.barbarysoftware.databaseframework.JdbcTemplateCore.update(JdbcTemplateCore.java:311) ... 15 more Caused by: java.lang.ArrayIndexOutOfBoundsException: 2048 at org.h2.store.Data.writeByte(Data.java:379) at org.h2.store.Data.writeValue(Data.java:441) at
