SQL Logger errors

[jh71283]

Hi all

It seems that SQL logger isn't quite working for me.

I'm getting this set of errors pretty regularly:

SQL Logger Error exception trying to update table device_history_348924970 ("TCP7612 In") -- unable to modify table
SQL Logger Error ERROR: column "alert" of relation "device_history_255383384" does not exist at character 39

SQL Logger Error exception trying to insert row into table device_history_348924970 ("TCP7612 In") -- unable to modify table
SQL Logger Error ERROR: column "alert" of relation "device_history_255383384" does not exist at character 39

SQL Logger Error INSERT INTO device_history_348924970 ("incount", "outcount", "port", "state", "state_connected", "state_disconnected", "state_idle", "state_listening", "state_preparing", "state_unavailable") VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s);
SQL Logger Error exception trying to insert row into table variable_history_1829474037 ("MQTT:house/powerusage")
SQL Logger Error ERROR: column "alert" of relation "device_history_255383384" does not exist at character 39

SQL Logger Error INSERT INTO variable_history_1829474037 (value) VALUES (%s);
SQL Logger Error exception trying to insert row into table variable_history_1850056273 ("LastMQTTMessage")
SQL Logger Error ERROR: column "alert" of relation "device_history_255383384" does not exist at character 39

SQL Logger Error INSERT INTO variable_history_1850056273 (value) VALUES (%s);
SQL Logger Error exception trying to update table device_history_348924970 ("TCP7612 In") -- unable to modify table
SQL Logger Error ERROR: column "alert" of relation "device_history_255383384" does not exist at character 39

SQL Logger Error exception trying to insert row into table device_history_348924970 ("TCP7612 In") -- unable to modify table
SQL Logger Error ERROR: column "alert" of relation "device_history_255383384" does not exist at character 39

SQL Logger Error INSERT INTO device_history_348924970 ("incount", "outcount", "port", "state", "state_connected", "state_disconnected", "state_idle", "state_listening", "state_preparing", "state_unavailable") VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s);
SQL Logger Error exception trying to insert row into table variable_history_1438735089 ("MQTT:house/voltage")
SQL Logger Error ERROR: column "alert" of relation "device_history_255383384" does not exist at character 39

SQL Logger Error INSERT INTO variable_history_1438735089 (value) VALUES (%s);
SQL Logger Error exception trying to insert row into table variable_history_1850056273 ("LastMQTTMessage")
SQL Logger Error ERROR: column "alert" of relation "device_history_255383384" does not exist at character 39

SQL Logger Error INSERT INTO variable_history_1850056273 (value) VALUES (%s);

Now, obviously I could add the column myself, but I don't know what type it is.

Can anybody help?

Thanks

[matt (support)]

I believe the type is string.

It looks like Indigo is trying to modify the table though but it is failing. I'm not sure why it would be failing, unless the database or table is locked. Do you have another script/plugin/app that might be trying to modify the database? And have you tried restarting your Mac to see if that might help get it working?

[jh71283]

I tried a reboot, and it got worse lol.

SQL Logger Error INSERT INTO device_history_172388437 ("highhumidity", "hightemp", "isrecordhigh", "isrecordlow", "lasthighlowreset", "lowhumidity", "lowtemp", "raining", "rainpending", "statedisplay", "statedisplay_ui") VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s);
SQL Logger creating table device_history_1111877440 for "Xmas Lights Outside"
SQL Logger Error exception trying to insert row into table device_history_1111877440 ("Xmas Lights Outside") -- unable to modify table
SQL Logger Error ERROR: relation "device_history_255383384" already exists

SQL Logger Error INSERT INTO device_history_1111877440 ("onoffstate") VALUES (%s);
SQL Logger Error exception trying to create table device_history_801924747 ("Xmas Tree")
SQL Logger Error ERROR: column "alert" of relation "device_history_255383384" does not exist at character 39

