Well, I'm using org.apache.tomcat.jdbc.pool.DataSource with Hibernate. So I never use Connection.createBlob / createClob but I don't know if then Hibernate do it internally. Make a reproducible test case is quite hard because the application is quite complex. I'm thinking if Sql logs would be useful for you.....what do you think?
Thanks 2015-01-14 9:10 GMT+01:00 Thomas Mueller <[email protected]>: > Hi, > > We would need to know LOBs are created. I don't think this is related to > MVCC. Are you using anything special, for example the JDBC API > Connection.createBlob / createClob? Basically we would need a reproducible > test case. > > Regards, > Thomas > > > > On Wed, Jan 14, 2015 at 8:31 AM, Daniele Renda <[email protected]> > wrote: > >> 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 <[email protected]>: >> >>> 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 < >>> [email protected]> 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 < >>>> [email protected]> 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 [email protected]. >>>>> To post to this group, send email to [email protected]. >>>>> 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 >>> [email protected]. >>> To post to this group, send email to [email protected]. >>> 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 [email protected]. >> To post to this group, send email to [email protected]. >> 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 > [email protected]. > To post to this group, send email to [email protected]. > 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 [email protected]. To post to this group, send email to [email protected]. Visit this group at http://groups.google.com/group/h2-database. For more options, visit https://groups.google.com/d/optout.
