JDBC Mysql persistence in OH3

That would be appreciated. Please scan the code briefly to find other lines which maybe have to be changed accordingly. Thanks.

Sure. I found a few spots to change. If I add the change centrally in JdbcBaseDAO, I need to also change in some of the other DB-specific DAOs. Haven’t found anything else yet.

I have a version I’m testing now.

thanks for the informative and exhaustive post - I am trying to set up this persistence as well and I’ll be building on your experience.
BTW, did you observe this issue https://github.com/openhab/openhab-core/issues/1973 yourself after installing JDBC MySQL? :slight_smile:
EDIT: forget it, I have been told that after installing the persistence you have to go to settings and select a default persistence otherwise everything is disconnectd. Leaving the comment for anyone having the same issue in the future

EDIT 2: I have installed successfully and data are being pushed to mySQL. Issue is that it looks like data cannot be used any longer to populate charts (Anlyze or custom made). Do you experience the same issue or it’s just my setup?
Apprecite your patience :blush:

Hi everyone.

My error is not the same one reported above, but it’s also related to JDBC-MySQL/MariaDB in OH3.
After I updated my existing configuration from 2.5 to 3.0 a few days ago, and setup the persistence-jdbc-mariadb, since the mysql driver I was using on 2.5 stopped being supported, I started having this problem:

12/26/2020 22:51:26.204 [ERROR] [tence.internal.PersistenceManagerImpl] - Exception occurred while querying persistence service 'jdbc': class java.lang.String cannot be cast to class java.lang.Number (java.lang.String and java.lang.Number are in module java.base of loader 'bootstrap') 
java.lang.ClassCastException: class java.lang.String cannot be cast to class java.lang.Number (java.lang.String and java.lang.Number are in module java.base of loader 'bootstrap')
at org.openhab.persistence.jdbc.db.JdbcBaseDAO.getState(JdbcBaseDAO.java:469) ~[?:?]
at org.openhab.persistence.jdbc.db.JdbcBaseDAO.doGetHistItemFilterQuery(JdbcBaseDAO.java:340) ~[?:?]
at org.openhab.persistence.jdbc.internal.JdbcMapper.getHistItemFilterQuery(JdbcMapper.java:162) ~[?:?]
at org.openhab.persistence.jdbc.internal.JdbcPersistenceService.query(JdbcPersistenceService.java:202) ~[?:?]
at jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[?:?]
   	
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:834) [?:?]

It’s the same reported on this thread:

Surprisingly that thread was created back on March but there seems to be no solution.

I tried uninstalling persistence-jdbc-mariadb and installing persistence-jdbc-mysql but nothing works.

Does anybody know how to fix it?

I’m running openHABian on a Raspberry Pi 3.

@nelson.aponte Can you provide a DEBUG log? Would be nice to know which value the service tries to cast and to which item type it belongs to. Thanks.

@cweitkamp the driver seems to completely mistake the tables.

