[Persistence] JDBC and MySQL restoreOnStartup throw error for Location items

[5iver]

[old issue... moving to correct repo]

This may affect other persistence bindings too, but I have experienced it with both MySQL and JDBC persistence bindings using a MariaDB db.
Forum topic
Expected Behavior

Location type items should restore on startup and not throw an error.
Current Behavior

2017-07-24 03:09:58.913 [ERROR] [pse.smarthome.core.items.GenericItem] - Tried to set invalid state 41.226653,-81.722503 (StringType) on item Home_Hinckley of type LocationItem, ignoring it
2017-07-24 03:09:58.943 [ERROR] [pse.smarthome.core.items.GenericItem] - Tried to set invalid state 41.2266068,-81.7225042 (StringType) on item Lisa_Location of type LocationItem, ignoring it
2017-07-24 03:09:59.042 [ERROR] [pse.smarthome.core.items.GenericItem] - Tried to set invalid state 41.2267487,-81.7224206 (StringType) on item Scott_Location of type LocationItem, ignoring it

Possible Solution

Location items are stored as VARCHAR in mysql/mariadb. I have not yet tested changing the datatype to GEOMETRY.
Steps to Reproduce (for Bugs)

With jdbc-mariadb persistence running, create and item of type Location and set its state.
Restart OH (or jdbc-mariadb binding) and observe error and item's value is not restored

Your Environment

OH 2.2.0 snapshot build 1033 (and previous versions)
jdbc persistence bundle 1.11.0.201709060110
Maria JDBC client 1.3.5.0

[9037568]

Needs a debug log.

[5iver]

Needs a debug log.

It was in there... just lost formatting pasting from other issue.

[9037568]

This?

2017-07-24 03:09:58.913 [ERROR] [pse.smarthome.core.items.GenericItem] - Tried to set invalid state 41.226653,-81.722503 (StringType) on item Home_Hinckley of type LocationItem, ignoring it

Is not a debug log.

[5iver]

Ah... debug... 🙄 sorry...

