OH3: Storing Images with jdbc persistence + MySQL

Hi,
I’m trying to store the images of my Doorbird doorbell in Mysql.
Items file looks like this:
Image Doorbell_PressedImage "Doorbell Pressed Image [%s]" (persistGroup) { channel="doorbird:d101:doorbell2:doorbellImage" }

Whenever a new image is created it is shown correctly in openhab.
But looking into the database, only the following string is stored:
raw type (image/jpeg): 54554 bytes

I already changed the item table holding the images to BLOB and also altered the jdbc.cfg to have this reflected in openhab:

sqltype.IMAGE=BLOB

What do I need to do so that the binary file is stored instead of just a string with some meta information?
By the way, this also leads to a problem when an Image item needs to be restored when openhab starts:

2021-08-19 20:13:16.528 [ERROR] [nal.common.AbstractInvocationHandler] - An error occurred while calling method 'QueryablePersistenceService.query()' on 'org.openhab.persistence.jdbc.internal.JdbcPersistenceService@5537295e': Invalid data URI syntax for argument raw type (image/jpeg): 52940 bytes
java.lang.IllegalArgumentException: Invalid data URI syntax for argument raw type (image/jpeg): 52940 bytes
        at org.openhab.core.library.types.RawType.valueOf(RawType.java:59) ~[bundleFile:?]
        at org.openhab.persistence.jdbc.db.JdbcBaseDAO.getState(JdbcBaseDAO.java:512) ~[?:?]
        at org.openhab.persistence.jdbc.db.JdbcBaseDAO.lambda$0(JdbcBaseDAO.java:344) ~[?:?]
        at java.util.stream.ReferencePipeline$3$1.accept(ReferencePipeline.java:195) ~[?:?]
        at java.util.ArrayList$ArrayListSpliterator.forEachRemaining(ArrayList.java:1655) ~[?:?]
        at java.util.stream.AbstractPipeline.copyInto(AbstractPipeline.java:484) ~[?:?]
        at java.util.stream.AbstractPipeline.wrapAndCopyInto(AbstractPipeline.java:474) ~[?:?]
        at java.util.stream.ReduceOps$ReduceOp.evaluateSequential(ReduceOps.java:913) ~[?:?]
        at java.util.stream.AbstractPipeline.evaluate(AbstractPipeline.java:234) ~[?:?]
        at java.util.stream.ReferencePipeline.collect(ReferencePipeline.java:578) ~[?:?]
        at org.openhab.persistence.jdbc.db.JdbcBaseDAO.doGetHistItemFilterQuery(JdbcBaseDAO.java:345) ~[?:?]
        at org.openhab.persistence.jdbc.internal.JdbcMapper.getHistItemFilterQuery(JdbcMapper.java:169) ~[?:?]
        at org.openhab.persistence.jdbc.internal.JdbcPersistenceService.query(JdbcPersistenceService.java:205) ~[?:?]
        at jdk.internal.reflect.GeneratedMethodAccessor193.invoke(Unknown Source) ~[?:?]
        at jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[?:?]
        at java.lang.reflect.Method.invoke(Method.java:566) ~[?:?]
        at org.openhab.core.internal.common.AbstractInvocationHandler.invokeDirect(AbstractInvocationHandler.java:154) [bundleFile:?]
        at org.openhab.core.internal.common.Invocation.call(Invocation.java:52) [bundleFile:?]
        at java.util.concurrent.FutureTask.run(FutureTask.java:264) [?:?]
        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:829) [?:?]
2021-08-19 20:13:16.532 [ERROR] [ence.internal.PersistenceManagerImpl] - Exception occurred while querying persistence service 'jdbc': Invalid data URI syntax for argument raw type (image/jpeg): 52940 bytes
java.lang.IllegalArgumentException: Invalid data URI syntax for argument raw type (image/jpeg): 52940 bytes
        at org.openhab.core.library.types.RawType.valueOf(RawType.java:59) ~[bundleFile:?]
        at org.openhab.persistence.jdbc.db.JdbcBaseDAO.getState(JdbcBaseDAO.java:512) ~[?:?]
        at org.openhab.persistence.jdbc.db.JdbcBaseDAO.lambda$0(JdbcBaseDAO.java:344) ~[?:?]
        at java.util.stream.ReferencePipeline$3$1.accept(ReferencePipeline.java:195) ~[?:?]
        at java.util.ArrayList$ArrayListSpliterator.forEachRemaining(ArrayList.java:1655) ~[?:?]
        at java.util.stream.AbstractPipeline.copyInto(AbstractPipeline.java:484) ~[?:?]
        at java.util.stream.AbstractPipeline.wrapAndCopyInto(AbstractPipeline.java:474) ~[?:?]
        at java.util.stream.ReduceOps$ReduceOp.evaluateSequential(ReduceOps.java:913) ~[?:?]
        at java.util.stream.AbstractPipeline.evaluate(AbstractPipeline.java:234) ~[?:?]
        at java.util.stream.ReferencePipeline.collect(ReferencePipeline.java:578) ~[?:?]
        at org.openhab.persistence.jdbc.db.JdbcBaseDAO.doGetHistItemFilterQuery(JdbcBaseDAO.java:345) ~[?:?]
        at org.openhab.persistence.jdbc.internal.JdbcMapper.getHistItemFilterQuery(JdbcMapper.java:169) ~[?:?]
        at org.openhab.persistence.jdbc.internal.JdbcPersistenceService.query(JdbcPersistenceService.java:205) ~[?:?]
        at jdk.internal.reflect.GeneratedMethodAccessor193.invoke(Unknown Source) ~[?:?]
        at jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[?:?]
        at java.lang.reflect.Method.invoke(Method.java:566) ~[?:?]
        at org.openhab.core.internal.common.AbstractInvocationHandler.invokeDirect(AbstractInvocationHandler.java:154) [bundleFile:?]
        at org.openhab.core.internal.common.Invocation.call(Invocation.java:52) [bundleFile:?]
        at java.util.concurrent.FutureTask.run(FutureTask.java:264) [?:?]
        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:829) [?:?]

