SQL Logger error

Posted on
Tue Nov 12, 2019 4:17 pm
bsp9493 offline
Posts: 146
Joined: Nov 30, 2017
Location: Kelowna, BC

SQL Logger error

Recently started receiving an error in my logs as noted below.

Not sure if this is an error due to sql limitations, or if it is an error with my source.

As always, your help is appreciated.

dave

Code: Select all
Nov 12, 2019 at 7:22:49 AM
   SQL Logger                      adding column "forecastgroup_forecast_1_snowlevel_textsummary_ui" to table device_history_974671683 ("dd.weather.gc.ca")
   SQL Logger Error                exception trying to update table device_history_974671683 ("dd.weather.gc.ca") -- unable to modify table
   SQL Logger Error                ERROR:  tables can have at most 1600 columns

   SQL Logger Error                exception trying to insert row into table device_history_974671683 ("dd.weather.gc.ca") -- unable to modify table
   SQL Logger Error                ERROR:  column "hourlyforecastgroup_hourlyforecast_7_datetimeutc_ui" of relation "device_history_974671683" does not exist at character 321

   SQL Logger Error                INSERT INTO device_history_974671683 ("hourlyforecastgroup_hourlyforecast_17_datetimeutc", "hourlyforecastgroup_hourlyforecast_10_datetimeutc", "hourlyforecastgroup_hourlyforecast_22_datetimeutc", "hourlyforecastgroup_hourlyforecast_11_lop_ui", "hourlyforecastgroup_hourlyforecast_20_datetimeutc", "datetime_2_hour_ui", "hourlyforecastgroup_hourlyforecast_7_datetimeutc_ui", "currentconditions_iconcode", "hourlyforecastgroup_hourlyforecast_1_datetimeutc", "datetime_1_hour", "currentconditions_datetime_2_textsummary_ui", "hourlyforecastgroup_hourlyforecast_7_wind_speed", "hourlyforecastgroup_hourlyforecast_5_iconcode_ui", "currentconditions_dewpoint", "currentconditions_wind_bearing_ui", "datetime_1_minute", "hourlyforecastgroup_hourlyforecast_21_datetimeutc_ui", "datetime_2_hour", "hourlyforecastgroup_hourlyforecast_14_datetimeutc", "datetime_2_timestamp_ui", "hourlyforecastgroup_hourlyforecast_23_datetimeutc", "datetime_2_textsummary_ui", "hourlyforecastgroup_hourlyforecast_15_datetimeutc_ui", "hourlyforecastgroup_hourlyforecast_18_datetimeutc", "currentconditions_dewpoint_ui", "datetime_2_minute_ui", "hourlyforecastgroup_hourlyforecast_21_wind_direction", "hourlyforecastgroup_hourlyforecast_24_datetimeutc", "hourlyforecastgroup_hourlyforecast_13_datetimeutc_ui", "currentconditions_wind_bearing", "hourlyforecastgroup_hourlyforecast_21_wind_speed_ui", "hourlyforecastgroup_hourlyforecast_4_datetimeutc_ui", "hourlyforecastgroup_hourlyforecast_6_datetimeutc_ui", "hourlyforecastgroup_hourlyforecast_24_wind_speed", "hourlyforecastgroup_hourlyforecast_13_datetimeutc", "hourlyforecastgroup_hourlyforecast_9_temperature_ui", "currentconditions_datetime_1_textsummary_ui", "hourlyforecastgroup_hourlyforecast_8_datetimeutc_ui", "hourlyforecastgroup_hourlyforecast_5_datetimeutc", "hourlyforecastgroup_hourlyforecast_16_datetimeutc", "hourlyforecastgroup_hourlyforecast_20_lop_ui", "hourlyforecastgroup_hourlyforecast_10_datetimeutc_ui", "currentconditions_wind_direction_ui", "datetime_1_timestamp", "hourlyforecastgroup_hourlyforecast_9_temperature", "hourlyforecastgroup_hourlyforecast_20_condition_ui", "hourlyforecastgroup_hourlyforecast_11_datetimeutc_ui", "currentconditions_datetime_1_minute", "hourlyforecastgroup_hourlyforecast_5_iconcode", "hourlyforecastgroup_hourlyforecast_7_datetimeutc", "hourlyforecastgroup_hourlyforecast_11_lop", "hourlyforecastgroup_hourlyforecast_24_wind_speed_ui", "hourlyforecastgroup_hourlyforecast_15_datetimeutc", "hourlyforecastgroup_hourlyforecast_20_iconcode_ui", "hourlyforecastgroup_hourlyforecast_9_datetimeutc", "hourlyforecastgroup_hourlyforecast_13_temperature", "hourlyforecastgroup_hourlyforecast_11_lop_category", "hourlyforecastgroup_hourlyforecast_7_temperature_ui", "currentconditions_datetime_2_hour_ui", "hourlyforecastgroup_hourlyforecast_24_wind_direction", "hourlyforecastgroup_hourlyforecast_18_datetimeutc_ui", "currentconditions_datetime_1_hour_ui", "hourlyforecastgroup_hourlyforecast_6_datetimeutc", "hourlyforecastgroup_hourlyforecast_13_wind_speed_ui", "hourlyforecastgroup_hourlyforecast_23_datetimeutc_ui", "hourlyforecastgroup_hourlyforecast_13_wind_speed", "hourlyforecastgroup_hourlyforecast_11_lop_category_ui", "currentconditions_condition", "hourlyforecastgroup_hourlyforecast_13_wind_direction_ui", "hourlyforecastgroup_hourlyforecast_22_datetimeutc_ui", "hourlyforecastgroup_hourlyforecast_16_datetimeutc_ui", "hourlyforecastgroup_hourlyforecast_8_temperature_ui", "hourlyforecastgroup_hourlyforecast_2_datetimeutc_ui", "currentconditions_visibility", "hourlyforecastgroup_hourlyforecast_12_datetimeutc", "hourlyforecastgroup_hourlyforecast_2_temperature_ui", "currentconditions_datetime_2_timestamp", "hourlyforecastgroup_hourlyforecast_1_datetimeutc_ui", "hourlyforecastgroup_hourlyforecast_2_datetimeutc", "datetime_1_textsummary_ui", "hourlyforecastgroup_hourlyforecast_7_wind_speed_ui", "hourlyforecastgroup_hourlyforecast_17_datetimeutc_ui", "currentconditions_datetime_1_textsummary", "hourlyforecastgroup_hourlyforecast_19_datetimeutc", "currentconditions_datetime_2_minute_ui", "hourlyforecastgroup_hourlyforecast_3_datetimeutc", "hourlyforecastgroup_hourlyforecast_20_lop", "hourlyforecastgroup_hourlyforecast_5_datetimeutc_ui", "hourlyforecastgroup_hourlyforecast_12_datetimeutc_ui", "hourlyforecastgroup_hourlyforecast_7_temperature", "hourlyforecastgroup_hourlyforecast_21_wind_speed", "currentconditions_temperature", "datetime_2_textsummary", "currentconditions_wind_speed_ui", "currentconditions_datetime_2_minute", "hourlyforecastgroup_hourlyforecast_5_condition", "currentconditions_iconcode_ui", "hourlyforecastgroup_hourlyforecast_11_condition_ui", "hourlyforecastgroup_hourlyforecast_23_temperature", "currentconditions_temperature_ui", "hourlyforecastgroup_hourlyforecast_2_temperature", "currentconditions_condition_ui", "datetime_1_timestamp_ui", "hourlyforecastgroup_hourlyforecast_21_datetimeutc", "currentconditions_datetime_1_timestamp", "hourlyforecastgroup_hourlyforecast_9_datetimeutc_ui", "currentconditions_datetime_1_timestamp_ui", "currentconditions_datetime_2_timestamp_ui", "currentconditions_wind_direction", "hourlyforecastgroup_hourlyforecast_24_wind_direction_ui", "hourlyforecastgroup_hourlyforecast_8_temperature", "hourlyforecastgroup_hourlyforecast_5_condition_ui", "datetime_1_textsummary", "hourlyforecastgroup_hourlyforecast_20_lop_category_ui", "hourlyforecastgroup_hourlyforecast_12_temperature_ui", "datetime_2_timestamp", "hourlyforecastgroup_hourlyforecast_20_iconcode", "hourlyforecastgroup_hourlyforecast_8_datetimeutc", "hourlyforecastgroup_hourlyforecast_14_datetimeutc_ui", "datetime_1_hour_ui", "hourlyforecastgroup_hourlyforecast_23_temperature_ui", "currentconditions_datetime_2_textsummary", "hourlyforecastgroup_hourlyforecast_13_temperature_ui", "currentconditions_datetime_1_hour", "hourlyforecastgroup_hourlyforecast_24_datetimeutc_ui", "hourlyforecastgroup_hourlyforecast_11_condition", "hourlyforecastgroup_hourlyforecast_20_datetimeutc_ui", "datetime_1_minute_ui", "currentconditions_datetime_2_hour", "hourlyforecastgroup_hourlyforecast_7_wind_direction_ui", "hourlyforecastgroup_hourlyforecast_4_datetimeutc", "hourlyforecastgroup_hourlyforecast_19_datetimeutc_ui", "currentconditions_wind_speed", "currentconditions_datetime_1_minute_ui", "hourlyforecastgroup_hourlyforecast_12_temperature", "datetime_2_minute", "hourlyforecastgroup_hourlyforecast_20_condition", "hourlyforecastgroup_hourlyforecast_21_wind_direction_ui", "currentconditions_visibility_ui", "hourlyforecastgroup_hourlyforecast_20_lop_category", "hourlyforecastgroup_hourlyforecast_7_wind_direction", "hourlyforecastgroup_hourlyforecast_3_datetimeutc_ui", "hourlyforecastgroup_hourlyforecast_11_datetimeutc", "hourlyforecastgroup_hourlyforecast_13_wind_direction") 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);


