OH 4.0.1 MySQL No new items being created in DB

Add-ons Persistence Services
1

Hi,
My OH 4.0.1 on Debian 12 stoped creating new persisted items in the database when adding a new item. Tried clearing cache, deleting / adding items multiple times but no progress.
When re-installing the JDBC binding noticed the following error:

java.lang.ClassCastException: class java.sql.Timestamp cannot be cast to class java.lang.String (java.sql.Timestamp is in module java.sql of loader 'platform'; java.lang.String is in module java.base of loader 'bootstrap')
        at org.openhab.persistence.jdbc.internal.db.JdbcBaseDAO.objectAsState(JdbcBaseDAO.java:697) ~[?:?]
        at org.openhab.persistence.jdbc.internal.db.JdbcBaseDAO.lambda$0(JdbcBaseDAO.java:479) ~[?:?]
        at java.util.stream.ReferencePipeline$3$1.accept(ReferencePipeline.java:197) ~[?:?]
        at java.util.ArrayList$ArrayListSpliterator.forEachRemaining(ArrayList.java:1625) ~[?:?]
        at java.util.stream.AbstractPipeline.copyInto(AbstractPipeline.java:509) ~[?:?]
        at java.util.stream.AbstractPipeline.wrapAndCopyInto(AbstractPipeline.java:499) ~[?:?]
        at java.util.stream.ReduceOps$ReduceOp.evaluateSequential(ReduceOps.java:921) ~[?:?]
        at java.util.stream.AbstractPipeline.evaluate(AbstractPipeline.java:234) ~[?:?]
        at java.util.stream.ReferencePipeline.collect(ReferencePipeline.java:682) ~[?:?]
        at org.openhab.persistence.jdbc.internal.db.JdbcBaseDAO.doGetHistItemFilterQuery(JdbcBaseDAO.java:480) ~[?:?]
        at org.openhab.persistence.jdbc.internal.JdbcMapper.getHistItemFilterQuery(JdbcMapper.java:238) ~[?:?]
        at org.openhab.persistence.jdbc.internal.JdbcPersistenceService.query(JdbcPersistenceService.java:230) ~[?:?]
        at jdk.internal.reflect.GeneratedMethodAccessor29.invoke(Unknown Source) ~[?:?]
        at jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[?:?]
        at java.lang.reflect.Method.invoke(Method.java:568) ~[?:?]
        at org.openhab.core.internal.common.AbstractInvocationHandler.invokeDirect(AbstractInvocationHandler.java:147) ~[?:?]
        at org.openhab.core.internal.common.Invocation.call(Invocation.java:52) ~[?:?]
        at java.util.concurrent.FutureTask.run(FutureTask.java:264) ~[?:?]
        at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1136) ~[?:?]
        at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:635) ~[?:?]
        at java.lang.Thread.run(Thread.java:833) ~[?:?]

Deleted the item in OH and added again, no change. Did not delete the item in the DB due to new item issue.

The item is as follows:

String               SFTMasterLastClea "Last cleaning" <time>                                     (gMasterEA,PersistY) ["Setpoint","Duration"]        { stateDescription=" "[ pattern="%s" ]}

Updated to Debian 12 and OH 4.0.1 about 3 days ago with no issues. Created a new persisted item yesterday with no issue, is the 3569th item in the DB. Couple hours later run into the new persisted item creation issue. Any suggestions how to resolve this issue are most welcome.

2

Ps. Didn’t mention but updating and reading from database works fine.

Decided to restore the VM of 24 hours ago, added an item that did not exisit before and is not in the DB.

Number     CALMainOPowerTMoT "Power consumption total per month" <energy>         (gEqPowerM,PersistY) ["Measurement","Energy"] {stateDescription=" "[ pattern="%.1f kWh" ]}

OH is reporting the following:

2023-08-08 13:29:11.504 [DEBUG] [jdbc.internal.JdbcPersistenceService] - JDBC::query: item is CALMainOPowerTMoT
2023-08-08 13:29:11.504 [DEBUG] [jdbc.internal.JdbcPersistenceService] - JDBC::query: unable to find table for item with name: 'CALMainOPowerTMoT', no data in database.

It checks the DB and correctly finds there is no table but then does not proceed to create one.

3

After cleaning items file and countless restart it works again, no idea but all good.