12/27/2021 11:05:00.526 [DEBUG] [.persistence.jdbc.internal.JdbcMapper] - JDBC::storeItemValue: item=K_TP_R_Current (Type=NumberItem, State=0.252 A, Label=Amperes, Category=null, Tags=[Measurement, Current], Groups=[group_Kitchen])
12/27/2021 11:05:00.531 [DEBUG] [openhab.persistence.jdbc.model.ItemVO] - JDBC:ItemVO tableName=Item23; newTableName=null;
12/27/2021 11:05:00.535 [DEBUG] [enhab.persistence.jdbc.db.JdbcBaseDAO] - JDBC::getItemType: Try to use ItemType NUMBERITEM for Item K_TP_R_Current
12/27/2021 11:05:00.539 [DEBUG] [enhab.persistence.jdbc.db.JdbcBaseDAO] - JDBC::storeItemValueProvider: item 'K_TP_R_Current' as Type 'NUMBERITEM' in 'Item23' with state '0.252 A'
12/27/2021 11:05:00.543 [DEBUG] [enhab.persistence.jdbc.db.JdbcBaseDAO] - JDBC::storeItemValueProvider: getState: '0.252 A'
12/27/2021 11:05:00.547 [DEBUG] [openhab.persistence.jdbc.model.ItemVO] - JDBC:ItemVO setValueTypes dbType=DOUBLE; javaType=class java.lang.Double;
12/27/2021 11:05:00.551 [DEBUG] [enhab.persistence.jdbc.db.JdbcBaseDAO] - JDBC::storeItemValueProvider: newVal.doubleValue: '0.252'
12/27/2021 11:05:00.555 [DEBUG] [enhab.persistence.jdbc.db.JdbcBaseDAO] - JDBC::doStoreItemValue sql=INSERT INTO Item23 (TIME, VALUE) VALUES( NOW(3), ? ) ON DUPLICATE KEY UPDATE VALUE= ? value='0.252'
12/27/2021 11:05:00.569 [DEBUG] [.jdbc.internal.JdbcPersistenceService] - JDBC: Stored item 'K_TP_R_Current' as '0.252 A' in SQL database at Sun Dec 27 11:05:00 TRT 2020 in 42 ms.
12/27/2021 11:05:00.573 [DEBUG] [.persistence.jdbc.internal.JdbcMapper] - JDBC::storeItemValue: item=K_TP_R_Voltage (Type=NumberItem, State=224.439 V, Label=Voltage, Category=null, Tags=[Measurement, Voltage], Groups=[group_Kitchen])
12/27/2021 11:05:00.577 [DEBUG] [openhab.persistence.jdbc.model.ItemVO] - JDBC:ItemVO tableName=Item24; newTableName=null;
12/27/2021 11:05:00.581 [DEBUG] [enhab.persistence.jdbc.db.JdbcBaseDAO] - JDBC::getItemType: Try to use ItemType NUMBERITEM for Item K_TP_R_Voltage
12/27/2021 11:05:00.585 [DEBUG] [enhab.persistence.jdbc.db.JdbcBaseDAO] - JDBC::storeItemValueProvider: item 'K_TP_R_Voltage' as Type 'NUMBERITEM' in 'Item24' with state '224.439 V'
12/27/2021 11:05:00.589 [DEBUG] [enhab.persistence.jdbc.db.JdbcBaseDAO] - JDBC::storeItemValueProvider: getState: '224.439 V'
12/27/2021 11:05:00.592 [DEBUG] [openhab.persistence.jdbc.model.ItemVO] - JDBC:ItemVO setValueTypes dbType=DOUBLE; javaType=class java.lang.Double;
12/27/2021 11:05:00.596 [DEBUG] [enhab.persistence.jdbc.db.JdbcBaseDAO] - JDBC::storeItemValueProvider: newVal.doubleValue: '224.439'
12/27/2021 11:05:00.600 [DEBUG] [enhab.persistence.jdbc.db.JdbcBaseDAO] - JDBC::doStoreItemValue sql=INSERT INTO Item24 (TIME, VALUE) VALUES( NOW(3), ? ) ON DUPLICATE KEY UPDATE VALUE= ? value='224.439'
12/27/2021 11:05:00.614 [DEBUG] [.jdbc.internal.JdbcPersistenceService] - JDBC: Stored item 'K_TP_R_Voltage' as '224.439 V' in SQL database at Sun Dec 27 11:05:00 TRT 2020 in 42 ms.
12/27/2021 11:05:00.619 [DEBUG] [.persistence.jdbc.internal.JdbcMapper] - JDBC::storeItemValue: item=K_TP_R_Power (Type=NumberItem, State=56.406 W, Label=Watts, Category=null, Tags=[Measurement, Power], Groups=[group_Kitchen])
12/27/2021 11:05:00.624 [DEBUG] [openhab.persistence.jdbc.model.ItemVO] - JDBC:ItemVO tableName=Item25; newTableName=null;
12/27/2021 11:05:00.628 [DEBUG] [enhab.persistence.jdbc.db.JdbcBaseDAO] - JDBC::getItemType: Try to use ItemType NUMBERITEM for Item K_TP_R_Power
12/27/2021 11:05:00.632 [DEBUG] [enhab.persistence.jdbc.db.JdbcBaseDAO] - JDBC::storeItemValueProvider: item 'K_TP_R_Power' as Type 'NUMBERITEM' in 'Item25' with state '56.406 W'
12/27/2021 11:05:00.641 [DEBUG] [enhab.persistence.jdbc.db.JdbcBaseDAO] - JDBC::storeItemValueProvider: getState: '56.406 W'
12/27/2021 11:05:00.644 [DEBUG] [openhab.persistence.jdbc.model.ItemVO] - JDBC:ItemVO setValueTypes dbType=DOUBLE; javaType=class java.lang.Double;
12/27/2021 11:05:00.649 [DEBUG] [enhab.persistence.jdbc.db.JdbcBaseDAO] - JDBC::storeItemValueProvider: newVal.doubleValue: '56.406'
12/27/2021 11:05:00.653 [DEBUG] [enhab.persistence.jdbc.db.JdbcBaseDAO] - JDBC::doStoreItemValue sql=INSERT INTO Item25 (TIME, VALUE) VALUES( NOW(3), ? ) ON DUPLICATE KEY UPDATE VALUE= ? value='56.406'
12/27/2021 11:05:00.666 [DEBUG] [.jdbc.internal.JdbcPersistenceService] - JDBC: Stored item 'K_TP_R_Power' as '56.406 W' in SQL database at Sun Dec 27 11:05:00 TRT 2020 in 46 ms.

