INDIGOplotD Causing SQL Logger Error

Posted on
Sun Jun 24, 2018 4:43 pm
GRWilde offline
User avatar
Posts: 173
Joined: Nov 15, 2005
Location: Los Angeles

INDIGOplotD Causing SQL Logger Error

Every morning at approximately one minute past midnight I am getting SQL Logger timed out errors for about a minute or two, then everything resumes as normal. The INDIGOplotD plots do not seem to be affected because this is a short term problem. The error always occurs immediately after the following INDIGOplotD command:

INDIGOplotD command: shift day done

I am using PostgreSQL, and it appears that any device attempting to log data for a minute or two following this INDIGOplotD command times out with an error. An example is the following:

2018-06-23 00:01:51.795 SQL Logger Error exception trying to insert row into table device_history_336233721 ("Ecobee Thermostat")
2018-06-23 00:01:51.978 SQL Logger Error timed out
2018-06-23 00:01:51.979 SQL Logger Error INSERT INTO device_history_336233721 ("authenticated", "autoaway", "autohome", "climate", "humidityinput1", "humidityinputsall", "hvaccoolerison", "hvacdehumidifierison", "hvacfanison", "hvacfanmode", "hvacfanmode_ui", "hvacfanmodeisalwayson", "hvacfanmodeisauto", "hvacheaterison", "hvachumidifierison", "hvacoperationmode", "hvacoperationmode_ui", "hvacoperationmodeisauto", "hvacoperationmodeiscool", "hvacoperationmodeisheat", "hvacoperationmodeisoff", "hvacoperationmodeisprogramauto", "hvacoperationmodeisprogramcool", "hvacoperationmodeisprogramheat", "occupied", "setpointcool", "setpointcool_ui", "setpointheat", "setpointheat_ui", "temperatureinput1", "temperatureinput1_ui", "temperatureinput2", "temperatureinput2_ui", "temperatureinputsall") VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s);

George Wilde

Posted on
Sun Jun 24, 2018 10:45 pm
kw123 offline
User avatar
Posts: 8333
Joined: May 12, 2013
Location: Dallas, TX

Re: INDIGOplotD Causing SQL Logger Error

Could you try To do
menu/ importsql for 2 days

Tests the SQLite command indigoplotd does shortly after midnight.
If that causes the error it means the sql dB is in overload and can not answer Indigos request for adding data.

What server do you use and how big is your sql db. And do you use Postgres or SQLite ?


Sent from my iPhone using Tapatalk

Posted on
Sun Jun 24, 2018 11:31 pm
GRWilde offline
User avatar
Posts: 173
Joined: Nov 15, 2005
Location: Los Angeles

Re: INDIGOplotD Causing SQL Logger Error

I am using Indigo running on a Mac Mini with PostgreSQL 10. The size of the database is about 1.2 GB.

George Wilde

Posted on
Mon Jun 25, 2018 8:45 am
kw123 offline
User avatar
Posts: 8333
Joined: May 12, 2013
Location: Dallas, TX

Re: INDIGOplotD Causing SQL Logger Error

can you try the manual reimport ..and check if it produces the same error?

And what kind of Mac mini? i5 i7 DUO (2008-9) ...? the DUO might have some performance issues , the i5 / i7 should not

Posted on
Mon Jun 25, 2018 1:26 pm
GRWilde offline
User avatar
Posts: 173
Joined: Nov 15, 2005
Location: Los Angeles

Re: INDIGOplotD Causing SQL Logger Error

I am using a 2GHz Intel Core i7 Mac Mini (Server version with two drives) (mid 2011 model).

I tried the two day manual import many times with no problems (although in one case it did result in a single SQL error - but nothing like what occurs during one to two minutes past midnight).

I suspect a lot of things are happening around midnight which produce excessive SQL logging demands. Is there any way to delay some of this pressure on SQL logging to a little later time? Even 5 minutes might help. I think some of my plugins try to log again every few seconds if they are unsuccessful, and this is causing repeated failures for a couple of minutes. It tends to be a couple of plugins which log lots of data that are seeing repeated failures, and I am plotting data from these plugins.

George Wilde

Posted on
Mon Jun 25, 2018 11:44 pm
kw123 offline
User avatar
Posts: 8333
Joined: May 12, 2013
Location: Dallas, TX

Re: INDIGOplotD Causing SQL Logger Error

Will add a time option to do the midnight sqload when I am back.

[EDIT] It currently does it at 15 minutes after midnight. that does not seem to fit with your sql error, they occur earlier..

"shift one day" only does some internal indexing has nothing to do with SQL


Sent from my iPhone using Tapatalk

Posted on
Tue Jun 26, 2018 3:48 am
GRWilde offline
User avatar
Posts: 173
Joined: Nov 15, 2005
Location: Los Angeles

Re: INDIGOplotD Causing SQL Logger Error

It must be that something else is causing the problem because there are no errors at 15 minutes past midnight. This problem is always within the first two or three minutes past midnight, and then does not occur any more. It just always occurs after the shift one day command so I assumed that it was causing the problem. I have no scheduled events occurring at that time and there are no other logged events. It may be that some plugin is doing extensive housekeeping at midnight and logging a bunch of data. Fortunately there is no real long term issue since the lost data is just momentary and is not even visible in the plots. If I were not capturing and reporting every Indigo Log error I probably never would have noticed it. Thanks for your help. I really enjoy using your plugin.

George Wilde

Posted on
Tue Jun 26, 2018 5:23 am
kw123 offline
User avatar
Posts: 8333
Joined: May 12, 2013
Location: Dallas, TX

Re: INDIGOplotD Causing SQL Logger Error

Which other plugin are running? The brut force method is to switch them off one by one and let midnight roll by.

Indigo plot does not need to run, it will get its info through the sqlimport once it starts.

The fastes algorithm is to cut the plugins in half. If it still happens it’s likely the ones the at left running then you do it again. And the other way around. Will take you 4-5 midnights to pinpoint it.
But if the error requires several plugins to run it naturally gets more complicated.

Pibeacon is updating device states frequently if you have many sensors and or iBeacon and rpis

UniFi and fingscan should not do too many updates.

These the ones I know

I have sometimes sql problems when device states change from string to number or float and the properties in the sql database change. But that only happens once for each state and not at midnight.


Sent from my iPhone using Tapatalk

Posted on
Wed Jul 04, 2018 6:15 pm
GRWilde offline
User avatar
Posts: 173
Joined: Nov 15, 2005
Location: Los Angeles

Re: INDIGOplotD Causing SQL Logger Error

Thanks for you help. I found that the problem was attributable to a single plugin (TED 5000, which is an outdated plugin no longer supported). Once I disabled it, there were no further SQL errors for the last several days. It is unfortunate that this plugin has not been updated, because I used it to generate great plots of my power consumption, voltage, and electricity cost vs time. I even had a Scatterplot showing color coded electricity cost keyed by my electricity time of day tiers. Several years ago I also used a Rainforest Energy Eagle Plugin that also generated great plots, and this plugin obtained data directly from my Southern California Edison Smart Meter. Unfortunately Rainforest changed their API and this plugin no longer works, although there are several online sites that do generate graphs of the Eagle usage. It is too bad that these energy plugins are no longer supported because they were a great way to monitor energy usage in Indigo. Perhaps someone could take a crack at updating them.

George Wilde

Page 1 of 1

Who is online

Users browsing this forum: No registered users and 3 guests