Hi Thomas,
thanks for your reply and for your time.
I'm using H2 1.4.184 from the day you released it. So I'm yet using it, but
the db grows every day. Now it is 2.9Gb, Argh!!!
I'm using Hibernate 4.3.7.Final in my application.
Can you suggest me what I can do to help you to fix the problem and avoid
my production db collaps?!
Thanks very much!
2015-01-14 7:47 GMT+01:00 Thomas Mueller <thomas.tom.muel...@gmail.com>:
> Hi,
>
> As why the database is so large:
>
> I got the large database and ran the Recover tool on it. The main problem
> seems to be a huge number of references to a small LOB (130 bytes). Such
> small LOBs should be inlined, which means there would be no reference. I'm
> not quite sure, but I think I fixed this / changed the behavior in version
> 1.4.184 such that LOBs are always inlined now. Could you try again with
> version 1.4.184 if the database still gets that big? If yes, we would need
> to know LOBs are created. Are you using anything special, for example the
> JDBC API Connection.createBlob / createClob?
>
>
> Details:
>
> > CREATE_BUILD 182
> So, the database was created with version 1.4.182.
>
> > grep "INSERT INTO O_" ecall.h2.sql | wc -l
> > 33'057'364
>
> The number of "insert" statements: 33 millions.
>
> Those are the LOB tables:
>
> > INSERT INTO INFORMATION_SCHEMA.LOBS SELECT * FROM O_2;
> > INSERT INTO INFORMATION_SCHEMA.LOB_MAP SELECT * FROM O_6;
> > INSERT INTO INFORMATION_SCHEMA.LOB_DATA SELECT * FROM O_10;
>
> > grep "INSERT INTO O_10 " ecall.h2.sql | wc -l
> > 3269
>
> So, actually not much LOB_DATA.
>
> > grep "INSERT INTO O_6 " ecall.h2.sql | wc -l
> > 16'471'645
> > grep "INSERT INTO O_2 " ecall.h2.sql | wc -l
> > 16'456'153
>
> Those are references to LOBs: a huge number (16 million references). It
> looks like most of those LOBs are very very small (130 bytes I think), and
> are temporary LOBs (type = -3).
>
> ------------
>
> As for the ArrayIndexOutOfBoundsException: the root cause is:
>
> Caused by: java.lang.ArrayIndexOutOfBoundsException: 0
> at org.h2.index.PageDataLeaf.getRowAt(PageDataLeaf.java:332)
> at org.h2.index.PageDataCursor.nextRow(PageDataCursor.java:101)
> at org.h2.index.PageDataCursor.next(PageDataCursor.java:67)
> at org.h2.result.ResultTempTable.next(ResultTempTable.java:271)
> at org.h2.result.LocalResult.next(LocalResult.java:242)
> at org.h2.jdbc.JdbcResultSet.nextRow(JdbcResultSet.java:3231)
> at org.h2.jdbc.JdbcResultSet.next(JdbcResultSet.java:122)
>
> There seems to be a a corruption in the database file (PageStore). I
> didn't further analyze this yet, I think the "large database file" issue is
> more urgent in this case.
>
>
> Regards,
> Thomas
>
> On Sun, Jan 4, 2015 at 11:15 AM, Thomas Mueller <
> thomas.tom.muel...@gmail.com> wrote:
>
>> Hi,
>>
>> I think part of the exception stack trace is missing (after the last
>> "caused by"). Could you post it as well?
>>
>> > Unfortunally I see that now the db is 1.3GB
>>
>> Did you try using the Recover tool? Could you run it, and post the last
>> 20 lines of the resulting *.sql file please?
>>
>> Regards,
>> Thomas
>>
>>
>>
>>
>>
>> On Wed, Dec 31, 2014 at 12:09 PM, Daniele Renda <daniele.re...@gmail.com>
>> wrote:
>>
>>> Hi,
>>> I'm using h2 db with my application.
>>> This is the connection string:
>>>
>>>
>>> jdbc:h2:{database};AUTO_SERVER=TRUE;MV_STORE=FALSE;MVCC=TRUE;DEFRAG_ALWAYS=TRUE;
>>>
>>> I'm using this approach to avoid problems I had in the past with mv
>>> store as you suggested me.
>>> All worked fine in this 3 weeks; my db has 56 tables and now there are
>>> many rows (about 125.000).
>>>
>>> Unfortunally I see that now the db is 1.3GB!!!! I'm sure that data
>>> inside are not so big, so I think there is some problem with lob in h2.
>>>
>>> To make a test I run 'SCRIPT TO 'C:\backup.sql'. The first time the
>>> command worked and I get a sql file of about 75Mb. Because it was just a
>>> test I removed that file and I repeated the comman but now I've this
>>> Exception:
>>>
>>>
>>> Caused by: java.lang.ArrayIndexOutOfBoundsException
>>>> 12-31 11:54:04 jdbc[3]: exception
>>>> org.h2.jdbc.JdbcSQLException: IO Exception: "java.io.IOException:
>>>> java.lang.ArrayIndexOutOfBoundsException"; "lob: null table: 62 id:
>>>> 753781"; SQL statement:
>>>> SCRIPT TO 'C:\backup.sql' [90031-184]
>>>> at org.h2.message.DbException.getJdbcSQLException(DbException.java:
>>>> 345)
>>>> at org.h2.message.DbException.get(DbException.java:168)
>>>> at org.h2.message.DbException.convertIOException(DbException.java:
>>>> 330)
>>>> at org.h2.value.ValueLobDb.getInputStream(ValueLobDb.java:394)
>>>> at org.h2.value.ValueLobDb.getReader(ValueLobDb.java:377)
>>>> at org.h2.value.ValueLobDb.getString(ValueLobDb.java:292)
>>>> at org.h2.value.ValueLobDb.getSQL(ValueLobDb.java:416)
>>>> at org.h2.command.dml.ScriptCommand.generateInsertValues(
>>>> ScriptCommand.java:426)
>>>> at org.h2.command.dml.ScriptCommand.query(ScriptCommand.java:300)
>>>> at org.h2.command.CommandContainer.query(CommandContainer.java:90)
>>>> at org.h2.command.Command.executeQuery(Command.java:197)
>>>> at org.h2.jdbc.JdbcStatement.executeInternal(JdbcStatement.java:179
>>>> )
>>>> at org.h2.jdbc.JdbcStatement.execute(JdbcStatement.java:158)
>>>> at org.jkiss.dbeaver.model.impl.jdbc.exec.JDBCStatementImpl.execute
>>>> (JDBCStatementImpl.java:369)
>>>> at org.jkiss.dbeaver.model.impl.jdbc.exec.JDBCStatementImpl.
>>>> executeStatement(JDBCStatementImpl.java:155)
>>>> at org.jkiss.dbeaver.runtime.sql.SQLQueryJob.executeSingleQuery(
>>>> SQLQueryJob.java:363)
>>>> at org.jkiss.dbeaver.runtime.sql.SQLQueryJob.extractData(
>>>> SQLQueryJob.java:633)
>>>> at org.jkiss.dbeaver.ui.editors.sql.SQLEditor$QueryResultsProvider.
>>>> readData(SQLEditor.java:1169)
>>>> at org.jkiss.dbeaver.ui.controls.resultset.ResultSetDataPumpJob.run
>>>> (ResultSetDataPumpJob.java:132)
>>>> at org.jkiss.dbeaver.runtime.AbstractJob.run(AbstractJob.java:91)
>>>> at org.eclipse.core.internal.jobs.Worker.run(Worker.java:54)
>>>> Caused by: java.io.IOException: java.lang.
>>>> ArrayIndexOutOfBoundsException
>>>> at org.h2.message.DbException.convertToIOException(DbException.java
>>>> :364)
>>>> at org.h2.store.LobStorageBackend.getInputStream(LobStorageBackend.
>>>> java:345)
>>>> at org.h2.value.ValueLobDb.getInputStream(ValueLobDb.java:392)
>>>> ... 17 more
>>>> Caused by: java.lang.ArrayIndexOutOfBoundsException
>>>>
>>>>
>>>>
>>>>
>>> At this point I've 2 questions:
>>>
>>>
>>> 1. There is a way to make a sql backup of the actual db?
>>> 2. Why the db is 1.3Gb and the first sql backup was only 75Mb?
>>>
>>> Thanks very much
>>>
>>>
>>> Daniele Renda
>>>
>>> --
>>> 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 a topic in the
> Google Groups "H2 Database" group.
> To unsubscribe from this topic, visit
> https://groups.google.com/d/topic/h2-database/ZVCOE0QErOo/unsubscribe.
> To unsubscribe from this group and all its topics, 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.
>
--
Daniele Renda
--
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.
