Jdbc persistence not working Error in SQL query!; (conn=1789) Data too long for column 'value' at row 1 Query: INSERT INTO item0024

Hi Guys,

Looks like that I found a new bug regarding the jdbc persitence and the official 4.2 oakley speedtest add-on.
The persistence tries to save the picture of the speedtest result into the db, which fails consistently with
org.openhab.persistence.jdbc.internal.exceptions.JdbcSQLException: Error in SQL query!!!; (conn=1789) Data too long for column ‘value’ at row 1 Query: INSERT INTO item0024 (time, value) VALUES( NOW(3), ? ) ON DUPLICATE KEY UPDATE VALUE= ? Parameters: [data:image/png;base64,

Regarding the persistence why can´t we offer a checkbox (store values in persistence) for each thing in the configuration menu?
Would be so nice instead of defining complicated filter rules, which do not work with exlusions?
That would be so nice to circumvent such problems and to minimize data storage, and so on…

Just to be certain, the type of this Item didn’t change, correct? It’s always been an Image Item?

Because Items are saved in persistence, not Things.

You can create an opt-in persistence config if that’s what you want using Design Pattern: Group Based Persistence which will let you configure which Items get persisted at the Item through Group membership, if that’s the way you want to configure persistence instead of the centralized persistence config.

I’m not sure what filter rules you have in mind here. Persistence when there is no configuration has a default behavior which is usually save every Item on every Change with restoreOnStartup.

If that doesn’t work for you, then you need to override the default behavior through configuration. By simply creating a persistence configuration you can exclude all Items from being saved to persistence and then add them one by one or through Group membership.

You can already control on a per Item basis which Items get saved to persistence and how they are saved. It’s only if you use the default that it saves everything.

What isn’t supported is “everything but these Items” type configs. I’m pretty sure that’s been ansed for many times but it’s overly challenging to implement so no one has volunteered to implement that.

Its really a default configuration and the item did not chance. Yes.
I have seen that Thing with the persistence setup but that would be very complicated in my setup because i have so many items :disappointed:. But would it br possible at least to persist all items with a special tag or property assigned?

You could create a rule to do that. Use a generic trigger filtered on Item events (an advanced approach but one I know how to do if you decide to go this route) and call .persist on those Items with the tag. But that’s going to be as much work if not more to tag each Item and write a rule as it would be to add each Item to a Group and configure persistence to save the members of the Group (as the design pattern linked to above describes), and with that approach you don’t need the rule.

Ultimately, the policy of the maintainers, and there are good reasons for this policy, is that by default all Items are saved on every change with restore on restart. If that’s not what you want, you need to do some work to define what you want saved and how. Whether you use the persistence config to list the Items you do want to save and how, use Group membership, or tags and a rule, you still somehow must define which Items you want to save and how.