Out of memory when loading in large SQL file

[Paul]

I am currently working on an application that downloads an SQL file
and loads it into the database using RunScript.execute(connection,
reader);. Usually the SQL file will only include changes that will
need to be made to the client database to bring it inline with the
source database, but there are also situations when the data will need
to be replaced. To solve this problem when a complete update is
issued the tables are dropped, and then created again (in the SQL
file).

During testing a 400Mb SQL file was loading into the database without
any problems, but when the exact same file is loaded back in again I
get the following error:

Exception in thread "main" org.h2.jdbc.JdbcSQLException: Out of
memory.; SQL statement:

at org.h2.message.Message.getSQLException(Message.java:106)
at org.h2.message.Message.convertThrowable(Message.java:298)
at org.h2.command.Command.executeUpdate(Command.java:231)
at org.h2.jdbc.JdbcStatement.execute(JdbcStatement.java:162)
at org.h2.tools.RunScript.process(RunScript.java:166)
at org.h2.tools.RunScript.execute(RunScript.java:154)
at com.test.PacketLoader.load(PacketLoader.java:56)
at Main.main(Main.java:76)
Caused by: java.lang.OutOfMemoryError: Java heap space
at org.h2.index.PageDataLeaf.removeRow(PageDataLeaf.java:271)
at org.h2.index.PageDataLeaf.remove(PageDataLeaf.java:393)
at org.h2.index.PageDataNode.remove(PageDataNode.java:254)
at org.h2.index.PageDataNode.remove(PageDataNode.java:254)
at org.h2.index.PageDataIndex.remove(PageDataIndex.java:291)
at org.h2.table.TableData.addRow(TableData.java:138)
at org.h2.command.dml.Insert.insertRows(Insert.java:119)
at org.h2.command.dml.Insert.update(Insert.java:82)
at org.h2.command.CommandContainer.update
(CommandContainer.java:72)
at org.h2.command.Command.executeUpdate(Command.java:209)
... 5 more
Java Result: 1

Thanks in advance for any help.

[Thomas Mueller]

Hi,

What version of H2 do you use? Usually the version is included in the
exception stack trace, but not in this case. It does look like version
1.2.120 or newer.

Could you send me the script (or upload it somewhere) so I can test it
myself? That would be the easiest solution... Please compress the file
first. If that's not possible I'm sure we find another solution.

Regards,
Thomas

[Paul]

Hi Thomas

What I've tried now is importing my SQL file into H2, then extract the
database using "SCRIPT NOPASSWORDS NOSETTINGS DROP TO 'output.sql';",
then loaded output.sql in a few times without any problems!

Looking at output.sql and my SQL file the only differences are where
the indexes are added. In mine the indexes are added in the table
create statement, where as in output.sql they are added at the end.
Do you think this could be the cause of the problem?


Many thanks
Paul

On Nov 9, 8:20 pm, Thomas Mueller <thomas.tom.muel...@gmail.com>
wrote:

> Hi,
>
> What version of H2 do you use? Usually the version is included in the
> exception stack trace, but not in this case. It does look like version
> 1.2.120 or newer.
>
> Could you send me the script (or upload it somewhere) so I can test it
> myself? That would be the easiest solution... Please compress the file
> first. If that's not possible I'm sure we find another solution.
>
> Regards,
> Thomas
>

[Thomas Mueller]

Hi,

> Looking at output.sql and my SQL file the only differences are where
> the indexes are added.  In mine the indexes are added in the table
> create statement, where as in output.sql they are added at the end.
> Do you think this could be the cause of the problem?

It still shouldn't run out of memory.

If you still have a script that runs out of memory each time, could
you send it? What version of H2 do you use?

Regards,
Thomas

[Ryan McKinley]

FYI, a test box (running 122) just got an out of memory error also:

2009-11-12 12:29:55,859 ERROR org.eclipse.jetty.util.log - Error for /
java.lang.OutOfMemoryError: Java heap space
at org.h2.value.Value.cache(Value.java:337)
at org.h2.value.ValueByte.get(ValueByte.java:132)
at org.h2.store.DataPage.readValue(DataPage.java:589)
at org.h2.log.UndoLogRecord.load(UndoLogRecord.java:200)
at org.h2.log.UndoLog.getLast(UndoLog.java:81)
at org.h2.engine.Session.commit(Session.java:460)
at org.h2.command.Command.stop(Command.java:178)
at org.h2.command.Command.executeQueryLocal(Command.java:148)
at org.h2.command.Command.executeQuery(Command.java:123)
at
org.h2.jdbc.JdbcConnection.getInternalAutoCommit(JdbcConnection.java:
386)
at org.h2.jdbc.JdbcConnection.getAutoCommit(JdbcConnection.java:378)
at
org
.apache
.commons
.dbcp.DelegatingConnection.getAutoCommit(DelegatingConnection.java:304)
at org.apache.commons.dbcp.PoolingDataSource
$PoolGuardConnectionWrapper.getAutoCommit(PoolingDataSource.java:224)
at
org
.apache
.commons
.dbcp.DelegatingConnection.getAutoCommit(DelegatingConnection.java:304)
at
org
.apache
.commons
.dbcp
.PoolableConnectionFactory
.passivateObject(PoolableConnectionFactory.java:358)
at
org
.apache
.commons
.pool.impl.GenericObjectPool.addObjectToPool(GenericObjectPool.java:
1377)
at
org
.apache
.commons
.pool.impl.GenericObjectPool.returnObject(GenericObjectPool.java:1340)
at
org
.apache.commons.dbcp.PoolableConnection.close(PoolableConnection.java:
87)
at org.apache.commons.dbcp.PoolingDataSource
$PoolGuardConnectionWrapper.close(PoolingDataSource.java:181)
at voyager.common.sql.SQLPool.statement(SQLPool.java:104)
at
voyager.index.sql.VoyagerDataStoreSQL.wipe(VoyagerDataStoreSQL.java:
1346)

