Error with location item and JDBC/MariaDB persistence

e36Alex · April 9, 2021, 8:25pm

Hello,

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(AbstractQueryRunner.java:392) ~[bundleFile:?]
        at org.apache.commons.dbutils.QueryRunner.update(QueryRunner.java:491) ~[bundleFile:?]
        at org.apache.commons.dbutils.QueryRunner.update(QueryRunner.java:457) ~[bundleFile:?]
        at org.knowm.yank.Yank.execute(Yank.java:194) [bundleFile:?]
        at org.knowm.yank.Yank.execute(Yank.java:177) [bundleFile:?]
        at org.openhab.persistence.jdbc.db.JdbcBaseDAO.doStoreItemValue(JdbcBaseDAO.java:329) [bundleFile:?]
        at org.openhab.persistence.jdbc.internal.JdbcMapper.storeItemValue(JdbcMapper.java:156) [bundleFile:?]
        at org.openhab.persistence.jdbc.internal.JdbcPersistenceService.store(JdbcPersistenceService.java:143) [bundleFile:?]
        at org.openhab.core.persistence.internal.PersistenceManagerImpl.handleStateEvent(PersistenceManagerImpl.java:152) [bundleFile:?]
        at org.openhab.core.persistence.internal.PersistenceManagerImpl.stateChanged(PersistenceManagerImpl.java:473) [bundleFile:?]
        at org.openhab.core.items.GenericItem.lambda$1(GenericItem.java:259) [bundleFile:?]
        at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1128) [?:?]
        at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:628) [?:?]
        at java.lang.Thread.run(Thread.java:834) [?:?]

I already chaged in jdbc.cfg from:

sqltype.LOCATION      =   VARCHAR(30)

to

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,
Alex

martinvw · April 13, 2021, 6:04am

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.

martinvw · April 13, 2021, 8:41pm

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.

e36Alex · April 14, 2021, 6:59pm

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?

martinvw · April 15, 2021, 6:42am

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

:org.apache.felix.configadmin.revision:=L"4"
enableLogTime="false"
password="****"
service.pid="org.openhab.jdbc"
sqltype.LOCATION="TEXT"
url="jdbc:mysql://****:3306/openhab"
user="****"

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

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.

cweitkamp · April 15, 2021, 2:15pm

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

martinvw · April 15, 2021, 2:18pm

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

cweitkamp · April 15, 2021, 2:21pm

Alright. I will take care.

e36Alex · April 15, 2021, 7:00pm

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.

martinvw · April 16, 2021, 7:26am

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.

e36Alex · April 16, 2021, 5:57pm

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!

cweitkamp · April 24, 2021, 7:16am

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

system · June 4, 2021, 11:17pm

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