SQL Logger Error exception trying to insert row into table device_history_801924747 ("Xmas Tree") -- unable to modify table
SQL Logger Error ERROR: relation "device_history_172388437" already exists

SQL Logger Error INSERT INTO device_history_801924747 ("onoffstate") VALUES (%s);
SQL Logger updating all variable tables
SQL Logger Error exception trying to create table variable_history_33198548 ("alarmArmed")
SQL Logger Error ERROR: column "statedisplay_ui" of relation "device_history_172388437" does not exist at character 186

SQL Logger Error exception trying to insert row into table variable_history_33198548 ("alarmArmed")
SQL Logger Error ERROR: relation "device_history_1111877440" already exists

SQL Logger Error INSERT INTO variable_history_33198548 (value) VALUES (%s);
SQL Logger creating table variable_history_251780703 for "AnybodyHome"
SQL Logger creating table variable_history_806054534 for "BathroomMotionOverride"
SQL Logger creating table variable_history_6786340 for "BedroomHumidity"
SQL Logger Error exception trying to insert row into table variable_history_6786340 ("BedroomHumidity")
SQL Logger Error ERROR: relation "variable_history_251780703" already exists

SQL Logger Error INSERT INTO variable_history_6786340 (value) VALUES (%s);
SQL Logger creating table variable_history_813780610 for "BedroomTemp"
SQL Logger Error exception trying to insert row into table variable_history_813780610 ("BedroomTemp")
SQL Logger Error ERROR: relation "variable_history_806054534" already exists

SQL Logger Error INSERT INTO variable_history_813780610 (value) VALUES (%s);
SQL Logger creating table variable_history_1316639895 for "CloakroomOccupied"
SQL Logger Error exception trying to insert row into table variable_history_1316639895 ("CloakroomOccupied")
SQL Logger Error ERROR: relation "variable_history_6786340" already exists

SQL Logger Error INSERT INTO variable_history_1316639895 (value) VALUES (%s);
SQL Logger creating table variable_history_132912499 for "EverybodyHome"
SQL Logger Error exception trying to insert row into table variable_history_132912499 ("EverybodyHome")
SQL Logger Error ERROR: relation "variable_history_813780610" already exists

SQL Logger Error INSERT INTO variable_history_132912499 (value) VALUES (%s);
SQL Logger creating table variable_history_1307484990 for "ExpectingTakeaway"
SQL Logger Error exception trying to insert row into table variable_history_1307484990 ("ExpectingTakeaway")
SQL Logger Error ERROR: relation "variable_history_1316639895" already exists

SQL Logger Error INSERT INTO variable_history_1307484990 (value) VALUES (%s);
SQL Logger creating table variable_history_1389001099 for "houseMode"
SQL Logger Error exception trying to insert row into table variable_history_1389001099 ("houseMode")
SQL Logger Error ERROR: relation "variable_history_132912499" already exists

SQL Logger Error INSERT INTO variable_history_1389001099 (value) VALUES (%s);
SQL Logger creating table variable_history_1174284640 for "INDIGOplotD-Script-Message"
SQL Logger Error exception trying to insert row into table variable_history_1174284640 ("INDIGOplotD-Script-Message")
SQL Logger Error ERROR: relation "variable_history_1307484990" already exists

SQL Logger Error INSERT INTO variable_history_1174284640 (value) VALUES (%s);
SQL Logger creating table variable_history_23078783 for "isDaylight"
SQL Logger Error exception trying to insert row into table variable_history_23078783 ("isDaylight")
SQL Logger Error ERROR: relation "variable_history_1389001099" already exists

SQL Logger Error INSERT INTO variable_history_23078783 (value) VALUES (%s);
SQL Logger creating table variable_history_379188213 for "JohnHome"
SQL Logger Error exception trying to insert row into table variable_history_379188213 ("JohnHome")
SQL Logger Error ERROR: relation "variable_history_1174284640" already exists