I’m running OH3.1 on U18.04.

Any help appreciated!

Why do you want to store binary data in a database anyways! :wink:
AFAIK the persistence service in openHAB is text-only, so you have to either write a own script/rule to write images to a folder or MAM system or use the built-in DoorBird Cloud (Pro version also saves video).

or use a NAS and integrate DoorBird in your NAS - I run Synology and DoorBird can be integrated in SurveillanceStation with both images and videos.

Thanks for your feedback.
After looking at https://community.openhab.org/t/oh3-issue-jdbc-mysql-table-creation-for-image-item/ I was under the impression that OH stores also the pictures itself and not only a representation.
If that is the case how to restore images for i.e. habpanel after a restart of openhab then? I find it odd that you have to fiddle with saving and restoring yourself.
DoorBird cloud is no option for me, I don’t want to store personal data there.

If you choose the right type of field, and right persistence rules, in my case OH restores pictures at startup, same as values. :slight_smile:

restoreOnStartup is the key

1 Like

I already have all items restored on startup.
My items file looks as follow:

Image Doorbell_PressedImage "Doorbell Pressed Image [%s]" (persistGroup) { channel="doorbird:d101:doorbell2:doorbellImage" }

And the persistence rules like that:

persistGroup* : strategy = everyChange, everyDay, restoreOnStartup

But still the Image item is not stored as binary (even though I changed the database field to *BLOB). It’s always just the short String representation.
And this of course fails with the above mentioned exception if Openhab wants to restore this into the Image item.

What do you mean by “right type of field”?

Just activated the debugging for the jdbc-persistence.

2021-08-22 20:37:16.975 [DEBUG] [persistence.jdbc.internal.JdbcMapper] - JDBC::storeItemValue: item=Doorbell_Image (Type=ImageItem, State=raw type (image/jpeg): 54256 bytes, Label=Doorbell Image, Category=null, Groups=[persistGroup])
2021-08-22 20:37:16.975 [DEBUG] [.openhab.persistence.jdbc.dto.ItemVO] - JDBC:ItemVO tableName=Item384; newTableName=null;
2021-08-22 20:37:16.975 [DEBUG] [nhab.persistence.jdbc.db.JdbcBaseDAO] - JDBC::getItemType: Try to use ItemType IMAGEITEM for Item Doorbell_Image
2021-08-22 20:37:16.975 [DEBUG] [nhab.persistence.jdbc.db.JdbcBaseDAO] - JDBC::storeItemValueProvider: item 'Doorbell_Image' as Type 'IMAGEITEM' in 'Item384' with state 'raw type (image/jpeg): 54256 bytes'
2021-08-22 20:37:16.975 [DEBUG] [nhab.persistence.jdbc.db.JdbcBaseDAO] - JDBC::storeItemValueProvider: getState: 'raw type (image/jpeg): 54256 bytes'
2021-08-22 20:37:16.976 [DEBUG] [.openhab.persistence.jdbc.dto.ItemVO] - JDBC:ItemVO setValueTypes dbType=BLOB; javaType=class java.lang.String;
2021-08-22 20:37:16.976 [DEBUG] [nhab.persistence.jdbc.db.JdbcBaseDAO] - JDBC::storeItemValueProvider: other: item.getState().toString(): 'raw type (image/jpeg): 54256 bytes'
2021-08-22 20:37:16.976 [DEBUG] [nhab.persistence.jdbc.db.JdbcBaseDAO] - JDBC::doStoreItemValue sql=INSERT INTO Item384 (TIME, VALUE) VALUES( NOW(3), ? ) ON DUPLICATE KEY UPDATE VALUE= ? value='raw type (image/jpeg): 54256 bytes'
2021-08-22 20:37:16.979 [DEBUG] [jdbc.internal.JdbcPersistenceService] - JDBC: Stored item 'Doorbell_Image' as 'raw type (image/jpeg): 54256 bytes' in SQL database at Sun Aug 22 20:37:16 CEST 2021 in 3 ms.

