Disable SQL logging not working?

Hi Dave,
I get a bunch of SQL Logger errors when downloading the OpenWeather hourly info (seems like too many columns?). I don't really need this logged with the SQL Logger so selected the option to disable for this device yet it still seems to create the table and columns. By the name of the option, I was expecting that to not happen - is that used for something different?

Thanks, Aldon

Hi Aldon - when this option is checked, the plugin is supposed to tell Indigo to ignore all the device states for the purposes of updating the SQL database. The "too many columns" error seems to indicate that's not working properly.

If you're comfortable doing so, please run the following Python code as an embedded script and post the results (replace 12345678 with the ID of your device).

Code: Select all

dev = indigo.devices[12345678]
indigo.server.log(u"{0}".format(dev.ownerProps))

There might be a bug in the way this setting is handled.

EDIT: be sure to mask your API key!!

Hi Dave,
Thanks for the reminder about the api key! Also removed lat and long.

Code: Select all

  Script                          com.fogbert.indigoplugin.GhostXML : (dict)
     curlArray :  (string)
     digestPass :  (string)
     digestUser :  (string)
     disableLogging : true (bool)
     doSubs : false (bool)
     feedType : JSON (string)
     maxRetries : 10 (string)
     refreshFreq : 300 (string)
     sourceXML : https://api.openweathermap.org/data/2.5/onecall?lat={lat}&lon={-yy}&exclude=current,minutely,daily,alerts&units=imperial&appid={api id}(string)
     sqlLoggerIgnoreStates : * (string)
     subA :  (string)
     subB :  (string)
     subC :  (string)
     subD :  (string)
     subE :  (string)
     timeout : 5 (string)
     tokenUrl :  (string)
     useDigest : None (string)


Aldon

According to this post:

Setting sqlLoggerIgnoreStates to * will ignore all state updates for the device. Note ignored state values will still be written to new device rows if there is a non-ignored state update (the ignore list only prevents triggering new rows to be added), but you will see far fewer new rows added to the table.

Your value is set properly, so it sounds like there may be legacy states in the SQL database which is causing at least some interaction with the SQL server. I don't use SQL--so I'm not 100 percent sure--but it may be necessary to recreate the device (create a new device) and disable SQL logging when initially creating the device. I think I'm going to change that setting to default to have logging disabled to reduce the risk of this happening.

Hopefully, Matt or Jay will see this message and let us both know if that's not the case.

Hi Dave,
I had previously created a new GhostXML device with the Ignore box checked but I did it again. I can see the table being created when I select the GhostXML model, then I configure the Device (including checking the Ignore option) and the table columns get created.

Based on the description you included, I'm now wondering if the table is going to get created along with the columns regardless of the state of the Ignore checkbox and it's only future state changes that this is controlling. Also wondering what the column limit is for the SQLite Logger.

Aldon

Aldon - this may be the result of a bug. I went back into the code and revised slightly how this behavior is controlled. That may make a difference in the what you're seeing. It think the way this control was initially implemented might have resulted in a race where the table was created before the ignore property was fully configured.

I'm planning to push an update to the plugin soon, so you might want to try that when it drops.

Dave

[matt (support)]

I'm not sure if Dave's latest changes will resolve this error. Having sqlLoggerIgnoreStates set to * only prevents new rows from being added as states are changing. The SQL Logger plugin will still try to write the initial table to the database and if that has too many columns the error will occur. I guess if the initial device doesn't have too many columns (states) and those are added later it might work (or maybe the error will only happen once).

Regardless, I've just modified the SQL Logger plugin so that hopefully in the next release it won't create the database at all if sqlLoggerIgnoreStates is set to *.

Thanks Matt & Dave!

Matt, do you know how many columns a table can have with the SQL Logger? I haven't done the math but I'd bet the OpenWeather OneCall API call would create over 1000 columns (considering the main column plus the .ui). It's a tradeoff of API call efficiency vs table efficiency I suppose but just wondering if I'm going to run into issues with that?

Thanks again, Aldon

[matt (support)]

I believe it is strictly a limitation of the database type being used. I think SQLite defaults to 2000 maximum number of columns, and Google is showing PostgreSQL tops out at 1600 columns.

Thanks Matt!

The base GhostXML device definition presently only has two or three states. The idea was that upon initial creation, the system would only write those three to the database and (with the option to ignore updates set to True) all the other states would be ignored. This was added to meet an obvious need, but it's clear now that applying the filter after the device has been synced even once will defeat the purpose. I am excited about the recent change you made. I will be pushing an update to the GhostXML plugin so that the feature defaults to ON and then if the user wants logging, they can turn it off at will.

Hi Dave,
Catching back up with this. I've upgraded to Indigo 7.5 and GhostXML 0.05.05 and I'm still getting the errors. Would I need to delete the GhostXML device and recreate for this to take effect?

In my current device, I have Disable SQL Logging checked but I'm still seeing SQL Logger errors (too many columns for the table).

thanks, Aldon

Hi Aldon -- I believe then answer may be yes--but I'm anxious to hear an expert opinion. Although it doesn't help you, I have since changed this to be the default option--such that everything should be ignored until SQL logging is affirmatively enabled.

OK, I'll see if Matt replies. I have a messy script parsing stuff and I hardcoded the device number so will have to fix that if you are right.

thanks, Aldon

[matt (support)]

If you haven't already, try disabling the SQL Logger plugin, then enabling the GhostXML Disable SQL Logging option for the device (maybe toggle it OFF, close UI, then open and re-toggle it), then finally re-enable the SQL Logger plugin. Do you still see the error? If so I'll probably have you dump the device to the Event Log so we can make sure the disable prop is getting set.

Hi Matt, looks like that worked. I'll keep an eye and post back if I see continuing issues.

thanks, Aldon