Microsoft Access Connector Java Heap Space

[mpsz]

Hi All,

I’m having troubles with MS Access connector trying to read a large MDB file (1.1GB).
The connector node keeps running for some time and then it crashes with a Java Heap Space error.
Seems that it´s trying to read the entire mdb file before passing the connection data to Table Selection node.
It´s 8GB RAM machine running KNIME 4.2.3 Windows 10 and the heap space parameter is set to: -Xmx4096m
The same flow runs Ok with smaller MDB files.

Here is my flow snippet:

Following is the error log:

2020-12-03 11:43:33,015 : ERROR : KNIME-Worker-5-Microsoft Access Connector 0:84 : : Node : Microsoft Access Connector : 0:84 : Execute failed: Java heap space
java.sql.SQLException: Java heap space
at org.knime.database.util.Futures.throwDatabaseExecutionException(Futures.java:112)
at org.knime.database.connection.impl.DBConnectionManager.initializeConnection(DBConnectionManager.java:516)
at org.knime.database.connection.impl.DBConnectionManager.(DBConnectionManager.java:275)
at org.knime.database.session.impl.DefaultDBSession.(DefaultDBSession.java:226)
at org.knime.database.session.impl.DBSessionFactory.create(DBSessionFactory.java:81)
at org.knime.database.session.DBSessionCache.getOrCreate(DBSessionCache.java:151)
at org.knime.database.node.connector.AbstractDBConnectorNodeModel.registerSession(AbstractDBConnectorNodeModel.java:388)
at org.knime.database.node.connector.AbstractDBConnectorNodeModel.execute(AbstractDBConnectorNodeModel.java:219)
at org.knime.database.node.connector.file.FileDBConnectorNodeModel.execute(FileDBConnectorNodeModel.java:108)
at org.knime.core.node.NodeModel.executeModel(NodeModel.java:576)
at org.knime.core.node.Node.invokeFullyNodeModelExecute(Node.java:1236)
at org.knime.core.node.Node.execute(Node.java:1016)
at org.knime.core.node.workflow.NativeNodeContainer.performExecuteNode(NativeNodeContainer.java:558)
at org.knime.core.node.exec.LocalNodeExecutionJob.mainExecute(LocalNodeExecutionJob.java:95)
at org.knime.core.node.workflow.NodeExecutionJob.internalRun(NodeExecutionJob.java:201)
at org.knime.core.node.workflow.NodeExecutionJob.run(NodeExecutionJob.java:117)
at org.knime.core.util.ThreadUtils$RunnableWithContextImpl.runWithContext(ThreadUtils.java:334)
at org.knime.core.util.ThreadUtils$RunnableWithContext.run(ThreadUtils.java:210)
at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511)
at java.util.concurrent.FutureTask.run(FutureTask.java:266)
at org.knime.core.util.ThreadPool$MyFuture.run(ThreadPool.java:123)
at org.knime.core.util.ThreadPool$Worker.run(ThreadPool.java:246)
Caused by: java.lang.OutOfMemoryError: Java heap space
at java.util.LinkedHashMap.newNode(LinkedHashMap.java:256)
at java.util.HashMap.putVal(HashMap.java:631)
at java.util.HashMap.put(HashMap.java:612)
at com.healthmarketscience.jackcess.impl.ColumnImpl.setRowValue(ColumnImpl.java:560)
at com.healthmarketscience.jackcess.impl.TableImpl.getRow(TableImpl.java:708)
at com.healthmarketscience.jackcess.impl.TableImpl.getRow(TableImpl.java:688)
at com.healthmarketscience.jackcess.impl.CursorImpl.getCurrentRow(CursorImpl.java:699)
at com.healthmarketscience.jackcess.impl.CursorImpl$BaseIterator.next(CursorImpl.java:822)
at com.healthmarketscience.jackcess.impl.CursorImpl$BaseIterator.next(CursorImpl.java:779)
at net.ucanaccess.converters.LoadJet$TablesLoader.loadTableData(LoadJet.java:829)
at net.ucanaccess.converters.LoadJet$TablesLoader.loadTableData(LoadJet.java:818)
at net.ucanaccess.converters.LoadJet$TablesLoader.loadTablesData(LoadJet.java:1007)
at net.ucanaccess.converters.LoadJet$TablesLoader.loadTables(LoadJet.java:1055)
at net.ucanaccess.converters.LoadJet$TablesLoader.access$3200(LoadJet.java:261)
at net.ucanaccess.converters.LoadJet.loadDB(LoadJet.java:1558)
at net.ucanaccess.jdbc.UcanaccessDriver.connect(UcanaccessDriver.java:218)
at org.knime.database.connection.UrlDBConnectionController$ControlledDriver.connect(UrlDBConnectionController.java:95)
at org.knime.database.connection.UrlDBConnectionController.createConnection(UrlDBConnectionController.java:308)
at org.knime.database.connection.AbstractConnectionProvider.createConnection(AbstractConnectionProvider.java:89)
at org.knime.database.connection.impl.DBConnectionManager.lambda$2(DBConnectionManager.java:501)
at org.knime.database.connection.impl.DBConnectionManager$$Lambda$1402/533676103.call(Unknown Source)
at java.util.concurrent.FutureTask.run(FutureTask.java:266)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
at java.lang.Thread.run(Thread.java:748)
2020-12-03 13:29:25,961 : ERROR : main : : WindowsDefenderExceptionHandler : : : PowerShell command powershell -inputformat none -outputformat text -NonInteractive -Command Get-MpComputerStatus -ErrorAction Stop | Select-Object -ExpandProperty AMServiceEnabled did not terminate successfully.
2020-12-03 13:29:25,977 : ERROR : main : : WindowsDefenderExceptionHandler : : : Stderr is:
2020-12-03 13:29:25,977 : ERROR : main : : WindowsDefenderExceptionHandler : : : Get-MpComputerStatus : NÆo foi poss¡vel executar o m‚todo extr¡nseco.
2020-12-03 13:29:25,977 : ERROR : main : : WindowsDefenderExceptionHandler : : : No linha:1 caractere:1
2020-12-03 13:29:25,977 : ERROR : main : : WindowsDefenderExceptionHandler : : : + Get-MpComputerStatus -ErrorAction Stop | Select-Object -ExpandPropert …
2020-12-03 13:29:25,977 : ERROR : main : : WindowsDefenderExceptionHandler : : : + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
2020-12-03 13:29:25,977 : ERROR : main : : WindowsDefenderExceptionHandler : : : + CategoryInfo : MetadataError: (MSFT_MpComputerStatus:ROOT\Microsoft…pComputerStatus) [Get-MpComputer
2020-12-03 13:29:25,977 : ERROR : main : : WindowsDefenderExceptionHandler : : : Status], CimException
2020-12-03 13:29:25,977 : ERROR : main : : WindowsDefenderExceptionHandler : : : + FullyQualifiedErrorId : MI RESULT 16,Get-MpComputerStatus
2020-12-03 13:29:25,977 : ERROR : main : : WindowsDefenderExceptionHandler : : :