SQL Logger Error INSERT INTO variable_history_379188213 (value) VALUES (%s);
SQL Logger creating table variable_history_26115586 for "JuanitaHome"
SQL Logger Error exception trying to insert row into table variable_history_26115586 ("JuanitaHome")
SQL Logger Error ERROR: relation "variable_history_23078783" already exists

SQL Logger Error INSERT INTO variable_history_26115586 (value) VALUES (%s);
SQL Logger creating table variable_history_1850056273 for "LastMQTTMessage"
SQL Logger Error exception trying to insert row into table variable_history_1850056273 ("LastMQTTMessage")
SQL Logger Error ERROR: relation "variable_history_379188213" already exists

SQL Logger Error INSERT INTO variable_history_1850056273 (value) VALUES (%s);
SQL Logger creating table variable_history_1344344083 for "MQTT:device/status"
SQL Logger Error exception trying to insert row into table variable_history_1344344083 ("MQTT:device/status")
SQL Logger Error ERROR: relation "variable_history_26115586" already exists

SQL Logger Error INSERT INTO variable_history_1344344083 (value) VALUES (%s);
SQL Logger creating table variable_history_1706490490 for "MQTT:house/currenttime"
SQL Logger Error exception trying to insert row into table variable_history_1706490490 ("MQTT:house/currenttime")
SQL Logger Error ERROR: relation "variable_history_1850056273" already exists

SQL Logger Error INSERT INTO variable_history_1706490490 (value) VALUES (%s);
SQL Logger creating table variable_history_1829474037 for "MQTT:house/powerusage"
SQL Logger Error exception trying to insert row into table variable_history_1829474037 ("MQTT:house/powerusage")
SQL Logger Error ERROR: relation "variable_history_1344344083" already exists

SQL Logger Error INSERT INTO variable_history_1829474037 (value) VALUES (%s);
SQL Logger creating table variable_history_1438735089 for "MQTT:house/voltage"
SQL Logger Error exception trying to insert row into table variable_history_1438735089 ("MQTT:house/voltage")
SQL Logger Error ERROR: relation "variable_history_1706490490" already exists

SQL Logger Error INSERT INTO variable_history_1438735089 (value) VALUES (%s);
SQL Logger creating table variable_history_999530598 for "MQTT:outside/temperature"
SQL Logger Error exception trying to insert row into table variable_history_999530598 ("MQTT:outside/temperature")
SQL Logger Error ERROR: relation "variable_history_1829474037" already exists

SQL Logger Error INSERT INTO variable_history_999530598 (value) VALUES (%s);
SQL Logger creating table variable_history_1034377540 for "MQTT:outside/weathersummary"
SQL Logger Error exception trying to insert row into table variable_history_1034377540 ("MQTT:outside/weathersummary")
SQL Logger Error ERROR: relation "variable_history_1438735089" already exists

SQL Logger Error INSERT INTO variable_history_1034377540 (value) VALUES (%s);
SQL Logger creating table variable_history_57837868 for "MQTT:weather/forecast/minsToRain"
SQL Logger Error exception trying to insert row into table variable_history_57837868 ("MQTT:weather/forecast/minsToRain")
SQL Logger Error ERROR: relation "variable_history_999530598" already exists

SQL Logger Error INSERT INTO variable_history_57837868 (value) VALUES (%s);
SQL Logger creating table variable_history_144224388 for "MQTT:weather/unsafe"
SQL Logger Error exception trying to insert row into table variable_history_144224388 ("MQTT:weather/unsafe")
SQL Logger Error ERROR: relation "variable_history_1034377540" already exists

SQL Logger Error INSERT INTO variable_history_144224388 (value) VALUES (%s);
SQL Logger creating table variable_history_1735278646 for "OvernightLow"
SQL Logger Error exception trying to insert row into table variable_history_1735278646 ("OvernightLow")
SQL Logger Error ERROR: relation "variable_history_57837868" already exists

