[MySQL] Frequent state changes are not added to database.

[kjasdal]

Expected Behavior

When using a persistence service, with strategy = everyChange, all changes of state should be stored in the database - regardless of how quickly the state changes.

Current Behavior

Frequent changes of state (changes within the time period of a second) are not persisted.

Possible Solution

As far as I can tell, the problem is related to the fact that the Time column in the database (table) only supports resolution down to seconds, and at the same time it is the PRIMARY KEY (that needs to be unique for each entry). This probably leads to entries on the same second not being accepted.

MySQL 5.7 introduces support for fractional seconds (down to microseconds):

http://dev.mysql.com/doc/refman/5.7/en/fractional-seconds.html

Modifying the following line (311/312) in MysqlPersistenceService.java should possibly be enough:

sqlCmd = new String( "CREATE TABLE " + tableName + " (Time DATETIME, Value " + mysqlType + ", PRIMARY KEY(Time));");

DATETIME => DATETIME(3)

An alternative solution (for MySQL version prior to 5.7) would be to change the Time column to some large general number type (e.g. BIGINT) and then simply use milliseconds as time stamp.

Steps to Reproduce (for bugs)

1. Set up MySQL persistence for an item using strategy = everyChange.
2. Write and execute a test rule that changes the state of the item several times as quickly as possible, e.g.

item.postUpdate(0) item.postUpdate(2) item.postUpdate(3) item.postUpdate(4)

1. Browse the openHAB events.log file to see that all state changes are "registered" by openHAB event bus.
2. Query the MySQL database (e.g. using MySQL Workbench) and verify that some of the state changes are missing from the database.

   Context

Any use of MySQL as persistence service.

Your Environment

• Version used: openHAB 1.8.3
• Environment name and version (e.g. Chrome 39, node.js 5.4, Java 8, ...): OpenJDK 1.8.0_91
• Operating System and version (desktop or mobile, Windows 10, Raspbian Jessie, ...): Ubuntu 14.04
• MySQL 5.7.13

[watou]

Thank you for the clear issue. Would changing the CREATE TABLE statement lead to any issues for existing users of the MySQL persistence service? If so, an alternative approach could be to add a new DAO class to the JDBC persistence service, maybe call it JdbcMysql57DAO, and add it to how the service can be configured. Maybe @lewie, you have a better suggestion?

[kjasdal]

I am not sure the use of fractional seconds is backwards compatible, so I think the best approach is to take into account what version of MySQL that is in use.

For a run time solution, it is possible to check the version of MySQL using the following query:

SELECT VERSION();

(In my case it reports "5.7.13")

An alternative approach would of course be a setting in openhab.cfg.

[lewie]

@kjasdal, fractional seconds works since version 5.6.

@watou, I'm testing a new DbMetaData Class using DatabaseMetaData and getMetaData() for checking db versions in JDBC-Persistence Bundle at the moment. This would be useful for other databases too. Extending JdbcMysqlDAO I will differ db version using Fractional Seconds for minor version greater 5. I hope next days I will have a PR for you.

[watou]

That sounds good, @kjasdal @lewie! Please also consider how a change might affect those who have existing tables populated with a previous version of the persistence service, where tables have already been created and populated without fractional seconds. Would a database containing tables created without fractional seconds, and then later new tables created with fractional seconds, lead to any conceivable regressions or unpleasant surprises for those who access the database external to openHAB?

[watou]

One more thought: if the JDBC persistence service allowed setting the SQL type for the TIME column by adding support for:

jdbc:sqltype.TIME        =   TIMESTAMP(6)

And changing code around here and here:

SQL_CREATE_ITEM_TABLE = "CREATE TABLE IF NOT EXISTS 
  #tableName# (time #timetype# NOT NULL, value #dbType#, PRIMARY KEY(time))";

This could put control completely in the user's hands, if the default behavior for a given DBMS version isn't what the user wants.

[lewie]

@watou, please can you review.. ;-) @kjasdal, testing?! For testing

[kjasdal]

Thanks for a quick resolution! I will certainly give it a spin and help out with testing. I am, however, currently on vacation with my family, so it will be a week before I can provide results.

1. jul. 2016 7.34 a.m. skrev "Helmut Lehmeyer" notifications@github.com:

@watou https://github.com/watou, please can you review.. ;-) @kjasdal https://github.com/kjasdal, testing?! For testing https://github.com/lewie/openhab/raw/JDBC_general_high_precision_test/bundles/persistence/org.openhab.persistence.jdbc/org.openhab.persistence.jdbc-1.9.0-SNAPSHOT.jar

— You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub https://github.com/openhab/openhab/issues/4546#issuecomment-234458329, or mute the thread https://github.com/notifications/unsubscribe-auth/AKpMSX0-KxxhFgJtopgppVI4qWZzeRWRks5qYFZjgaJpZM4JOT1B .

[kjasdal]

Sorry about the delay!

I have now finally found the time to test the SNAPSHOT jar that @lewie provided above, and it seems to work just fine meaning that it solves the problem reported at the start of this thread.

What I did:

1) Stopped the openHAB service. 2) Replaced my original version of org.openhab.persistence.jdbc-xxx.jar (1.8.3) with the SNAPSHOT version provided above. 3) Added the following configuration options to openhab.cfg: jdbc:url=<...> jdbc:user=<...> jdbc:password=<...> 4) Dropped all tables from the MySQL database (version 5.7.13). 5) Started the openHAB service. 6) Ran a simple test scenario (as indicated in the problem description).

Result: All state changes are present in the MySQL database, and the time stamp uses fractional seconds with milliseconds resolution (.000 - .999).

Thx for the fix @lewie! I will use this in my production setup, while waiting from a 1.8.4 release - if that ever comes.

BTW; Is this addon also used in 2.0, or is there possibly a need for a similar fix "over there"?

[watou]

Is this addon also used in 2.0, or is there possibly a need for a similar fix "over there"?

This code is used in the openHAB 2 distro. There is currently no plan for a 1.8.4 release that I'm aware of, but after PR #4550 is merged, you should be able to get the JAR from here.

[lewie]

@watou, It will probably take a few more days, unfortunately. @kjasdal, thanks for testing!

[watou]

@lewie, no worries, thanks for checking in!