OH3: Issue jdbc mysql Table Creation for Image Item

JensH · January 8, 2021, 3:49pm

Hello together,

using OH3 (stable) on Ubuntu 20.4 with jdbc mysql.

Because default database is now set automatically to jdbc for recovery for all items there is the need to persistence.

If having an item for an Image (e.g. openweather-binding image owm current) the creation of the table gives a failure:

java.sql.SQLException: Column length too big for column ‘value’ (max = 21845); use BLOB or TEXT instead Query: CREATE TABLE IF NOT EXISTS owmcurrentconditionicondel_1839 (time TIMESTAMP(3) NOT NULL, value VARCHAR(65500), PRIMARY KEY(time)) Parameters:

I try to solve this create the table directly my hand with TYPE BLOB, will see if that will work.

But the binding should be able to create this tables in OH3 on itself too.

mhilbush · January 8, 2021, 5:05pm

Are you running mysql or mariadb?

That looks like the create statement that’s used for mysql, but OH limits the size to16255 for mariadb here.

So if you’re running mariadb, but specified mysql in openHAB, that’s one way to get that error. There may be other ways too.

As for BLOB, @cweitkamp and I talked about that change possibly being needed in a future release. But it was too much work to squeeze into 3.0.

JensH · January 8, 2021, 5:25pm

It’s MySQL

Server: Localhost via UNIX socket
Server-Typ: MySQL
Server-Verbindung: SSL wird nicht verwendet
Server-Version: 8.0.22-0ubuntu0.20.04.3 - (Ubuntu)
Protokoll-Version: 10
Benutzer: xxxxxxx@localhost
Server-Zeichensatz: UTF-8 Unicode (utf8mb4)

mhilbush · January 8, 2021, 8:49pm

Well, it was worth a shot.

FWIW, that CREATE TABLE statement works when I run it against the version of mysql I’m running (5.7.32-0ubuntu0.18.04.1 - (Ubuntu)).

It may be a sql_mode setting, or perhaps the character set.

Edit:

My db is using latin1, which I think is the default in 5.7. Not sure about 8.0.

mysqlcharset

mhilbush · January 8, 2021, 9:16pm

Here’s one other thing you could try (and I’m not sure of this will work).

In your jdbc.cfg, try adding this line, then restart the bundle (or restart openHAB).

sqltype.IMAGE=VARCHAR(21800)

Edit: I used 21800 instead of 21845 because I think that number may represent the sum of all columns in the table. So I allowed some wiggle room to account for the time column.

JensH · January 8, 2021, 11:50pm

I just added and will have a look next days. Some tables I already created with BLOB, but I’ll try the others now without manual intervention and the added config.

JensH · January 14, 2021, 11:21pm

Because tables at the moment all created, I can not tell if the added line works.

But I can inform, VARCHAR(21800) don’t works.

persistence saving the pictures then, but isn’t able to reload giving errors:

2021-01-15 00:07:36.982 [ERROR] [org.openhab.core.items.GenericItem  ] - Tried to set invalid state raw type (image/png): 2773 bytes (StringType) on item owmFC3hConditionIcon of type ImageItem, ignoring it
2021-01-15 00:07:37.003 [ERROR] [org.openhab.core.items.GenericItem  ] - Tried to set invalid state raw type (image/png): 2773 bytes (StringType) on item owmFC24hConditionIcon of type ImageItem, ignoring it
2021-01-15 00:07:37.049 [ERROR] [org.openhab.core.items.GenericItem  ] - Tried to set invalid state raw type (image/png): 2773 bytes (StringType) on item owmFC6hConditionIcon of type ImageItem, ignoring it
2021-01-15 00:07:37.120 [ERROR] [org.openhab.core.items.GenericItem  ] - Tried to set invalid state raw type (image/png): 3901 bytes (StringType) on item owmFC9hConditionIcon of type ImageItem, ignoring it

I will try with mysql tables type blob now.

ohb1138 · April 5, 2021, 8:18am

@JensH

Hi Jens, any solution found. I am in my first upgrade approach and get the same errors …

JensH · April 5, 2021, 9:19am

Just do like this, but with BLOB:

sqltype.IMAGE=BLOB

Or if your images bigger with Other BLOB-types

ohb1138 · April 5, 2021, 9:32am

