SQL Logger Errors

Posted on
Mon Nov 16, 2020 8:48 pm
Mattias offline
Posts: 35
Joined: May 30, 2014

Re: SQL Logger Errors

where I could see the first error in the log after the plugin restart.

Posted on
Sat Nov 28, 2020 3:46 pm
matt (support) offline
Site Admin
User avatar
Posts: 21411
Joined: Jan 27, 2003
Location: Texas

Re: SQL Logger Errors

Here is the part where the connection actually breaks:

Mattias wrote:
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 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


After this point any attempt to access the Postgres DB is going to fail since the connection has been closed. From that broken pipe error message I believe it means the network connection to the Postgres DB literally got closed, either by the Postgres server or by the OS (Indigo didn't close it).

Mattias wrote:
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.

I don't understand what you mean by the above. Specifically what steps are you doing and what are you seeing (in more detail)? Are you saying if you restart the SQL Logger plugin that Pgadmin reports the connection is dropped? That would be odd. That connection should remain up regardless of what SQL Logger plugin is doing. Or maybe that error message you see in Pgadmin isn't related to the SQL Logger plugin restarting, but is Pgadmin reporting the same broken connection that Indigo is seeing?

Image

Posted on
Sun Nov 29, 2020 10:24 am
Mattias offline
Posts: 35
Joined: May 30, 2014

Re: SQL Logger Errors

it feels like a small miracle I got this to work in the first place, since the postgres db is quite confusing to me....: :) , but it's been very stable for some time (much more so that the SQLite).

Regarding the ‘application has lost database connection’ message, it comes when I query a table in the db with PGAdmin, (last 100 rows), it shows the rows correct, but when I restart the SQL logger the message shows up in PGAdmin (after refresh). Perhaps fully logical.

So, every time I restart the SQLlogger 1 row is added to each device in the database (at least the devices I looked at), and then it the connection stops working.
the postgr database is running all the time (at least I can query tables in it, it's just that the tables are not updated. Indigo is working as normal (part from all parts using the postgr DB.

Any suggestions? (I don't think i've run any specific OS or db updates (but since I'm not the only user of the computer, it might have happen....)

Posted on
Wed Dec 02, 2020 3:31 pm
matt (support) offline
Site Admin
User avatar
Posts: 21411
Joined: Jan 27, 2003
Location: Texas

Re: SQL Logger Errors

A couple of things to try from here.

First, try disabling the Auto Prune Older option by setting it to Never inside the SQL Logger configuration dialog (Plugins->SQL Logger->Configure... menu item). Does that help? That isn't a good long term solution really, but I'm wondering if it is failing when Indigo tries to delete rows from the table.

You might also try using a clean new Postgres database. That is, I'm wondering if the problem is because there is something wrong/corrupt with the database itself.

Image

Posted on
Wed Dec 02, 2020 3:41 pm
Mattias offline
Posts: 35
Joined: May 30, 2014

Re: SQL Logger Errors

I reinstalled postgres this morning (ran the v12 installer again) and it solved the errors (just checked and it still looks fine). So there was probably something corrupt in postgres.

Thanks for your help!
Mattias

Who is online

Users browsing this forum: No registered users and 2 guests