JDBC Mysql persistence in OH3

cweitkamp · December 29, 2020, 7:35pm

Sry, I was completely stumped. Now I got it. You migrated from old MYSQL persistence bundle to JDBC/MariaDB persistence addon. Is that correct? And I have to admit that I have no clue how that is working except from what I can see from the config file.

One thing I notices is that your table is called “Items” (notice the capital “I” at the beginning) where as the JDBC binding tries to read the item table matching from “items” (notice the lower “i” at the beginning). This value/name is hard-coded and cannot be changed like prefix for the table name for the items themselves. Can you try to rename your table?

apella12 · December 29, 2020, 10:54pm

I’m just driving by this post & a novice, but a long time ago I downloaded the mariaDB because the mysql was no longer available (as I recall) and had problems. The trick turned out to be not use jdbc:mariadb:, but stick with jdbc:mysql as you had before.

More recently on the upgrade from 2.5 to 3.0, the items in my tables were renumbered, so you might want to check that also. I use the mysql workbench. Edit for clarity Item1 became item0001. Could be the capitalization mentioned above

Hope that helps.

nelson.aponte · December 30, 2020, 7:36pm

That’s right. I wasn’t using the JDBC persistence add-on on OH 2.5, I was using the standard MySQL one, and I was forced to use JDBC on OH 3.0.

I never meant to install MariaDB, I don’t remember if I used the official MySQL repository or some default repository on OpenHABian, but somewhere along OH 2.x I realized that I didn’t have exactly MySQL installed, but MariaDB. Nonetheless, I decided to stay with the MySQL persistence add-on, instead of implementing JDBC-MariaDB.

On OH 3.0 I tried both but JDBC-MySQL couldn’t connect to the DB, so I had to stick to JDBC-MariaDB.

This is my list of tables;

MariaDB [lake_home]> SHOW TABLES;
    +---------------------+
    | Tables_in_lake_home |
    +---------------------+
    | Item1               |
    | Item10              |
    | Item11              |
    | Item12              |
    | Item13              |
    | Item14              |
    | Item15              |
    | Item16              |
    | Item17              |
    | Item18              |
    | Item19              |
    | Item2               |
    | Item20              |
    | Item21              |
    | Item22              |
    | Item23              |
    | Item24              |
    | Item25              |
    | Item26              |
    | Item27              |
    | Item28              |
    | Item29              |
    | Item3               |
    | Item30              |
    | Item31              |
    | Item32              |
    | Item33              |
    | Item34              |
    | Item35              |
    | Item36              |
    | Item37              |
    | Item38              |
    | Item39              |
    | Item4               |
    | Item40              |
    | Item41              |
    | Item42              |
    | Item43              |
    | Item44              |
    | Item45              |
    | Item46              |
    | Item47              |
    | Item48              |
    | Item49              |
    | Item5               |
    | Item50              |
    | Item51              |
    | Item52              |
    | Item53              |
    | Item54              |
    | Item6               |
    | Item7               |
    | Item8               |
    | Item9               |
    | Items               |
    | items               |
    +---------------------+

Apparently JDBC created a table called “items”.

Where did you see that? I still can’t see it in any of the things that I shared.

I will try to purge all the trash JDBC created, change the name of the items table for Items and see if it works. I’ll keep you posted.

BeanzBE · December 31, 2020, 9:07am

This thread explained a lot of things for me, so thanks.
I’m running OH3 next to OH2.5 where I wanted to check if connection is still working.
OH2.5 is read/write, OH3 only read.
But I see that new tables are created (without capital i) and populated.
Are all items now persisted? I must have missed that part of information.

cweitkamp · December 31, 2020, 1:21pm

That explains why your item / table mapping is broken. When you compare the datasets in both tables (“Items” and “items”) you probably will see all your items but with different table ids.

In the code for the JDBC persistence service. The name is hard-nosed and cannot be changed by the user.

@BeanzBE Yes, OH3 ships a default persistence for all items (RRD4J).

nelson.aponte · January 1, 2021, 1:18pm

Happy New Year guys.

Yes, all the items had a completely different order in the “items” table, so I gave it a different name and then renamed the “Items” table to “items” and it works now. I can even see graphs of the data from OH 2.5 on the MainUI.
Of course, I did the renaming after deleting all the trash data JDBC could persist in the wrong tables.

All Items are being correctly persisted now.

Nontheless, this seems like a bug to me. Typo? Maybe users should be able to define the name of the table in the jdbc.config file. What do you think @cweitkamp?

cweitkamp · January 1, 2021, 6:23pm

I think that might be a good idea. At least for user like you who have to switch from MySQL to JDBC persistence. Let’s file an issue on GitHub to not forget it.

Beside that it would be very nice to provide a short summary for others to wrap up how the migration works step by step. May I ask you to write a short how-to?

haybolat · January 2, 2021, 7:21am

Happy New Year guys.

I don’t want some items to be saved in mysql. how can I do that?

cweitkamp · January 2, 2021, 5:36pm

Define your own persistence strategies and exclude whatever item you do not want to persist. JDBC by default persists all values in change.

BeanzBE · January 2, 2021, 5:54pm

Meaning that if there is no .persist file, everything is saved.
When the file is present, only that is applied?
If so, good to know
Can I help by adding this to documentation somewhere?

haybolat · January 2, 2021, 6:03pm

I have a file called mysql.persist but the others are kept. Mysql is also stored for those not on this list.

cweitkamp · January 2, 2021, 6:09pm

Let’s specify this a little bit. If there is no textual configuration file for a persistence service it delivers its own default strategy. If you do not like it you have to define you own strategies in separate files - one file for each persistence service installed.

haybolat · January 2, 2021, 6:12pm

I apologize, I’m a little inexperienced in this matter. I will be glad if you explain in detail.

cweitkamp · January 2, 2021, 6:14pm

@haybolat Before we try to dig deeper please tell us which OH version you are using and which persistence service. MySQL or JDBC? If your are using JDBC than you .persist file has a wrong name.

haybolat · January 2, 2021, 6:17pm

I hope I answered your question.

cweitkamp · January 2, 2021, 6:18pm

Yes, everything. Thanks.

… and rename persistence/mysql.persist to persistence/jdbc.persist …

Can you give it a try?

haybolat · January 2, 2021, 6:23pm

Yes, thank you very much.

cweitkamp · January 2, 2021, 6:29pm

You’re welcome. Please report back if it is working.

haybolat · January 2, 2021, 6:31pm

Yes, as before, the items I want are working when I want them. I am grateful to you.

frieso · January 3, 2021, 6:37pm

Hello @nelson.aponte

I’m having the same issue as you: migrated from OH 2.5 to OH3.0 and using mariaDB. I get lots of:

Exception occurred while querying persistence service ‘jdbc’: class java.lang.Double cannot be cast to class java.lang.String (java.lang.Double and java.lang.String are in module java.base of loader ‘bootstrap’)
java.lang.ClassCastException: class java.lang.Double cannot be cast to class java.lang.String (java.lang.Double and java.lang.String are in module java.base of loader ‘bootstrap’)

for every item persisted in mariaDB. Where you able to solve your issue?
KR