SQL Log Entries

Posted on
Sat Nov 04, 2017 2:35 am
Colly offline
Posts: 535
Joined: Jan 16, 2016
Location: Ireland

SQL Log Entries

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
 

Posted on
Sat Nov 04, 2017 9:03 am
siclark offline
Posts: 1960
Joined: Jun 13, 2017
Location: UK

Re: SQL Log Entries

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.

Posted on
Sat Nov 04, 2017 9:03 am
siclark offline
Posts: 1960
Joined: Jun 13, 2017
Location: UK

Re: SQL Log Entries

Then use the utilities plugin to create backups.

Posted on
Sat Nov 04, 2017 9:44 am
matt (support) offline
Site Admin
User avatar
Posts: 21411
Joined: Jan 27, 2003
Location: Texas

Re: SQL Log Entries

FYI, here is Karl's Utilities Plugin he mentioned above.

Image

Posted on
Sat Nov 04, 2017 10:44 am
kw123 offline
User avatar
Posts: 8333
Joined: May 12, 2013
Location: Dallas, TX

Re: SQL Log Entries

Better switch to Postgres app.
Had no corruption in 3+ years.


Sent from my iPhone using Tapatalk

Posted on
Sat Nov 04, 2017 11:09 am
Colly offline
Posts: 535
Joined: Jan 16, 2016
Location: Ireland

Re: SQL Log Entries

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?

Posted on
Sat Nov 04, 2017 12:09 pm
kw123 offline
User avatar
Posts: 8333
Joined: May 12, 2013
Location: Dallas, TX

Re: SQL Log Entries

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

Posted on
Sat Nov 04, 2017 1:48 pm
FlyingDiver offline
User avatar
Posts: 7189
Joined: Jun 07, 2014
Location: Southwest Florida, USA

Re: SQL Log Entries

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.

joe (aka FlyingDiver)
my plugins: http://forums.indigodomo.com/viewforum.php?f=177

Posted on
Sun Nov 05, 2017 3:00 pm
Colly offline
Posts: 535
Joined: Jan 16, 2016
Location: Ireland

Re: SQL Log Entries

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.

Posted on
Mon Nov 06, 2017 12:32 am
agame offline
Posts: 514
Joined: Jul 13, 2017
Location: Melbourne, Australia

Re: SQL Log Entries

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??

Posted on
Mon Nov 06, 2017 5:46 am
kw123 offline
User avatar
Posts: 8333
Joined: May 12, 2013
Location: Dallas, TX

Re: SQL Log Entries

in postgres app / prefs do:
Attachments
Screen Shot 2017-11-06 at 05.44.25.png
Screen Shot 2017-11-06 at 05.44.25.png (60.63 KiB) Viewed 4410 times

Posted on
Mon Dec 04, 2017 5:23 am
agame offline
Posts: 514
Joined: Jul 13, 2017
Location: Melbourne, Australia

Re: SQL Log Entries

thanks. sorted. (in fact it was with the switch to postgres.app - not sure how this popped up today.) thanks anyhow.

Page 1 of 1

Who is online

Users browsing this forum: No registered users and 1 guest