iCloud binding persist with jdbc:mysql throws error for lastLocationUpdate

Add-ons Persistence Services
, ,
1

Hi,

i’ve installed the iCloud Binding which really works like a charm. BUT: i persist all my data into a mysql database installed at the same system. All values for all items get persisted properly but not the “lastLocationUpdate” of the iCloud binding.

It seems that the corresponding mysql-table for those items does not get created. OpenHab2 tries to store new incoming values to the database but there is an error message showing up in the log:

2019-08-09 12:50:12.204 [ERROR] [org.knowm.yank.Yank ] - Error in SQL query!!! java.sql.SQLException: Table 'OpenHAB.item0077' doesn't exist Query: INSERT INTO item0077 (TIME, VALUE) VALUES( NOW(3), ? ) ON DUPLICATE KEY UPDATE VALUE= ? Parameters: [2019-08-09 12:47:39.383, 2019-08-09 12:47:39.383]

With some searching i could find another error message at the log where openHab seems to have a problem generating the items-table for this item:

2019-08-08 10:58:05.026 [ERROR] [org.knowm.yank.Yank ] - Error in SQL query!!! java.sql.SQLException: Invalid default value for 'value' Query: CREATE TABLE IF NOT EXISTS item0077 (time TIMESTAMP(3) NOT NULL, value TIMESTAMP(3), PRIMARY KEY(time)) Parameters: []

I can reproduce this for all items of this thing-channel.

Any idea where to look or why this might happen?

Best regards

BoxSon

2

Yep, so far as I can make the persistence service (essentially a 1.x legacy) does not yet work with Location type Items

That thread also suggests workarounds

3

Funny thing is that this item is not the location item itself, it‘s the timestamp of the last location update. (Datetime)

4

found a solution. I’ve created the mysql table manually by login in to mysql at the console and executing a modified create query. Seems that for timestamp rows a default value needs to be defined:

CREATE TABLE IF NOT EXISTS item0115 (time TIMESTAMP(3) NOT NULL, value TIMESTAMP(3) default now(3), PRIMARY KEY(time));

replace the table name (item0115 in this example) with the name of the table which needs to be creted in your situation.

Same problem showed up after installing CalDav binding, as there are also many time-based values.