SQL Errors

[macrho]

When I'm devices I'm getting errors in my log (running 3.0.5947.32109):

<table class="log_table_row" cellspacing="0"><tbody><tr><td class="LogDateLong LogDateTime0" colspan="1" style=" white-space: nowrap;" align="left">Apr-23 4:50:04 PM </td><td class="LogPri0" colspan="1" align="left"> </td><td class="LogType0" colspan="3" align="left"> Database </td><td class="LogEntry0" colspan="8" align="left"> Error - Save of the devices to the database did not succeed - see the log for more details. </td></tr> </tbody></table> <table class="log_table_row" cellspacing="0"><tbody><tr> <td class="LogDateLong LogDateTime1" colspan="1" style=" white-space: nowrap;" align="left"> Apr-23 4:50:03 PM </td><td class="LogPri1" colspan="1" align="left"> </td><td class="LogType1" colspan="3" align="left"> Database </td><td class="LogEntry1" colspan="8" align="left"> ERROR, device First Floor Hobbit Hole Gateway Latency [1 Day] failed to be updated or added to the database. </td></tr> </tbody></table> <table class="log_table_row" cellspacing="0"><tbody><tr> <td class="LogDateLong LogDateTime0" colspan="1" style=" white-space: nowrap;" align="left"> Apr-23 4:50:03 PM </td><td class="LogPri0" colspan="1" align="left"> </td><td class="LogType0" colspan="3" align="left"> Error </td><td class="LogEntry0" colspan="8" align="left"> SQL Exception : UPDATE [Devices] SET [ref]=402,[name]='Gateway Latency [1 Day]',[location]='Hobbit Hole',[location2]='First Floor',[Last_Change]='2016-04-23 16:49:17',[devValue]=NaN,[devString]='',[object_version]=101,[object_data]=@ByteArray WHERE [ref]=402; </td></tr> </tbody></table> <table class="log_table_row" cellspacing="0"><tbody><tr> <td class="LogDateLong LogDateTime1" colspan="1" style=" white-space: nowrap;" align="left"> Apr-23 4:50:03 PM </td><td class="LogPri1" colspan="1" align="left"> </td><td class="LogType1" colspan="3" align="left"> Error </td><td class="LogEntry1" colspan="8" align="left"> SQL Exception updating device record in the database: SQL logic error or missing database no such column: NaN, Code=1, Link= </td></tr></tbody></table>

[Ultrajones]

When I'm devices I'm getting errors in my log (running 3.0.5947.32109):

<table class="log_table_row" cellspacing="0"><tbody><tr><td class="LogDateLong LogDateTime0" colspan="1" style=" white-space: nowrap;" align="left">Apr-23 4:50:04 PM </td><td class="LogPri0" colspan="1" align="left"> </td><td class="LogType0" colspan="3" align="left"> Database </td><td class="LogEntry0" colspan="8" align="left"> Error - Save of the devices to the database did not succeed - see the log for more details. </td></tr> </tbody></table> <table class="log_table_row" cellspacing="0"><tbody><tr> <td class="LogDateLong LogDateTime1" colspan="1" style=" white-space: nowrap;" align="left"> Apr-23 4:50:03 PM </td><td class="LogPri1" colspan="1" align="left"> </td><td class="LogType1" colspan="3" align="left"> Database </td><td class="LogEntry1" colspan="8" align="left"> ERROR, device First Floor Hobbit Hole Gateway Latency [1 Day] failed to be updated or added to the database. </td></tr> </tbody></table> <table class="log_table_row" cellspacing="0"><tbody><tr> <td class="LogDateLong LogDateTime0" colspan="1" style=" white-space: nowrap;" align="left"> Apr-23 4:50:03 PM </td><td class="LogPri0" colspan="1" align="left"> </td><td class="LogType0" colspan="3" align="left"> Error </td><td class="LogEntry0" colspan="8" align="left"> SQL Exception : UPDATE [Devices] SET [ref]=402,[name]='Gateway Latency [1 Day]',[location]='Hobbit Hole',[location2]='First Floor',[Last_Change]='2016-04-23 16:49:17',[devValue]=NaN,[devString]='',[object_version]=101,[object_data]=@ByteArray WHERE [ref]=402; </td></tr> </tbody></table> <table class="log_table_row" cellspacing="0"><tbody><tr> <td class="LogDateLong LogDateTime1" colspan="1" style=" white-space: nowrap;" align="left"> Apr-23 4:50:03 PM </td><td class="LogPri1" colspan="1" align="left"> </td><td class="LogType1" colspan="3" align="left"> Error </td><td class="LogEntry1" colspan="8" align="left"> SQL Exception updating device record in the database: SQL logic error or missing database no such column: NaN, Code=1, Link= </td></tr></tbody></table>

