Persistence problem - Item cannot be cast from string to int

schossel · November 18, 2022, 6:43pm

Hi,
On OH3 restart I’m getting the following 2 messages 6 times. I think that it will tell me that persistence tries to get historic item data which is a string in the DB but the item now seems to be integer!?
I can’t remember if I changed items from string to int (number?) or when. I can access the database and could delete the wrong values but I don’t know how to find them!?
Can anybody help me how I can figure out which items in the database causing the error?

2022-11-18 17:03:48.203 [ERROR] [nal.common.AbstractInvocationHandler] - An error occurred while calling method 'QueryablePersistenceService.query()' on 'org.openhab.persistence.jdbc.internal.JdbcPersistenceService@e1799e': class java.lang.String cannot be cast to class java.lang.Integer (java.lang.String and java.lang.Integer are in module java.base of loader 'bootstrap')
java.lang.ClassCastException: class java.lang.String cannot be cast to class java.lang.Integer (java.lang.String and java.lang.Integer are in module java.base of loader 'bootstrap')
	at org.openhab.persistence.jdbc.db.JdbcBaseDAO.objectAsInteger(JdbcBaseDAO.java:544) ~[?:?]
	at org.openhab.persistence.jdbc.db.JdbcBaseDAO.getState(JdbcBaseDAO.java:514) ~[?:?]
	at org.openhab.persistence.jdbc.db.JdbcBaseDAO.lambda$0(JdbcBaseDAO.java:348) ~[?:?]
	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:349) ~[?:?]
	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.GeneratedMethodAccessor133.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) [?:?]

2022-11-18 17:03:48.235 [ERROR] [ence.internal.PersistenceManagerImpl] - Exception occurred while querying persistence service 'jdbc': class java.lang.String cannot be cast to class java.lang.Integer (java.lang.String and java.lang.Integer are in module java.base of loader 'bootstrap')
java.lang.ClassCastException: class java.lang.String cannot be cast to class java.lang.Integer (java.lang.String and java.lang.Integer are in module java.base of loader 'bootstrap')
	at org.openhab.persistence.jdbc.db.JdbcBaseDAO.objectAsInteger(JdbcBaseDAO.java:544) ~[?:?]
	at org.openhab.persistence.jdbc.db.JdbcBaseDAO.getState(JdbcBaseDAO.java:514) ~[?:?]
	at org.openhab.persistence.jdbc.db.JdbcBaseDAO.lambda$0(JdbcBaseDAO.java:348) ~[?:?]
	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:349) ~[?:?]
	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.GeneratedMethodAccessor133.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) [?:?]

rossko57 · November 18, 2022, 8:19pm

Seems reasonable. Note that ‘numeric’ target Items includes Dimmer, Rollershutter types as well. Not so sure if Color or Location types are also possibilities, I think probably not, Like DateTimes.

First job would be to review which Items you have set up your JDBC to restore, for a starting list.

You might also look for further complaints during normal use, even if they do not identify an Item you might be able to deduce what is being persisted at that moment.

laursen · November 19, 2022, 10:41am

If you enable TRACE log level, you should be able to see which item is causing the problem:
ssh -p 8101 openhab@localhost 'log:set trace org.openhab.persistence.jdbc'

I have created this issue for some planned work on a command to help identify such issues:

github.com/openhab/openhab-addons

[jdbc] Provide console command for performing schema integrity check/repair

opened 03:21PM - 13 Nov 22 UTC

jlaur

enhancement

Over time the column types in a database might no longer match the types used wh…en creating new tables: - JDBC persistence service has changed. Examples: #10542, #9394 - JDBC drivers might have changed. - Database defaults might have changed (for example signed vs. unsigned when not specified). - Item datatype might have changed. Having a tool to identify and possibly repair such inconsistencies would be helpful. ### Notes Checking columns: ```sql SELECT * FROM information_schema.columns WHERE table_schema = 'test' AND table_name = 'Item0001' ``` This is a follow-up to #13661.

schossel · November 19, 2022, 7:35pm

HOLY S!!T

I was about to go through A LOT of tables to search for the error. I have the default logging for everything activated.
This helped SO much!!!
It was 3 colorpicker items with type color

Color colorpicker_Buero "Colorpicker Farbe Buero"

and 1 item I created myself which counts the days until next holiday.

Number next_feiertag_tage  "Feiertag Tage [%s]"

I checked the database and none of them had something else except numbers in them. I don’t know why it said “can’t cast str to int”.
I emptied the 4 tables and restarted. No errors since then!
I set the logging back to default with

 ssh -p 8101 openhab@localhost 'log:set default org.openhab.persistence.jdbc'

Thank you for the hint!

I have just 1 further question. Before, when starting, I saw all persistence items getting their values in frontail but since I set the log to default I don’t see it anymore. What type do I have to change it to, to see them again?

laursen · November 23, 2022, 10:42pm

I believe INFO should be sufficient, but otherwise you can try DEBUG and see the level of those messages you are interested in.