Help with RM4 rule error "Column "name" not found [42122-197] on line 1517 (sunrisesetHandler)"



5:07pm was sunset in my that was the trigger.

Slowly trying to convert simple rules to RM4 (as practice) and man...this is not an easy rule language. I'm sure I missed something simple.

The rule looks fine to me (I see what you are doing and should note that it will also turn the light off after three minutes after any opened or closed event between sunrise and sunset, but perhaps that is what you want, and in real life I assume that would have little effect since you are unlikely to manually have it on then in the first place). The error you see in the logs is usually a database problem, though I'm not sure if it could also come about just through a corrupted rule. You may wish to try re-creating the rule to see if that eliminates the error, or check your past logs to see if you find more SQLException errors, which may be a corrupt database.

Thanks for looking at it. Yes for that situation that's the intended use. We really never used that light. But it's handy to have it come on automatically anytime someone goes out that door in the dark. Which is pretty rare.

Maybe @bravenel can shed some light?

He'll certainly have good ideas, but I'd recommend trying this first to see if you can figure out more on your own:

(to be clear, the intent with re-creation was to see if it eliminates a problem due a rule that may have somehow become messed up, or whether you can find a set of steps to reproduce this error, though it's an unusual error that I haven't seen posted here much until recently and has often been indicative of a corrupt hub database...something you may or may not find more signs of if you look through past logs)

I don't know if you intend this...but even when the door is open, if the time is between sunrise and sunset, the light is going to turn off 3 minutes after you open the door. So, if you walk into the basement, and open the door, because it's not between sunset and sunrise, a light turning off event will schedule. So, if you then turned on the light manually, it would shut off 3 minutes later on you. You might want to change the "Else" to an "Else-if Door Closed". Just trying to keep you from shutting the light off on someone and getting yourself (and hubitat) yelled at. :slight_smile: I'm also not sure why you have the sunrise/sunset triggers in there. Triggering off the door and just having the condition of the time should be enough to catch it. The light should never be left on with that rule.

We have already settled that. :slight_smile:

It wasn't clear to me that;s what he was referring to. Also, I started writing this when there were no responses and walked away from my PC for a I didn't bother reading what was posted in the interim. :zipper_mouth_face:

This was the only error looking at past logs. I'll re-create the rule and see what happens. Thanks. Thinking I'm going to have to wait for sunrise and sunset to see if I get the same error thoughts? no?

Recreated the rule...and was going to modify it a bit...but after reading yours and Ryan's post..I'm actually a bit confused on the rule now.

So you say this:

However I'm not understanding why the light would turn off in 3 minutes after the door was opened, and left open, between Sunset and Sunrise.

The intent here was at night (dark only) to have the light turn on if the door was opened and stay on.

If the door got shut, wait 3 minutes and turn off.

If the door was opened in the morning (before sunrise) and left open past sunrise. Sunrise trigger would then turn off the light in 3 minutes.

Ditto to if the door was opened before sunset (no light) then sunset happens...the light comes on if the door is already open.

What am I missing?

(In reality this door never gets left open for more than 3 minutes and is generally just someone going outside from the basement to grab something. So maybe that's probably why I've never noticed an issue with it. But now I'm just curious)

It won't. What we're saying is that it will do so, however, between sunrise and sunset (so the opposite timeframe, i.e., during the day). This is because your actions run every time any trigger fires, and your IF will evaluate to true when it's night and the door is opened. Your ELSE will be true every other time, which includes not only when the door is closed (and it's night) but also any time it's day regardless of the door state (True AND True = True, while False AND anything = False).

Here's one way you could adjust your actions to accomodate this:

IF (Basement Man Door open) THEN
  Cancel Delayed Actions
  IF (Time between sunset and sunrise AND Under deck door light is off) THEN
    On: Under deck door light
  Off: Under deck door light --> delayed 0:03:00 (cancelable)

Now, it will turn on the light between sunset and sunrise when the door is open and keep it on as long as the door is open. When the door is closed it will turn the light off three minutes later (still regardless of time or whether this automation was responsible for turning it on in the first place). You also might not need that "IF ... Under deck light is off" check as sending a second on() to a device that is already on is generally harmless, but it's usually equally harmless to check and perhaps you have a reason for doing so.

All that being said, in real life, I'm guessing that even the "undesired" behavior won't matter much if you either are never in the area for long or natural light provides enough illumination. Still nice to know what's going on, though. :slight_smile:

Yep, read it backwards. Ok we are on the same page then. Yes no reason ever for this to come on during the day. It's an outside light fixture next to a door from my basement that opens up underneath our deck. 99% of the time we are exiting the deck door to the back yard. So I just thought it would be handy to have that light turn on automatically if someone goes out. The main reason I automated the light in the first place was it was an easy light to get left on for long periods of time. Someone went out real quick and turned the light on and forgot to turn it off....days later...the light's still on. Thanks!!

Ok, I deleted that rule and re-created it. Today at sunset no error....

however....I just go these two errors tonight.

This one from a RM rule

and this one from HubConnect

So now we're back to my original suspicion:

A soft reset with a known-good database backup would be a good thing to try if you haven't already contacted Support or feel comfortable just trying this yourself first. (Also a good idea to keep backups stored locally, off the hub, though as long as you can still get to it, it should keep at least one per day automatically.)

1 Like

Did it last night...we'll see how it goes today. Thanks!

I do that periodically. Generally before and after I make rule changes/etc.

Got another error today:

That app is "Rule Machine" I just rebuilt the database a day or two ago.