[jdbc.mysql] Persistence works not safely with images

[JensHoRi]

Field-Length for storage of items from TYPE IMAGE is actual VARCHAR, not long enough if for example Datanbase is UTF-8 or Image is to big.

So Type of value-Field should be BLOB.

Tested successfully with adding sqltype.IMAGE = BLOB in jdbc.cfg

https://community.openhab.org/t/oh3-issue-jdbc-mysql-table-creation-for-image-item/113474

Option a (quick-win) Change

openhab-addons/bundles/org.openhab.persistence.jdbc/src/main/resources/OH-INF/config/

adding sqltype.IMAGE = BLOB

Option b) Change default for item type IMAGE (didn't found where they are defined)

[cweitkamp]

@mhilbush Do you like to jump in?

[mhilbush]

I'm not sure what to do about this. While I'm no database expert, there are a few things worth considering.

• There are 4 different BLOB types in MySql and MariaDB. I'm not sure about the other supported DBs. As it's not known what size the user wants, setting one blob type as the default would be a guess.

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

• The disk storage implications could be an issue, especially for users on smallish platforms. My opinion is that it's generally not a great idea to be persisting images in openHAB. OTOH, using the BLOB type would be no worse than what's there today, since the size of BLOB is about the same as the current VARCHAR implementation.

• The primary reason for my change in #9334 was to avoid the warning messages that were being generated every time OH tried to persist an Image or Player item into MySql. The persistence bundle already was storing images as VARCHAR, it just complained in the log every time it stored one.

So, at a minimum, it might make sense to discuss this in the documentation, especially since the blob support will vary across db platforms. We also could describe how the user can set the sqltype.IMAGE config parameter to suit their specific need. In fact, I missed updating the README here when I did my PR.

WDYT?

[JensHoRi]

Sorry for late reply. As default BLOB and in Documentation a hint for larger Images would be great. In my case BLOB is able to save much bigger images as VARCHAR can, because VARCHAR depends on charsets in system / mysql.

[foobar26]

Just want to add that the image type - even if you change the db column type to BLOB - currently isn't stored correctly (see #11148). So even small images where the current default db column type VARCHAR would be sufficient cannot be stored/restored correctly.

[lsiepel]

If i understand it correctly, the default db type for images should not be changed. Maybe a documentation can be made, any suggestion of the text? Maybe a paragraph in https://www.openhab.org/addons/persistence/jdbc/#technical-notes would be good.