JDBC Mysql Persistance ( java.lang.ClassCastException: class java.lang.Integer cannot be cast to class java.lang.Long )

spectroman · August 8, 2021, 5:37pm

Hi,

I’ve had read several threads about this, and nothing seems to apply here.

I had some cfg from v2 which now im using on a new install for v3.1.

I see the openhab3 reaching and connecting in the mysql, but he isn’t creating tables.

| 654713 | openhab | 192.168.1.247:40174    | openhab3     | Sleep   |  167 |          | NULL             |    0.000 |
| 654714 | openhab | 192.168.1.247:40176    | openhab3     | Sleep   |   31 |          | NULL             |    0.000 |
| 654715 | openhab | 192.168.1.247:40178    | openhab3     | Sleep   |  167 |          | NULL             |    0.000 |

I see lots of errors like:

2021-08-08 19:22:16.129 [WARN ] [.zaxxer.hikari.util.DriverDataSource] - Registered driver with driverClassName=com.mysql.jdbc.Driver was not found, trying d
irect instantiation.

Which as I understood in other threads its most likely not something that will stop him, but then later, there is:

2021-08-08 19:22:18.537 [ERROR] [nal.common.AbstractInvocationHandler] - An error occurred while calling method 'QueryablePersistenceService.query()' on 'org.openhab.persistence.jdbc.internal.JdbcPersistenceService@69c50678': class java.lang.Integer cannot be cast to class java.lang.Long (java.lang.Integer and java.lang.Long are in module java.base of loader 'bootstrap')
java.lang.ClassCastException: class java.lang.Integer cannot be cast to class java.lang.Long (java.lang.Integer and java.lang.Long are in module java.base of loader 'bootstrap')
        at org.openhab.persistence.jdbc.db.JdbcMysqlDAO.doPingDB(JdbcMysqlDAO.java:95) ~[?:?]
        at org.openhab.persistence.jdbc.internal.JdbcMapper.pingDB(JdbcMapper.java:72) ~[?:?]
        at org.openhab.persistence.jdbc.internal.JdbcMapper.checkDBAccessability(JdbcMapper.java:211) ~[?:?]
        at org.openhab.persistence.jdbc.internal.JdbcPersistenceService.query(JdbcPersistenceService.java:163) ~[?:?]
        at jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[?:?]
        at jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) ~[?:?]
        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) [?:?]

this one seems to be repeating everytime he tries to write on the database.

I already created the DB with UTF8 using:

create database openhab3 character set utf8 collate utf8_bin;

but he won’t create any tables.

anyone have seen that?

spectroman · September 1, 2021, 9:58am

If you got here I got bad news for you. (So far) I could not make ANY of the JDBC drivers to work. They are all pretty much broken so absolutely no persistence with anything based on this driver, on my new OH3 install.

I tried MySQL, MariaDB, hsqldb, h2 and all of them fail for one reason or another.

When using Mysql / Mariadb variants, I can see he is able to reach the server, keep connected but he is unable to create tables and keep giving useless errors which doesn’t help to fix the problem…

hsqldb and h2, I can see he create some stuff but still gives errors on drivers , says that DBs are not present, all the useless information that will prevent you to find a solution.

I ended up using a MongoDB driver which worked well, and its not a JDBC driver, then it worked on the first attempt.

Apparently there is a lack of support for the JDBC thingie, as I can see several other posts with problems around that and mostly people gets roasted for apparently no reason around this, the driver is bad and the migration is also quite awful for that aspect.

So Im just giving a nudge, if you stumbled in an error like that, better not waste much time, because doesn’t seem to be easily solvable… I tried a lot of stuff to no avail.

Although I could have overlooked something, … if you think it so, post some other solution here, I would also be happy to test.

MaxHeadroom · August 25, 2022, 9:26pm

I had the same issue with cast errors, but now its working under OH3.3/MariaDB.
The problem with my configuration was a mix of file based (jdbc.cfg) and UI based entries (e.g. JDBC persistence service page).
After probably playing too much, OH took a mixture of those entries for real, which led to the cast error.
From a distance it felt like a UI-based parameter was edited, then I switched to file-based config, but not changing the UI paraemter. The UI parameters were still taken! Seems logical from a programming perspective, but a trapdoor for me

Solution

was to delete the openhab/userdata/config folder after shutting down. Of course, basic UI parameters like region, timezone, etc. needed to be edit one more time then
only edit UI page “settings/persistence” default = JDBC
furthermore use file based configuration
My jdbc.cfg looks like this now

url=jdbc:mariadb://192.168.x.xx:3306/openhab?serverTimezone=Europe/Berlin
user=OH
password=OH
sqltype.STRING = VARCHAR(2000)

addons.cfg

...
persistence = jdbc-mariadb
...

Could be an idea to “lock” and ignore the UI page, if a file overrules. Similar to things/channels configuration.

Another learning is about the jdbc.persist file and yes, it´s written in the docs

single item names can be persisted
group status (no members!)
all group members (but given the whole name with a star at the end). No group wild cards!
Say, if one wants to persist some similar written groups like (gRoom1, gRoom2, gRoom3, etc.) including members … gRoom* won´t work. Use a list of these groups instead “gRoom1*, gRoom2*, gRoom3*”

Having done this, 102 items are persisted within 15min for testing… not a single error popped up.