If you look at the first line, it’s trying to store item K_TP_R_Current, and according to line 2, the table for that item is Item23. However, the correct table is Item2. This is the mapping of tables from the Items table:

MariaDB [lake_home]> SELECT * FROM Items;
+--------+-----------------------------------------------------------+
| ItemId | ItemName                                                  |
+--------+-----------------------------------------------------------+
|      1 | K_TP_R_Power                                              |
|      2 | K_TP_R_Current                                            |
|      3 | K_TP_R_Voltage                                            |
|      4 | K_TP_H_Switch                                             |
|      5 | DR_Light_Switch                                           |
|      6 | DR_Light_Color                                            |
|      7 | Weather_OWM_Rain                                          |
|      8 | Weather_OWM_Snow                                          |
|      9 | Weather_OWM_Temperature                                   |
|     10 | LR_Light_Color                                            |
|     11 | LR_Light_Switch                                           |
|     12 | Day_White_Lights_Switch                                   |
|     13 | MBR_Light_Switch                                          |
|     14 | MBR_Light_Color                                           |
|     15 | Night_Yellow_Lights_Switch                                |
|     16 | systeminfo_computer_openHABianPi_storage_availablePercent |
|     17 | systeminfo_computer_openHABianPi_memory_availablePercent  |
|     18 | systeminfo_computer_openHABianPi_cpu_load                 |
|     19 | Weather_OWM_Humidity                                      |
|     20 | Wake_up_Eylem_Switch                                      |
|     21 | Wake_up_Routine_Switch                                    |
|     22 | Override_Sleep_Mode                                       |
|     23 | Override_Evening_Routines                                 |
|     24 | MBR_Sensor_Motion_LastUpdate                              |
|     25 | Override_Home_Away                                        |
|     26 | Override_Vacation_Mode                                    |
|     27 | Override_MBR_Sensor_Motion                                |
|     28 | systeminfo_computer_openHABianPi_cpu_load15               |
|     29 | systeminfo_computer_openHABianPi_network_DataSent         |
|     30 | systeminfo_computer_openHABianPi_network_DataReceived     |
|     31 | systeminfo_computer_openHABianPi_sensor_CPUtemp           |
|     32 | Override_MBR_Sensor_Illuminance_LowThreshold              |
|     33 | Override_MBR_Sensor_Illuminance                           |
|     34 | MBR_Temperature_Threshold_Min                             |
|     35 | MBR_Temperature_Threshold_Max                             |
|     36 | Override_Baby_Mode                                        |
|     37 | Override_DR_Automated_Lights                              |
|     38 | Override_MBR_Automated_Lights                             |
|     39 | Override_LR_Automated_Lights                              |
|     40 | Override_Automated_Heating                                |
|     41 | NBR_Light_Switch                                          |
|     42 | Override_NBR_Automated_Lights                             |
|     43 | Override_NBR_Sensor_Motion                                |
|     44 | Override_NBR_Sensor_Illuminance_LowThreshold              |
|     45 | Override_NBR_Sensor_Illuminance                           |
|     46 | K_TP_Heater_Threshold_Min                                 |
|     47 | K_TP_Heater_Threshold_Active                              |
|     48 | NBR_Light_Color                                           |
|     49 | HW2_Light_Switch                                          |
|     50 | Override_HW2_Automated_Lights                             |
|     51 | NBR_Temperature_Threshold_Min                             |
|     52 | NBR_Temperature_Threshold_Max                             |
|     53 | LR2_Light_Switch                                          |
|     54 | LR2_Light_Color                                           |
+--------+-----------------------------------------------------------+
54 rows in set (0.00 sec)