That's not my plug-in. That's HomeSeer itself producing that error. Trying deleting that device. The plug-in should recreate it.

[mminehan]

I'm getting the same errors:

May-01 11:40:03 a.m. Database Error - Save of the devices to the database did not succeed - see the log for more details.
May-01 11:40:02 a.m. Database ERROR, device Plug-ins UltraMon3 Plugin Internet Latency [1 Day] failed to be updated or added to the database.
May-01 11:40:02 a.m. Error SQL Exception : UPDATE [Devices] SET [ref]=1114,[name]='Internet Latency [1 Day]',[location]='UltraMon3 Plugin',[location2]='Plug-ins',[Last_Change]='2016-05-01 11:38:38',[devValue]=NaN,[devString]='',[object_version]=101,[object_data]=@ByteArray WHERE [ref]=1114;
May-01 11:40:02 a.m. Error SQL Exception updating device record in the database: SQL logic error or missing database no such column: NaN, Code=1, Link=
May-01 11:40:02 a.m. Database ERROR, device Plug-ins UltraMon3 Plugin Gateway Latency [1 Day] failed to be updated or added to the database.
May-01 11:40:02 a.m. Error SQL Exception : UPDATE [Devices] SET [ref]=1107,[name]='Gateway Latency [1 Day]',[location]='UltraMon3 Plugin',[location2]='Plug-ins',[Last_Change]='2016-05-01 11:38:00',[devValue]=NaN,[devString]='',[object_version]=101,[object_data]=@ByteArray WHERE [ref]=1107;
May-01 11:40:02 a.m. Error SQL Exception updating device record in the database: SQL logic error or missing database no such column: NaN, Code=1, Link=

I'll try deleting the device as you suggest. But it is interesting that someone else is having the same issue.

Interestingly this is fequently preceded a few minutes by a loss of connection to my Brultech GEM:

May-01 11:39:54 a.m. Warning Watchdog Timer indicates No response response from the Brultech device '192.168.1.21:8000' for 320 seconds.. Attempting to reconnect ...

Marty

[Ultrajones]

How often do those errors appear in the logs?

[mminehan]

Roughly every hour and 10 minutes or so. I did a search on the log for "SQL Exception updating device record in the database" to filter it:

May-01 1:18:51 p.m. Error SQL Exception updating device record in the database: SQL logic error or missing database no such column: NaN, Code=1, Link=
May-01 1:18:51 p.m. Error SQL Exception updating device record in the database: SQL logic error or missing database no such column: NaN, Code=1, Link=
May-01 11:40:02 a.m. Error SQL Exception updating device record in the database: SQL logic error or missing database no such column: NaN, Code=1, Link=
May-01 11:40:02 a.m. Error SQL Exception updating device record in the database: SQL logic error or missing database no such column: NaN, Code=1, Link=
May-01 10:33:02 a.m. Error SQL Exception updating device record in the database: SQL logic error or missing database no such column: NaN, Code=1, Link=
May-01 10:33:02 a.m. Error SQL Exception updating device record in the database: SQL logic error or missing database no such column: NaN, Code=1, Link=
May-01 9:27:01 a.m. Error SQL Exception updating device record in the database: SQL logic error or missing database no such column: NaN, Code=1, Link=
May-01 9:27:01 a.m. Error SQL Exception updating device record in the database: SQL logic error or missing database no such column: NaN, Code=1, Link=
May-01 8:20:00 a.m. Error SQL Exception updating device record in the database: SQL logic error or missing database no such column: NaN, Code=1, Link=
May-01 8:20:00 a.m. Error SQL Exception updating device record in the database: SQL logic error or missing database no such column: NaN, Code=1, Link=
May-01 7:13:00 a.m. Error SQL Exception updating device record in the database: SQL logic error or missing database no such column: NaN, Code=1, Link=
May-01 7:13:00 a.m. Error SQL Exception updating device record in the database: SQL logic error or missing database no such column: NaN, Code=1, Link=
May-01 6:09:00 a.m. Error SQL Exception updating device record in the database: SQL logic error or missing database no such column: NaN, Code=1, Link=
May-01 6:09:00 a.m. Error SQL Exception updating device record in the database: SQL logic error or missing database no such column: NaN, Code=1, Link=
May-01 6:09:00 a.m. Error SQL Exception updating device record in the database: SQL logic error or missing database no such column: NaN, Code=1, Link=
May-01 6:09:00 a.m. Error SQL Exception updating device record in the database: SQL logic error or missing database no such column: NaN, Code=1, Link=

