Page 1 of 1

SQL Log Entries

PostPosted: Sat Nov 04, 2017 2:35 am
by Colly
Need some help here with my log being inundated with SQL entries. See example below, this seems to have started when my Mac Mini restarted by itself - not sure why. Below is just a snapshot, I've actually had to disable it for the moment.
Any suggestions on solving would be greatly appreciated.
Code: Select all
   SQL Logger                      updating all device tables
   SQL Logger Error                exception trying to create table device_history_1423602820 ("003 - Binary Sensor")
   SQL Logger Error                database disk image is malformed
   SQL Logger Error                exception trying to insert row into table device_history_1423602820 ("003 - Binary Sensor")
   SQL Logger Error                database disk image is malformed
   SQL Logger Error                INSERT INTO device_history_1423602820 ("onoffstate") VALUES (%s);
   SQL Logger Error                exception trying to create table device_history_1015521582 ("003 - Binary Sensor 2")
   SQL Logger Error                database disk image is malformed
   SQL Logger Error                exception trying to insert row into table device_history_1015521582 ("003 - Binary Sensor 2")
   SQL Logger Error                database disk image is malformed
   SQL Logger Error                INSERT INTO device_history_1015521582 ("onoffstate") VALUES (%s);
   SQL Logger Error                exception trying to create table device_history_1916264067 ("003 - Temperature")
   SQL Logger Error                database disk image is malformed
   SQL Logger Error                exception trying to insert row into table device_history_1916264067 ("003 - Temperature")
   SQL Logger Error                database disk image is malformed
   SQL Logger Error                INSERT INTO device_history_1916264067 ("accumenergytimedelta", "accumenergytotal", "curenergylevel", "sensorvalue", "sensorvalue_ui") VALUES (%s, %s, %s, %s, %s);
   SQL Logger Error                exception trying to create table device_history_600073177 ("014 - Temperature")
   SQL Logger Error                database disk image is malformed
   SQL Logger Error                exception trying to insert row into table device_history_600073177 ("014 - Temperature")
   SQL Logger Error                database disk image is malformed
   SQL Logger Error                INSERT INTO device_history_600073177 ("accumenergytimedelta", "accumenergytimedelta_ui", "accumenergytotal", "accumenergytotal_ui", "curenergylevel", "sensorvalue", "sensorvalue_ui") VALUES (%s, %s, %s, %s, %s, %s, %s);
   SQL Logger Error                exception trying to create table device_history_1623834288 ("015 - Relay Switch 2")
   SQL Logger Error                database disk image is malformed
   SQL Logger Error                exception trying to insert row into table device_history_1623834288 ("015 - Relay Switch 2")
   SQL Logger Error                database disk image is malformed
   SQL Logger Error                INSERT INTO device_history_1623834288 ("accumenergytimedelta", "accumenergytimedelta_ui", "accumenergytotal", "accumenergytotal_ui", "curenergylevel", "curenergylevel_ui", "onoffstate") VALUES (%s, %s, %s, %s, %s, %s, %s);
   SQL Logger Error                exception trying to create table device_history_1084713715 ("015 - Temperature")
   SQL Logger Error                database disk image is malformed
   SQL Logger Error                exception trying to insert row into table device_history_1084713715 ("015 - Temperature")
   SQL Logger Error                database disk image is malformed
   SQL Logger Error                INSERT INTO device_history_1084713715 ("accumenergytimedelta", "accumenergytimedelta_ui", "accumenergytotal", "curenergylevel", "curenergylevel_ui", "sensorvalue", "sensorvalue_ui") VALUES (%s, %s, %s, %s, %s, %s, %s);
   SQL Logger Error                exception trying to create table device_history_1365258102 ("019 - Input 2")
   SQL Logger Error                database disk image is malformed
   SQL Logger Error                exception trying to insert row into table device_history_1365258102 ("019 - Input 2")
   SQL Logger Error                database disk image is malformed
   SQL Logger Error                INSERT INTO device_history_1365258102 ("onoffstate") VALUES (%s);
   SQL Logger Error                exception trying to create table device_history_1903043483 ("019 - Input 3")
   SQL Logger Error                database disk image is malformed
   SQL Logger Error                exception trying to insert row into table device_history_1903043483 ("019 - Input 3")
   SQL Logger Error                database disk image is malformed
   SQL Logger Error                INSERT INTO device_history_1903043483 ("onoffstate") VALUES (%s);
   SQL Logger Error                exception trying to create table device_history_1240977495 ("019 - Temperature")
   SQL Logger Error                database disk image is malformed
   SQL Logger Error                exception trying to insert row into table device_history_1240977495 ("019 - Temperature")
   SQL Logger Error                database disk image is malformed
 

