# OH3: Issue jdbc mysql Table Creation for Image Item **URL:** \ **Category:** Persistence Services\ **Tags:** binding, jdbc, oh3\ **Created:** [January 8, 2021, 3:49pm UTC](https://community.openhab.org/t/oh3-issue-jdbc-mysql-table-creation-for-image-item/113474 "2021-01-08T15:49:03Z")\ **Posts on this page:** 20\ **Page:** 1
**Author:** ![JensH](https://community.openhab.org/user_avatar/community.openhab.org/jensh/32/33433_2.png) [@JensH](https://community.openhab.org/u/JensH)\ **Post date:** [January 8, 2021, 3:49pm UTC](https://community.openhab.org/t/oh3-issue-jdbc-mysql-table-creation-for-image-item/113474/1 "2021-01-08T15:49:03Z")
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. ---
**Author:** ![mhilbush](https://community.openhab.org/user_avatar/community.openhab.org/mhilbush/32/56108_2.png) [@mhilbush](https://community.openhab.org/u/mhilbush)\ **Post date:** [January 8, 2021, 5:05pm UTC](https://community.openhab.org/t/oh3-issue-jdbc-mysql-table-creation-for-image-item/113474/2 "2021-01-08T17:05:17Z")
> [@JensH](#): > > VARCHAR(65500), 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](https://github.com/openhab/openhab-addons/blob/main/bundles/org.openhab.persistence.jdbc/src/main/java/org/openhab/persistence/jdbc/db/JdbcMariadbDAO.java#L51). 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. ---
**Author:** ![JensH](https://community.openhab.org/user_avatar/community.openhab.org/jensh/32/33433_2.png) [@JensH](https://community.openhab.org/u/JensH)\ **Post date:** [January 8, 2021, 5:25pm UTC](https://community.openhab.org/t/oh3-issue-jdbc-mysql-table-creation-for-image-item/113474/3 "2021-01-08T17:25:18Z")
It’s MySQL - Server: Localhost via UNIX socket - Server-Typ: MySQL - Server-Verbindung: SSL wird nicht verwendet [![Dokumentation](http://intern.holhome.de/phpmyadmin/themes/dot.gif "Dokumentation")](http://intern.holhome.de/phpmyadmin/doc/html/setup.html#ssl) - Server-Version: 8.0.22-0ubuntu0.20.04.3 - (Ubuntu) - Protokoll-Version: 10 - Benutzer: xxxxxxx@localhost - Server-Zeichensatz: UTF-8 Unicode (utf8mb4) ---
**Author:** ![mhilbush](https://community.openhab.org/user_avatar/community.openhab.org/mhilbush/32/56108_2.png) [@mhilbush](https://community.openhab.org/u/mhilbush)\ **Post date:** [January 8, 2021, 8:49pm UTC](https://community.openhab.org/t/oh3-issue-jdbc-mysql-table-creation-for-image-item/113474/4 "2021-01-08T20:49:41Z")
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: > [@JensH](#): > > Server-Zeichensatz: UTF-8 Unicode (utf8mb4) My db is using latin1, which I think is the default in 5.7. Not sure about 8.0. ![mysqlcharset](https://community-openhab-org.s3.dualstack.eu-central-1.amazonaws.com/original/3X/c/1/c1b55e7aaff7313adee58f8c19e6632b362a173a.jpeg) ---
**Author:** ![mhilbush](https://community.openhab.org/user_avatar/community.openhab.org/mhilbush/32/56108_2.png) [@mhilbush](https://community.openhab.org/u/mhilbush)\ **Post date:** [January 8, 2021, 9:16pm UTC](https://community.openhab.org/t/oh3-issue-jdbc-mysql-table-creation-for-image-item/113474/5 "2021-01-08T21:16:25Z")
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). ```auto 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. ---
**Author:** ![JensH](https://community.openhab.org/user_avatar/community.openhab.org/jensh/32/33433_2.png) [@JensH](https://community.openhab.org/u/JensH)\ **Post date:** [January 8, 2021, 11:50pm UTC](https://community.openhab.org/t/oh3-issue-jdbc-mysql-table-creation-for-image-item/113474/6 "2021-01-08T23:50:38Z")
> [@mhilbush](#): > > In your `jdbc.cfg`, try adding this line, then restart the bundle (or restart openHAB). 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. ---
**Author:** ![JensH](https://community.openhab.org/user_avatar/community.openhab.org/jensh/32/33433_2.png) [@JensH](https://community.openhab.org/u/JensH)\ **Post date:** [January 14, 2021, 11:21pm UTC](https://community.openhab.org/t/oh3-issue-jdbc-mysql-table-creation-for-image-item/113474/7 "2021-01-14T23:21:16Z")
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. ---
**Author:** ![ohb1138](https://community.openhab.org/letter_avatar_proxy/v4/letter/o/9f8e36/32.png) [@ohb1138](https://community.openhab.org/u/ohb1138)\ **Post date:** [April 5, 2021, 8:18am UTC](https://community.openhab.org/t/oh3-issue-jdbc-mysql-table-creation-for-image-item/113474/8 "2021-04-05T08:18:50Z")
@JensH Hi Jens, any solution found. I am in my first upgrade approach and get the same errors … ---
**Author:** ![JensH](https://community.openhab.org/user_avatar/community.openhab.org/jensh/32/33433_2.png) [@JensH](https://community.openhab.org/u/JensH)\ **Post date:** [April 5, 2021, 9:19am UTC](https://community.openhab.org/t/oh3-issue-jdbc-mysql-table-creation-for-image-item/113474/9 "2021-04-05T09:19:43Z")
> [@mhilbush](#): > > In your `jdbc.cfg`, try adding this line, then restart the bundle (or restart openHAB). > > ```auto > sqltype.IMAGE=VARCHAR(21800) > > ``` Just do like this, but with BLOB: ```auto sqltype.IMAGE=BLOB ``` Or if your images bigger with Other BLOB-types ---
**Author:** ![ohb1138](https://community.openhab.org/letter_avatar_proxy/v4/letter/o/9f8e36/32.png) [@ohb1138](https://community.openhab.org/u/ohb1138)\ **Post date:** [April 5, 2021, 9:32am UTC](https://community.openhab.org/t/oh3-issue-jdbc-mysql-table-creation-for-image-item/113474/10 "2021-04-05T09:32:54Z")
THANK YOU! I will try this! ---
**Author:** ![JensH](https://community.openhab.org/user_avatar/community.openhab.org/jensh/32/33433_2.png) [@JensH](https://community.openhab.org/u/JensH)\ **Post date:** [April 5, 2021, 10:05am UTC](https://community.openhab.org/t/oh3-issue-jdbc-mysql-table-creation-for-image-item/113474/11 "2021-04-05T10:05:13Z")
Just searched out from github-bug to this: TINYBLOB (255 B) BLOB (64 KB) MEDIUMBLOB (16 MB) LONGBLOB (4 GB) ---
**Author:** ![ohb1138](https://community.openhab.org/letter_avatar_proxy/v4/letter/o/9f8e36/32.png) [@ohb1138](https://community.openhab.org/u/ohb1138)\ **Post date:** [April 5, 2021, 10:18am UTC](https://community.openhab.org/t/oh3-issue-jdbc-mysql-table-creation-for-image-item/113474/12 "2021-04-05T10:18:37Z")
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! ---
**Author:** ![JensH](https://community.openhab.org/user_avatar/community.openhab.org/jensh/32/33433_2.png) [@JensH](https://community.openhab.org/u/JensH)\ **Post date:** [April 5, 2021, 11:21am UTC](https://community.openhab.org/t/oh3-issue-jdbc-mysql-table-creation-for-image-item/113474/13 "2021-04-05T11:21:05Z")
What kind of errors? ---
**Author:** ![ohb1138](https://community.openhab.org/letter_avatar_proxy/v4/letter/o/9f8e36/32.png) [@ohb1138](https://community.openhab.org/u/ohb1138)\ **Post date:** [April 5, 2021, 11:46am UTC](https://community.openhab.org/t/oh3-issue-jdbc-mysql-table-creation-for-image-item/113474/14 "2021-04-05T11:46:01Z")
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 … ```yaml 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') ``` ---
**Author:** ![JensH](https://community.openhab.org/user_avatar/community.openhab.org/jensh/32/33433_2.png) [@JensH](https://community.openhab.org/u/JensH)\ **Post date:** [April 5, 2021, 11:55am UTC](https://community.openhab.org/t/oh3-issue-jdbc-mysql-table-creation-for-image-item/113474/15 "2021-04-05T11:55:20Z")
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. ---
**Author:** ![ohb1138](https://community.openhab.org/letter_avatar_proxy/v4/letter/o/9f8e36/32.png) [@ohb1138](https://community.openhab.org/u/ohb1138)\ **Post date:** [April 5, 2021, 12:06pm UTC](https://community.openhab.org/t/oh3-issue-jdbc-mysql-table-creation-for-image-item/113474/16 "2021-04-05T12:06:21Z")
Thank you for your support. > [@JensH](#): > > So stop OH3, clear cache and restart. 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 … > [@JensH](#): > > Can it be, that this errors only come at starting OH3? 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. > [@JensH](#): > > 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. I have a `jdbc.persist`: ```yaml 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: ```yaml # A comma-separated list of persistence services to install (e.g. "rrd4j,jpa") persistence = jdbc-mariadb ``` ---
**Author:** ![JensH](https://community.openhab.org/user_avatar/community.openhab.org/jensh/32/33433_2.png) [@JensH](https://community.openhab.org/u/JensH)\ **Post date:** [April 5, 2021, 12:12pm UTC](https://community.openhab.org/t/oh3-issue-jdbc-mysql-table-creation-for-image-item/113474/17 "2021-04-05T12:12:19Z")
> [@ohb1138](#): > > 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 … Openhab has a function for it. ```auto 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 ---
**Author:** ![ohb1138](https://community.openhab.org/letter_avatar_proxy/v4/letter/o/9f8e36/32.png) [@ohb1138](https://community.openhab.org/u/ohb1138)\ **Post date:** [April 5, 2021, 12:15pm UTC](https://community.openhab.org/t/oh3-issue-jdbc-mysql-table-creation-for-image-item/113474/18 "2021-04-05T12:15:53Z")
> [@JensH](#): > > 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. 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? > [@JensH](#): > > Openhab has a function for it. > > ```auto > openhab-cli clean-cache > > ``` 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 !!! ---
**Author:** ![ohb1138](https://community.openhab.org/letter_avatar_proxy/v4/letter/o/9f8e36/32.png) [@ohb1138](https://community.openhab.org/u/ohb1138)\ **Post date:** [April 5, 2021, 12:30pm UTC](https://community.openhab.org/t/oh3-issue-jdbc-mysql-table-creation-for-image-item/113474/19 "2021-04-05T12:30:50Z")
@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: ```yaml 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!!! ---
**Author:** ![JensH](https://community.openhab.org/user_avatar/community.openhab.org/jensh/32/33433_2.png) [@JensH](https://community.openhab.org/u/JensH)\ **Post date:** [April 5, 2021, 12:40pm UTC](https://community.openhab.org/t/oh3-issue-jdbc-mysql-table-creation-for-image-item/113474/20 "2021-04-05T12:40:14Z")
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 [Next page](https://community.openhab.org/t/oh3-issue-jdbc-mysql-table-creation-for-image-item/113474.md?page=2)