In DM 10.1 CU16 I am seeing the following MySQL query in workbench all the time on my larger system that has me concerned. I am very familiar with the DM database cleaning process in 9.6 and have not seen this particular query in my 9.6 systems. In 9.6 systems the Delete query is not a JOIN statement like below and they timeout and get canceled after 1800 sec. This DM 10.1 Query with the Join is running for 3600sec. Questions:
- Is this new expected behavior in DM10
- Is there a chance that this will be more problematic that how 9.6 works
I am getting ready to upgrade a system with large database from 9.6 to 10.1 and want to know if I should hold off until Skyline Engineering can take a look at this to see if it is a problem.
Thanks
DELETE a FROM dataavg_31856_7217 a
LEFT OUTER JOIN (
SELECT iPid, iStatus, chIndex, max(iiD) m from dataavg_31856_7217 WHERE (iStatus < 0 AND dtFirst < '2021-09-08 13:45:33') OR
((iStatus = 5 AND dtFirst < '2022-08-09 13:45:33') OR
(iStatus = 60 AND dtFirst < '2021-09-08 13:45:33') OR
(iStatus = 120 AND dtFirst < '2021-09-08 13:45:33'))
GROUP BY iPid ,chIndex, iStatus
) as b
on a.iPid = b.iPid AND a.chIndex = b.chIndex and a.iStatus = b.iStatus
WHERE ((a.iStatus < 0 AND a.dtFirst < '2021-09-08 13:45:33') OR
(a.iStatus = 5 AND a.dtFirst < '2022-08-09 13:45:33') OR
(a.iStatus = 60 AND a.dtFirst < '2021-09-08 13:45:33') OR
(a.iStatus = 120 AND a.dtFirst < '2021-09-08 13:45:33')) AND b.m != a.iiD
Hi Jeff,
I had a look at the code and it seems no changes where done between the versions mentioned. The query you posted should occur on both 9.6 and 10.1 so whilst not new it is definitely expected behaviour. The query should occur every hour for every element in the system.
I am not entirely sure where the difference would come from between 9.6 and 10.1 aside from some versions containing issues in generating the query. Since this is part of the cleaning routine for average trending I would suspect that having it run would cause less issues than when it is missing.
Kind regards,
Thank you for your response Brent. Strange I have not seen this before on my 9.6 systems and I been spending considerable amounts of time recently looking at this stuff. Follow up questions:
1) I am seeing this query run for 3600 sec and then disappear. I assume this is because it is not finishing within the allotted 60 min so DM cancels the query just like is does on the basic (non Join) delete statements were they timeout after 1800sec (30Min). Is this a correct assumption?
2) If my assumption above is correct what is the side affect of this? Does this effectively mean that the database is NOT getting cleaned out since the statement take too long? I know this is the case with the basic (non Join) delete statements after 30min and can be seen as error statements in the SLDBConnection log file (Below is a snip it from my DM10 system)
2022/09/13 11:07:39.022|SLDBConnection|SQLCleaning.RunThreadFunc|ERR|0|124|Error occurred in SQLCleaningThread: MySql.Data.MySqlClient.MySqlException (0x80004005): Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. —> System.TimeoutException: Unable to read data from the transport connection: A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond. —> System.IO.IOException: Unable to read data from the transport connection: A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond. —> System.Net.Sockets.SocketException: A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond
at System.Net.Sockets.NetworkStream.Read(Byte[] buffer, Int32 offset, Int32 size)
— End of inner exception stack trace —
at System.Net.Sockets.NetworkStream.Read(Byte[] buffer, Int32 offset, Int32 size)
at MySql.Data.Common.MyNetworkStream.Read(Byte[] buffer, Int32 offset, Int32 count)
— End of inner exception stack trace —
at MySql.Data.Common.MyNetworkStream.Read(Byte[] buffer, Int32 offset, Int32 count)
at MySql.Data.MySqlClient.TimedStream.Read(Byte[] buffer, Int32 offset, Int32 count)
at System.IO.BufferedStream.Read(Byte[] array, Int32 offset, Int32 count)
at MySql.Data.MySqlClient.MySqlStream.ReadFully(Stream stream, Byte[] buffer, Int32 offset, Int32 count)
at MySql.Data.MySqlClient.MySqlStream.LoadPacket()
at MySql.Data.MySqlClient.MySqlStream.ReadPacket()
at MySql.Data.MySqlClient.NativeDriver.GetResult(Int32& affectedRow, Int64& insertedId)
at MySql.Data.MySqlClient.Driver.NextResult(Int32 statementId, Boolean force)
at MySql.Data.MySqlClient.MySqlDataReader.NextResult()
at MySql.Data.MySqlClient.MySqlCommand.ExecuteReader(CommandBehavior behavior)
at MySql.Data.MySqlClient.ExceptionInterceptor.Throw(Exception exception)
at MySql.Data.MySqlClient.MySqlConnection.HandleTimeoutOrThreadAbort(Exception ex)
at MySql.Data.MySqlClient.MySqlCommand.ExecuteReader(CommandBehavior behavior)
at MySql.Data.MySqlClient.MySqlCommand.ExecuteNonQuery()
at Skyline.DataMiner.SLDatabase.SLMySQL.ExecuteNonQuery(MySqlConnection conn, String sql, Int32 timeOut)
at Skyline.DataMiner.SLDatabase.SLMySQL.CleanAverageDataTable(String id, DateTime range5min, DateTime range1Hour, DateTime range1Day, Boolean partitioned)
Thanks again
As this question has been inactive for a long time, we will now close it. If you want further assistance, feel free to post a new question or contact techsupport@skyline.be.