Re: SQL Log Entries

PostPosted: Sat Nov 04, 2017 9:03 am
by siclark
Hate to say it but your SQL file is corrupt. You may be able to restore from a time machine backup but that's unlikely. Best bet is to use the utilities plugin and try to fix the errors on the current version of the file, ie non time machine one.

Re: SQL Log Entries

PostPosted: Sat Nov 04, 2017 9:03 am
by siclark
Then use the utilities plugin to create backups.

Re: SQL Log Entries

PostPosted: Sat Nov 04, 2017 9:44 am
by matt (support)
FYI, here is Karl's Utilities Plugin he mentioned above.

Re: SQL Log Entries

PostPosted: Sat Nov 04, 2017 10:44 am
by kw123
Better switch to Postgres app.
Had no corruption in 3+ years.


Sent from my iPhone using Tapatalk

Re: SQL Log Entries

PostPosted: Sat Nov 04, 2017 11:09 am
by Colly
OK so, definitely out of my depth here. What have I lost if the SQL file is corrupt? What do I need to do to setup Postgres? All help appreciated.
Any idea why the SQL file got corrupted?

Re: SQL Log Entries

PostPosted: Sat Nov 04, 2017 12:09 pm
by kw123
SQL logger stores historical values of devices/ states and variables. nothing else. NOT the current config of indigo.

sqlite db is a simple file, everything is in there. if you have a write error everything is lost .

for postgres app NOT full postgres:
https://postgresapp.com/

follow the first steps "installing "

then I believe the next step is just switch to postgres in indigo sqllogger.

you will start with a new database (historical values of devices state and variables.)

if you use one of my plugins (utilities, indigoplotd ) you will need to add
Code: Select all
/Applications/Postgres.app/Contents/Versions/9.6/bin/psql indigo_history -U postgres
in the config utilities and
Code: Select all
/Applications/Postgres.app/Contents/Versions/9.6/bin/psql  indigo_history postgres
in indigoplotd

reset SHOULD be automatically handled by indigo I believe, have open that only once 2+years ago.

there is also a thread about postgres APP, but I can't find it.

a fallback: delete the sqlite DB and start fresh with sqlite in terminal:
Code: Select all
rm '/Library/Application Support/Perceptive Automation/Indigo 7/Logs/indigo_history.sqlite'

you need to stop sqllogger then delete the file then enable sqllogger

Karl

Re: SQL Log Entries

PostPosted: Sat Nov 04, 2017 1:48 pm
by FlyingDiver
Colly wrote:
OK so, definitely out of my depth here. What have I lost if the SQL file is corrupt? What do I need to do to setup Postgres? All help appreciated.
Any idea why the SQL file got corrupted?


Are you actually using the SQL logging for anything? If not, just turn it off. It's not required for Indigo to work properly.

Re: SQL Log Entries

PostPosted: Sun Nov 05, 2017 3:00 pm
by Colly
Thanks Karl for the details on installing Postgres. I'll go for it later.
FlyingDiver wrote:
Are you actually using the SQL logging for anything? If not, just turn it off. It's not required for Indigo to work properly.

Thanks Joe, good to know it's not an essential component as I really wasn't sure.

Re: SQL Log Entries

PostPosted: Mon Nov 06, 2017 12:32 am
by agame
not sure if this is the right thread (at least its current).... I am finding that posgres isn't restarting adequately when my Mac restarts for any reason. reams of red error messages in the log.

but I can fix it by opening one of the postgres utility apps that evidently force a restart of the database.

if I quit that window, however, the errors resume.

any insight on what config is wrong here??

Re: SQL Log Entries

PostPosted: Mon Nov 06, 2017 5:46 am
by kw123
in postgres app / prefs do:

Re: SQL Log Entries

PostPosted: Mon Dec 04, 2017 5:23 am
by agame
thanks. sorted. (in fact it was with the switch to postgres.app - not sure how this popped up today.) thanks anyhow.