Error with location item and JDBC/MariaDB persistence


today i set up jdbc persistence services with mariadb. Everything works fine so far, i just got a problem with my icloud location items.
I get the following error message to my log:

21:07:26.327 [ERROR] [org.knowm.yank.Yank                  ] - Error in SQL query!!!
java.sql.SQLException: Data too long for column 'value' at row 1 Query: INSERT INTO applewatchig_location_0193 (TIME, VALUE) VALUES( NOW(3), ? ) ON DUPLICATE KEY UPDATE VALUE= ? Parameters: [53.11111111111111,8.999999999999999,0.0, [53.11111111111111,8.999999999999999,0.0]
        at org.apache.commons.dbutils.AbstractQueryRunner.rethrow( ~[bundleFile:?]
        at org.apache.commons.dbutils.QueryRunner.update( ~[bundleFile:?]
        at org.apache.commons.dbutils.QueryRunner.update( ~[bundleFile:?]
        at org.knowm.yank.Yank.execute( [bundleFile:?]
        at org.knowm.yank.Yank.execute( [bundleFile:?]
        at org.openhab.persistence.jdbc.db.JdbcBaseDAO.doStoreItemValue( [bundleFile:?]
        at org.openhab.persistence.jdbc.internal.JdbcMapper.storeItemValue( [bundleFile:?]
        at [bundleFile:?]
        at org.openhab.core.persistence.internal.PersistenceManagerImpl.handleStateEvent( [bundleFile:?]
        at org.openhab.core.persistence.internal.PersistenceManagerImpl.stateChanged( [bundleFile:?]
        at org.openhab.core.items.GenericItem.lambda$1( [bundleFile:?]
        at java.util.concurrent.ThreadPoolExecutor.runWorker( [?:?]
        at java.util.concurrent.ThreadPoolExecutor$ [?:?]
        at [?:?]

I already chaged in jdbc.cfg from:

sqltype.LOCATION      =   VARCHAR(30)


sqltype.LOCATION      =   VARCHAR(200)

because my unterstanding is that the field is too short. After i changed the value i deleted the table from mariadb and restarted openhab, but i still get the error and i dont know whats the reason.

The type of the item at openhab is location.

Thanks in advance for your help,

I just updated my column’s to text both manually for existing tables in the config, I’ll try to keep you updated on how it works on my side.

1 Like

I changed to type to text through the UI and now it recreated my tables and it works. I did restart openHAB in the meantime, just to be sure.

1 Like

Hello @martinvw ,

thanks for your reply and help!
I didn’t quite understand what exactly you changed, though. Could you please give me some additional information on that?

I removed my jdbc.cfg and went to /#!/settings/services/org.openhab.jdbc

And there I did set the following property

That config did end up in userdata/config/org/openhab/jdbc.config


It could be that somehow those two are conflicting, I must admit that I do not know which file should win :slight_smile:

A small difference I see is that my value ends up quoted, but that does not seem like the main problem I would expect an error in that case.

Thanks for testing. Do we need to increase the default VARCHAR size for Location Items in the code?

1 Like

Yes, somewhere in time the height was also added, since than 30 is not enough

1 Like

Alright. I will take care.

Thank you!

I also deleted the jbdc.cfg and entered the data at the ui. I changed the SqlType from VARCHAR(200) to TEXT like at your screenshot.
I even checked the jdbc.config and everything seems to be fine.
I restarted openhab and cleared the cache.

But still i get the same error message.

It does not change existing tables, that you also remove the old table and the entry from the items table? If you do not do that you have to manually change existing tables.

1 Like

Thank you, thats it! I droped the tables but forgot to delete them from item table.
I cleared the cache again and now it works fine. Great!

1 Like

I created a PR couple of days ago but it has not been merged yet.

1 Like

This topic was automatically closed 41 days after the last reply. New replies are no longer allowed.