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


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 |    | openhab3     | Sleep   |  167 |          | NULL             |    0.000 |
| 654714 | openhab |    | openhab3     | Sleep   |   31 |          | NULL             |    0.000 |
| 654715 | openhab |    | 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?

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.