[ANSWERED]: SQL Logger Errors on Reboot

Posted on
Mon May 25, 2015 7:19 am
DaveL17 offline
User avatar
Posts: 6742
Joined: Aug 20, 2013
Location: Chicago, IL, USA

[ANSWERED]: SQL Logger Errors on Reboot

Gentlemen - I don't think that this is necessarily a problem, but I thought I'd alert you in any event. Upon rebooting the iMac which houses the Indigo server, I received numerous errors of the type:
Code: Select all
2015-05-25 08:12:12.352   SQL Logger Error   INSERT INTO variable_history_561964105 (value) VALUES (%s);
2015-05-25 08:12:12.384   SQL Logger   creating table variable_history_950128135 for "humidDown"
2015-05-25 08:12:12.390   SQL Logger Error   exception trying to insert row into table variable_history_950128135 ("humidDown")
2015-05-25 08:12:12.390   SQL Logger Error   ERROR:  relation "variable_history_950128135" already exists

2015-05-25 08:12:12.391   SQL Logger Error   INSERT INTO variable_history_950128135 (value) VALUES (%s);
2015-05-25 08:12:12.535   SQL Logger   creating table variable_history_187913970 for "humidDownTarget"
2015-05-25 08:12:12.548   SQL Logger Error   exception trying to insert row into table variable_history_187913970 ("humidDownTarget")
2015-05-25 08:12:12.549   SQL Logger Error   ERROR:  relation "variable_history_187913970" already exists

2015-05-25 08:12:12.549   SQL Logger Error   INSERT INTO variable_history_187913970 (value) VALUES (%s);
2015-05-25 08:12:12.626   SQL Logger   creating table variable_history_23078783 for "isDaylight"
2015-05-25 08:12:12.634   SQL Logger Error   exception trying to insert row into table variable_history_23078783 ("isDaylight")
2015-05-25 08:12:12.635   SQL Logger Error   ERROR:  relation "variable_history_23078783" already exists


After the server restart was complete, I reloaded the SQL Logger plugin, and received this:
Code: Select all
Reloading plugin "SQL Logger 1.1.8"
  Stopping plugin "SQL Logger 1.1.8" (pid 323)
  SQL Logger                      closing SQL connection
  Stopped plugin "SQL Logger 1.1.8"
  Starting plugin "SQL Logger 1.1.8" (pid 456)
  Started plugin "SQL Logger 1.1.8"
  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                      updating all variable tables
  SQL Logger                      update complete

I suspect all is well, but wanted to let you know just in case.
Dave

I came here to drink milk and kick ass....and I've just finished my milk.

[My Plugins] - [My Forums]

Posted on
Tue May 26, 2015 1:07 am
kw123 offline
User avatar
Posts: 8333
Joined: May 12, 2013
Location: Dallas, TX

Re: SQL Logger Errors on Reboot

Just to make sure, may I suggest to run some full (all data) queries for these variables (and may be some others)
As I understand Indigo, it "just" adds data and never looks at data after it creates the columns.

Karl

Posted on
Tue May 26, 2015 8:56 am
matt (support) offline
Site Admin
User avatar
Posts: 21411
Joined: Jan 27, 2003
Location: Texas

Re: SQL Logger Errors on Reboot

Yes, please make sure the data is being written correctly. Also let us know if you see the problem again – I've never heard reports of this one before.

Thanks for the report.

Image

Posted on
Tue May 26, 2015 8:59 am
DaveL17 offline
User avatar
Posts: 6742
Joined: Aug 20, 2013
Location: Chicago, IL, USA

Re: [ANSWERED]: SQL Logger Errors on Reboot

Thanks Karl and Matt--I will do some more investigating tonight.

No more errors since the reboot.

Dave

I came here to drink milk and kick ass....and I've just finished my milk.

[My Plugins] - [My Forums]

Posted on
Wed May 27, 2015 7:50 pm
DaveL17 offline
User avatar
Posts: 6742
Joined: Aug 20, 2013
Location: Chicago, IL, USA

Re: [ANSWERED]: SQL Logger Errors on Reboot

Just to report back -- I'm pretty sure that there is something wrong with the database -- had a sea of red log messages tonight:

Code: Select all
SQL Logger Error                exception trying to update table device_history_64102542 ("AVR (Device Info)") -- unable to modify table
SQL Logger Error                ERROR:  column "devicecapabilities_operation_favoritestation_favorite1_shortcut" of relation "device_history_64102542" already exists

And many more.

Dave

I came here to drink milk and kick ass....and I've just finished my milk.

[My Plugins] - [My Forums]

Posted on
Thu May 28, 2015 9:27 am
jay (support) offline
Site Admin
User avatar
Posts: 18199
Joined: Mar 19, 2008
Location: Austin, Texas

Re: [ANSWERED]: SQL Logger Errors on Reboot

First, check the permissions on the file to make sure that Indigo can actually write to it (it should be owned with write permission by the account you run the server under). I use SQLiteManager which has a verify function so if you have a tool like that you might see if there's something structurally wrong with the DB.