THANK YOU!

I will try this!

JensH · April 5, 2021, 10:05am

Just searched out from github-bug to this:

TINYBLOB (255 B)
BLOB (64 KB)
MEDIUMBLOB (16 MB)
LONGBLOB (4 GB)

ohb1138 · April 5, 2021, 10:18am

I am currently fine with BLOB

I have other JDBC errors at the moment … The BLOB seemed to have solved the issue. I have created a new database with the BLOB for images and now I have different errors which I am fighting now … Thanks for the update!

JensH · April 5, 2021, 11:21am

What kind of errors?

ohb1138 · April 5, 2021, 11:46am

I have created a new blank database from scratch for the new OH version as I migrated from OH 2.5.12 to OH 3.1.0-M3. The acess works as OH creates all tables and some of them receive updates … So it cannot be a general problem. I am using a mariadb via the JDBC driver …

13:27:44.343 [ERROR] [rnal.common.AbstractInvocationHandler] - An error occurred while calling method 'QueryablePersistenceService.query()' on 'org.openhab.persistence.jdbc.internal.JdbcPersistenceService@7e37e595': class [B cannot be cast to class java.lang.String ([B and java.lang.String are in module java.base of loader 'bootstrap')
13:27:44.358 [ERROR] [tence.internal.PersistenceManagerImpl] - Exception occurred while querying persistence service 'jdbc': class [B cannot be cast to class java.lang.String ([B and java.lang.String are in module java.base of loader 'bootstrap')
13:27:44.564 [ERROR] [rnal.common.AbstractInvocationHandler] - An error occurred while calling method 'QueryablePersistenceService.query()' on 'org.openhab.persistence.jdbc.internal.JdbcPersistenceService@7e37e595': class [B cannot be cast to class java.lang.String ([B and java.lang.String are in module java.base of loader 'bootstrap')
13:27:44.577 [ERROR] [tence.internal.PersistenceManagerImpl] - Exception occurred while querying persistence service 'jdbc': class [B cannot be cast to class java.lang.String ([B and java.lang.String are in module java.base of loader 'bootstrap')
13:27:44.776 [ERROR] [rnal.common.AbstractInvocationHandler] - An error occurred while calling method 'QueryablePersistenceService.query()' on 'org.openhab.persistence.jdbc.internal.JdbcPersistenceService@7e37e595': class [B cannot be cast to class java.lang.String ([B and java.lang.String are in module java.base of loader 'bootstrap')
13:27:44.780 [ERROR] [tence.internal.PersistenceManagerImpl] - Exception occurred while querying persistence service 'jdbc': class [B cannot be cast to class java.lang.String ([B and java.lang.String are in module java.base of loader 'bootstrap')
13:27:44.856 [ERROR] [rnal.common.AbstractInvocationHandler] - An error occurred while calling method 'QueryablePersistenceService.query()' on 'org.openhab.persistence.jdbc.internal.JdbcPersistenceService@7e37e595': class [B cannot be cast to class java.lang.String ([B and java.lang.String are in module java.base of loader 'bootstrap')
13:27:44.861 [ERROR] [tence.internal.PersistenceManagerImpl] - Exception occurred while querying persistence service 'jdbc': class [B cannot be cast to class java.lang.String ([B and java.lang.String are in module java.base of loader 'bootstrap')
13:27:45.093 [ERROR] [rnal.common.AbstractInvocationHandler] - An error occurred while calling method 'QueryablePersistenceService.query()' on 'org.openhab.persistence.jdbc.internal.JdbcPersistenceService@7e37e595': class [B cannot be cast to class java.lang.String ([B and java.lang.String are in module java.base of loader 'bootstrap')
13:27:45.099 [ERROR] [tence.internal.PersistenceManagerImpl] - Exception occurred while querying persistence service 'jdbc': class [B cannot be cast to class java.lang.String ([B and java.lang.String are in module java.base of loader 'bootstrap')
13:27:45.410 [ERROR] [rnal.common.AbstractInvocationHandler] - An error occurred while calling method 'QueryablePersistenceService.query()' on 'org.openhab.persistence.jdbc.internal.JdbcPersistenceService@7e37e595': class [B cannot be cast to class java.lang.String ([B and java.lang.String are in module java.base of loader 'bootstrap')
13:27:45.414 [ERROR] [tence.internal.PersistenceManagerImpl] - Exception occurred while querying persistence service 'jdbc': class [B cannot be cast to class java.lang.String ([B and java.lang.String are in module java.base of loader 'bootstrap')
13:27:45.854 [ERROR] [rnal.common.AbstractInvocationHandler] - An error occurred while calling method 'QueryablePersistenceService.query()' on 'org.openhab.persistence.jdbc.internal.JdbcPersistenceService@7e37e595': class [B cannot be cast to class java.lang.String ([B and java.lang.String are in module java.base of loader 'bootstrap')
13:27:45.858 [ERROR] [tence.internal.PersistenceManagerImpl] - Exception occurred while querying persistence service 'jdbc': class [B cannot be cast to class java.lang.String ([B and java.lang.String are in module java.base of loader 'bootstrap')
13:27:46.148 [ERROR] [rnal.common.AbstractInvocationHandler] - An error occurred while calling method 'QueryablePersistenceService.query()' on 'org.openhab.persistence.jdbc.internal.JdbcPersistenceService@7e37e595': class [B cannot be cast to class java.lang.String ([B and java.lang.String are in module java.base of loader 'bootstrap')
13:27:46.151 [ERROR] [tence.internal.PersistenceManagerImpl] - Exception occurred while querying persistence service 'jdbc': class [B cannot be cast to class java.lang.String ([B and java.lang.String are in module java.base of loader 'bootstrap')

JensH · April 5, 2021, 11:55am

O.K. not 100% sure, but I had this too some earlier and searched the forum, if remember right, its a caching problem. So stop OH3, clear cache and restart.

Can it be, that this errors only come at starting OH3?

OH 2.x has for restoring allways rrd4j active. while OH3 rrd4j not the default persistence. So you should have a look on your persistence rules and default database too.

ohb1138 · April 5, 2021, 12:06pm

Thank you for your support.

I am a bit cautious with clearing the cache because when removing everything from the cache directory it can also cause more problems … tmp directory should not be the problem but I am not happy deleting everything in the cache directory … but I will consider this …

Yes, it seems so … I have not let run the instance for more than 20 minutes as I am also debugging some problems with rules but it seems so.

I have a jdbc.persist:

Strategies {
    every5Minutes : "0 0/5 * * * ?"
    default = everyChange, restoreOnStartup
}

Items {
    * : strategy = everyChange, every5Minutes, restoreOnStartup
}

and in my addons.cfg I have JDBC-MariabDB as persistence. I don’t know where to check what is the default persistence:

# A comma-separated list of persistence services to install (e.g. "rrd4j,jpa")
persistence = jdbc-mariadb

JensH · April 5, 2021, 12:12pm

Openhab has a function for it.

openhab-cli clean-cache

If switching between OH 2.5 and 3 during migration I would just ignore those errors, because they dont have a negative effect, just some items dont have a state until refreshed / start fully done

ohb1138 · April 5, 2021, 12:15pm

Thank you for this explanation … I found nothing like this in the other topics … I get used to not become scared about warnings but ERRORS are normally better solved … I will stay calm and see if this disappears over time.
But I am wondering that these errors should not occur anymore once all items finally received a status, right?

I will try that but I am running OH in a docker container … not sure if the command exists there as well … I will find out

Thanks for your support on easter monday !!!

ohb1138 · April 5, 2021, 12:30pm

@JensH,

I have just deleted the directories 231 and 249 under ./cache/org.eclipse.osgi/as they are related to the JDBC persistence with these bundle ids:

openhab> bundle:list | grep JDBC
231 │ Active    │  80 │ 1.4.6.0                 │ MariaDB JDBC Client
249 │ Active    │  80 │ 3.1.0.M3                │ openHAB Add-ons :: Bundles :: Persistence Service :: JDBC
openhab>

And after restarting OH there are no more errors

!!! YOU MADE MY DAY !!!

Thanks a million!!!

JensH · April 5, 2021, 12:40pm

All fine. Got a lot of support here and glad to give some back!

Happy Easter

Maybe the garbage collection for jdbc is interesting for you too. I rewrote for openhab3, not sure if the same for Maria DB as for mysql: just search for sql maintenance