The table is never created for the LocationItem (and I've given it a value). This is after an OH restart.

2019-01-10 02:02:29.748 [DEBUG] [org.openhab.persistence.jdbc.internal.JdbcPersistenceService] - JDBC::query: item is Test_Location
2019-01-10 02:02:29.748 [WARN ] [org.openhab.persistence.jdbc.internal.JdbcPersistenceService] - JDBC::query: unable to find table for query, no data in database for item 'Test_Location'. Current number of tables in the database: 3111
2019-01-10 02:02:29.748 [WARN ] [org.openhab.persistence.jdbc.internal.JdbcPersistenceService] - JDBC::query: no way to generate the table for item 'Test_Location'

[9037568]

With jdbc-mariadb persistence running, create and item of type Location and set its state.

Ok, after poking at the code a bit, I have another question. How are you setting the state of the LocationItem initially?

It appears to me this should fail whether persistence is running or not...

[5iver]

I think I did it yesterday through Jython, so something like this (different location through)...

events.sendCommand("Test_Location", "41.555, -81.555")

But I just did it through Rules DSL with...

sendCommand("Test_Location", "41.555, -81.555")

Checked that it went through with...

openhab> smarthome:status Test_Location
41.555,-81.555

[9037568]

So this:

JDBC::query: unable to find table for query, no data in database for item 'Test_Location'. Current number of tables in the database: 3111

Indicates there's no data stored for the item in question. Do you have the logs for the time that the item was created?

[tnemrap]

I have the same problem JDBC does not store LOCATION Items.
2019-02-04 14:24:45.017 [WARN ] [jdbc.internal.JdbcPersistenceService] - JDBC::query: unable to find table for query, no data in database for item 'VolvoLocation'. Current number of tables in the database: 442 2019-02-04 14:24:45.028 [WARN ] [jdbc.internal.JdbcPersistenceService] - JDBC::query: no way to generate the table for item 'VolvoLocation'
I have tried with both mysql and mariadb.

[5iver]

I think when I made my initial report, I had previously created a StringItem to store the location, and then had changed it to a LocationItem. This would explain how the table was originally created, and the error at startup. Recently, I created a new LocationItem, and the table was never created.

Here are fresh logs after creating a new LocationItem...

2019-02-04 11:56:46.459 [DEBUG] [org.openhab.persistence.jdbc.internal.JdbcPersistenceService] - JDBC::query: item is Test_Location_1
2019-02-04 11:56:46.459 [WARN ] [org.openhab.persistence.jdbc.internal.JdbcPersistenceService] - JDBC::query: unable to find table for query, no data in database for item 'Test_Location_1'. Current number of tables in the database: 3594
2019-02-04 11:56:46.460 [WARN ] [org.openhab.persistence.jdbc.internal.JdbcPersistenceService] - JDBC::query: no way to generate the table for item 'Test_Location_1'
2019-02-04 11:56:46.469 [INFO ] [smarthome.event.ItemAddedEvent] - Item 'Test_Location_1' has been added.

And after it receives an update, there is nothing in a persistence debug log, as if it is not being tracked.

2019-02-04 12:03:22.528 [INFO ] [smarthome.event.ItemCommandEvent] - Item 'Test_Location_1' received command 41.555,-81.555
2019-02-04 12:03:22.529 [INFO ] [smarthome.event.ItemStateEvent] - Test_Location_1 updated to 41.555,-81.555
2019-02-04 12:03:22.530 [INFO ] [smarthome.event.ItemStateChangedEvent] - Test_Location_1 changed from NULL to 41.555,-81.555

@9037568 , is there anything more that could help you?

[9037568]

See the last note in https://community.openhab.org/t/persistence-not-working-for-gps-location-formatted-xx-xxx75788937955-x-xxxx170208076405/37883/27

…

On Mon, Feb 4, 2019 at 12:12 PM Scott Rushworth ***@***.***> wrote: I think when I made my initial report, I had previously created a StringItem to store the location, and then had changed it to a LocationItem. This would explain how the table was originally created, and the error at startup. Recently, I created a new LocationItem, and the table was never created. Here are fresh logs after creating a new LocationItem... 2019-02-04 11:56:46.459 [DEBUG] [org.openhab.persistence.jdbc.internal.JdbcPersistenceService] - JDBC::query: item is Test_Location_1 2019-02-04 11:56:46.459 [WARN ] [org.openhab.persistence.jdbc.internal.JdbcPersistenceService] - JDBC::query: unable to find table for query, no data in database for item 'Test_Location_1'. Current number of tables in the database: 3594 2019-02-04 11:56:46.460 [WARN ] [org.openhab.persistence.jdbc.internal.JdbcPersistenceService] - JDBC::query: no way to generate the table for item 'Test_Location_1' 2019-02-04 11:56:46.469 [INFO ] [smarthome.event.ItemAddedEvent] - Item 'Test_Location_1' has been added. And after it receives an update, there is nothing in a persistence debug log, as if it is not being tracked. 2019-02-04 12:03:22.528 [INFO ] [smarthome.event.ItemCommandEvent] - Item 'Test_Location_1' received command 41.555,-81.555 2019-02-04 12:03:22.529 [INFO ] [smarthome.event.ItemStateEvent] - Test_Location_1 updated to 41.555,-81.555 2019-02-04 12:03:22.530 [INFO ] [smarthome.event.ItemStateChangedEvent] - Test_Location_1 changed from NULL to 41.555,-81.555 @9037568 </~https://github.com/9037568> , is there anything more that could help you? — You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub <#5761 (comment)>, or mute the thread </~https://github.com/notifications/unsubscribe-auth/AJdQOX-8ObhzQSLt0Wr9botYAdIX-cJEks5vKGoAgaJpZM4Z4TW1> .

[5iver]

Since there's a compat1x LocationItem, shouldn't it work with 1.x persistence? Although, persistence moving to 2.x is coming, so that should surely resolve this.

[lewie]

The problem is, the compatibility layer to openHAB 2 can't do anything with the itemRegistry returns null for Location Items:
/~https://github.com/openhab/openhab1-addons/blob/master/bundles/persistence/org.openhab.persistence.jdbc/java/org/openhab/persistence/jdbc/internal/JdbcPersistenceService.java#L164

I will port the binding to openHAB 2 once it is generally clear how persistence bindings can be implemented/distributed in openHAB 2.

@openhab-5iver, or are there any examples already?

[5iver]

or are there any examples already?

@lewie, Kai's on it. There are a trail of PR's/issues, but this one should get you there... #5844.

[cweitkamp]

Closed by openhab/openhab-addons#10501