Looks like the persistence addon gets the string representation from the ImageItem.

JDBC::storeItemValueProvider: getState: 'raw type (image/jpeg): 54256 bytes'

Same happens if I add a log statements for this item in a rule:

logInfo("Doorbell", "Doorbell image changed, value: " + Doorbell_Image.state)

produces this:

2021-08-22 20:43:02.264 [INFO ] [g.openhab.core.model.script.Doorbell] - Doorbell image changed, value: raw type (image/jpeg): 51930 bytes

The image (as base64 encoded string) is only returned if I use Doorbell_Image.state.toFullString.
So it looks like the persistence addon is incompatible with the ImageItem.

I added a bugreport for this:

I have nearly same definition for openweathermapicon, bit without [%s] in label and persistence works fine (JDBC mysql).

If looking in phpmyadmin, the value ist shown as [ BLOB - 32] and Image ist stored binary

Hello Frank,

as you mentioned in your bugreport, your Image was to big for BLOB and MEDIUMBLOB worked. so please close bugreport an mark this thread as solution.

Hi Jens,
no the MEDIUMBLOB change was only because my image was too big (exceeded 64k).
The main problem was, that Image.state.toString returns just meta information about the image and this method is used inside of JDBCBaseDAO.storeItemValueProvider:

               } else {// fall back to String
                    vo.setValueTypes(it, java.lang.String.class);
                    logger.warn("JDBC::storeItemValueProvider: item.getState().toString(): '{}'", convertedState);
                    vo.setValue(convertedState.toString());
                }

Therefore I needed to change this method to use Image.state.toFullString.
From my point of view it’s still a bug. With this change all is working. If I revert the change and just leave the MEDIUMBLOB, still just the meta info is stored (ie. raw type (image/jpeg): 55033 bytes).

Maybe our settings differ somewhere. I migrated from OH 2.5 Mysql persistence to OH 3.0 and later 3.1 JDBC persistence. Maybe that’s the difference?

Could you enable DEBUG logging for the jdbc bundle in OH console and see what the output is when an Image value is stored? Would be interesting for me to see the difference.

log:set DEBUG org.openhab.persistence.jdbc

Thanks
Frank

Just digged a bit deeper in the source of OpenHAB.
The image state for me is a RawType (openhab-core/RawType.java at main · openhab/openhab-core · GitHub).
Rawtype.toString() just returns meta data of the item:

    public String toString() {
        return String.format("raw type (%s): %d bytes", mimeType, bytes.length);
    }

Only the toFullString represents its full state as base64 encoded string:

    @Override
    public String toFullString() {
        return String.format("data:%s;base64,%s", mimeType, Base64.getEncoder().encodeToString(bytes));
    }

If the Rawtype will be restored, it expects the String which was returned by toFullString(), otherwise it’s throwing an Exception.

    public static RawType valueOf(String value) {
        int idx, idx2;
        if (value.isEmpty()) {
            throw new IllegalArgumentException("Argument must not be blank");
        } else if (!value.startsWith("data:") || ((idx = value.indexOf(",")) < 0)) {
            throw new IllegalArgumentException("Invalid data URI syntax for argument " + value);
        } else if ((idx2 = value.indexOf(";")) <= 5) {
            throw new IllegalArgumentException("Missing MIME type in argument " + value);
        }
        return new RawType(Base64.getDecoder().decode(value.substring(idx + 1)), value.substring(5, idx2));
    }

Knowing that the JDBCBaseDao uses toString, this can never work.
So there needs to be something different on your side.

Best
Frank

Hello foobar,

sorry, my mistake. I don’t have this problem, because at every start Image is directly loaded again, so no need for persistence. Never looked so deep for it.

For your case: Directly saving the Image:

In JDBCBaseDAO.storeItemValueProvider you looked at the wrong place in your post.

In the procedure storeItemValueProvider(Item item, ItemVO vo) there is no

case “IMAGE”: with the right conversion to store an Image.

So its not a bug, it’s a missing feature, because at the moment JDBC.mysql has got no assistance for saving images.

And the default allways falls back to a normal string without a warning.

So if there would be a case for IMAGE (maybe with the raw-Storage-Routine you found) it could work. Not familiar with java enough to do it.

Ok, this explains why it was working on your end.
You can argue whether it’s a bug or a missing feature.
If its unsupported I would have expected some note about that you cannot use Images for persistence or something.
Currently, if you are using images, you end up in having Exceptions in the log which from my point not at all should happen.
With the small change posted in the bugreport, this is possible, because the RawType itself can deserialize its state to a base64 encoded format and later restore it. As mentioned I tested it and it is working.