Jay (Indigo Support)
Twitter | Facebook | LinkedIn

Posted on
Fri May 29, 2015 11:09 am
RogueProeliator offline
User avatar
Posts: 2501
Joined: Nov 13, 2012
Location: Baton Rouge, LA

Re: [ANSWERED]: SQL Logger Errors on Reboot

I just updated the WUnderground plugin and saw similar errors regarding SQL Logger:

Code: Select all
  SQL Logger Error                exception trying to update table device_history_1953252921 ("BTR Weather Underground Hourly") -- unable to modify table
  SQL Logger Error                ERROR:  column "h01_timelong" of relation "device_history_1953252921" does not exist at character 166

  SQL Logger Error                exception trying to insert row into table device_history_1953252921 ("BTR Weather Underground Hourly") -- unable to modify table
  SQL Logger Error                ERROR:  column "d01_date" of relation "device_history_1475208462" does not exist at character 58

  SQL Logger Error                INSERT INTO device_history_1953252921 ("estimated", "h01_cond", "h01_icon", "h01_precip", "h01_precip_ui", "h01_proper_icon", "h01_temp", "h01_temp_ui", "h01_time", "h01_timelong", "h01_winddir", "h01_windspeed", "h02_cond", "h02_icon", "h02_precip", "h02_precip_ui", "h02_proper_icon", "h02_temp", "h02_temp_ui", "h02_time", "h02_timelong", "h02_winddir", "h02_windspeed", "h03_cond", "h03_icon", "h03_precip", "h03_precip_ui", "h03_proper_icon", "h03_temp", "h03_temp_ui", "h03_time", "h03_timelong", "h03_winddir", "h03_windspeed", "h04_cond", "h04_icon", "h04_precip", "h04_precip_ui", "h04_proper_icon", "h04_temp", "h04_temp_ui", "h04_time", "h04_timelong", "h04_winddir", "h04_windspeed", "h05_cond", "h05_icon", "h05_precip", "h05_precip_ui", "h05_proper_icon", "h05_temp", "h05_temp_ui", "h05_time", "h05_timelong", "h05_winddir", "h05_windspeed", "h06_cond", "h06_icon", "h06_precip", "h06_precip_ui", "h06_proper_icon", "h06_temp", "h06_temp_ui", "h06_time", "h06_timelong", "h06_winddir", "h06_windspeed", "h07_cond", "h07_icon", "h07_precip", "h07_precip_ui", "h07_proper_icon", "h07_temp", "h07_temp_ui", "h07_time", "h07_timelong", "h07_winddir", "h07_windspeed", "h08_cond", "h08_icon", "h08_precip", "h08_precip_ui", "h08_proper_icon", "h08_temp", "h08_temp_ui", "h08_time", "h08_timelong", "h08_winddir", "h08_windspeed", "h09_cond", "h09_icon", "h09_precip", "h09_precip_ui", "h09_proper_icon", "h09_temp", "h09_temp_ui", "h09_time", "h09_timelong", "h09_winddir", "h09_windspeed", "h10_cond", "h10_icon", "h10_precip", "h10_precip_ui", "h10_proper_icon", "h10_temp", "h10_temp_ui", "h10_time", "h10_timelong", "h10_winddir", "h10_windspeed", "h11_cond", "h11_icon", "h11_precip", "h11_precip_ui", "h11_proper_icon", "h11_temp", "h11_temp_ui", "h11_time", "h11_timelong", "h11_winddir", "h11_windspeed", "h12_cond", "h12_icon", "h12_precip", "h12_precip_ui", "h12_proper_icon", "h12_temp", "h12_temp_ui", "h12_time", "h12_timelong", "h12_winddir", "h12_windspeed", "h13_cond", "h13_icon", "h13_precip", "h13_precip_ui", "h13_proper_icon", "h13_temp", "h13_temp_ui", "h13_time", "h13_timelong", "h13_winddir", "h13_windspeed", "h14_cond", "h14_icon", "h14_precip", "h14_precip_ui", "h14_proper_icon", "h14_temp", "h14_temp_ui", "h14_time", "h14_timelong", "h14_winddir", "h14_windspeed", "h15_cond", "h15_icon", "h15_precip", "h15_precip_ui", "h15_proper_icon", "h15_temp", "h15_temp_ui", "h15_time", "h15_timelong", "h15_winddir", "h15_windspeed", "h16_cond", "h16_icon", "h16_precip", "h16_precip_ui", "h16_proper_icon", "h16_temp", "h16_temp_ui", "h16_time", "h16_timelong", "h16_winddir", "h16_windspeed", "h17_cond", "h17_icon", "h17_precip", "h17_precip_ui", "h17_proper_icon", "h17_temp", "h17_temp_ui", "h17_time", "h17_timelong", "h17_winddir", "h17_windspeed", "h18_cond", "h18_icon", "h18_precip", "h18_precip_ui", "h18_proper_icon", "h18_temp", "h18_temp_ui", "h18_time", "h18_timelong", "h18_winddir", "h18_windspeed", "h19_cond", "h19_icon", "h19_precip", "h19_precip_ui", "h19_proper_icon", "h19_temp", "h19_temp_ui", "h19_time", "h19_timelong", "h19_winddir", "h19_windspeed", "h20_cond", "h20_icon", "h20_precip", "h20_precip_ui", "h20_proper_icon", "h20_temp", "h20_temp_ui", "h20_time", "h20_timelong", "h20_winddir", "h20_windspeed", "h21_cond", "h21_icon", "h21_precip", "h21_precip_ui", "h21_proper_icon", "h21_temp", "h21_temp_ui", "h21_time", "h21_timelong", "h21_winddir", "h21_windspeed", "h22_cond", "h22_icon", "h22_precip", "h22_precip_ui", "h22_proper_icon", "h22_temp", "h22_temp_ui", "h22_time", "h22_timelong", "h22_winddir", "h22_windspeed", "h23_cond", "h23_icon", "h23_precip", "h23_precip_ui", "h23_proper_icon", "h23_temp", "h23_temp_ui", "h23_time", "h23_timelong", "h23_winddir", "h23_windspeed", "h24_cond", "h24_icon", "h24_precip", "h24_precip_ui", "h24_proper_icon", "h24_temp", "h24_temp_ui", "h24_time", "h24_timelong", "h24_winddir", "h24_windspeed", "onoffstate", "onoffstate_ui") 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, %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, %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, %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, %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, %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, %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, %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);
  SQL Logger Error                exception trying to update table device_history_1953252921 ("BTR Weather Underground Hourly") -- unable to modify table
  SQL Logger Error                ERROR:  column "h01_timelong" of relation "device_history_1953252921" does not exist at character 166