Next it tries to get K_TP_R_Voltage from Item24, but the correct table is Item3.

This is my jdbc.cfg:

############################ JDBC Persistence Service ##################################
# I N S T A L L J D B C P E R S I S T E N C E S E R V I C E
#
# https://github.com/openhab/openhab/wiki/JDBC-Persistence
#
# Tested databases/url-prefix: jdbc:derby, jdbc:h2, jdbc:hsqldb, jdbc:mariadb, jdbc:mysql, jdbc:postgresql, jdbc:sqlite
#
# derby, h2, hsqldb, sqlite can be embedded,
# If no database is available it will be created, for example the url 'jdbc:h2:./testH2' creates a new DB in OpenHab Folder.
#
# Create new database, for example on a MySQL-Server use:
# CREATE DATABASE 'yourDB' CHARACTER SET utf8 COLLATE utf8_general_ci;

# D A T A B A S E C O N F I G
# Some URL-Examples, 'service' identifies and activates internally the correct jdbc driver.
# required database url like 'jdbc:<service>:<host>[:<port>;<attributes>]'
# url=jdbc:derby:./testDerby;create=true
# url=jdbc:h2:./testH2
# url=jdbc:hsqldb:./testHsqlDb
url=jdbc:mariadb://localhost/lake_home
# url=jdbc:mysql://localhost/lake_home
# url=jdbc:postgresql://192.168.0.1:5432/testPostgresql
# url=jdbc:sqlite:./testSqlite.db
# url=

# required database user
user=XXXXXXX

# required database password
password=XXXXXXXXX

# E R R O R   H A N D L I N G
# optional when Service is deactivated (optional, default: 0 -> ignore)
#errReconnectThreshold=

# I T E M   O P E R A T I O N S
# optional tweaking SQL datatypes
# see: https://mybatis.github.io/mybatis-3/apidocs/reference/org/apache/ibatis/type/JdbcType.html
# see: http://www.h2database.com/html/datatypes.html
# see: http://www.postgresql.org/docs/9.3/static/datatype.html
# defaults:
#sqltype.CALL          =   VARCHAR(200)
#sqltype.COLOR         =   VARCHAR(70)
#sqltype.CONTACT       =   VARCHAR(6)
#sqltype.DATETIME      =   DATETIME
#sqltype.DIMMER        =   TINYINT
#sqltype.LOCATION      =   VARCHAR(30)
#sqltype.NUMBER        =   DOUBLE
#sqltype.ROLLERSHUTTER =   TINYINT
#sqltype.STRING        =   VARCHAR(65500)
#sqltype.SWITCH        =   VARCHAR(6)

# For Itemtype "Number" default decimal digit count (optional, default: 3)
#numberDecimalcount=

# T A B L E   O P E R A T I O N S
# Tablename Prefix String (optional, default: "item")
# for Migration from MYSQL-Bundle set to 'Item'.
tableNamePrefix=Item
# Tablename Prefix generation, using Item real names or "item" (optional, default: false -> "item")

# If true, 'tableNamePrefix' is ignored.
tableUseRealItemNames=false

# Tablename Suffix length (optional, default: 4 -> 0001-9999)
# for Migration from MYSQL-Bundle set to 0.
tableIdDigitCount=0

# Rename existing Tables using tableUseRealItemNames and tableIdDigitCount (optional, default: false)
# USE WITH CARE! Deactivate after Renaming is done!
#rebuildTableNames=true

# D A T A B A S E C O N N E C T I O N S
# Some embeded Databases can handle only one Connection (optional, default: configured per database in packet org.openhab.persistence.jdbc.db.* )
# see: https://github.com/brettwooldridge/HikariCP/issues/256
# jdbc.maximumPoolSize = 1
# jdbc.minimumIdle = 1

# T I M E K E E P I N G
# (optional, default: false)
#enableLogTime=true

Should I fill an issue for this?

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?

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.

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.

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.

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).

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?

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?

Happy New Year guys.

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

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

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 :slight_smile:
Can I help by adding this to documentation somewhere?

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

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.

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