4

It would be interesting if you could reproduce since this seems like a bug:
https://github.com/openhab/openhab-addons/blob/7ab04b20a16de9dd2b847b22597aa8403175b69f/bundles/org.openhab.persistence.jdbc/src/main/java/org/openhab/persistence/jdbc/internal/db/JdbcBaseDAO.java#L697

I’m wondering what kind of item might have triggered this?

5

Hi Jacob, While I don’t know how to reproduce, I did clean some items which also came up intermittend in the log while trying to fix the problem but those errors where there from some time before. Suspect that is the area you send in the code. There were about 10 items with the following issue:

  • UI Item was setup as string but MySQL table column was a timestamp
  • UI Item was setup as double but MySQL table column as TinyInt

Corrected the column in the DB manually and restarted. I must mention my DB is in use since the first OH 3 version and due to this and another issue been cleaning my 3000 Items and 1600 persisted the last couple of days. Not sure why they were out of sync but I did migrate from text file to UI as soon as it came out years ago. Still have to setup some comparison between the items and the DB to see if any other items have this issue but for now my startup log is clean. Hope this helps, let me know if I can do anything to assist.

6

I managed to reproduce it from a similar scenario. I have created an issue for this:
https://github.com/openhab/openhab-addons/issues/15433

And I’m currently working on some improvements - as minimum better error handling, so that it will be possible to see which item is causing such issue.

1 Like
7

Happy to hear you could reproduce and created an issue, knowing which item is causing the issue will be a great help. Much appriciated.

8

Hi Jacob, You probably know this but based on other issue where @J-N-K suggested using the openhab command in Karaf figured to mention this incase it helps somebody else doing some housekeeping:

openhab> openhab:jdbc schema check
Table                   Item               Issue
----------------------  -----------------  ----------------------------------------------------------------
guiguest1acloudco_1321  GUIGuest1ACloudCo  Column type 'TINYINT' expected, but is 'INT'
guiguest1ecloudco_1320  GUIGuest1ECloudCo  Column type 'TINYINT' expected, but is 'INT'
guiguest2acloudco_1325  GUIGuest2ACloudCo  Column type 'TINYINT' expected, but is 'INT'
guiguest2ecloudco_1324  GUIGuest2ECloudCo  Column type 'TINYINT' expected, but is 'INT'
guihallwaecloudco_2192  GUIHallwaECloudCo  Column type 'TINYINT' expected, but is 'INT'
guikitcheecloudco_1610  GUIKitcheECloudCo  Column type 'TINYINT' expected, but is 'INT'
guilaundrecloudco_1538  GUILaundrECloudCo  Column type 'TINYINT' expected, but is 'INT'
guilivinbecloudco_1692  GUILivinBECloudCo  Column type 'TINYINT' expected, but is 'INT'
sftlivinglastclea_2070  SFTLivingLastClea  Column type 'VARCHAR(21717)' expected, but is 'VARCHAR(127)'
sftmasterlastclea_0809  SFTMasterLastClea  Column type 'VARCHAR(21717)' expected, but is 'VARCHAR(127)'
openhab> openhab:jdbc schema fix
Fixed table 'guiguest1acloudco_1321' for item 'GUIGuest1ACloudCo'
Fixed table 'guiguest1ecloudco_1320' for item 'GUIGuest1ECloudCo'
Fixed table 'guiguest2acloudco_1325' for item 'GUIGuest2ACloudCo'
Fixed table 'guiguest2ecloudco_1324' for item 'GUIGuest2ECloudCo'
Fixed table 'guihallwaecloudco_2192' for item 'GUIHallwaECloudCo'
Fixed table 'guikitcheecloudco_1610' for item 'GUIKitcheECloudCo'
Fixed table 'guilaundrecloudco_1538' for item 'GUILaundrECloudCo'
Fixed table 'guilivinbecloudco_1692' for item 'GUILivinBECloudCo'
Fixed table 'sftlivinglastclea_2070' for item 'SFTLivingLastClea'
Fixed table 'sftmasterlastclea_0809' for item 'SFTMasterLastClea'
openhab> openhab:jdbc schema check
Table                   Item               Issue
----------------------  -----------------  ----------------------------------------------------------------
openha
9

Thanks for sharing this, I’m glad you like those console commands. :wink:

1 Like