I have around 100M row in each tables/views and loading from MySQL. I
cannot use link table as it is too slow(mentioned in earlier thread).
So I am loading myself by simple select and insert using prepared statement
and batch.
I am committing every 100000, Here is the part of database load.
while (mySqLRS.next()) {
for (int j = 1; j <= meta.getColumnCount(); j++) {
final int type = meta.getColumnType(j);
switch (type) {
case Types.TIMESTAMP:
Timestamp timestamp = null;
try {
timestamp = mySqLRS.getTimestamp(j);
} catch (final Exception ex) {
logger.error("Error from :" +
currentMySQLSelect, ex);
} finally {
memDBPrepStmt.setTimestamp(j, timestamp);
}
break;
case Types.DATE:
Date date = null;
try {
date = mySqLRS.getDate(j);
} catch (final Exception ex) {
logger.error("Error from :" +
currentMySQLSelect, ex);
} finally {
memDBPrepStmt.setDate(j, date);
}
break;
case Types.DECIMAL:
BigDecimal decimal = mySqLRS.getBigDecimal(j);
if(decimal != null)
decimal =
(BigDecimal)objectPool.getObject(decimal);
memDBPrepStmt.setBigDecimal(j, decimal);
break;
case Types.BIGINT:
Long ll = mySqLRS.getLong(j);
ll = (Long)objectPool.getObject(ll);
memDBPrepStmt.setLong(j, ll);
break;
case Types.INTEGER:
if (memMeta.getColumnType(j) == Types.SMALLINT) {
Short s = mySqLRS.getShort(j);
s = (Short)objectPool.getObject(s);
memDBPrepStmt.setShort(j, s);
} else {
Integer ii = mySqLRS.getInt(j);
ii = (Integer)objectPool.getObject(ii);
memDBPrepStmt.setInt(j, ii);
}
break;
case Types.SMALLINT:
Short s = mySqLRS.getShort(j);
s = (Short)objectPool.getObject(s);
memDBPrepStmt.setShort(j, s);
break;
case Types.BIT:
final int bit = mySqLRS.getInt(j);
memDBPrepStmt.setBoolean(j, bit == 1);
break;
case Types.TINYINT:
break;
case java.sql.Types.BOOLEAN:
final boolean bool = mySqLRS.getBoolean(j);
memDBPrepStmt.setBoolean(j, bool);
break;
case java.sql.Types.CHAR:
case java.sql.Types.VARCHAR:
case java.sql.Types.LONGVARCHAR:
String value = mySqLRS.getString(j);
if(value != null)
value = (String)objectPool.getObject(value);
memDBPrepStmt.setString(j, value);
break;
default:
value = mySqLRS.getString(j);
if(value != null)
value = (String)objectPool.getObject(value);
memDBPrepStmt.setString(j, value);
}
}
i++;
memDBPrepStmt.addBatch();
if (i % 100000 == 0) {
memDBPrepStmt.executeBatch();
}
}
if (i % 10000 != 0) {
memDBPrepStmt.executeBatch();
i = 0;
}
DbUtils.closeQuietly(memDBPrepStmt);
memDBStmt = memDBConn.createStatement();
memDBRS = memDBStmt.executeQuery("select count(0) from " +
memoryTable.getMemoryTableName());
if (memDBRS.next()) {
logger.info(memDBRS.getInt(1) + " row(s) loaded into " +
memoryTable.getMemoryTableName() + " table.");
}
DbUtils.closeQuietly(memDBRS);
} catch (final Exception e) {
logger.error("Exception connecting to database:" +
e.getMessage(), e);
e.printStackTrace();
} finally {
DbUtils.closeQuietly(mySQLStmt);
DbUtils.closeQuietly(mySQLConn);
DbUtils.closeQuietly(memDBRS);
DbUtils.closeQuietly(memDBStmt);
DbUtils.closeQuietly(memDBPrepStmt);
DbUtils.closeQuietly(memDBConn);
}
On Saturday, June 28, 2014 8:34:15 PM UTC-7, Vishwa wrote:
>
> I have been getting this exception on 1.4.178 and 1.4.179.
> Problem still persist on latest release
> No issue with older version 1.3.176.
>
> I am using these options on database url-:
> DB_CLOSE_DELAY=-1;MULTI_THREADED=1;MODE=MYSQL;CACHE_SIZE=1073741824;PAGE_SIZE=32768;CACHE_TYPE=SOFT_LRU;
>
> I can use older version, but i thought newer version performance will
> better.
>
> 2014-06-28 19:36:15,701 ERROR [Job Manager 45]
> b.t.i.d.m.MemoryDatabaseLoader [MemoryDatabaseLoader.java:449] Exception
> connecting to database:General error: "java.lang.IndexOutOfBoundsException:
> Index: 1, Size: 1"; SQL statement:
> insert into exr(currencycodefrom,currencycodeto,monthyearvalidfor,rate)
> values (?,?,?,?) [50000-179]
> org.h2.jdbc.JdbcBatchUpdateException: General error:
> "java.lang.IndexOutOfBoundsException: Index: 1, Size: 1"; SQL statement:
> insert into exr(currencycodefrom,currencycodeto,monthyearvalidfor,rate)
> values (?,?,?,?) [50000-179]
> at
> org.h2.jdbc.JdbcPreparedStatement.executeBatch(JdbcPreparedStatement.java:1198)
>
> ~[h2-1.4.179.jar:1.4.179]
> at
> com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeBatch(NewProxyPreparedStatement.java:2465)
>
> ~[c3p0-0.9.5-pre8.jar:0.9.5-pre8]
> at
> biz.tradescape.ingestion.database.memory.MemoryDatabaseLoader.loadSingleTable(MemoryDatabaseLoader.java:432)
>
> [h2server.jar:na]
> at
> biz.tradescape.ingestion.database.memory.MemoryDatabaseLoader.access$1(MemoryDatabaseLoader.java:313)
>
> [h2server.jar:na]
> at
> biz.tradescape.ingestion.database.memory.MemoryDatabaseLoader$1.run(MemoryDatabaseLoader.java:80)
>
> ~[h2server.jar:na]
> at
> java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:471)
> ~[na:1.7.0_25]
> at
> java.util.concurrent.FutureTask$Sync.innerRun(FutureTask.java:334)
> ~[na:1.7.0_25]
> at java.util.concurrent.FutureTask.run(FutureTask.java:166)
> ~[na:1.7.0_25]
> at
> java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145)
>
> ~[na:1.7.0_25]
> at
> java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615)
>
> ~[na:1.7.0_25]
> at java.lang.Thread.run(Thread.java:724) ~[na:1.7.0_25]
> org.h2.jdbc.JdbcBatchUpdateException: General error:
> "java.lang.IndexOutOfBoundsException: Index: 1, Size: 1"; SQL statement:
> insert into exr(currencycodefrom,currencycodeto,monthyearvalidfor,rate)
> values (?,?,?,?) [50000-179]
> at
> org.h2.jdbc.JdbcPreparedStatement.executeBatch(JdbcPreparedStatement.java:1198)
> at
> com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeBatch(NewProxyPreparedStatement.java:2465)
> at
> biz.tradescape.ingestion.database.memory.MemoryDatabaseLoader.loadSingleTable(MemoryDatabaseLoader.java:432)
> at
> biz.tradescape.ingestion.database.memory.MemoryDatabaseLoader.access$1(MemoryDatabaseLoader.java:313)
> at
> biz.tradescape.ingestion.database.memory.MemoryDatabaseLoader$1.run(MemoryDatabaseLoader.java:80)
> at
> java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:471)
> at
> java.util.concurrent.FutureTask$Sync.innerRun(FutureTask.java:334)
> at java.util.concurrent.FutureTask.run(FutureTask.java:166)
> at
> java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145)
> at
> java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615)
> at java.lang.Thread.run(Thread.java:724)
> org.h2.jdbc.JdbcSQLException: General error:
> "java.lang.IndexOutOfBoundsException: Index: 1, Size: 1"; SQL statement:
> insert into exr(currencycodefrom,currencycodeto,monthyearvalidfor,rate)
> values (?,?,?,?) [50000-179]
> 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.mvstore.db.MVTable.addRow(MVTable.java:614)
> at org.h2.command.dml.Insert.insertRows(Insert.java:156)
> at org.h2.command.dml.Insert.update(Insert.java:114)
> at org.h2.command.CommandContainer.update(CommandContainer.java:78)
> at org.h2.command.Command.executeUpdate(Command.java:253)
> at
> org.h2.jdbc.JdbcPreparedStatement.executeUpdateInternal(JdbcPreparedStatement.java:157)
> at
> org.h2.jdbc.JdbcPreparedStatement.executeBatch(JdbcPreparedStatement.java:1183)
> at
> com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeBatch(NewProxyPreparedStatement.java:2465)
> at
> biz.tradescape.ingestion.database.memory.MemoryDatabaseLoader.loadSingleTable(MemoryDatabaseLoader.java:432)
> at
> biz.tradescape.ingestion.database.memory.MemoryDatabaseLoader.access$1(MemoryDatabaseLoader.java:313)
> at
> biz.tradescape.ingestion.database.memory.MemoryDatabaseLoader$1.run(MemoryDatabaseLoader.java:80)
> at
> java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:471)
> at
> java.util.concurrent.FutureTask$Sync.innerRun(FutureTask.java:334)
> at java.util.concurrent.FutureTask.run(FutureTask.java:166)
> at
> java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145)
> at
> java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615)
> at java.lang.Thread.run(Thread.java:724)
> Caused by: java.lang.IndexOutOfBoundsException: Index: 1, Size: 1
> at java.util.ArrayList.rangeCheck(ArrayList.java:604)
> at java.util.ArrayList.get(ArrayList.java:382)
> at org.h2.mvstore.MVMap.openVersion(MVMap.java:1166)
> at org.h2.mvstore.MVStore.storeNow(MVStore.java:972)
> at org.h2.mvstore.MVStore.commitAndSave(MVStore.java:907)
> at org.h2.mvstore.MVStore.beforeWrite(MVStore.java:2102)
> at org.h2.mvstore.MVMap.beforeWrite(MVMap.java:1040)
> at org.h2.mvstore.MVMapConcurrent.put(MVMapConcurrent.java:43)
> at org.h2.mvstore.MVMap.putIfAbsent(MVMap.java:574)
> at
> org.h2.mvstore.db.TransactionStore$TransactionMap.trySet(TransactionStore.java:1089)
> at
> org.h2.mvstore.db.TransactionStore$TransactionMap.set(TransactionStore.java:1011)
> at
> org.h2.mvstore.db.TransactionStore$TransactionMap.put(TransactionStore.java:989)
> at
> org.h2.mvstore.db.MVSecondaryIndex.add(MVSecondaryIndex.java:209)
> at org.h2.mvstore.db.MVTable.addRow(MVTable.java:610)
> ... 16 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 [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.