I'm not sure if the GEM issue is related or not. I am having issues with the GEM loosing it's connection to UltraECM. So I have temporarily disabled the UltraMon plug in to see if the GEM issue goes away or not. But I won't be able to tell for a few hours.

I have previously rebooted the GEM, HS and my wireless router (GEM connected via WiFi) but this did not seem to fix the problem. I'll keep you informed.

Ohh.....just to complicated matters I have just now upgraded HS to v.288.


Marty

[mminehan]

So...I think UltraMon is causing some weird errors. Occasionally I am getting the following:

May-09 4:31:38 p.m. 1 Error System.InvalidCastException: Conversion from type 'DBNull' to type 'String' is not valid. at Microsoft.VisualBasic.CompilerServices.Conversions.ToString( Object Value) at HSPI_ULTRAMON3.hspi_plugin.ProcessPingReply(DataRow& drHost, PingReply& MyPingReply)
May-09 4:31:38 p.m. 1 Error System.InvalidCastException

I addition it seems to be causing problems with my GEM connection. I say this because a while ago I was getting Watchdog errors from the GEM plugin while UltraMon was running, in addition to the SQL errors from UltraMon. So I disabled the UltraMon plugin for a while, the SQL errors and GEM watchdog errors went away. Yesterday afternoon I re-enabled the UltraMon plugin and the watchdog errors have returned.

May-09 1:33:53 p.m. Warning Watchdog Timer indicates No response response from the Brultech device '192.168.1.21:8000' for 324 seconds.. Attempting to reconnect ...

I know this is an anecdote at this stage but I can't help but think these are related. Is this possible?

Marty

[Ultrajones]

Can you send a screenshot of the UltraMon3 HSPI options web page to ultrajones@hotmail.com?

[mminehan]

Ok...done.

Here are all the errors since a reboot. My system reboots at 2am.


May-10 8:36:13 a.m. Warning Watchdog Timer indicates No response response from the Brultech device '192.168.1.21:8000' for 315 seconds.. Attempting to reconnect ...
May-10 8:05:12 a.m. Warning Watchdog Timer indicates No response response from the Brultech device '192.168.1.21:8000' for 315 seconds.. Attempting to reconnect ...
May-10 4:38:42 a.m. Warning Watchdog Timer indicates No response response from the Brultech device '192.168.1.21:8000' for 330 seconds.. Attempting to reconnect ...
May-10 3:46:42 a.m. Warning Watchdog Timer indicates No response response from the Brultech device '192.168.1.21:8000' for 314 seconds.. Attempting to reconnect ...
May-10 3:03:12 a.m. Warning Watchdog Timer indicates No response response from the Brultech device '192.168.1.21:8000' for 329 seconds.. Attempting to reconnect ...

Marty

[mminehan]

Hmmm.....seem to be having ongoing issues with UltraMon:

