Hi, Even thought I couldn't reproduce the problem on the database level, I think I found the problem. I have a test case on the MVStore level, and a fix for it.
Regards, Thomas On Sunday, June 29, 2014, Vishwa <[email protected]> wrote: > 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] > <javascript:_e(%7B%7D,'cvml','h2-database%[email protected]');> > . > To post to this group, send email to [email protected] > <javascript:_e(%7B%7D,'cvml','[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 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.