[ipazin]

Hello @mpsz,

can you increase memory to 6GB and then give it a try? Additionally check this topic where @mlauber71 shared his experience with big MS Access databases:

Accessing huge Microsoft Access DB

Hi, I am using the standard MS-Access Node to for a MS-Access-DB. This database is quite huge (approx. 2GByte). When executing this node, it get’s not finished. Any hints how to deal with big MS-Access-databases? Thanks Werner

Br,
Ivan

[mlauber71]

Here are a few things you can try. First thing is to increase the memory allocated to KNIME like @ipazin suggested. Also you could try and make sure the MS Access DB does not contain any unwanted garbage and is compressed (https://support.microsoft.com/en-us/office/compact-and-repair-a-database-6ee60f16-aed0-40ac-bf22-85fa9f4005b2).

Then you could try and deactivate the “retrieve in configuration” setting in the Connector node. If this is active it will try and make sure that every setting and column is present before you are able to access it - which is useful but might slow down or hinder things with really large databases or murky connections. The downside is you would have to know what you are doing without additional checks.

You might see if there are further tweaks to the connector settings. Especially concerning timeouts (increase the time KNIME has to finish the operation).

Also if you just want to get data from the MS Access DB without big operations. Instead of the combination:
DB Table selector -> DB Reader

you could just use:
DB Query Reader

So there would be no need for additional VIEW created.

And then you might want to make sure there is a maximum of Java Heap space available:

• So do not run any other workflows
• Make sure the workflow is as small as it gets
• maybe run a garbage collector or heavy garbage collector before the operation

Here are some further hints about KNIME and performance you might want to check out:

A meta collection about KNIME and performance and performance tuning and some...

OK and there are some hints concerning Windows Defender in your Log. So you might also want to check if there is no aggressive virus scanner hindering your jobs.

Also you might want to set the LOG level to DEBUG and see if further hints might come up.

[mlauber71]

@mpsz

space parameter is set to: -Xmx4096m

4 GB to process a 1.5 GB table could be a tricky. Also Access is maybe not the best storage if you have other options.

DB Writer to Access DB Hangs at 99%

@qdmt one idea could be to test H2/SQL database: http://h2database.com/html/advanced.html#limits_limitations You could create a new database connection with a current H2 driver (from Maven) and add the “split” parameter to the connection string: jdbc:h2:split:[location=file?<file>][location=in-memory?mem:<database>] [image] You will have to select this new “h2_split” database driver in the H2 Connector instead of the built-in one: [image] The data would then be stored in a local H2/S…