May-13 8:08:59 a.m. 1 Error System.Net.Sockets.SocketException (0x80004005): An existing connection was forcibly closed by the remote host at System.Net.Sockets.Socket.ReceiveFrom(Byte[] buffer, Int32 offset, Int32 size, SocketFlags socketFlags, EndPoint& remoteEP) at System.Net.Sockets.UdpClient.Receive(IPEndPoint& remoteEP) at HSPI_ULTRAMON3.hspi_plugin.UPnPDiscovery()
May-13 8:08:59 a.m. 1 Error System.Net.Sockets.SocketException
May-13 8:08:59 a.m. Error An error occured while processing the UPnP notify packet.
May-13 8:08:59 a.m. 1 Error System.Net.NetworkInformation.PingException: An exception occurred during a Ping request. ---> System.ComponentModel.Win32Exception: Unknown error (0x2b2a) at System.Net.NetworkInformation.Ping.InternalSend(IPAddress address, Byte[] buffer, Int32 timeout, PingOptions options, Boolean async) at System.Net.NetworkInformation.Ping.Send(IPAddress address, Int32 timeout, Byte[] buffer, PingOptions options) --- End of inner exception stack trace --- at System.Net.NetworkInformation.Ping.Send(IPAddress address, Int32 timeout, Byte[] buffer, PingOptions options) at System.Net.NetworkInformation.Ping.Send(String hostNameOrAddress, Int32 timeout, Byte[] buffer, PingOptions options) at System.Net.NetworkInformation.Ping.Send(String hostNameOrAddress, Int32 timeout, Byte[] buffer) at HSPI_ULTRAMON3.hspi_plugin.MonitorGatewayConnection()
May-13 8:08:59 a.m. 1 Error System.Net.NetworkInformation.PingException

Marty

[Ultrajones]

Hmmm.....seem to be having ongoing issues with UltraMon:

May-13 8:08:59 a.m. 1 Error System.Net.Sockets.SocketException (0x80004005): An existing connection was forcibly closed by the remote host at System.Net.Sockets.Socket.ReceiveFrom(Byte[] buffer, Int32 offset, Int32 size, SocketFlags socketFlags, EndPoint& remoteEP) at System.Net.Sockets.UdpClient.Receive(IPEndPoint& remoteEP) at HSPI_ULTRAMON3.hspi_plugin.UPnPDiscovery()
May-13 8:08:59 a.m. 1 Error System.Net.Sockets.SocketException
May-13 8:08:59 a.m. Error An error occured while processing the UPnP notify packet.
May-13 8:08:59 a.m. 1 Error System.Net.NetworkInformation.PingException: An exception occurred during a Ping request. ---> System.ComponentModel.Win32Exception: Unknown error (0x2b2a) at System.Net.NetworkInformation.Ping.InternalSend(IPAddress address, Byte[] buffer, Int32 timeout, PingOptions options, Boolean async) at System.Net.NetworkInformation.Ping.Send(IPAddress address, Int32 timeout, Byte[] buffer, PingOptions options) --- End of inner exception stack trace --- at System.Net.NetworkInformation.Ping.Send(IPAddress address, Int32 timeout, Byte[] buffer, PingOptions options) at System.Net.NetworkInformation.Ping.Send(String hostNameOrAddress, Int32 timeout, Byte[] buffer, PingOptions options) at System.Net.NetworkInformation.Ping.Send(String hostNameOrAddress, Int32 timeout, Byte[] buffer) at HSPI_ULTRAMON3.hspi_plugin.MonitorGatewayConnection()
May-13 8:08:59 a.m. 1 Error System.Net.NetworkInformation.PingException

Marty

Those errors look like your HomeSeer computer's network interface was reset. Can you check your System log to see if you see anything about the same timestamp?

Regards,
Ultrajones

[mminehan]

Hi Ultra,

yes....you are correct. At that time the 'e1dexpress' ethernet became 'disconnected'. I will keep an eye on UltraMon errors and compare them to system logs to see if it is a hardware issue.

Marty

[Ultrajones]

For those experiencing the SQL errors, please download and install UltraMon3 HSPI version 3.0.6013.19563 to see if it resolves the issue.

Regards,
Ultrajones

[mminehan]

Hi Ultra,
I have been running the updated plugin for a few days now and can report that there have been no SQL errors that I can see. All good so far.
And the network errors (loss of connection to the brultech GEM) seem to have stopped too.
Thank you.