SQL Logger Error INSERT INTO variable_history_1735278646 (value) VALUES (%s);
SQL Logger creating table variable_history_1438527176 for "OvernightLowVal"
SQL Logger Error exception trying to insert row into table variable_history_1438527176 ("OvernightLowVal")
SQL Logger Error ERROR: relation "variable_history_144224388" already exists

SQL Logger Error INSERT INTO variable_history_1438527176 (value) VALUES (%s);
SQL Logger creating table variable_history_1893703788 for "sprinklerDurationMultiplier"
SQL Logger Error exception trying to insert row into table variable_history_1893703788 ("sprinklerDurationMultiplier")
SQL Logger Error ERROR: relation "variable_history_1735278646" already exists

SQL Logger Error INSERT INTO variable_history_1893703788 (value) VALUES (%s);
SQL Logger creating table variable_history_1576223682 for "UpstairsLightMotionLevel"
SQL Logger Error exception trying to insert row into table variable_history_1576223682 ("UpstairsLightMotionLevel")
SQL Logger Error ERROR: relation "variable_history_1438527176" already exists

SQL Logger Error INSERT INTO variable_history_1576223682 (value) VALUES (%s);
SQL Logger creating table variable_history_1987834398 for "UpstairsLightNoMotionLevel"
SQL Logger Error exception trying to insert row into table variable_history_1987834398 ("UpstairsLightNoMotionLevel")
SQL Logger Error ERROR: relation "variable_history_1893703788" already exists

SQL Logger Error INSERT INTO variable_history_1987834398 (value) VALUES (%s);
SQL Logger creating table variable_history_129416031 for "variable"
SQL Logger Error exception trying to insert row into table variable_history_129416031 ("variable")
SQL Logger Error ERROR: relation "variable_history_1576223682" already exists

SQL Logger Error INSERT INTO variable_history_129416031 (value) VALUES (%s);
SQL Logger update complete
SQL Logger Error exception trying to insert row into table device_history_348924970 ("TCP7612 In") -- unable to modify table
SQL Logger Error ERROR: relation "variable_history_1987834398" already exists

SQL Logger Error INSERT INTO device_history_348924970 ("incount", "outcount", "port", "state", "state_connected", "state_disconnected", "state_idle", "state_listening", "state_preparing", "state_unavailable") VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s);
SQL Logger Error exception trying to insert row into table device_history_348924970 ("TCP7612 In") -- unable to modify table
SQL Logger Error ERROR: relation "variable_history_129416031" already exists

Seeing as the data is now incomplete I might just drop the database and try again.

[matt (support)]

Yeah, definitely looks like the database has gotten into a bad state. If you want to continue to troubleshoot this (versus dropping DB and creating a clean one), then I'm curious if manually adding rows works correctly? And is this a SQLLite or PostgreSQL database?

[kw123]

You could try indigo utilities. It has a dump/ reimport feature that fixes some of these situations.
It also has an option to do a regular backup.


Sent from my iPhone using Tapatalk

[siclark]

Hi

I think I'm having similar issues. Not sure if this is corrupt dB or just timing out due to lack of resources on my server.
I did see in the postgres stats that it looks like I have 2000+ tables which might be a problem. Not sure how but most appear to be variable ones which could be from things like fingscan etc. Is there an easy way to drop all tables that no longer have live devices or variables? I am sure I saw a command somewhere along those lines bit can't at it any more

Cheers


Sent from my iPhone using Tapatalk

[kw123]

In indigo logger you an
1. Delete old variables
2. Prune variables

In utilities you can prune individual variables.


Sent from my iPhone using Tapatalk

[siclark]

Thanks. Sure I had tried that before but turned on auto delete deleted variables then ran the option to remove them and 5 mins later 1200 tables had been deleted.
Fingers crossed it helps the performance issues.


Sent from my iPhone using Tapatalk

[siclark]

This seems to have helped. But seeing any SQL logger errors anymore, even after a restart when i used to get lots.


Sent from my iPhone using Tapatalk