Menu ▾ ▴

Fail to import large amount of record through importResultSet or PreparedStatement.

Help
Bryan Cheng
2016-11-04
2016-11-16

  • Bryan Cheng

    Bryan Cheng - 2016-11-04

    Hi All,

    I've successfully created the table structure via Jackcess API, and all dependencies are imported to the project. The actual table is around 20 columns with DECIMAL and TEXT only.

    Three approaches I've tried.

    1. Import via API of Jackcess
      ImportUtil.importResultSet(ResultSet, Database, tableName, SimpleImportFilter.INSTANCE, true);

    2. Batch insert via PreparedStatement of related project UCanAccess (Tried batch size 20 and 2000)
      PreparedStatement preparedStatement = ucanConn.prepareStatement("INSERT INTO Testing (UID, Name, Address) VALUES (?, ?, ?);
      ...
      preparedStatement.addBatch();
      rowNum++;
      batchSize++;
      if (batchSize == 2000) {
      preparedStatement.executeBatch();
      preparedStatement.clearBatch();
      target.commit();
      batchSize = 0;
      }

    if (batchSize > 0) {
    preparedStatement.executeBatch();
    preparedStatement.clearBatch();
    target.commit();
    }

    1. On top of step 2, I've added autonumber for primary key to see wheather the problem is fixed. But unluckyly it's not.

    Both API gave me the following exception:
    net.ucanaccess.jdbc.UcanaccessSQLException: UCAExc:::3.0.7 -1
    at net.ucanaccess.jdbc.UcanaccessStatement.executeBatch(UcanaccessStatement.java:198)
    at com.xxx.xxx.controlm.job.DownloadDatasetJob.importResultSet(DownloadDatasetJob.java:190)
    at com.xxx.xxx.controlm.job.DownloadDatasetJob.<init>(DownloadDatasetJob.java:142)
    at com.xxx.xxx.controlm.job.DownloadDatasetJob.main(DownloadDatasetJob.java:45)
    Caused by: net.ucanaccess.jdbc.UcanaccessSQLException: UCAExc:::3.0.7 -1
    at net.ucanaccess.jdbc.UcanaccessConnection.commit(UcanaccessConnection.java:212)
    at net.ucanaccess.jdbc.AbstractExecute.executeBase(AbstractExecute.java:161)
    at net.ucanaccess.jdbc.ExecuteUpdate.executeBatch(ExecuteUpdate.java:55)
    at net.ucanaccess.jdbc.UcanaccessStatement.executeBatch(UcanaccessStatement.java:196)
    ... 3 more
    Caused by: net.ucanaccess.jdbc.UcanaccessSQLException: UCAExc:::3.0.7 -1
    at net.ucanaccess.jdbc.UcanaccessConnection.flushIO(UcanaccessConnection.java:348)
    at net.ucanaccess.jdbc.UcanaccessConnection.commit(UcanaccessConnection.java:205)
    ... 6 more
    Caused by: java.lang.ArrayIndexOutOfBoundsException: -1
    at sun.nio.ch.NativeThreadSet.remove(Unknown Source)
    at sun.nio.ch.FileChannelImpl.size(Unknown Source)
    at com.healthmarketscience.jackcess.impl.PageChannel.allocateNewPage(PageChannel.java:327)
    at com.healthmarketscience.jackcess.impl.TempPageHolder.setNewPage(TempPageHolder.java:104)
    at com.healthmarketscience.jackcess.impl.UsageMap$ReferenceHandler.createNewUsageMapPage(UsageMap.java:748)
    at com.healthmarketscience.jackcess.impl.UsageMap$ReferenceHandler.addOrRemovePageNumber(UsageMap.java:732)
    at com.healthmarketscience.jackcess.impl.UsageMap.removePageNumber(UsageMap.java:322)
    at com.healthmarketscience.jackcess.impl.PageChannel.allocateNewPage(PageChannel.java:352)
    at com.healthmarketscience.jackcess.impl.TempPageHolder.setNewPage(TempPageHolder.java:104)
    at com.healthmarketscience.jackcess.impl.UsageMap$ReferenceHandler.createNewUsageMapPage(UsageMap.java:748)
    at com.healthmarketscience.jackcess.impl.UsageMap$ReferenceHandler.addOrRemovePageNumber(UsageMap.java:732)
    at com.healthmarketscience.jackcess.impl.UsageMap.removePageNumber(UsageMap.java:322)
    at com.healthmarketscience.jackcess.impl.PageChannel.allocateNewPage(PageChannel.java:352)
    at com.healthmarketscience.jackcess.impl.TempPageHolder.setNewPage(TempPageHolder.java:104)
    at com.healthmarketscience.jackcess.impl.UsageMap$ReferenceHandler.createNewUsageMapPage(UsageMap.java:748)
    at com.healthmarketscience.jackcess.impl.UsageMap$ReferenceHandler.addOrRemovePageNumber(UsageMap.java:732)
    at com.healthmarketscience.jackcess.impl.UsageMap.removePageNumber(UsageMap.java:322)
    at com.healthmarketscience.jackcess.impl.PageChannel.allocateNewPage(PageChannel.java:352)
    at com.healthmarketscience.jackcess.impl.TempPageHolder.setNewPage(TempPageHolder.java:104)
    at com.healthmarketscience.jackcess.impl.UsageMap$ReferenceHandler.createNewUsageMapPage(UsageMap.java:748)
    at com.healthmarketscience.jackcess.impl.UsageMap$ReferenceHandler.addOrRemovePageNumber(UsageMap.java:732)
    at com.healthmarketscience.jackcess.impl.UsageMap.removePageNumber(UsageMap.java:322)
    at com.healthmarketscience.jackcess.impl.PageChannel.allocateNewPage(PageChannel.java:352)
    at com.healthmarketscience.jackcess.impl.TempPageHolder.setNewPage(TempPageHolder.java:104)
    at com.healthmarketscience.jackcess.impl.UsageMap$ReferenceHandler.createNewUsageMapPage(UsageMap.java:748)
    at com.healthmarketscience.jackcess.impl.UsageMap$ReferenceHandler.addOrRemovePageNumber(UsageMap.java:732)
    at com.healthmarketscience.jackcess.impl.UsageMap.removePageNumber(UsageMap.java:322)
    at com.healthmarketscience.jackcess.impl.PageChannel.allocateNewPage(PageChannel.java:352)
    at com.healthmarketscience.jackcess.impl.TempPageHolder.setNewPage(TempPageHolder.java:104)
    at com.healthmarketscience.jackcess.impl.UsageMap$ReferenceHandler.createNewUsageMapPage(UsageMap.java:748)
    at com.healthmarketscience.jackcess.impl.UsageMap$ReferenceHandler.addOrRemovePageNumber(UsageMap.java:732)
    at com.healthmarketscience.jackcess.impl.UsageMap.removePageNumber(UsageMap.java:322)
    at com.healthmarketscience.jackcess.impl.PageChannel.allocateNewPage(PageChannel.java:352)
    at com.healthmarketscience.jackcess.impl.TempPageHolder.setNewPage(TempPageHolder.java:104)
    at com.healthmarketscience.jackcess.impl.UsageMap$ReferenceHandler.createNewUsageMapPage(UsageMap.java:748)
    at com.healthmarketscience.jackcess.impl.UsageMap$ReferenceHandler.addOrRemovePageNumber(UsageMap.java:732)
    at com.healthmarketscience.jackcess.impl.UsageMap.removePageNumber(UsageMap.java:322)
    at com.healthmarketscience.jackcess.impl.PageChannel.allocateNewPage(PageChannel.java:352)
    at com.healthmarketscience.jackcess.impl.TempPageHolder.setNewPage(TempPageHolder.java:104)
    at com.healthmarketscience.jackcess.impl.UsageMap$ReferenceHandler.createNewUsageMapPage(UsageMap.java:748)
    at com.healthmarketscience.jackcess.impl.UsageMap$ReferenceHandler.addOrRemovePageNumber(UsageMap.java:732)
    at com.healthmarketscience.jackcess.impl.UsageMap.removePageNumber(UsageMap.java:322)
    at com.healthmarketscience.jackcess.impl.PageChannel.allocateNewPage(PageChannel.java:352)
    at com.healthmarketscience.jackcess.impl.TempPageHolder.setNewPage(TempPageHolder.java:104)
    at com.healthmarketscience.jackcess.impl.UsageMap$ReferenceHandler.createNewUsageMapPage(UsageMap.java:748)
    at com.healthmarketscience.jackcess.impl.UsageMap$ReferenceHandler.addOrRemovePageNumber(UsageMap.java:732)
    at com.healthmarketscience.jackcess.impl.UsageMap.removePageNumber(UsageMap.java:322)
    at com.healthmarketscience.jackcess.impl.PageChannel.allocateNewPage(PageChannel.java:352)
    at com.healthmarketscience.jackcess.impl.TempPageHolder.setNewPage(TempPageHolder.java:104)
    at com.healthmarketscience.jackcess.impl.UsageMap$ReferenceHandler.createNewUsageMapPage(UsageMap.java:748)
    at com.healthmarketscience.jackcess.impl.UsageMap$ReferenceHandler.addOrRemovePageNumber(UsageMap.java:732)
    at com.healthmarketscience.jackcess.impl.UsageMap.removePageNumber(UsageMap.java:322)
    at com.healthmarketscience.jackcess.impl.PageChannel.allocateNewPage(PageChannel.java:352)
    at com.healthmarketscience.jackcess.impl.TempPageHolder.setNewPage(TempPageHolder.java:104)
    at com.healthmarketscience.jackcess.impl.UsageMap$ReferenceHandler.createNewUsageMapPage(UsageMap.java:748)
    at com.healthmarketscience.jackcess.impl.UsageMap$ReferenceHandler.addOrRemovePageNumber(UsageMap.java:732)
    at com.healthmarketscience.jackcess.impl.UsageMap.removePageNumber(UsageMap.java:322)
    at com.healthmarketscience.jackcess.impl.PageChannel.allocateNewPage(PageChannel.java:352)
    at com.healthmarketscience.jackcess.impl.TempPageHolder.setNewPage(TempPageHolder.java:104)
    at com.healthmarketscience.jackcess.impl.UsageMap$ReferenceHandler.createNewUsageMapPage(UsageMap.java:748)
    at com.healthmarketscience.jackcess.impl.UsageMap$ReferenceHandler.addOrRemovePageNumber(UsageMap.java:732)
    at com.healthmarketscience.jackcess.impl.UsageMap.removePageNumber(UsageMap.java:322)
    at com.healthmarketscience.jackcess.impl.PageChannel.allocateNewPage(PageChannel.java:352)
    at com.healthmarketscience.jackcess.impl.TempPageHolder.setNewPage(TempPageHolder.java:104)
    at com.healthmarketscience.jackcess.impl.UsageMap$ReferenceHandler.createNewUsageMapPage(UsageMap.java:748)
    at com.healthmarketscience.jackcess.impl.UsageMap$ReferenceHandler.addOrRemovePageNumber(UsageMap.java:732)
    at com.healthmarketscience.jackcess.impl.UsageMap.removePageNumber(UsageMap.java:322)
    at com.healthmarketscience.jackcess.impl.PageChannel.allocateNewPage(PageChannel.java:352)
    at com.healthmarketscience.jackcess.impl.TempPageHolder.setNewPage(TempPageHolder.java:104)
    at com.healthmarketscience.jackcess.impl.UsageMap$ReferenceHandler.createNewUsageMapPage(UsageMap.java:748)
    at com.healthmarketscience.jackcess.impl.UsageMap$ReferenceHandler.addOrRemovePageNumber(UsageMap.java:732)
    at com.healthmarketscience.jackcess.impl.UsageMap.removePageNumber(UsageMap.java:322)
    at com.healthmarketscience.jackcess.impl.PageChannel.allocateNewPage(PageChannel.java:352)
    at com.healthmarketscience.jackcess.impl.TempPageHolder.setNewPage(TempPageHolder.java:104)
    at com.healthmarketscience.jackcess.impl.UsageMap$ReferenceHandler.createNewUsageMapPage(UsageMap.java:748)
    at com.healthmarketscience.jackcess.impl.UsageMap$ReferenceHandler.addOrRemovePageNumber(UsageMap.java:732)
    at com.healthmarketscience.jackcess.impl.UsageMap.removePageNumber(UsageMap.java:322)
    at com.healthmarketscience.jackcess.impl.PageChannel.allocateNewPage(PageChannel.java:352)
    at com.healthmarketscience.jackcess.impl.TempPageHolder.setNewPage(TempPageHolder.java:104)
    at com.healthmarketscience.jackcess.impl.UsageMap$ReferenceHandler.createNewUsageMapPage(UsageMap.java:748)
    at com.healthmarketscience.jackcess.impl.UsageMap$ReferenceHandler.addOrRemovePageNumber(UsageMap.java:732)
    at com.healthmarketscience.jackcess.impl.UsageMap.removePageNumber(UsageMap.java:322)
    at com.healthmarketscience.jackcess.impl.PageChannel.allocateNewPage(PageChannel.java:352)
    at com.healthmarketscience.jackcess.impl.TempPageHolder.setNewPage(TempPageHolder.java:104)
    at com.healthmarketscience.jackcess.impl.UsageMap$ReferenceHandler.createNewUsageMapPage(UsageMap.java:748)
    at com.healthmarketscience.jackcess.impl.UsageMap$ReferenceHandler.addOrRemovePageNumber(UsageMap.java:732)
    at com.healthmarketscience.jackcess.impl.UsageMap.removePageNumber(UsageMap.java:322)
    at com.healthmarketscience.jackcess.impl.PageChannel.allocateNewPage(PageChannel.java:352)
    at com.healthmarketscience.jackcess.impl.TempPageHolder.setNewPage(TempPageHolder.java:104)
    at com.healthmarketscience.jackcess.impl.UsageMap$ReferenceHandler.createNewUsageMapPage(UsageMap.java:748)
    at com.healthmarketscience.jackcess.impl.UsageMap$ReferenceHandler.addOrRemovePageNumber(UsageMap.java:732)
    at com.healthmarketscience.jackcess.impl.UsageMap.removePageNumber(UsageMap.java:322)
    at com.healthmarketscience.jackcess.impl.PageChannel.allocateNewPage(PageChannel.java:352)
    at com.healthmarketscience.jackcess.impl.TempPageHolder.setNewPage(TempPageHolder.java:104)
    at com.healthmarketscience.jackcess.impl.UsageMap$ReferenceHandler.createNewUsageMapPage(UsageMap.java:748)
    at com.healthmarketscience.jackcess.impl.UsageMap$ReferenceHandler.addOrRemovePageNumber(UsageMap.java:732)
    at com.healthmarketscience.jackcess.impl.UsageMap.removePageNumber(UsageMap.java:322)
    at com.healthmarketscience.jackcess.impl.PageChannel.allocateNewPage(PageChannel.java:352)
    at com.healthmarketscience.jackcess.impl.TempPageHolder.setNewPage(TempPageHolder.java:104)
    at com.healthmarketscience.jackcess.impl.UsageMap$ReferenceHandler.createNewUsageMapPage(UsageMap.java:748)
    at com.healthmarketscience.jackcess.impl.UsageMap$ReferenceHandler.addOrRemovePageNumber(UsageMap.java:732)
    at com.healthmarketscience.jackcess.impl.UsageMap.removePageNumber(UsageMap.java:322)
    at com.healthmarketscience.jackcess.impl.PageChannel.allocateNewPage(PageChannel.java:352)
    at com.healthmarketscience.jackcess.impl.TempPageHolder.setNewPage(TempPageHolder.java:104)
    at com.healthmarketscience.jackcess.impl.UsageMap$ReferenceHandler.createNewUsageMapPage(UsageMap.java:748)
    at com.healthmarketscience.jackcess.impl.UsageMap$ReferenceHandler.addOrRemovePageNumber(UsageMap.java:732)
    at com.healthmarketscience.jackcess.impl.UsageMap.removePageNumber(UsageMap.java:322)
    at com.healthmarketscience.jackcess.impl.PageChannel.allocateNewPage(PageChannel.java:352)
    at com.healthmarketscience.jackcess.impl.TempPageHolder.setNewPage(TempPageHolder.java:104)
    at com.healthmarketscience.jackcess.impl.UsageMap$ReferenceHandler.createNewUsageMapPage(UsageMap.java:748)
    at com.healthmarketscience.jackcess.impl.UsageMap$ReferenceHandler.addOrRemovePageNumber(UsageMap.java:732)
    at com.healthmarketscience.jackcess.impl.UsageMap.removePageNumber(UsageMap.java:322)
    at com.healthmarketscience.jackcess.impl.PageChannel.allocateNewPage(PageChannel.java:352)
    at com.healthmarketscience.jackcess.impl.TempPageHolder.setNewPage(TempPageHolder.java:104)
    at com.healthmarketscience.jackcess.impl.UsageMap$ReferenceHandler.createNewUsageMapPage(UsageMap.java:748)
    at com.healthmarketscience.jackcess.impl.UsageMap$ReferenceHandler.addOrRemovePageNumber(UsageMap.java:732)
    at com.healthmarketscience.jackcess.impl.UsageMap.removePageNumber(UsageMap.java:322)
    at com.healthmarketscience.jackcess.impl.PageChannel.allocateNewPage(PageChannel.java:352)
    at com.healthmarketscience.jackcess.impl.TempPageHolder.setNewPage(TempPageHolder.java:104)
    at com.healthmarketscience.jackcess.impl.UsageMap$ReferenceHandler.createNewUsageMapPage(UsageMap.java:748)
    at com.healthmarketscience.jackcess.impl.UsageMap$ReferenceHandler.addOrRemovePageNumber(UsageMap.java:732)
    at com.healthmarketscience.jackcess.impl.UsageMap.removePageNumber(UsageMap.java:322)
    at com.healthmarketscience.jackcess.impl.PageChannel.allocateNewPage(PageChannel.java:352)
    at com.healthmarketscience.jackcess.impl.TempPageHolder.setNewPage(TempPageHolder.java:104)
    at com.healthmarketscience.jackcess.impl.UsageMap$ReferenceHandler.createNewUsageMapPage(UsageMap.java:748)
    at com.healthmarketscience.jackcess.impl.UsageMap$ReferenceHandler.addOrRemovePageNumber(UsageMap.java:732)
    at com.healthmarketscience.jackcess.impl.UsageMap.removePageNumber(UsageMap.java:322)
    at com.healthmarketscience.jackcess.impl.PageChannel.allocateNewPage(PageChannel.java:352)
    at com.healthmarketscience.jackcess.impl.TempPageHolder.setNewPage(TempPageHolder.java:104)
    at com.healthmarketscience.jackcess.impl.UsageMap$ReferenceHandler.createNewUsageMapPage(UsageMap.java:748)
    at com.healthmarketscience.jackcess.impl.UsageMap$ReferenceHandler.addOrRemovePageNumber(UsageMap.java:732)
    at com.healthmarketscience.jackcess.impl.UsageMap.removePageNumber(UsageMap.java:322)
    at com.healthmarketscience.jackcess.impl.PageChannel.allocateNewPage(PageChannel.java:352)
    at com.healthmarketscience.jackcess.impl.TempPageHolder.setNewPage(TempPageHolder.java:104)
    at com.healthmarketscience.jackcess.impl.UsageMap$ReferenceHandler.createNewUsageMapPage(UsageMap.java:748)
    at com.healthmarketscience.jackcess.impl.UsageMap$ReferenceHandler.addOrRemovePageNumber(UsageMap.java:732)
    at com.healthmarketscience.jackcess.impl.UsageMap.removePageNumber(UsageMap.java:322)</init>

     

    Last edit: Bryan Cheng 2016-11-07

  • James Ahlborn

    James Ahlborn - 2016-11-05

    i assume you are using the latest versions of all the related projects.

    that stack trace certainly looks like a bug. would be great if you had a repeatable test case.

     

  • Bryan Cheng

    Bryan Cheng - 2016-11-07

    Hi James,

    I'm trying to write a test case for this problem, if it can repeatable, I will upload here.

    But in the meanwhile, I've the observation from the problem.

    1. When I only import the table with problem to an empty mdb database (V2000 format), don't have problem.
    2. Import same resultset to difference table names, without problems. i.e. table1, table2.. total 300MB, 800,000 records per table.
      ImportUtil.importResultSet(resultSet, db, "tableX", SimpleImportFilter.INSTANCE, true);
    3. The problem only with the case with difference resultset to difference tables.[Dead at 142 MB]
      ImportUtil.importResultSet(ResultSet, Database, tableName, SimpleImportFilter.INSTANCE, true);

    I'll try to do other stuff to preventing problem.

    1. Close connection before and after every import process. [Failed]

    Hope can short term prevent the problem. The libraries of my projects as image is uploaded.

    Regards,
    Bryan

     

    Last edit: Bryan Cheng 2016-11-07
    Thumbnail
    libraries.png

  • Bryan Cheng

    Bryan Cheng - 2016-11-07

    Hi James,

    I know that what is the problem happening on the bug.

    The following is the step for regenerate the problem

    1. Create a MS Access file (MDB) manually.
    2. Import some rubbish data to database.i.e. for my test case around 150MB.
    3. Delete all rubbish database from MS Access. The mdb data file will keep the size remain unchanged.
    4. Use ImportUtil.importResultSet(ResultSet, Database, tableName, SimpleImportFilter.INSTANCE, true); for input normal records. The problem will be generated.

    Actually everytime the Jackcess API will try to allocate new page size (around 25MB for my case) before import data into table without concern about existing free space (The space free after used the function "Compact and Repair Database" function on MS Access) on the database. But the program try to somehow cache all free space to in-menory storage (i.e. Page Size), as a result the program cannot handle over-sized of free space.

    This problem can explain why once I re-ordering the import table sequence, the problem will happen in the import of difference table.

    Case may be:

    1. 10MB free -> 20MB free -> 15MB free -> 35MB free (dead)
    2. 10MB free -> 15MB free -> 40MB free (dead)
      It depends on the new allocate size and how many free space remain unused.

    Regards,
    Bryan

     

    Last edit: Bryan Cheng 2016-11-07

  • James Ahlborn

    James Ahlborn - 2016-11-07

    For steps 1 and 2 in your example above, are you using MS Access itself to create and interact with the database, or some other tool?

     

  • Bryan Cheng

    Bryan Cheng - 2016-11-08

    I've used MS Access itself to create Version 2000 format.

    I think no matter any method to create a large size mdb file and then remove the data. You can reproduce the same problem.

     

  • Bryan Cheng

    Bryan Cheng - 2016-11-11

    Can you reproduce the test case? Any things can I help, i.e. Create a bug item or provide more information?

     

  • James Ahlborn

    James Ahlborn - 2016-11-11

    I haven't had the opportunity to try to put together a test case. if you could create a bug, attach a database and some code which will generate that problem, that would be a great help.

     

  • James Ahlborn

    James Ahlborn - 2016-11-12

    i have replicated the problem.

     

  • James Ahlborn

    James Ahlborn - 2016-11-16

    For future reference, this issue has been filed as https://sourceforge.net/p/jackcess/bugs/138/

     

Log in to post a comment.

MongoDB Logo MongoDB