mySQL: Could not store item

Setup, Configuration and Use Beginners
,
1

Hi Forum

I’m getting continously the following error message:

2015-10-19 04:00:00.157 [ERROR] [.p.m.i.MysqlPersistenceService] - mySQL: Could not store item 'gDGZi1HueDimm' in database with statement 'INSERT INTO Item3 (TIME, VALUE) VALUES(NOW(),?) ON DUPLICATE KEY UPDATE VALUE=?;': Incorrect integer value: 'OFF' for column 'Value' at row 1
2015-10-19 04:00:00.157 [ERROR] [.p.m.i.MysqlPersistenceService] - mySQL: Error count exceeded 1. Disconnecting database.
2015-10-19 04:00:00.173 [ERROR] [.p.m.i.MysqlPersistenceService] - mySQL: Could not store item 'gDGZi1HueColTemp' in database with statement 'INSERT INTO Item15 (TIME, VALUE) VALUES(NOW(),?) ON DUPLICATE KEY UPDATE VALUE=?;': Incorrect integer value: 'OFF' for column 'Value' at row 1
2015-10-19 04:00:00.173 [ERROR] [.p.m.i.MysqlPersistenceService] - mySQL: Error count exceeded 1. Disconnecting database.

items

Group:Dimmer:OR(ON, OFF)	gDGZi1HueDimm		"Hue Spots DG/Zi1 Dimmer"									(gDG_Zi1, gDGZi1Hue)
Group:Dimmer:OR(ON, OFF)	gDGZi1HueColTemp	"Hue Spots DG/Zi1 Farb-Temperatur"							(gDG_Zi1, gDGZi1Hue)

Obviously mysql is expecting a value between 0 and 100 but gets a string instead. Is there any possibility to resolve that error?

Thx & Regards

2

This is my guess…

Dimmer items accept an OnOffType state, but ON and OFF are converted to 0 and 100 respectively. The actual dimmer state is a PercentType (0-100). The OR group summary operator will return the first value (ON) if at least of the group items has that state, otherwise it will return the second value (OFF). None of the dimmer items literally have the ON state (although they may be at 100 percent), so OFF is always returned. This is consistent with the mySQL errors in the log.

I’m not exactly sure what you are trying to accomplish with the OR(ON, OFF) summary but if you used OR(100, 0) instead it should work for persistence although the value will be zero even if all dimmer items are at 99 (for example). If you use OR(0, 100), then the value would be 100 if none of the dimmer items are set to 0. Othewise, maybe a MAX or MIN summary would work better for you.

3

Hi @steve1

thanks a lot for your quick answer :smile: I was already thinking that I might have provided too less information for good understanding of my setup. Also thanks for your explanation of how group items work - wasn’t that clear to me.

Setup:

  • basic system is KNX
  • above mentioned lights are Philips Hue
  • integration is done via OH with KNX and Hue binding
  • the idea behind the group dimmer is to steer all lights of one room (Group) together

Do you see any better possbilities to implement this?

4

I just tried to change the dimmer group item to

items

Group:Dimmer:OR(100, 0)	gDGZi1HueDimm		"Hue Spots DG/Zi1 Dimmer"									(gDG_Zi1, gDGZi1Hue)
Group:Dimmer:OR(100, 0)	gDGZi1HueColTemp	"Hue Spots DG/Zi1 Farb-Temperatur"							(gDG_Zi1, gDGZi1Hue)

but now I get the following error in the log

015-10-19 16:12:21.360 [ERROR] [i.internal.GenericItemProvider] - Group function 'OR' requires two arguments. Using Equality instead.
2015-10-19 16:12:21.360 [ERROR] [i.internal.GenericItemProvider] - Group function 'NOT AND' requires two arguments. Using Equality instead.
2015-10-19 16:12:21.360 [ERROR] [i.internal.GenericItemProvider] - Group function 'OR' requires two arguments. Using Equality instead.
2015-10-19 16:12:21.360 [ERROR] [i.internal.GenericItemProvider] - Group function 'NOT AND' requires two arguments. Using Equality instead.

and the OH desginer complains with

"no viable alternative at input '100'"

What did I misunderstand?

5

Looking at the items.xtext parsing rules, it appears OR will only accept identifiers or strings. You could try OR(‘100’, ‘0’).

When you say you want all the lights steered together do you mean you want them all set to the same dimmer percent?

6

Short answer: Yes :smile:

Long answer: every bulb of Philips Hue can be steered individually but in many cases I just want to adjust brightness for the whole room which means to dim the lights of a group of hue bulbs --> group dimmer.

7

Great, that works perfect and it seems to be that mysql is not complaining anymore :smile:

Thank you Steve