QAID # 22402 NOT Published
Please do NOT provide the article number (QAID) and/or URL of this Knowledgebase article or its contents to external customers, as it is NOT Published and/or * INTERNAL ONLY *.
Question / Problem:
When attempting to "Delete data older than" a specified value, the query fails to delete or end requiring IIS to be restarted. What is the best way to approach this?
Answer / Solution:
The following error displays in the "InsightServer_WinApp.log" showing the query timing out:
2017-04-12 04:45:43,550 [f2047b23-7bba-4ba4-b10a-9001d2322afa] ERROR
AltoSoft.Insight.DatabaseManager.SQLQueryExecutor - Executing query:
delete from "HistoryOfField" where "ReceivedTime" < (select min("Date") from "HistoryOfFieldOldest");
delete from "HistoryOfFieldTimeout" where "ReceivedTime" < (select min("Date") from "HistoryOfFieldOldest");
end; TimeOut 1800
Connection: Data Source: User Id=PKAFC_DATA;Password=********; Data Source=APOCRP;
Database name: Oracle Provider name: odp
Oracle.DataAccess.Client.OracleException ORA-01013: user requested cancel of current operation
ORA-06512: at line 2 at Oracle.DataAccess.Client.OracleException.HandleErrorHelper(Int32 errCode, OracleConnection conn, IntPtr opsErrCtx, OpoSqlValCtx* pOpoSqlValCtx, Object src, String procedure, Boolean bCheck)
at AltoSoft.Insight.DatabaseManager.SQLQueryExecutor. b a(IDbCommand dbCommand)
at AltoSoft.Insight.DatabaseManager.SQLQueryExecutor.ExecuteEx(SQLExecutionContext context, CommandExecuteMethod commandExecuteMethod, Boolean read)
This behavior will occur when the data set to delete is too large and the time it takes to delete it exceeds the timeout period.
The menu to delete field data may be launched by loading Altosoft Viewer|Administrator|Admin|Data Processing and under "Delete Fields Older than"
The larger the value you enter, the less data will be deleted. For example if you enter 100 days and and your oldest data is 101 days old it will only delete one day of data.
You may run the following query on the KAFC data database to determine the oldest date that data is logged.
Ex.: select * from "HistoryofField" where "ReceivedTime" <= '7-jun-2017'
Once you determined the date of the oldest data, you may calculate the number of days from that date to present: https://www.timeanddate.com/date/durationresult.html? m1=11&d1=06&y1=2016&m2=4&d2=19&y2=2017
Attempt to delete one day first ("Total days til oldest date"-1). Next decrease the total value to increase the number of days deleted until a desire range is determined.