Posted on
Wed Dec 04, 2019 4:31 pm
matt (support) offline
Site Admin
User avatar
Posts: 20075
Joined: Jan 27, 2003
Location: Texas

Re: SQL Logger error

Hi Dave,

Unfortunately based on the error it sounds like you are hitting an SQL table limit (at most 1600 columns). That said, based on what I'm seeing the table doesn't have anywhere near 1600 columns (it has a bit less than 200), so I'm a bit puzzled. You might have luck deleting the device in Indigo (be careful – Indigo will want to delete dependent objects as well) and re-creating it. As new states are added to devices Indigo adds them to the SQL Logger table, but it never tries to remove states. So starting "clean" with the device might get it working. If you don't care about losing the MySQL Database data (so just historical state information) then you can just try deleting the SQL file (in the Logs folder) and Indigo will re-create it the next time the SQL Logger plugin restarts.

Image

Posted on
Sun Dec 15, 2019 7:51 pm
bsp9493 offline
Posts: 146
Joined: Nov 30, 2017
Location: Kelowna, BC

Re: SQL Logger error

Thanx Matt...

just wanna make sure i have the right folder. you are talking about the indigo7.4/logs/indigoplugin.sql-logger - correct.

I did try to delete everything in there, but no luck.

any way I can delete the entries for the individual tables in the sql database?

ie one issue "exception trying to update table device_history_974671683 ("dd.weather.gc.ca") -- unable to modify table"

but I have come across others, or would deleting the logs as you noted accomplish the same thing. Not too worried about historical data.

Last resort is delete the device and start again.

Thanx

dave

Posted on
Sun Dec 15, 2019 9:47 pm
kw123 offline
User avatar
Posts: 7122
Joined: May 12, 2013
Location: Dallas, TX

Re: SQL Logger error

delete THIS file:
[code/Library/Application Support/Perceptive Automation/Indigo 7.4/Logs/indigo_history.sqlite[/code]

then sqlite should start from scratch

OR install PSQL. Its very simple, only some mouse clicks: download the app, click on install, tell indigo to use psql (in sqllogger menu)

and PSQL is an order of magnitude more stable, better faster ...

Karl

Page 1 of 1

Who is online

Users browsing this forum: No registered users and 1 guest