A word of warning if you are using MySQL as persistence service. I recently discovered that state changes of my persisted items were missing from the database. Digging into the cause of the problem, I believe it to be related to the fact that the “time resolution” in the MySQL server (prior to version 5.7) is only seconds. Since the Time column of the tables used to hold the historic states for an item is also the PRIMARY KEY (that needs to be unique) it appears that any state change within the time frame of one second from the previous is simply discarded!
I have raised an issue on github for this (#4546).
Yes, I know, and that is the root cause of this problem. Since the time stamp can only support seconds resolution, there is bound to be state changes that are not stored. To me this is not acceptable in a persistence solution - all state changes (if registered by the openHAB event bus) needs to be stored regardless of how frequent they happen.
I am new to OH and haven’t played with persistence yet (other than what happen sin the demo).
However, I am surprised that primary key is on the datum and not on the “thing”.
I would suspect the table to look like this: id, datum, thing (PK), state. Set-up this way should not cause a problem, based on identical time stamps.
I looked at this a couple of years back while I was working on mySQL. If I remember correcly, I recall that the latest version of mySQL has the ability to store timestamps in milliseconds, but most (at the time) didn’t. Maybe now everything has moved over to support this and it would allow to support this.
Sorry for warming this old discussion, but after a lot of in depth analysis I come to the conclusion that the problem still exists exactly as described by @KjetilA 1,5 years ago.
I am using MySQL persistence (not JDBC) and switched my rules to use group based persistence and work with groups in rules. I noticed that the rule which should catch the last update timestamp for my items did not work reliable and did a lot of testing. In the end the problem seems to be exactly the primary key with the “second resolution” so that updates to the persisted group item are only recorded/triggered once per second which is indeed limiting the use of groups in rules as more frequent updates are not processed reliable. The individuale item tables show all updates ( i use strategy everyUpdate) but if 2 or more items updated in the same second one will be lost.
@chris Would it be possible to make the timestamp more precise?
Or are there any other persistence solutions which already support more detailed time information?
Ok, I found the solution already myself. JDBC persistence supports this. You just have to set the SQL datatype to DATETIME(3) (=3 digits for ms as fraction of the second) in the jdbc.cfg config file:
sqltype.DATETIME = DATETIME(3)
With this setting the MySQL Datbase offers much higher timestamp precision:
…and the rules using group based persistence can store their results also when 2 items changed in the same second.
