SQL Logger Errors

This morning I saw these errors (the event log is flooded by the errors. it It seems that the sql logger is not updating the Postgres db. Indigo works fine for all things not needing the Postgres db.

Anyone any thoughts?



Schedule flowControl_setVariable_FlowFlowing
SQL Logger Error exception trying to insert row into table variable_history_957539585 ("varWemos241LastStateDate_current")
SQL Logger Error 'NoneType' object has no attribute 'execute'
SQL Logger Error INSERT INTO variable_history_957539585 (value) VALUES (%s);
SQL Logger Error exception trying to insert row into table variable_history_1438049675 ("varWemos241ChangeTimeSinceLast")
SQL Logger Error 'NoneType' object has no attribute 'execute'
SQL Logger Error INSERT INTO variable_history_1438049675 (value) VALUES (%s);
SQL Logger Error exception trying to insert row into table variable_history_725709259 ("varWemos233LastStateDate_current")
SQL Logger Error 'NoneType' object has no attribute 'execute'
SQL Logger Error INSERT INTO variable_history_725709259 (value) VALUES (%s);
SQL Logger Error exception trying to insert row into table variable_history_1023216641 ("varWemos233ChangeTimeSinceLast")
SQL Logger Error 'NoneType' object has no attribute 'execute'
SQL Logger Error INSERT INTO variable_history_1023216641 (value) VALUES (%s);
INDIGOplotD Error restarting SQL import, it seems to hang. If this happens several times reload INDIGOplotD :
SQL Logger Error exception trying to insert row into table device_history_865742562 ("hueSensor_luminance")
SQL Logger Error 'NoneType' object has no attribute 'execute'
SQL Logger Error INSERT INTO device_history_865742562 ("lastupdated_ui") VALUES (%s);

Your issue seems to be that somehow the connection to the PostgreSQL DB is not establishing or has been otherwise lost (the .execute referenced is the connection object and it is not properly set. ) I would start with the easiest-to-check... Have you opened the SQL Logger Configuration screen and verified that everything looks good? I have no idea what would be the root cause, but if the preferences have gotten messed up then that could potentially cause this.

Also, how are you running the DB -- as the application or the full service or on another box?

Adam

I looked a little more at it. It's all strange to me..
The configuration screen looks ok (as far as I can tell from the instructions)
Every time I Stop and Start the Indigo Server it connects to the server and stores a value (for each device) in the Postgres DB. Then nothing more in the db, and errors in the indigo event log.

Try
Disable logger plugin
wait 1 minute
Enable sqllogger

When the logger is busy reindexing with large databases indigo might time out when updating sql and that could create these error messages

By stopping sqllogger you give it time to do the reindexing.

That worked for me

Karl


Sent from my iPhone using Tapatalk

thanks for the tip. I tested to disable the sql logger for 3 hours, but unfortunately when I re-activated it, the errors have come back. (as well as one post in the data base of the device I was looking at.
So it seems to connect one time and not more.

Mattias

[matt (support)]

Is your Postgres server on the same Mac as the Indigo Server? Wonder if there is a network issue that is breaking the socket connection?

Also, try connecting another client (GUI or command line) to the Postgres server before when you restart the SQL Logger plugin, then see if that connection remains stable or if it is also dropped. Maybe the entire Postgres server is going down?

Yes its on the same server. I’m using the tool pgadmin4 to look at the tables, and that seem to work all the time. Not sure if that’s what you mean though (I’m not really in my comfort zone here...

So, if I have Pgadmin open looking at a table - restarts sqllogger - i get a ‘application has lost database connection’ when I refresh pgadmin. Second refresh the table shows up with a new row of device data

[matt (support)]

Have you tried turning on the Show debug logging of SQL commands checkbox in the plugin's configuration dialog? I'd be curious if that gives additional details around the error. I'd also like to see what it logs in the several lines before the errors start showing.

I turned on the debug and restarted the plugin . Here's the first part:

Stopped plugin "SQL Logger 7.4.1"
Starting plugin "SQL Logger 7.4.1" (pid 2892)
Started plugin "SQL Logger 7.4.1"
SQL Logger connected to indigo_history as postgres on 127.0.0.1
SQL Logger searching for unused device and variable tables
SQL Logger search complete (none found)
SQL Logger updating all device tables
SQL Logger Debug INSERT INTO device_history_1493527508 ("apparenttemperature", "cloudcover", "cloudcover_ui", "dewpoint", "humidity", "humidity_ui", "icon", "ozone", "pressure", "pressure_ui", "state", "state_preparing", "state_ready", "state_unavailable", "summary", "temperature", "visibility", "visibility_ui", "windbearing", "windbearing_ui", "windspeed", "windspeed_ui") VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s);
SQL Logger Debug (u'74.6', u'25.0', u'25.0%', u'69.4', u'87.0', u'87.0%', u'partly-cloudy-night', u'304.9', u'1011.32', u'1011.32 mb', u'unavailable', u'False', u'False', u'True', u'Partly Cloudy', u'73.4', u'10.0', u'10.0 mi', u'203.0', u'203.0\xb0', u'5.04', u'5.04 mph')
SQL Logger Debug DELETE FROM device_history_1493527508 WHERE ts < %s;
SQL Logger Debug ('2017-11-17',)
SQL Logger Debug INSERT INTO device_history_1936802572 ("accumenergytimedelta", "accumenergytimedelta_ui", "accumenergytotal", "accumenergytotal_ui", "curenergylevel", "curenergylevel_ui", "onoffstate") VALUES (%s, %s, %s, %s, %s, %s, %s);
SQL Logger Debug (u'0', u'0 seconds', u'2162.204', u'2162.204 kWh', u'1.162', u'1.162 W', u'True')
SQL Logger Debug DELETE FROM device_history_1936802572 WHERE ts < %s;
SQL Logger Debug ('2017-11-17',)
SQL Logger Debug INSERT INTO device_history_466243771 ("accumenergytimedelta", "accumenergytimedelta_ui", "accumenergytotal", "accumenergytotal_ui", "curenergylevel", "curenergylevel_ui", "onoffstate") VALUES (%s, %s, %s, %s, %s, %s, %s);
SQL Logger Debug (u'62134100', u'62134100 seconds', u'22.118', u'22.118 kWh', u'2.315', u'2.315 W', u'True')
SQL Logger Debug DELETE FROM device_history_466243771 WHERE ts < %s;
SQL Logger Debug ('2017-11-17',)
SQL Logger Debug INSERT INTO device_history_1538597399 ("accumenergytimedelta", "accumenergytimedelta_ui", "accumenergytotal", "accumenergytotal_ui", "curenergylevel", "curenergylevel_ui", "onoffstate") VALUES (%s, %s, %s, %s, %s, %s, %s);
SQL Logger Debug (u'47824895', u'47824895 seco

from the middle of the textstream:

Stopped plugin "SQL Logger 7.4.1"
Starting plugin "SQL Logger 7.4.1" (pid 2892)
Started plugin "SQL Logger 7.4.1"
SQL Logger connected to indigo_history as postgres on 127.0.0.1
SQL Logger searching for unused device and variable tables
SQL Logger search complete (none found)
SQL Logger updating all device tables
SQL Logger Debug INSERT INTO device_history_1493527508 ("apparenttemperature", "cloudcover", "cloudcover_ui", "dewpoint", "humidity", "humidity_ui", "icon", "ozone", "pressure", "pressure_ui", "state", "state_preparing", "state_ready", "state_unavailable", "summary", "temperature", "visibility", "visibility_ui", "windbearing", "windbearing_ui", "windspeed", "windspeed_ui") VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s);
SQL Logger Debug (u'74.6', u'25.0', u'25.0%', u'69.4', u'87.0', u'87.0%', u'partly-cloudy-night', u'304.9', u'1011.32', u'1011.32 mb', u'unavailable', u'False', u'False', u'True', u'Partly Cloudy', u'73.4', u'10.0', u'10.0 mi', u'203.0', u'203.0\xb0', u'5.04', u'5.04 mph')
SQL Logger Debug DELETE FROM device_history_1493527508 WHERE ts < %s;
SQL Logger Debug ('2017-11-17',)
SQL Logger Debug INSERT INTO device_history_1936802572 ("accumenergytimedelta", "accumenergytimedelta_ui", "accumenergytotal", "accumenergytotal_ui", "curenergylevel", "curenergylevel_ui", "onoffstate") VALUES (%s, %s, %s, %s, %s, %s, %s);
SQL Logger Debug (u'0', u'0 seconds', u'2162.204', u'2162.204 kWh', u'1.162', u'1.162 W', u'True')
SQL Logger Debug DELETE FROM device_history_1936802572 WHERE ts < %s;
SQL Logger Debug ('2017-11-17',)
SQL Logger Debug INSERT INTO device_history_466243771 ("accumenergytimedelta", "accumenergytimedelta_ui", "accumenergytotal", "accumenergytotal_ui", "curenergylevel", "curenergylevel_ui", "onoffstate") VALUES (%s, %s, %s, %s, %s, %s, %s);
SQL Logger Debug (u'62134100', u'62134100 seconds', u'22.118', u'22.118 kWh', u'2.315', u'2.315 W', u'True')
SQL Logger Debug DELETE FROM device_history_466243771 WHERE ts < %s;
SQL Logger Debug ('2017-11-17',)
SQL Logger Debug INSERT INTO device_history_1538597399 ("accumenergytimedelta", "accumenergytimedelta_ui", "accumenergytotal", "accumenergytotal_ui", "curenergylevel", "curenergylevel_ui", "onoffstate") VALUES (%s, %s, %s, %s, %s, %s, %s);
SQL Logger Debug (u'47824895', u'47824895 seco

[matt (support)]

Your 2nd Event Log snippet looks identical to the first?

Yes, it’s just a cut three pages down. Just wanted toshowit contiues down ina similar fashon

[matt (support)]

So there are no errors now and it is working?

I have no idea why I choose that snippet This is a new one a bit further down in the log, after I restarted the plugin

SQL Logger Debug (u'inactive', u'0', u'0', u'0', u'0', u'120', u'inactive', u'False', u'True', u'False')
SQL Logger Debug DELETE FROM device_history_1703387849 WHERE ts < %s;
SQL Logger Debug ('2017-11-17',)
SQL Logger Debug INSERT INTO device_history_107654000 ("longstatusstring", "timeleftdays", "timelefthours", "timeleftminutes", "timeleftseconds", "timerstartvalueseconds", "timerstatus", "timerstatus_active", "timerstatus_inactive", "timerstatus_paused") VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s);
SQL Logger Debug (u'inactive', u'0', u'0', u'0', u'0', u'120', u'inactive', u'False', u'True', u'False')
SQL Logger Debug DELETE FROM device_history_107654000 WHERE ts < %s;
SQL Logger Debug ('2017-11-17',)
SQL Logger Debug INSERT INTO device_history_1672970085 ("longstatusstring", "timeleftdays", "timelefthours", "timeleftminutes", "timeleftseconds", "timerstartvalueseconds", "timerstatus", "timerstatus_active", "timerstatus_inactive", "timerstatus_paused") VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s);
SQL Logger Debug (u'inactive', u'0', u'0', u'0', u'0', u'3600', u'inactive', u'False', u'True', u'False')
SQL Logger Debug DELETE FROM device_history_1672970085 WHERE ts < %s;
SQL Logger Debug ('2017-11-17',)
SQL Logger Debug INSERT INTO device_history_751898018 ("longstatusstring", "timeleftdays", "timelefthours", "timeleftminutes", "timeleftseconds", "timerstartvalueseconds", "timerstatus", "timerstatus_active", "timerstatus_inactive", "timerstatus_paused") VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s);
SQL Logger Debug (u'inactive', u'0', u'0', u'0', u'0', u'1200', u'inactive', u'False', u'True', u'False')
SQL Logger Debug DELETE FROM device_history_751898018 WHERE ts < %s;
SQL Logger Debug ('2017-11-17',)
SQL Logger Debug INSERT INTO device_history_1523305554 ("longstatusstring", "timeleftdays", "timelefthours", "timeleftminutes", "timeleftseconds", "timerstartvalueseconds", "timerstatus", "timerstatus_active", "timerstatus_inactive", "timerstatus_paused") VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s);
SQL Logger Debug (u'inactive', u'0', u'0', u'0', u'0', u'252000', u'inactive', u'False', u'True', u'False')
SQL Logger Debug DELETE FROM device_history_1523305554 WHERE ts < %s;
SQL Logger Debug ('2017-11-17',)
SQL Logger Error exception trying to insert row into table device_history_1523305554 ("timersPester_tidMellanBuskarSprinkler_70h") -- unable to modify table
SQL Logger Error Connection to backend closed
SQL Logger Error INSERT INTO device_history_1523305554 ("longstatusstring", "timeleftdays", "timelefthours", "timeleftminutes", "timeleftseconds", "timerstartvalueseconds", "timerstatus", "timerstatus_active", "timerstatus_inactive", "timerstatus_paused") VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s);
SQL Logger Error exception trying to create table device_history_1888213338 ("timersPester_tidMellanPlantSprinkler_46h")
SQL Logger Error Connection to backend closed
SQL Logger Debug INSERT INTO device_history_1888213338 ("longstatusstring", "timeleftdays", "timelefthours", "timeleftminutes", "timeleftseconds", "timerstartvalueseconds", "timerstatus", "timerstatus_active", "timerstatus_inactive", "timerstatus_paused") VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s);
SQL Logger Debug (u'inactive', u'0', u'0', u'0', u'0', u'165600', u'inactive', u'False', u'True', u'False')
SQL Logger Error broken pipe error trying to insert row into table device_history_1888213338 ("timersPester_tidMellanPlantSprinkler_46h")
SQL Logger closing SQL connection
SQL Logger Error exception trying to create table device_history_821779449 ("TP_B-julstjarnor")
SQL Logger Error not connected to database
SQL Logger Debug INSERT INTO device_history_821779449 ("alias", "onoffstate", "rssi") VALUES (%s, %s, %s);
SQL Logger Debug (u'TP_B', u'True', u'-65')
SQL Logger Error exception trying to insert row into table device_history_821779449 ("TP_B-julstjarnor")
SQL Logger Error 'NoneType' object has no attribute 'execute'
SQL Logger Error INSERT INTO device_history_821779449 ("alias", "onoffstate", "rssi") VALUES (%s, %s, %s);
SQL Logger Error exception trying to create table device_history_5481455 ("TP_C julstjarna Sovrum")
SQL Logger Error not connected to database
SQL Logger Debug INSERT INTO device_history_5481455 ("alias", "onoffstate", "rssi") VALUES (%s, %s, %s);
SQL Logger Debug (u'TP_C', u'True', u'-59')
SQL Logger Error exception trying to insert row into table device_history_5481455 ("TP_C julstjarna Sovrum")
SQL Logger Error 'NoneType' object has no attribute 'execute'
SQL Logger Error INSERT INTO device_history_5481455 ("alias", "onoffstate", "rssi") VALUES (%s, %s, %s);
SQL Logger Error exception trying to create table device_history_1982773061 ("Twilio_Mattias")
SQL Logger Error not connected to database
SQL Logger Debug INSERT INTO device_history_1982773061 ("contactstatus") VALUES (%s);
SQL Logger Debug (u'',)
SQL Logger Error exception trying to insert row into table device_history_1982773061 ("Twilio_Mattias")
SQL Logger Error 'NoneType' object has no attribute 'execute'
SQL Logger Error INSERT INTO device_history_1982773061 ("contactstatus") VALUES (%s);
SQL Logger Error exception trying to create table device_history_496113970 ("Twilio_nummer9193357864")
SQL Logger Error not connected to database
SQL Logger Debug INSERT INTO device_history_496113970 ("last_auth", "matchresult", "messagefrom", "messagetext", "messageto", "numberstatus") VALUES (%s, %s, %s, %s, %s, %s);
SQL Logger Debug (u'', u'', u'', u'', u'', u'Message Sent')
SQL Logger Error exception trying to insert row into table device_history_496113970 ("Twilio_nummer9193357864")
SQL Logger Error 'NoneType' object has no attribute