This was caused while trying to delete everything from a table with a
lot of data:
DELETE FROM table_name;

After this OOM, the db is then corrupted:


Caused by: org.h2.jdbc.JdbcSQLException: General error:
java.lang.ClassCastException: org.h2.store.PageStreamData cannot be
cast to org.h2.index.PageData [50000-122]
at org.h2.message.Message.getSQLException(Message.java:106)
at org.h2.message.Message.convert(Message.java:283)
at org.h2.engine.Database.openDatabase(Database.java:262)
at org.h2.engine.Database.<init>(Database.java:222)
at org.h2.engine.Engine.openSession(Engine.java:58)
at org.h2.engine.Engine.openSession(Engine.java:142)
at org.h2.engine.Engine.getSession(Engine.java:122)
at
org
.h2
.engine
.SessionFactoryEmbedded.createSession(SessionFactoryEmbedded.java:17)
at
org.h2.engine.SessionRemote.connectEmbeddedOrServer(SessionRemote.java:
245)
at org.h2.engine.SessionRemote.createSession(SessionRemote.java:223)
at org.h2.jdbc.JdbcConnection.<init>(JdbcConnection.java:110)
at org.h2.jdbc.JdbcConnection.<init>(JdbcConnection.java:94)
at org.h2.Driver.connect(Driver.java:58)
at
org
.apache
.commons
.dbcp
.DriverConnectionFactory.createConnection(DriverConnectionFactory.java:
38)
at
org
.apache
.commons
.dbcp
.PoolableConnectionFactory.makeObject(PoolableConnectionFactory.java:
294)
at
org
.apache
.commons
.dbcp.BasicDataSource.validateConnectionFactory(BasicDataSource.java:
1247)
at
org
.apache
.commons.dbcp.BasicDataSource.createDataSource(BasicDataSource.java:
1221)
... 54 more
Caused by: java.lang.ClassCastException: org.h2.store.PageStreamData
cannot be cast to org.h2.index.PageData
at org.h2.index.PageDataIndex.getPage(PageDataIndex.java:201)
at org.h2.index.PageDataNode.getLastKey(PageDataNode.java:222)
at org.h2.index.PageDataNode.getLastKey(PageDataNode.java:222)
at org.h2.index.PageDataNode.getLastKey(PageDataNode.java:222)
at org.h2.index.PageDataIndex.<init>(PageDataIndex.java:73)
at org.h2.table.TableData.<init>(TableData.java:83)
at org.h2.store.PageStore.addMeta(PageStore.java:1267)
at org.h2.store.PageStore.readMetaData(PageStore.java:1205)
at org.h2.store.PageStore.recover(PageStore.java:1014)
at org.h2.store.PageStore.openExisting(PageStore.java:296)
at org.h2.store.PageStore.open(PageStore.java:254)
at org.h2.engine.Database.getPageStore(Database.java:2294)
at org.h2.engine.Database.open(Database.java:625)
at org.h2.engine.Database.openDatabase(Database.java:228)
... 68 more


The DB is at a customer site, and I am downloading it now ~120MB zipped

thanks
ryan

> --
>
> You received this message because you are subscribed to the Google
> Groups "H2 Database" group.
> To post to this group, send email to h2-da...@googlegroups.com.
> To unsubscribe from this group, send email to h2-database...@googlegroups.com
> .
> For more options, visit this group at http://groups.google.com/group/h2-database?hl=
> .
>
>

[priyanka]

hello sir,

In my table i have 35 columns and using union iam iterating my table 5 times so first 2 or 3 times the table is getting executed but after that if i try to execute it is throwing exception as Out of memory.; SQL statement:

example of my query is like this
select * from table_name union  select * from table_name union select * from table_name union select * from table_name union select * from table_name

I need to execute my table like this using union and some conditions and table is getting executed also.....

But suddenly throwing exception as Out of memory.; SQL statement:
this is my url
jdbc:h2:tcp://localhost/~/abc;auto_reconnect=true;auto_server=true;mvcc=true;cache_size=8150;DB_CLOSE_DELAY=-1;

Please suggest me..the solution...

Thanks in Advance....