Hi,
After updating DataHub another error was fixed, but this one appeared.
The error is as following:
Error itgendhb212: itgendhb212: External error in Invantive Data Hub.
Exception of type 'System.OutOfMemoryException' was thrown.
System.OutOfMemoryException
I assume this means that the machine using this error is out of memory, since it’s an “external error”.
Is there an easy way to reduce memory usage or split the import of transactionlinesbulk?
An OutOfMemory can be raised when some storage elements exceeds 4 GB (hard limit for some operations even on 64-bit).
Are you replicating TransactionLinesBulk of Exact Online? If so, how many companies or how many lines in total do you expect?
Can you include part of the call stack in the error message above?
Not sure how many lines, at least about 160 companies.
Full message below:
itgendhb204: Connected! Connecting took 3.813 ms.
itgendhb118: Configured to exit on errors.
itgendhb087: Changed provider attribute values (1 rows affected). (406 ms).
itgendhb087: Changed provider attribute values (1 rows affected). (0 ms).
itgendhb087: Changed provider attribute values (1 rows affected). (16 ms).
itgendhb087: Changed provider attribute values (1 rows affected). (16 ms).
itgendhb087: Changed provider attribute values (1 rows affected). (31 ms).
itgendhb085: Select 1 rows. (13.416 ms).
itgendhb117: Configured to continue on errors.
itgendhb018: Changed partitions to use (1 rows affected). (31 ms).
itgendhb085: Select 1 rows. (172 ms).
**** Warning itgendch646: One or more caches remain too long ready and other remain too long obsoleted instead of being purged from the database. Please check contents of Invantive SQL table dc_warning or check the view dc_warnings_r in the backing database. (146 table partitions over freshness in ready state, oldest 8-10-2020 04:00:34, 148 table partitions obsolete, oldest 8-10-2020 12:00:34)
itgendhb085: Select 1 rows. (3.438 ms).
itgendhb085: Select 1 rows. (203 ms).
itgendhb018: Changed partitions to use (156 rows affected). (0 ms).
itgendhb210: Procedural code handled 1 rows. (0 ms).
itgendhb085: Select 1 rows. (922 ms).
itgendhb085: Select 1 rows. (1.844 ms).
itgendhb085: Select 1 rows. (875 ms).
**** Warning itgendhb173: Context: Select /*+ ods(true, interval '20 hours') */ count(*) from ExactOnlineREST.Cashflow.Receivables@eol
Error itgenoda061: itgenoda061: Request timeout. Please consider reducing the scope of your request.
The remote server returned an error: (400) Bad Request.
System.Net.WebException
ValidationException
at System.Net.HttpWebRequest.GetResponse()
at Invantive.Data.Providers.OData.ODataProvider.DoRequest(GlobalState owner, ExecutionOptions executionOptions, HttpWebRequest request, String url, ObjectDefinition objectDefinition, String partitionCode, String callSafeNameOverrule, String anonymizedPostText, Boolean allowRetryOnConnectionLoss, ExecutionStatistics& statistics, ODataErrorProcessingInstructions& oDataErrorProcessingInstructions)
itgendhb085: Select 1 rows. (1.063 ms).
itgendhb085: Select 1 rows. (4.766 ms).
**** Warning itgendhb173: Context: --PATH TO SQL SCRIPT--: select /*+ ods(true, interval '20 hours') */ count(*) from transactionlinesbulk@eol
Error itgendhb212: itgendhb212: External error in Invantive Data Hub.
System.OutOfMemoryException
ValidationException
at System.Text.StringBuilder.ToString()
at Invantive.Data.ResultSet.ContentAsJsonUnspecified(GlobalState owner, ExecutionOptions executionOptions, List`1 converters)
at Invantive.Data.ResultSet.ContentAsJsonDataSet(GlobalState owner, ExecutionOptions executionOptions)
at Invantive.Data.ResultSet.ToJsonDataSet(GlobalState owner, ExecutionOptions executionOptions)
at Invantive.Data.Providers.DataCache.DataCacheProvider.SaveFactsOnDisk(GlobalState owner, ExecutionOptions executionOptions, DcTablePartitionVersion tablePartitionVersion, String diskCacheFileNamePath, ResultSet valueRows, Boolean compressFactsInDiskCache, Boolean encryptFactsInDiskCache)
at Invantive.Data.P...tring actionSql, ParameterList parameters, String callSafeNameOverrule)
at Invantive.Data.ActionProceduresBase.PassthroughSqlActionTable(GlobalState owner, ExecutionOptions executionOptions, String actionSql, ParameterList parameters)
at IDH.P.ProcessStatement(GlobalState owner, ExecutionOptions executionOptions, String statement, ResultSet& resultSet, Action`2 fetchMoreRows, Action`9 executeStatement, Boolean isInInteractiveMode, ScriptingLanguageNextStepSpecification& nextStepSpecification)
at IDH.P.ProcessStatements(GlobalState owner, ExecutionOptions executionOptions, Action`2 fetchMoreRows, Action`9 executeStatement, IEnumerable`1 statements, String sourceFileName, ScriptingLanguageNextStepSpecification& exitSpecification)
itgendhb085: Select 1 rows. (63.740 ms).
itgendhb085: Select 1 rows. (13.318 ms).
itgendhb085: Select 1 rows. (22.763 ms).
itgendhb041: End program execution with exit code 2.
itgendhb068: Program finished on 19-10-2020 11:58:59.
itgenpgm001: Program finished.The itgenoda061 error is something from the Exact Online server and has no relationship with the OutOfMemoryException thrown. For the itgenoda061 Data Hub should retry a few times automatically and then bail out. Please check whether the error occurred at a time when Exact Online might be down for maintenance. When it lasts longer than 10-15 minutes you might receive such an error.
The OutOfMemoryException is triggered in the code (method SaveFactsOnDisk) due to a current limitation. Starting release 20.0, Data Replicator maintain an on-disk copy in addition to the database replica created. This on-disk copy is stored locally, often in %USERPROFILE%\invantive. However, when the on-disk copy exceeds a size of 4 GB for one partition (something in the range of 1-4 million general ledger transactions) this error occurs.
Invantive is working on an improved algorithm to still maintain the on-disk copy even for large result sets. For now, you have to disable the on-disk copy using the connector attribute beta-store-facts-on-disk. From documentation:
Whether to store facts in the disk cache.
The attribute must be defined in the Data Cache connection string on the <connection> tag in the settings*.xml file:
## Corrected Connection String
<connection
name="..."
dataCacheConnectionString="provider=SqlServer;beta-store-facts-on-disk=false;connectionString={Server=...;Database=...;User Id=...;Password=...;Encrypt=false}"
>
This setting beta-store-facts-on-disk=false applies to all providers; it is a generic Data Replicator feature.
Maybe I missed something, but still getting an error; below is the string I used (censored some info)
dataCacheConnectionString="provider=SqlServer;connectionString={Server=--;Encrypt=true;beta-store-facts-on-disk=false;TrustServerCertificate=True;Database=--;User Id=--;Password=--">
This is the error shown now:
**** Warning itgendhb173: Context: select /*+ ods(true, interval '20 hours') */ count(*) from ExactOnlineREST.System.SystemDivisions@eol
Error itgenasl001: itgenasl001: The repository can not be opened due to errors. Please resolve the errors first and then try again.
itgenasl001: The keyword 'beta-store-facts-on-disk' could not be included in the connection string.
Keyword not supported: 'beta-store-facts-on-disk'.Sorry, my error. Did not test it. See corrected version above.
Seemed to have solved both the memory error and the itgenoda061. Somehow I guess they were linked.
Now the script took only 40 seconds.
If you enable the data-cache storage does that mean that ALL data gets stored in the cache and after that the necessary data gets loaded into the SQL DB?
The itgenoda061 was probably not-related; when it occurs it is typically during the night. But I am guessing.
The described functionality for the use of the Data Replicator disk cache is not yet publicly available, but the functional description you give is correct plus some other improvements.
itgenoda061 appeared the first time a few weeks ago, a colleague tried some thing during normal hours (when other scripts worked fine). That’s why I’m pretty sure EOL was not offline or having issues.
Anyhow, thanks for the help once again.
OK, it is special that an itgenoda061 Bad Request appears during office hours on Exact Online. That should only occur when Exact either crashes/has a bug or Invantive code does something stupid. When it happens again, can you create a new topic including the time at which it occurred, excluding confidential data? We can then together study what might cause it and when necessary involve someone from Exact.
Addendum: it seems that the last two weeks more users are expecting higher failure rates from the Exact Online APIs as before. This can be normal (although not pleasant); it seems to improve and deteriorate over time.
I got the same error message the last few days. Adding this line to the connection string worked for me:
beta-store-facts-on-disk=false
Does this mean this environment can’t use trickle loading now?
Enabling/disabling the use of the replica on disk of the database contents has no impact on trickle loading. The replica on disk can be used to reduce the number of interactions between Data Replicator and the backing database such as SQL Server. Trickle loading can be used to reduce the number of interactions between Data Replicator and the source platform (in casu: Exact Online).