This only occurred after the update -- and there were a crap ton of those errors in there. I'm using Postgre so it isn't a permissions issue. I'll see if it happens each update or what. Something dealing with new or changed states or something? Haven't investigated it, but will let you know if I have time to and figure anything out.

Adam

Posted on
Fri May 29, 2015 12:02 pm
DaveL17 offline
User avatar
Posts: 6742
Joined: Aug 20, 2013
Location: Chicago, IL, USA

Re: [ANSWERED]: SQL Logger Errors on Reboot

That is so weird. I haven't changed those states (and I've added only a couple new ones.)

I will go back and check the change log. Please do let me know if you spot something suspicious.

Sorry in advance if it's my fault!

I came here to drink milk and kick ass....and I've just finished my milk.

[My Plugins] - [My Forums]

Posted on
Fri May 29, 2015 12:58 pm
RogueProeliator offline
User avatar
Posts: 2501
Joined: Nov 13, 2012
Location: Baton Rouge, LA

Re: [ANSWERED]: SQL Logger Errors on Reboot

Sorry in advance if it's my fault!

I don't *think* so -- just narrowing it down to one query/device, I see that it fails to modify a column which doesn't exist (as per the logs). I will try to dig down into the SQL when I get to the server this weekend. Almost like it thinks the state should already be there or was already added, so it attempts modification... the insert then fails, but it is trying to insert that "phantom" column, so that failing makes sense and is because of the first.

Adam

Posted on
Fri May 29, 2015 1:36 pm
RogueProeliator offline
User avatar
Posts: 2501
Joined: Nov 13, 2012
Location: Baton Rouge, LA

Re: [ANSWERED]: SQL Logger Errors on Reboot

It looks like SQL Logger just wasn't creating the columns properly -- I turned on debugging, restarted the SQL Logger plugin and updated WUnderground manually... it properly altered the tables for the missing columns and all updates fine now.

I'll chalk it up to a fluke for now... and I HAD gone in and saved the devices in question to be sure they had a clean save. Will watch it for later, but seems that solved it for me.

Adam

Posted on
Fri May 29, 2015 2:24 pm
DaveL17 offline
User avatar
Posts: 6742
Joined: Aug 20, 2013
Location: Chicago, IL, USA

Re: [ANSWERED]: SQL Logger Errors on Reboot

Good deal.

I hate gremlins like that!

I came here to drink milk and kick ass....and I've just finished my milk.

[My Plugins] - [My Forums]

Posted on
Fri May 29, 2015 6:00 pm
matt (support) offline
Site Admin
User avatar
Posts: 21411
Joined: Jan 27, 2003
Location: Texas

Re: [ANSWERED]: SQL Logger Errors on Reboot

Let us know if you see a similar problem in the future.

Image

Posted on
Fri May 29, 2015 6:20 pm
DaveL17 offline
User avatar
Posts: 6742
Joined: Aug 20, 2013
Location: Chicago, IL, USA

Re: [ANSWERED]: SQL Logger Errors on Reboot

I ran several queries against my Postgres database (using Liya) and received no errors. I'll keep an eye on things.

Dave

I came here to drink milk and kick ass....and I've just finished my milk.

[My Plugins] - [My Forums]

Page 1 of 1

Who is online

Users browsing this forum: No registered users and 2 guests